SQL179 每个6/7级用户活跃情况

发布于:2025-08-12 ⋅ 阅读:(15) ⋅ 点赞:(0)

描述

现有用户信息表user_info(uid用户ID,nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间):

id uid nick_name achievement level job register_time
1 1001 牛客1号 3100 7 算法 2020-01-01 10:00:00
2 1002 牛客2号 2300 7 算法 2020-01-01 10:00:00
3 1003 牛客3号 2500 7 算法 2020-01-01 10:00:00
4 1004 牛客4号 1200 5 算法 2020-01-01 10:00:00
5 1005 牛客5号 1600 6 C++ 2020-01-01 10:00:00
6 1006 牛客6号 2600 7 C++ 2020-01-01 10:00:00

试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):

id exam_id tag difficulty duration release_time
1 9001 SQL hard 60 2021-09-01 06:00:00
2 9002 C++ easy 60 2021-09-01 06:00:00
3 9003 算法 medium 80 2021-09-01 10:00:00

试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):

uid exam_id start_time submit_time score
1001 9001 2021-09-01 09:01:01 2021-09-01 09:31:00 78
1001 9001 2021-09-01 09:01:01 2021-09-01 09:31:00 81
1005 9001 2021-09-01 19:01:01 2021-09-01 19:30:01 85
1005 9002 2021-09-01 12:01:01 2021-09-01 12:31:02 85
1006 9003 2021-09-07 10:01:01 2021-09-07 10:21:59 84
1006 9001 2021-09-07 10:01:01 2021-09-07 10:21:01 81
1002 9001 2020-09-01 13:01:01 2020-09-01 13:41:01 81
1005 9001 2021-09-01 14:01:01 (NULL) (NULL)

题目练习记录表practice_record(uid用户ID, question_id题目ID, submit_time提交时间, score得分):

uid question_id submit_time score
1001 8001 2021-08-02 11:41:01 60
1004 8001 2021-08-02 19:38:01 70
1004 8002 2021-08-02 19:48:01 90
1001 8002 2021-08-02 19:38:01 70
1004 8002 2021-08-02 19:48:01 90
1006 8002 2021-08-04 19:58:01 94
1006 8003 2021-08-03 19:38:01 70
1006 8003 2021-08-02 19:48:01 90
1006 8003 2020-08-01 19:38:01 80

请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数,按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下:

uid act_month_total act_days_2021 act_days_2021_exam act_days_2021_question
1006 3 4 1 3
1001 2 2 1 1
1005 1 1 1 0
1002 1 0 0 0
1003 0 0 0 0

解释:6/7级用户共有5个,其中1006在202109、202108、202008共3个月活跃过,2021年活跃的日期有20210907、20210804、20210803、20210802共4天,2021年在试卷作答区20210907活跃1天,在题目练习区活跃了3天。

SELECT 
    u.uid,
    COALESCE(act_month_total, 0) AS act_month_total,
    COALESCE(act_days_2021_total, 0) AS act_days_2021_total,
    COALESCE(er_days, 0) AS act_days_2021_exam,
    COALESCE(pr_days, 0) AS act_days_2021_question
FROM 
    -- 第一步:选出所有 6级 和 7级 用户
    (SELECT uid FROM user_info WHERE level IN (6, 7)) u

-- 第二步:左连接 总活跃月份数
LEFT JOIN (
    SELECT 
        uid,
        COUNT(DISTINCT DATE_FORMAT(record_date, '%Y%m')) AS act_month_total
    FROM (
        -- 考试记录的月份
        SELECT uid, start_time AS record_date FROM exam_record
        UNION ALL
        -- 练习记录的月份
        SELECT uid, submit_time AS record_date FROM practice_record
    ) t_month
    GROUP BY uid
) m ON u.uid = m.uid

-- 第三步:左连接 2021年总活跃天数
LEFT JOIN (
    SELECT 
        uid,
        COUNT(DISTINCT DATE_FORMAT(record_date, '%Y%m%d')) AS act_days_2021_total
    FROM (
        -- 2021年考试记录
        SELECT uid, start_time AS record_date FROM exam_record WHERE YEAR(start_time) = 2021
        UNION ALL
        -- 2021年练习记录
        SELECT uid, submit_time AS record_date FROM practice_record WHERE YEAR(submit_time) = 2021
    ) t_day
    GROUP BY uid
) d ON u.uid = d.uid

