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

#统计各活跃等级的用户占比
#1.用户 2.活跃信息
#步骤1.最大登出时间是最新的活跃时间、最小的登录时间是最早的登录时间、对最大登出时间按照uid进行分组再次进行开窗聚合的话可以得到所有日期里最玩的时间也就是今天
#步骤2.DATEDIFF(now_dt,new_dt)<=6 今天和最早活跃时间的日期差小于一周的为新晋用户
#   DATEDIFF(now_dt,max_dt)<=6 AND DATEDIFF(now_dt,new_dt)>6 今天和最晚登出时间小于一周 并且不符合新进用户为忠实用户
#   WHEN DATEDIFF(now_dt,max_dt) BETWEEN 7 AND 29 登录时间距离今天在7到29，也就是    
WITH t1 AS(
    	SELECT
         uid, 
         MAX(DATE(out_time)) max_dt,
         MIN(DATE(in_time)) new_dt,
         MAX(MAX(DATE(out_time)))OVER() now_dt
	FROM tb_user_log
     GROUP BY uid),
t2 as(
SELECT 
		CASE WHEN DATEDIFF(now_dt,new_dt)<=6 THEN '新晋用户'  #近7天新增
		      WHEN DATEDIFF(now_dt,max_dt)<=6 AND DATEDIFF(now_dt,new_dt)>6 THEN'忠实用户' #近7天活跃过且非新晋用户
              WHEN DATEDIFF(now_dt,max_dt) BETWEEN 7 AND 29 THEN '沉睡用户'
		      ELSE '流失用户' END user_grade
FROM  t1
)
# 根据user_grade进行分组，然后总的通过sum(count(user_grade)) over() 开窗进行聚合，因为如果直接聚合的话
-- t1表  
SELECT user_grade,ROUND(COUNT(user_grade)/SUM(COUNT(user_grade))OVER(),2)ratio
FROM t2 GROUP BY user_grade ORDER BY ratio DESC;

1.问题拆解：最大登出时间是最新的活跃时间、最小的登录时间是最早的登录时间、对最大登出时间按照uid进行分组再次进行开窗聚合的话可以得到所有日期里最玩的时间也就是今天

2.主要涉及到窗口函数的使用：sum(count(1)) over() 相当于对所有列进行大的聚合，因为聚合函数之间不能嵌套使用，窗口函数的优先级有低于聚合函数，所有通过窗口函数对聚合函数进行聚合就可以执行，over里面不写partition by相当于对所有列进行聚合，写partition by相当于对分区列进行聚合操作。

沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。沉睡用户和流失用户没有理解好