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

描述

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

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

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

题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

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

解释：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_totalFROM (-- 考试记录的月份SELECT uid, start_time AS record_date FROM exam_recordUNION ALL-- 练习记录的月份SELECT uid, submit_time AS record_date FROM practice_record) t_monthGROUP 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_totalFROM (-- 2021年考试记录SELECT uid, start_time AS record_date FROM exam_record WHERE YEAR(start_time) = 2021UNION ALL-- 2021年练习记录SELECT uid, submit_time AS record_date FROM practice_record WHERE YEAR(submit_time) = 2021) t_dayGROUP BY uid
) d ON u.uid = d.uid-- 第四步：左连接 2021年试卷作答活跃天数
LEFT JOIN (SELECT uid,COUNT(DISTINCT DATE_FORMAT(start_time, '%Y%m%d')) AS er_daysFROM exam_recordWHERE YEAR(start_time) = 2021GROUP BY uid
) er ON u.uid = er.uid-- 第五步：左连接 2021年答题活跃天数
LEFT JOIN (SELECT uid,COUNT(DISTINCT DATE_FORMAT(submit_time, '%Y%m%d')) AS pr_daysFROM practice_recordWHERE YEAR(submit_time) = 2021GROUP 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_totalFROM (SELECT uid, start_time AS record_date FROM exam_recordUNION ALLSELECT uid, submit_time AS record_date FROM practice_record) t_monthGROUP 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_totalFROM (SELECT uid, start_time AS record_date FROM exam_record WHERE YEAR(start_time) = 2021UNION ALLSELECT uid, submit_time AS record_date FROM practice_record WHERE YEAR(submit_time) = 2021) t_dayGROUP BY uid
) d ON u.uid = d.uid
LEFT JOIN ( -- 2021年考试天数SELECT uid, COUNT(DISTINCT DATE_FORMAT(start_time, '%Y%m%d')) AS er_daysFROM exam_recordWHERE YEAR(start_time) = 2021GROUP BY uid
) er ON u.uid = er.uid
LEFT JOIN ( -- 2021年练习天数SELECT uid, COUNT(DISTINCT DATE_FORMAT(submit_time, '%Y%m%d')) AS pr_daysFROM practice_recordWHERE YEAR(submit_time) = 2021GROUP 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_recordUNION ALLSELECT 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;

🔍 对比分析（核心学习点）

💡 核心思想提炼

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

• 思路清晰，像“搭积木”一样，每一步只解决一个问题。
• 适合初学者理解，逻辑不容易出错。
• 缺点是代码冗长，性能可能不高（尤其是数据量大时，多次 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_questionFROM 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 实现条件计数，比原版更高效。

🏁 总结：

🎯 学习建议

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

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