题解 | #统计活跃间隔对用户分级结果#很笨比的方法，做下记

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

https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af

WITH
    today_time AS (
        SELECT
            DATE (MAX(all_time)) AS today
        FROM
            (
                SELECT
                    MAX(in_time) AS all_time
                FROM
                    tb_user_log
                UNION ALL
                SELECT
                    MAX(out_time)
                FROM
                    tb_user_log
            ) all_time_table
    ),
    first_cus AS (
        SELECT
            uid,
            DATE (MIN(in_time)) AS first_time
        FROM
            tb_user_log
        GROUP BY
            uid
    ),
    active_cus AS (
        SELECT
            uid,
            DATE (in_time) AS act_time
        FROM
            tb_user_log
        UNION
        SELECT
            uid,
            DATE (out_time) AS act_time
        FROM
            tb_user_log
    )
SELECT 
    tag,
    ROUND(
        cnt/SUM(cnt)OVER(),
        2
    ) AS rate
FROM
    (
        SELECT
            tag,
            COUNT(*) cnt
        FROM
            (
                SELECT
                    uid,
                    "新晋用户" tag
                FROM
                    first_cus
                    CROSS JOIN today_time
                WHERE
                    DATEDIFF (today_time.today, first_cus.first_time) < 7
                UNION
                SELECT
                    uid,
                    "忠实用户" tag
                FROM
                    (
                        SELECT DISTINCT
                            uid
                        FROM
                            active_cus
                            CROSS JOIN today_time
                        WHERE
                            DATEDIFF (today, act_time) < 7
                    ) t1
                WHERE
                    uid NOT IN (
                        SELECT DISTINCT
                            uid
                        FROM
                            first_cus
                            CROSS JOIN today_time
                        WHERE
                            DATEDIFF (today_time.today, first_cus.first_time) < 7
                    )
                UNION
                SELECT DISTINCT
                    uid,
                    "流失用户" tag
                FROM
                    active_cus
                WHERE
                    uid NOT IN (
                        SELECT DISTINCT
                            uid
                        FROM
                            active_cus
                            CROSS JOIN today_time
                        WHERE
                            DATEDIFF (today, act_time) < 30
                    )
                UNION
                SELECT DISTINCT
                    uid,
                    "沉睡用户" tag
                FROM
                    active_cus
                WHERE
                    uid NOT IN (
                        SELECT DISTINCT
                            uid
                        FROM
                            active_cus
                            CROSS JOIN today_time
                        WHERE
                            DATEDIFF (today, act_time) < 7
                    )
                    AND uid NOT IN (
                        SELECT
                            uid
                        FROM
                            active_cus
                        WHERE
                            uid NOT IN (
                                SELECT DISTINCT
                                    uid
                                FROM
                                    active_cus
                                    CROSS JOIN today_time
                                WHERE
                                    DATEDIFF (today, act_time) < 30
                            )
                    )
            ) t2
        GROUP BY
            tag
    ) t3
ORDER BY rate DESC, tag

目标

分类用户并计算各类用户占总用户数的比例。分类包括新晋用户、忠实用户、流失用户和沉睡用户。

主要步骤

计算最新日期：找到日志记录中的最新时间（today），这是所有日期比较的基准。
计算首次访问时间：统计每个用户的首次访问时间（first_time），用于判断是否是新晋用户。
计算所有访问时间：收集所有用户的访问时间（act_time），包括进入和退出时间，用于判断用户的活跃状态。
分类用户：新晋用户：首次访问时间在最近7天内的用户。忠实用户：最近7天内有访问记录但不属于新晋用户的用户。流失用户：最近30天内没有访问记录的用户。沉睡用户：最近7天内没有访问记录且不属于流失用户的用户。
计算每类用户数量和比例：统计每类用户的数量。计算每类用户占总用户数的比例。

具体实现思路

1. 获取最新日期 (`today_time`):

使用MAX(in_time)和MAX(out_time)来找到日志中的最新时间。
将最新时间转化为日期格式。

2. 获取每个用户的首次访问时间 (`first_cus`):

使用MIN(in_time)来找到每个用户的首次访问时间，并将其转化为日期格式。
结果包括用户ID和首次访问日期。

3. 获取所有用户的访问时间 (`active_cus`):

收集每个用户的in_time和out_time，并将其转化为日期格式。
结果包括用户ID和访问日期。

4. 分类用户：

新晋用户：比较用户的首次访问时间和最新日期，如果首次访问时间在最近7天内，则该用户为新晋用户。
忠实用户：筛选出最近7天内有访问记录的用户，排除新晋用户后，剩下的为忠实用户。
流失用户：筛选出最近30天内没有任何访问记录的用户。
沉睡用户：筛选出最近7天内没有访问记录且不属于流失用户的用户。

5. 计算用户分类数量和比例：

统计每类用户的数量。
计算每类用户数量占总用户数的比例，并将结果按比例降序排列。

参考题解，修改以后的代码：

WITH
    user_time AS (
        SELECT
            uid,
            DATE (MIN(in_time)) first_dt,
            DATE (MAX(out_time)) last_dt
        FROM
            tb_user_log
        GROUP BY
            uid
    ),
    curr_time AS (
        SELECT
            DATE (MAX(curr_dt)) curr_dt
        FROM
            (
                SELECT
                    MAX(in_time) curr_dt
                FROM
                    tb_user_log
                UNION
                SELECT
                    MAX(out_time) curr_dt
                FROM
                    tb_user_log
            ) curr_t
    )


SELECT
tag,
ROUND(
    cnt/SUM(cnt)OVER() ,
    2
) rate
FROM(
    SELECT
    tag,
    count(*) cnt
FROM(
    SELECT
    uid,
    CASE
        WHEN first_diff < 7 THEN "新晋用户"
        ELSE CASE
            WHEN last_diff < 7 THEN "忠实用户"
            WHEN last_diff < 30 THEN "沉睡用户"
            ELSE "流失用户"
        END
    END tag
FROM
    (
        SELECT
            uid,
            DATEDIFF (curr_dt, first_dt) first_diff,
            DATEDIFF (curr_dt, last_dt) last_diff
        FROM
            user_time
            CROSS JOIN curr_time
    ) user_dt_diff
)user_grade
GROUP BY tag
)user_grade_cnt
ORDER BY rate DESC,tag

首先定义两个公共表表达式（CTE）：user_time 和 curr_time。

user_time CTE 从 tb_user_log 表中提取用户的 uid、第一次登录时间 first_dt 和最后一次登出时间 last_dt。curr_time CTE 计算 tb_user_log 表中最新的活动日期 curr_dt。

接下来，通过内部子查询 user_dt_diff 计算每个用户的日期差异，包括 first_diff（当前日期和第一次登录日期的差异）和 last_diff（当前日期和最后一次登出日期的差异）。

然后，使用 CASE 语句对用户进行分类。如果 first_diff 小于 7 天，则用户被分类为“新晋用户”。否则，根据 last_diff 进一步分类：last_diff 小于 7 天的用户为“忠实用户”，last_diff 小于 30 天的用户为“沉睡用户”，其他用户为“流失用户”。

分类完成后，通过中间子查询 user_grade_cnt 对分类后的用户按 tag 分组，并计算每个类别的用户数 cnt。

最后，使用窗口函数 SUM(cnt) OVER() 计算总用户数，并计算每个类别占总用户数的比例，结果按比例和标签排序输出。