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

select user_grade, round(count(uid)/max(user_cnt),2) ratio
from(
select uid,user_cnt,
case when ld>=date_sub(cur_dt,interval 6 day) and fd<date_sub(cur_dt,interval 6 day) then '忠实用户'
when fd>=date_sub(cur_dt,interval 6 day) then '新晋用户'
when ld<date_sub(cur_dt,interval 6 day) and ld>=date_sub(cur_dt,interval 29 day) then '沉睡用户'
else '流失用户'
end user_grade
from 
(select uid,min(date(in_time)) fd,max(date(out_time)) ld from tb_user_log
group by uid) as t1,
(select max(date(out_time)) cur_dt,count(distinct uid) user_cnt from tb_user_log
) as t2
) as t3 
group by user_grade
order by ratio desc
;

做了好久。。。

根据题意，一开始的思路就判断出以下几个所需数据：

（1）表1：（用户ID，最早登陆时间，最晚活跃时间）

1.用户最早登陆时间：根据用户分组后，min(date(in_time)) fd (first_date)---用于判断是否为7天内的新增用户

2.用户最后一次活跃时间：根据用户分组后，max(date(out_time)) ld (last_date)--用于判断用户最后一次活跃时间在哪个时间段

（2）表2：（今天的日期，总用户数）

1.今天就是数据中所有日期的最大值：所有时间的最大值，max(date(out_time)) cur_date---基准时间（今天T）

2.总用户数：count（distinct UID）user_cnt--总人数

(3)用户类别判断方式：

1.忠实用户(近7天活跃过且非新晋用户)：

近7天活跃：fd>=T-6

非新晋用户：ld<T-6

2.新晋用户(近7天新增)：ld>=T-6

3.沉睡用户(近7天未活跃但更早前活跃过)：

近7天未活跃：fd<T-6

在更早前活跃过：即在近30天内活跃过：fd>=T-29

4.流失用户(近30天未活跃但更早前活跃过)：fd<T-29

具体语法：使用case when

这里的问题是我在没有相同字段名的两个表怎么连接上懵掉了，我忘了除了表连接，可以直接用

select 字段名 from t1,t2 的方式直接选出所需数据。。

也看了下评论区大佬的方式，先计算出时间差（回头自己试下）