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

-- 思路：

-- 1.构建用户活跃表

-- 2.计算出每个用户的最早和最近登录时间

-- 3.构建用户等级字段,并统计出整体用户数

-- 4.分组计算比例，排序

补充：聚合函数count()与窗口函数count() over()的区别？

窗口函数和聚合函数在使用上的区别主要体现在作用域、用途、参数以及与GROUP BY的关系上。

1. 作用域：聚合函数操作整个结果集，计算并返回一个单一的值；而窗口函数在查询的每一行上执行计算，生成一个结果集，这个结果集中的每一行对应原始结果集中的一行。
2. 用途：聚合函数用于对数据进行汇总和统计，例如计算总和、平均值或计数等；而窗口函数常用于执行与每一行数据相关的计算，例如计算移动平均值、累计总和或排名等。
3. 参数：聚合函数通常接受一个或多个列作为参数，并返回一个单一的值；而窗口函数通常接受一个或多个列以及一个窗口范围作为参数，并在该窗口范围内执行计算。
4. 与GROUP BY的关系：在使用聚合函数时，通常需要将非聚合列包含在GROUP BY子句中，以确保查询结果的确定性；而窗口函数与GROUP BY子句是独立的，你可以在带有GROUP BY子句的查询中使用窗口函数，也可以在不带GROUP BY子句的查询中使用窗口函数。

-- 当你使用窗口函数（如over()）时，MySQL允许SELECT列表中包含非聚合列，因为窗口函数是在每一行上独立计算的，而不是对整个结果集进行聚合。

-- 思路：
--      1.构建用户活跃表
--      2.计算出每个用户的最早和最近登录时间
--      3.构建用户等级字段,并统计出整体用户数
--      4.分组计算比例，排序
with t_active as (
-- 1.构建用户活跃表
select uid,date(in_time) as dt
from tb_user_log
union all 
select uid,date(out_time) as dt
from tb_user_log
)

-- 4.分组计算比例，排序
select 
    user_grade,
    round(count(uid)/ max(usr_cnt), 2) as ratio
from (
-- 3.构建用户等级字段,并统计出整体用户数
select 
    uid,
    case 
        when datediff(recent_day, last_dt) > 29 then '流失用户'
        when datediff(recent_day, last_dt) > 6 then '沉睡用户'
        when datediff(recent_day, first_dt) <= 6 then '新晋用户'
    else '忠实用户' end as user_grade,
-- 当你使用窗口函数（如over()）时，MySQL允许SELECT列表中包含非聚合列，因为窗口函数是在每一行上独立计算的，而不是对整个结果集进行聚合。
    count(uid) over() as usr_cnt
from(
-- 2.计算出每个用户的最早和最近登录时间
select 
    uid,
    min(dt) as first_dt,
    max(dt) as last_dt
from t_active 
group by uid
) as t1 , (
    select max(dt) as recent_day
    from t_active
) as t2
) as t3
group by user_grade
order by ratio desc