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

WITH a AS (
SELECT 
    MAX(DATE(out_time)) AS today,
    DATE_SUB(MAX(DATE(out_time)), INTERVAL 6 DAY) AS day1,
    DATE_SUB(MAX(DATE(out_time)), INTERVAL 29 DAY) AS day2
FROM tb_user_log 
)
SELECT 
    T2.category AS user_grade,
    ROUND(COUNT(DISTINCT T2.uid) / (SELECT COUNT(DISTINCT uid) FROM tb_user_log), 2) AS ratio 
FROM(
SELECT 
    T.uid,
    CASE WHEN MIN(T.day) BETWEEN (SELECT day1 FROM a) AND (SELECT today FROM a) 
         THEN "新晋用户"
         WHEN MAX(T.day) < (SELECT day2 FROM a)
         THEN "流失用户"
         WHEN (MAX(T.day) BETWEEN (SELECT day1 FROM a) AND (SELECT today FROM a))
              AND (MIN(T.day) < (SELECT day1 FROM a)) 
         THEN "忠实用户"
         ELSE "沉睡用户"  
    END AS category
FROM     
(
SELECT uid, DATE(in_time) AS day FROM tb_user_log
UNION
SELECT uid, DATE(out_time) AS day FROM tb_user_log
) T
GROUP BY uid 
) T2
GROUP BY T2.category
ORDER BY ratio DESC

with
    t2 as (
        select
            date_sub(max(date (in_time)), interval 6 day) mid_time
        from
            tb_user_log
    ),
    t1 as (
        select
            max(date (in_time)) max_time
        from
            tb_user_log
    ),
    t3 as (
        select
            date_sub(max(date (in_time)), interval 29 day) least_time
        from
            tb_user_log
    ),
    t4 as (
        select
            uid,
            date (in_time) time,
            case
                when date (in_time) >= mid_time then 100
                when date (in_time) < least_time then 25
                else 50
            end score
        from
            tb_user_log,
            t1,
            t2,
            t3
        order by
            uid,
            time desc
    ),
    t5 as (
        select distinct
            (uid) uid,
            score
        from
            t4
    ),
    t6 as (
        select
        uid,
        sum(score),
        case
            when sum(score) > 100 then '忠实用户'
            when sum(score) =100 then '新晋用户'
            when sum(score) =25 then '流失用户'
            else '沉睡用户'
            end '用户类型'
        from
            t5
        group by 
            uid
    )
select 用户类型,round(count(*)/(select count(*) from t6),2) ratio from t6 group by 用户类型 order by ratio descx

select  user_type,
        round(count(distinct uid)/ cnt,2) ratio
from (
    select uid,
            (case when date_diff >= 30  then "流失用户"
                when date_diff between 7 and 29 and timestampdiff(day,first_in_time,cur_date)>=7 then "沉睡用户"
                when (date_diff <= 7 and timestampdiff(day,first_in_time,cur_date)<7) then "新晋用户"
                when (date_diff <= 7 and timestampdiff(day,first_in_time,cur_date)>7)  then "忠实用户" 
                end ) as user_type,
                (select count(distinct uid) from tb_user_log ) as cnt
    from 
        ( select uid,
                min(timestampdiff(day,in_time,(select max(out_time) from tb_user_log))) date_diff,
                min(in_time) first_in_time,
                (select max(out_time) from tb_user_log) cur_date
            from tb_user_log
            group by uid) t
) t
group by user_type
order by ratio desc

select distinct t3.user_grade, round(count(*) over(partition by user_grade) /count(*) over(),2 )ratio_rate from ( select t2.uid, case  when min(diff_date) <= 7 and max(diff_date) >= 7 then '忠实用户'  when max(diff_date) <= 7 and min(diff_date) <= 7 then '新晋用户'  when min(diff_date) >= 7 and min(diff_date) < 30 and max(diff_date) >= 7 then '沉睡用户'  when min(diff_date) >= 30 and max(diff_date) then '流失用户'  end user_grade from ( select uid, today, login_date, datediff(today, login_date) diff_date from ( select distinct uid, date(in_time) login_date, max(date(out_time)) over ( ) today from db5.tb_user_log
                       ) t1 group by uid, login_date, today
              ) t2 group by uid  ) t3

