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

明确题意：

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

问题分解：

  • 计算每个用户最早最晚活跃日期（作为子表t_uid_first_last）：

    • 按用户ID分组：GROUP BY uid
    • 统计最早活跃：MIN(DATE(in_time)) as first_dt
    • 统计最晚活跃：MAX(DATE(out_time)) as last_dt

  • 计算当前日期和总用户数（作为子表t_overall_info）：

    • 获取当前日期：MAX(DATE(out_time)) as cur_dt
    • 统计总用户数：COUNT(DISTINCT uid) as user_cnt

  • 左连接两表，即将全表统计信息追加到每一行上：t_uid_first_last LEFT JOIN t_overall_info ON 1

  • 计算最早最晚活跃离当前天数差（作为子表t_user_info）：

    • 最早活跃距今天数：TIMESTAMPDIFF(DAY,first_dt,cur_dt) as first_dt_diff
    • 最晚（最近）活跃距今天数：TIMESTAMPDIFF(DAY,last_dt,cur_dt) as last_dt_diff

  • 计算每个用户的活跃等级：

    CASE
    WHEN last_dt_diff >= 30 THEN "流失用户"
    WHEN last_dt_diff >= 7 THEN "沉睡用户"
    WHEN first_dt_diff < 7 THEN "新晋用户"
    ELSE "忠实用户"
END as user_grade

  • 统计每个等级的占比：

    • 按用户等级分组：GROUP BY user_grade

    • 计算占比，总人数从子表得到，非聚合列避免语法错误加了MAX：COUNT(uid) / MAX(user_cnt) as ratio

    • 保留2位小数：ROUND(x, 2)

细节问题：

  • 表头重命名：as
  • 按占比降序排序：ORDER BY ratio DESC;;

完整代码：

SELECT user_grade, ROUND(COUNT(uid) / MAX(user_cnt), 2) as ratio
FROM (
    SELECT uid, user_cnt,
        CASE
            WHEN last_dt_diff >= 30 THEN "流失用户"
            WHEN last_dt_diff >= 7 THEN "沉睡用户"
            WHEN first_dt_diff < 7 THEN "新晋用户"
            ELSE "忠实用户"
        END as user_grade
    FROM (
        SELECT uid, user_cnt,
            TIMESTAMPDIFF(DAY,first_dt,cur_dt) as first_dt_diff, 
            TIMESTAMPDIFF(DAY,last_dt,cur_dt) as last_dt_diff
        FROM (
            SELECT uid, MIN(DATE(in_time)) as first_dt,
                MAX(DATE(out_time)) as last_dt
            FROM tb_user_log
            GROUP BY uid
        ) as t_uid_first_last
        LEFT JOIN (
            SELECT MAX(DATE(out_time)) as cur_dt,
                COUNT(DISTINCT uid) as user_cnt
            FROM tb_user_log
        ) as t_overall_info ON 1
    ) as t_user_info
) as t_user_grade
GROUP BY user_grade
ORDER BY ratio DESC;