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

--SQL SERVER

with tb as
(
  select uid
  ,MIN(CONVERT(nvarchar(10),in_time,120)) as fistTime
  ,max(CONVERT(nvarchar(10),out_time,120)) as lastTime
  ,(select max(CONVERT(nvarchar(10),out_time,120)) from tb_user_log) as today
  from tb_user_log
  group by uid
)


select t1.user_grade,round(COUNT(t1.uid)*1.0/(SELECT COUNT(1) FROM tb),2) as rate
from
(
select uid
,case when DATEDIFF(DAY,fistTime,today) <= 6 then '新晋用户'
when DATEDIFF(DAY,lastTime,today) <= 6 and  DATEDIFF(DAY,fistTime,today) > 6 then '忠实用户'
when DATEDIFF(DAY,lastTime,today) > 29 and  DATEDIFF(DAY,fistTime,today) > 6 then '流失用户'
when DATEDIFF(DAY,lastTime,today) > 6 and  DATEDIFF(DAY,fistTime,today) > 6 then '沉睡用户'
else '其他'
end as user_grade
from tb
) t1
group by t1.user_grade
order by round(COUNT(t1.uid)*1.0/(SELECT COUNT(1) FROM tb),2) desc

--SQL SERVER 优化

with tb as
(
  select uid
  ,MIN(CONVERT(nvarchar(10),in_time,120)) as fistTime
  ,MAX(CONVERT(nvarchar(10),out_time,120)) as lastTime
  ,(select MAX(CONVERT(nvarchar(10),out_time,120)) from tb_user_log) as today
  from tb_user_log
  group by uid
)


select t1.user_grade,round(COUNT(t1.uid)*1.0/(SELECT COUNT(1) FROM tb),2) as rate
from
(
select uid
,case when DATEDIFF(DAY,fistTime,today) <= 6 then '新晋用户'
when DATEDIFF(DAY,lastTime,today) > 29  then '流失用户'
when DATEDIFF(DAY,lastTime,today) > 6  then '沉睡用户'
else '忠实用户'
end as user_grade
from tb
) t1
group by t1.user_grade
order by round(COUNT(t1.uid)*1.0/(SELECT COUNT(1) FROM tb),2) desc

--mySQL

with tb as
(
  select uid
  ,MIN(date(in_time)) as fistTime
  ,MAX(date(out_time)) as lastTime
  ,(select MAX(date(in_time)) from tb_user_log) as today
  from tb_user_log
  group by uid
)


select t1.user_grade,round(COUNT(t1.uid)*1.0/(SELECT COUNT(1) FROM tb),2) as rate
from
(
select uid
,case when timestampdiff(DAY,fistTime,today) <= 6 then '新晋用户'
when timestampdiff(DAY,lastTime,today) > 29  then '流失用户'
when timestampdiff(DAY,lastTime,today) > 6  then '沉睡用户'
else '忠实用户'
end as user_grade
from tb
) t1
group by t1.user_grade
order by round(COUNT(t1.uid)*1.0/(SELECT COUNT(1) FROM tb),2) desc