关于根据活跃度给用户分类的SQL语句编写和优化

前言

2022.01.25
用了一个小时完成了牛客网上面的一道题，从35行代码改到23行，附上过程和一些思考总结。

题目概述

描述
用户行为日志表tb_user_log
（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

问题：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

注：
用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
假设今天就是数据中所有日期的最大值。
近7天表示包含当天T的近7天，即闭区间[T-6, T]。

总思路

1.得到用户首次登录时间（因为要计算新增）
2.得到用户最近一次的登录时间（因为要算近7天）
3.通过这两个时间去case when用户进行分类
其中，得到首次登录时间和最近一次登录时间的方法为分两次开窗排序（升序和降序）再把两个表连接起来
最初版本的代码如下：

SELECT
    user_grade,
    round(count(uid)/(SELECT COUNT(DISTINCT uid) cnt FROM tb_user_log),2) as ratio
FROM(
    SELECT
        uid,
        case
            when datediff('2021-11-04',recent_dt) <= 6 and datediff('2021-11-04',first_dt) > 6 then '忠实用户'
            when datediff('2021-11-04',first_dt) <= 6 then '新晋用户'
            when datediff('2021-11-04',recent_dt) BETWEEN 6 and 29 then '沉睡用户'
            else '流失用户'
        END as user_grade
    from(
        SELECT 
            first_log.uid,
            first_log.dt as first_dt,
            recent_log.dt as recent_dt
        FROM (
            select
                uid, date(in_time) as dt,
                count(artical_id) over(partition by uid order by in_time asc) as first_label
            FROM tb_user_log
            ) first_log
            LEFT JOIN(
            select
                uid, date(in_time) as dt,
                count(artical_id) over(partition by uid order by in_time DESC) as recent_label
            FROM tb_user_log
            ) recent_log
            ON first_log.uid = recent_log.uid and first_log.first_label = recent_log.recent_label
        where first_label = 1
        ) a
    ) B 
GROUP BY user_grade
ORDER BY ratio DESC, user_grade

改进

1.删去因为思路多出来的一个子循环
2.用另一种得到首次登录时间和最近一次登录时间的方法，直接用min和max groupby uid，不用开窗+连接

SELECT
    user_grade,
    round(count(uid)/(SELECT COUNT(DISTINCT uid) cnt FROM tb_user_log),2) as ratio
FROM(
    SELECT
        uid,
        case
            when datediff('2021-11-04',recent_dt) <= 6 and datediff('2021-11-04',first_dt) > 6 then '忠实用户'
            when datediff('2021-11-04',first_dt) <= 6 then '新晋用户'
            when datediff('2021-11-04',recent_dt) BETWEEN 6 and 29 then '沉睡用户'
            else '流失用户'
        END as user_grade
    from(
        select
            uid, 
            min(date(in_time)) as first_dt,
            max(date(in_time)) as recent_dt
        FROM tb_user_log
        GROUP BY uid
        ) a
    ) B 
GROUP BY user_grade
ORDER BY ratio DESC, user_grade

思考

之所以开始的时候费很大力气写了一个用开窗得到首次登录时间和最近一次登录时间的方法，是因为之前不久学到了这种方法，在之前的例子里，用开窗更方便一些。
那么两种方法哪一种更好呢？it depends
开窗适用于只取一种时间，这时用聚合也是一样的，但是具体用什么可以视上下文而定
聚合适用于同时取到首次登录时间和最近一次登录时间的时候，非常简便