游戏玩家消费数据RFM分析报告

select avg(R) as avg_R
from(
select *,row_number() over (partition by R)  as rank_R
from(
select uid,datediff('2024-07-01',max(usr_datetime)) as R
from detail_usr_comsume_list
where consume>0
group by uid
        )t
)tt
where rank_R between 600 and 1800;

with tb1 as(
select uid,count(usr_datetime) cum_cnt
from detail_usr_comsume_list
where consume>0
group by uid
),
tb2 as(
select uid
from(
select uid,cnt_rank
from (
    select uid, row_number() over (order by cum_cnt) as cnt_rank
    from tb1
)t
where cnt_rank between 600 and 1800
)tt
)
select avg(cum_cnt) as avg_F
from tb1
where uid in (select uid from tb2);

with tb1 as(
select uid,sum(consume) cum_sum
from detail_usr_comsume_list
where consume>0
group by uid
),
tb2 as(
select uid
from(
select uid,cum_rank
from (
    select uid, row_number() over (order by cum_sum) as cum_rank
    from tb1
)t
where cum_rank between 600 and 1800
)tt
)
select avg(cum_sum) as avg_M
from tb1
where uid in (select uid from tb2);

create table pay_usr_tag as
with tb as (
 select uid ,
       datediff('2024-07-01',max(usr_datetime))  as R,
       count(usr_datetime) as F,
       sum(consume) as M
from detail_usr_comsume_list
where consume>0
group by uid
)
select *,case
when R < 16  and F >2 and  M>=682 then '重要价值用户'
when R < 16  and F <=2 and M>=682 then '重要发展用户'
when R >=16  and F >2 and  M>=682 then '重要保持用户'
when R >=16  and F <=2 and M>=682 then '重要挽留用户'
when R < 16  and F >2 and  M<682 then '一般价值用户'
when R < 16  and F <=2 and M<682 then '一般发展用户'
when R >=16  and F >2 and  M<682 then '一般保持用户'
when R >=16  and F <=2 and M<682 then '一般挽留用户'end as usr_tag
from tb;

create table usr_tag_info as
select usr_tag,count(*) as cnt,sum(M) as sum_amount
from pay_usr_tag
group by usr_tag;

create table point_usr_info as
with tb1 as
(
select uid
from pay_usr_tag
where usr_tag='重要价值用户'
)
select parts,
       sum(if(gender='男',1,0)) as male,sum(if(gender='女',1,0)) as female,
       count(city) city_cnt,
       round(avg(age),1) as avg_age,
       round(avg(levels),1) as avg_level,
       round(avg(usr_amount),1) as avg_amount,
       round(sum(usr_amount),1 )as sum_amount
from usr_info_list_PT lateral view explode(split(forums," ")) fm as parts
where usr_info_list_PT.uid in (select tb1.uid from tb1)
group by parts;