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

1.找到每个用户最早登录时间min（in_time）和最晚使用时间max（date（out_time））作为子表a 2.找到max（count（distinct uid））总的在线人数；max（date（out_time））当天时间为子表格b a和b要分开是因为a需要按照uid分组，这样才能匹配到个人 3.碰到a和b这种需要链接但不知道怎么链接的情况可以用 on 1来连接，表示全连接 4.利用timestampdiff(day, first_date, cur_dt) first_date_diff,

•   timestampdiff(day, last_date, cur_dt) last_date_diff,找到最早登录日期和最晚登录日期的时间差
  

5. case when

• first_date_diff < 7 then "新晋用户"
• when last_date_diff >= 30 then "流失用户"

•   when last_date_diff >=7 then "沉睡用户"
  

• else "忠实用户" end as user_grade, 利用时间差来对顾客进行分层（注意：>=30要写在>=7 前面，即范围大的要写在范围小的前面，否则会出现后者把前者的内容吞并的情况） 6.因为a子查询利用uid对数据进行分组了，故可以用count(uid)/ max(uid_cnt)计算得到比率
• select user_grade,
• round(count(uid)/ max(uid_cnt),2) ratio
• from (select
• case when
• first_date_diff < 7 then "新晋用户"
• when last_date_diff >= 30 then "流失用户"

•   when last_date_diff >=7 then "沉睡用户"
  

• else "忠实用户" end as user_grade,
• uid,
• uid_cnt

•   from(select
  

•   timestampdiff(day, first_date, cur_dt) first_date_diff,
  

•   timestampdiff(day, last_date, cur_dt) last_date_diff,
  

•        uid,
  

•        uid_cnt
  

•        from 
  

• (select
• min(date_format(in_time,'%Y-%m-%d')) first_date,
• max(date_format(out_time,'%Y-%m-%d')) last_date,
• uid
• from tb_user_log
• group by uid) a
• join ( select count(distinct uid) uid_cnt,

•   max(date_format(out_time,'%Y-%m-%d')) cur_dt
  

•   from tb_user_log) b on 1
  

• ) c
• )d
• group by user_grade
• order by ratio desc;