-- 第四步:左连接 2021年试卷作答活跃天数
LEFT JOIN (
    SELECT 
        uid,
        COUNT(DISTINCT DATE_FORMAT(start_time, '%Y%m%d')) AS er_days
    FROM exam_record
    WHERE YEAR(start_time) = 2021
    GROUP BY uid
) er ON u.uid = er.uid

-- 第五步:左连接 2021年答题活跃天数
LEFT JOIN (
    SELECT 
        uid,
        COUNT(DISTINCT DATE_FORMAT(submit_time, '%Y%m%d')) AS pr_days
    FROM practice_record
    WHERE YEAR(submit_time) = 2021
    GROUP BY uid
) pr ON u.uid = pr.uid

-- 排序输出
ORDER BY 
    act_month_total DESC, 
    act_days_2021_total DESC;

📚 SQL 学习笔记:用户活跃度统计(6级 & 7级用户)

🎯 题目背景(简化理解)

统计 6级和7级用户的以下信息:

  1. 总活跃月份数(考试 + 练习)
  2. 2021年总活跃天数
  3. 2021年试卷作答活跃天数(仅考试)
  4. 2021年答题活跃天数(仅练习)

最终按活跃月数、活跃天数降序排序。


✅ 解法一:我的版本(分步左连接)

SELECT 
    u.uid,
    COALESCE(act_month_total, 0) AS act_month_total,
    COALESCE(act_days_2021_total, 0) AS act_days_2021_total,
    COALESCE(er_days, 0) AS act_days_2021_exam,
    COALESCE(pr_days, 0) AS act_days_2021_question
FROM 
    (SELECT uid FROM user_info WHERE level IN (6, 7)) u
LEFT JOIN ( -- 总活跃月份数
    SELECT uid, COUNT(DISTINCT DATE_FORMAT(record_date, '%Y%m')) AS act_month_total
    FROM (
        SELECT uid, start_time AS record_date FROM exam_record
        UNION ALL
        SELECT uid, submit_time AS record_date FROM practice_record
    ) t_month
    GROUP BY uid
) m ON u.uid = m.uid
LEFT JOIN ( -- 2021年总活跃天数
    SELECT uid, COUNT(DISTINCT DATE_FORMAT(record_date, '%Y%m%d')) AS act_days_2021_total
    FROM (
        SELECT uid, start_time AS record_date FROM exam_record WHERE YEAR(start_time) = 2021
        UNION ALL
        SELECT uid, submit_time AS record_date FROM practice_record WHERE YEAR(submit_time) = 2021
    ) t_day
    GROUP BY uid
) d ON u.uid = d.uid
LEFT JOIN ( -- 2021年考试天数
    SELECT uid, COUNT(DISTINCT DATE_FORMAT(start_time, '%Y%m%d')) AS er_days
    FROM exam_record
    WHERE YEAR(start_time) = 2021
    GROUP BY uid
) er ON u.uid = er.uid
LEFT JOIN ( -- 2021年练习天数
    SELECT uid, COUNT(DISTINCT DATE_FORMAT(submit_time, '%Y%m%d')) AS pr_days
    FROM practice_record
    WHERE YEAR(submit_time) = 2021
    GROUP BY uid
) pr ON u.uid = pr.uid
ORDER BY act_month_total DESC, act_days_2021_total DESC;

✅ 解法二:大佬版本(聚合 IF + UNION 优化)

SELECT 
    ui.uid as uid,
    COUNT(DISTINCT DATE_FORMAT(act_time,'%Y%m')) as act_month_total,
    COUNT(DISTINCT IF(YEAR(act_time)=2021, DATE(act_time), null)) as act_days_2021,
    COUNT(DISTINCT IF(YEAR(act_time)=2021 and tag='exam', DATE(act_time), null)) as act_days_2021_exam,
    COUNT(DISTINCT IF(YEAR(act_time)=2021 and tag='question', DATE(act_time), null)) as act_days_2021_question
FROM user_info ui
LEFT JOIN (
    SELECT uid, DATE(start_time) as act_time, 'exam' as tag FROM exam_record
    UNION ALL
    SELECT uid, DATE(submit_time) as act_time, 'question' as tag FROM practice_record
) eq ON ui.uid = eq.uid
WHERE level >= 6
GROUP BY uid
ORDER BY act_month_total DESC, act_days_2021 DESC;

🔍 对比分析(核心学习点)

