with merge_user_tb as ( SELECT DISTINCT uid, FIRST_VALUE(dt) over(PARTITION by uid ORDER BY dt) as start_day, FIRST_VALUE(dt) over(PARTITION by uid ORDER BY dt desc) as end_day, max(dt) over() as today FROM( SELECT DATE_FORMAT(in_time,'%Y-%m-%d') dt,uid FROM tb_user_log UNION SELECT DATE_FORMAT(out_time,'%Y-%m-%d') dt,uid FROM tb_user_log )t_1 ), caculate_user_tb as ( SELECT *,case when DATEDIFF(today,start_day) >= 7 and DATEDIFF(today,end_day) < 7 then '忠实用户' when DATEDIFF(today,start_day) < 7 and DATEDIFF(today,end_day) < 7 then '新晋用户' when DATEDIFF(today,start_day) >= 7 and DATEDIFF(today,end_day) >= 7 and DATEDIFF(today,end_day) < 30 then '沉睡用户' when DATEDIFF(today,start_day) >= 30 and DATEDIFF(today,start_day)>=30 then '流失用户' else null end as user_grade, DATEDIFF(today,start_day),DATEDIFF(today,end_day) FROM merge_user_tb) SELECT user_grade,round(count(*)/(select count(*) FROM caculate_user_tb),2) as ratio FROM caculate_user_tb GROUP BY user_grade ORDER BY ratio desc,user_grade;
临界值不好把控