题解 | #统计活跃间隔对用户分级结果#

1. 学习新窗口函数使用方法 在使用group by 时如果要获取全局的total 数量 可以用 total_cnt / sum(total_cnt) over () 则可以计算各部门之和与全公司之和的比值
2. 学习四种互斥方法，用到4个参数需要统计 （7日内使用量，7-30日内使用量， 7+日使用量与 30日+使用量统计 ），之后逐个根据条件进行Case When
3. Order by时要注意要order by id

select user_grade,  round(total_cnt / sum(total_cnt) over (),2) as ratio
from 
(select user_grade,  count(user_grade) as total_cnt
from level_table
group by user_grade

with lastLogin as  (
select max(in_time) as last_time
from tb_user_log
), user_log as (
    select uid,date_format(in_time,'%Y-%m-%d') as dt,datediff((select last_time from lastLogin),date_format(in_time,'%Y-%m-%d') ) as diff
    from tb_user_log
    order by uid
),recent_activity as (
    select uid,sum(case when diff <=6 then 1 else 0 end) as 7days_cnt,sum(case when  diff >= 7 and  diff < 30 then 1 else 0 end)  as 30days_cnt,sum(case when diff >= 7 then 1 else 0 end)  as infinitedays_cnt,sum(case when diff >= 30 then 1 else 0 end)  as 2ndinfinitedays_cnt
    from  user_log
    group by uid
),tmp_table as (
select ul.uid,sum(ra.7days_cnt) as 7d,sum(ra.30days_cnt) as 30d,sum(ra.infinitedays_cnt) as dd,sum(ra.2ndinfinitedays_cnt) as 2dd
from user_log ul join recent_activity ra on ul.uid = ra.uid
group by ul.uid),


level_table as (

SELECT 
    uid,
    CASE 
        WHEN `7d` > 0 AND `dd` = 0 THEN '新晋用户'
        WHEN `7d` > 0 AND `dd` > 0 THEN '忠实用户'
        WHEN `7d` = 0 AND `dd` > 0 AND `2dd` = 0 THEN '沉睡用户'
        WHEN `7d` = 0 AND `30d` = 0 AND `2dd` > 0 THEN '流失用户'
    END AS user_grade
FROM 
    tmp_table)

select user_grade,  round(total_cnt / sum(total_cnt) over (),2) as ratio
from 
(select user_grade,  count(user_grade) as total_cnt
from level_table
group by user_grade) tt
order by  round(total_cnt / sum(total_cnt) over (),2) desc,user_grade



# group by user_grade,uid