# 新晋用户:全表最大时间(今天)-注册时间<7
# 沉睡用户:全表最大时间(今天)-最近登录>=7 and 全表最大时间(今天)-最近登录<=29
# 流失用户:全表最大时间(今天)-最近登录>=30
# 忠实用户:全表最大时间(今天)-最近登录<7 and 全表最大时间(今天)-注册时间>=7
select 
    user_grade
    ,round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio
from
(
    select
        uid,
        case
            when datediff(max_intime,min_in_time)<7  then '新晋用户'
            when datediff(max_intime,max_in_time)>=7 and datediff(max_intime,max_in_time)<=29 then '沉睡用户'
            when datediff(max_intime,max_in_time)>=30 then '流失用户'
            else '忠实用户'
        end user_grade 
    from 
    (
        select 
            uid
            ,(select max(date(in_time)) from tb_user_log) as max_intime # 全表最大时间(今天)
            ,max(date(in_time)) as max_in_time # 每个用户登录的最大时间(最近登录)
            ,min(date(in_time)) as min_in_time # 每个用户登录的最小时间(注册时间)
        from
            tb_user_log
        group by 
            uid
    ) t1
    group by
        uid
) t2
group by
    user_grade
order by
    ratio desc

select @maxdd :=max(in_time) from tb_user_log tul ;

select @ucnt :=count(distinct uid) from tb_user_log tul ;

select case

when datediff(@maxdd,max_d)<=6 and datediff(@maxdd,min_d)<=6 then '新晋用户'

when datediff(@maxdd,max_d)<=6 and datediff(@maxdd,min_d)>6 then '忠实用户'

when datediff(@maxdd,max_d)>6 and datediff(@maxdd,max_d)<=30 then '沉睡用户'

else '流失用户'

end as user_grade,round(count(uid)/@ucnt,2) as ratio

from

(select uid,max(in_time) as max_d,min(in_time) as min_d from tb_user_log tul group by uid) as t1

#用户活跃日期
with t as (select uid,date(in_time) dt
from tb_user_log
union
select uid,date(out_time) dt
from tb_user_log),

#统计用户最早活跃日期，最晚活跃日期，今天
t1 as (select *,max(max)over() today
from
(select uid,min(dt) min,max(dt) max from t group by uid) a),

#用户分层
t2 as (select uid
,(case when datediff(today,max)>=30 then '流失用户'
       when datediff(today,max)>=7 then '沉睡用户'
       when datediff(today,min)<7 then '新晋用户'
       when datediff(today,min)>=7 and today-max<7 then '忠实用户' end) user_grade
from t1)

select user_grade,round(count(*)/sum(count(*))over(),2) ratio
from t2
group by user_grade
order by ratio desc

with dwd_data as (
select 
 uid,
 -- 最大日期 今天
 max(date(in_time)) over() as max_dt,
 -- 每个用户，最大日期
 max(date(in_time)) over(partition by uid) as max_user_dt,
  -- 每个用户，最小日期
 min(date(in_time)) over(partition by uid) as min_user_dt
from tb_user_log
)

select 
t1.user_grade,
round(count(distinct t1.uid)/ avg(t2.all_user),2) as ratio
from (
select 
 uid,
 1 as flag,
-- 忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
case when datediff(max_dt,max_user_dt) <=6 and  datediff(max_dt,min_user_dt) >6 then '忠实用户'
     when datediff(max_dt,min_user_dt) <=6 then '新晋用户'
     when datediff(max_dt,max_user_dt) >29 then '流失用户'
     when datediff(max_dt,max_user_dt) >6 then '沉睡用户'
end user_grade
from dwd_data 
)t1
left join (select 1 as flag,count(distinct uid) as all_user from dwd_data) t2
on t1.flag = t2.flag
group by t1.user_grade
order by 2 desc;

