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

-- 1、第一部分
with t as
(
    SELECT uid,
    min(in_time) firstt,     -- 用户最初活跃时间，以此判断是否是新用户
    max(in_time) lastt,     -- 用户最新活跃时间
    (select max(date(out_time)) from tb_user_log) maxd,     -- 所有用户的最新活跃日（今天）
    (select date_sub(max(date(out_time)),interval 6 day) from tb_user_log) near7,   -- 7天前的日子
    (select date_sub(max(date(out_time)),interval 29 day) from tb_user_log) near30  -- 30天前的日子
    FROM tb_user_log 
    group by uid
)

-- 3、第三部分
select user_grade,round(count(1)/avg(user_num),2) ratio
from
(
    -- 2、第二部分
    select uid,count(1)over() user_num,
    case
        -- 若用户最新活跃时间（日）在7天内 且 最初活跃时间!=最终活跃时间，也就是说曾经活动过。则为忠实用户
        when (date(lastt) between near7 and maxd) and (firstt<>lastt) then '忠实用户'   
        -- 若用户最新活跃时间（日）在7天内 且 最初活跃时间=最终活跃时间，最近7天只活动一次。则为新晋用户
        when (date(lastt) between near7 and maxd) and (firstt=lastt) then '新晋用户'
        -- 只要最新活跃日是7日-30日之间，则为沉睡用户
        when (date(lastt) between near30 and near7) then '沉睡用户'
        -- 最新活跃日是30日之前，则为流失用户
        else '流失用户'
    end user_grade
    from t
) t2
group by user_grade
order by ratio desc

一、代码解释

1、with t as () 部分

查询：用户最初活跃时间、用户最新活跃时间、全部用户最新活跃日期、其前7、30日对应日期

结果如下

注意：

i) 所有用户最新活跃日使用date(out_time)，数据类型是（年月日）

ii)某用户的最新、最初活跃时间采用的是in_time，数据类型是（年月日+时分秒）。如若在最近7日之内，当两者相等的时候，显然为新用户（新晋）；否则为老用户（忠实）

2、第二部分

经过第一部分得到临时表t，即可根据相关条件来判别用户类型。结果如下：

忠实用户--->

①最近活跃日期（年月日）在7日之内：date(lastt) between near7 and maxd

②不能为7日之内的唯一一次活跃：firstt<>lastt（反过来想，唯一一次活跃那么最初和最近的start_time相等）

新晋用户--->

①最近活跃日期（年月日）在7日之内：date(lastt) between near7 and maxd

②7日之内的唯一一次活跃：firstt=lastt

沉睡用户--->

①最近活跃日期（年月日）在7日-30日之内：date(lastt) between near30 and near7

流失用户--->

①最近活跃日期（年月日）在30日之前：else (i.e. date(lastt) < near30 )

注意：使用开窗函数计算出用户数：方便进行求比例的计算

2、第三部分

第二部分结果上进行分组函数求出结果，排序即可。

注意：round(count(1)/avg(user_num),2) （分母：利用第二部分的开窗函数之便）