对比维度 我的版本 大佬版本
整体结构 多次 LEFT JOIN 多个子查询 一次 LEFT JOIN + 聚合函数
数据源处理 分开处理考试、练习 用 UNION ALL 合并成统一行为表
2021年过滤 在子查询中提前过滤 在 COUNT(IF(...)) 中条件聚合
NULL处理 用 COALESCE 补 0 依赖 COUNT 本身不计 NULL,天然为 0
性能 ⚠️ 可能较差(多次扫描 + 多次 JOIN) ✅ 更优(一次扫描 + 一次 GROUP BY)
可读性 ✅ 高(逻辑清晰,分步明确) ⚠️ 中等(需要理解 IF + COUNT 的条件聚合)
扩展性 ❌ 差(新增维度要加 JOIN) ✅ 好(新增维度只需加一个 COUNT(IF(...))

💡 核心思想提炼

✅ 我的版本:“分而治之”

  • 思路清晰,像“搭积木”一样,每一步只解决一个问题。
  • 适合初学者理解,逻辑不容易出错。
  • 缺点是代码冗长,性能可能不高(尤其是数据量大时,多次 JOIN 和 GROUP BY 代价高)。

✅ 大佬版本:“统一建模 + 条件聚合”

  • 把“考试”和“练习”视为两种“用户行为”,用 tag 字段区分。
  • 用 UNION ALL 把两张表变成一张“行为日志表”。
  • 用 COUNT(DISTINCT IF(...)) 实现条件去重计数,非常高效。
  • 这是 数据仓库/BI 分析的经典套路

🌟 关键技巧学习

1. UNION ALL 合并行为日志

SELECT uid, DATE(start_time), 'exam' FROM exam_record
UNION ALL
SELECT uid, DATE(submit_time), 'question' FROM practice_record

👉 把不同行为统一成“用户-时间-行为类型”三元组,便于统一分析。

2. COUNT(DISTINCT IF(condition, value, NULL))

COUNT(DISTINCT IF(YEAR(act_time)=2021, DATE(act_time), NULL))

👉 只对满足条件的记录去重计数,一行代码实现条件聚合,非常强大!

3. DATE_FORMAT(act_time, '%Y%m') → 按月统计

👉 比 YEAR() + MONTH() 更简洁,推荐使用。

4. WHERE level >= 6 vs IN (6,7)

  • 如果未来有 8 级,你的版本要改,大佬版本不用改。
  • 但题目明确是 6 和 7 级,所以两者都对。

✅ 优化建议(结合两者优点)

-- ✅ 推荐写法:清晰 + 高效
SELECT 
    ui.uid,
    COUNT(DISTINCT DATE_FORMAT(er.start_time, '%Y%m')) 
    + COUNT(DISTINCT DATE_FORMAT(pr.submit_time, '%Y%m')) AS act_month_total,

    COUNT(DISTINCT IF(YEAR(er.start_time)=2021, DATE(er.start_time), NULL))
    + COUNT(DISTINCT IF(YEAR(pr.submit_time)=2021, DATE(pr.submit_time), NULL)) AS act_days_2021_total,

    COUNT(DISTINCT IF(YEAR(er.start_time)=2021, DATE(er.start_time), NULL)) AS act_days_2021_exam,
    COUNT(DISTINCT IF(YEAR(pr.submit_time)=2021, DATE(pr.submit_time), NULL)) AS act_days_2021_question

FROM user_info ui
LEFT JOIN exam_record er ON ui.uid = er.uid
LEFT JOIN practice_record pr ON ui.uid = pr.uid
WHERE ui.level IN (6, 7)
GROUP BY ui.uid
ORDER BY act_month_total DESC, act_days_2021_total DESC;

✅ 优点:只用两个 LEFT JOIN,用 IF 实现条件计数,比原版更高效。


🏁 总结:

技能 掌握情况
多表聚合统计
UNION ALL 合并行为
COUNT(DISTINCT IF(...)) 条件去重计数 ✅(重点!)
DATE_FORMAT 按年月日分组
COALESCE 处理 NULL
LEFT JOIN 保证用户不丢失
性能与可读性的权衡

🎯 学习建议

  1. 先学我的版本:理解逻辑分步。
  2. 再学大佬版本:掌握“统一建模 + 条件聚合”的高级思想。
  3. 最后尝试优化版:写出既清晰又高效的 SQL。

💬 记住:好 SQL 不只是“能跑”,更是“高效、易读、可维护”。