with data_tmp as (
    select 
        main_list.uid,
        case when datediff(today,first_day)<=6 then '新晋用户'
            when datediff(today,first_day)>6 and datediff(today,last_day)<=6 then '忠实用户' 
            when datediff(today,first_day)>6 and datediff(today,last_day)<=29 then '沉睡用户' 
            when datediff(today,first_day)>6 and datediff(today,last_day)>29 then '流失用户' 
            else 'null' end as user_grade,
        today,
        first_day,
        last_day,
        count(1) over (partition by today) all_num 
    from ( -- 用户列表以及成为新用户时间
        select
            uid,
            min(date(in_time)) first_day
        from 
            tb_user_log
        group by 
            uid
    ) main_list 
    left join ( -- 最近一次用户活跃记录
        select 
            uid,
            max(date(in_time)) last_day
        from tb_user_log
        group by 
            uid
    ) t1 on main_list.uid = t1.uid
    left join ( -- 今天时间
        select
            max(date(in_time)) today
        from 
            tb_user_log
    ) t2 on 1 = 1
    group by 1,2,3,4,5
)

select 
    user_grade,
    round(count(1)/all_num,2) ratio
from data_tmp
group by 
    user_grade 
order by 
    ratio desc

select '忠实用户'as user_grade,
round(sum(u1)/count(uid),2) as ratio from   
(select uid,
if(datediff((select max(date(out_time)) from tb_user_log),max(date(out_time)))<=6 and datediff((select max(date(out_time)) from tb_user_log),min(date(in_time)))>6,1,0) as u1 
from tb_user_log
group by uid) ui1
union
select '新晋用户'as user_grade,
round(sum(u2)/count(uid),2) as ratio from   
(select uid,
if(datediff((select max(date(out_time)) from tb_user_log),min(date(in_time)))<=6,1,0) as u2
from tb_user_log
group by uid) ui2
union
select '沉睡用户'as user_grade,
round(sum(u3)/count(uid),2) as ratio from   
(select uid,
if(datediff((select max(date(out_time)) from tb_user_log),max(date(out_time)))>6 and datediff((select max(date(out_time)) from tb_user_log),min(date(in_time)))<=29,1,0) as u3
from tb_user_log
group by uid) ui3
union
select '流失用户'as user_grade,
round(sum(u4)/count(uid),2) as ratio from   
(select uid,
if(datediff((select max(date(out_time)) from tb_user_log),max(date(out_time)))>29 and datediff((select max(date(out_time)) from tb_user_log),min(date(in_time)))>29,1,0) as u4
from tb_user_log
group by uid) ui4

with t as( select 
case when datediff(md,date(in_time)) <=6 and news=0 then '忠实用户'
when datediff(md,date(in_time)) <=6 and news=1 then '新晋用户'
when datediff(md,date(in_time)) >6 and datediff(md,date(in_time)) <30 and news=1 then '沉睡用户'
when datediff(md,date(in_time)) >=30 and news=1 then'流失用户'
end as user_grade
from (
select in_time,
if(date(min(in_time) over(partition by uid))=date(in_time),1,0) news
,row_number() over(partition by uid order by in_time desc) rk
from tb_user_log ) t1,
(select max(in_time) md from tb_user_log) t2
where rk=1
)

select user_grade, round(count(1)/(select count(*) from t),2) ratio
from t
group by 1
order by 2 desc, 1 asc

select
     user_grade
    ,round(count(1)/sum(count(1)) over(), 2) as ratio
from (
    select
         uid
        ,case when datediff(recent_dt, first_dt) <= 6 then '新晋用户'
            when datediff(recent_dt, last_dt) <= 6 then '忠实用户'
            when datediff(recent_dt, last_dt) > 29 then '流失用户'
            else '沉睡用户' end as user_grade
    from (
        select
             uid
            ,min(date_format(in_time, '%Y-%m-%d')) as first_dt
            ,max(date_format(in_time, '%Y-%m-%d')) as last_dt
            ,max(max(date_format(in_time, '%Y-%m-%d'))) over() as recent_dt
        from tb_user_log
        group by
            uid
    ) as t1
) as tt1
group by
     user_grade
order by
     ratio desc