综合练习

这一课你将能做到

1. 独立跑通 5 个递进难度的分析任务：聚合报表、窗口 Top-1、连续天数、日期序列补零、行转列
2. 用「CTE + 窗口函数」的差值法写出「连续练习天数」这类经典面试题的完整可跑版本
3. 用事务 + SAVEPOINT + 校验查询完成一次安全的批量数据导入，并顺手做一轮 EXPLAIN 优化验证

为什么要学

前面 11 课是零件，本课是总装。真实工作里没有「单独考 GROUP BY」的任务，只有「运营要一份每日报表」「系统要导入一批历史数据」这种混合需求——一条 SQL 里往往同时有聚合、窗口、日期函数、CTE 和事务。把本课 7 个任务全部跑通并与预期结果核对，你就有了一整套可以写进简历、拿去面试复现的实战案例。

人话：这是本 track 的「期末考」。做题平台业务表就是你的考场，每个任务对应一种真实工作场景，做完一遍相当于把 11 课串讲了一遍。

先建立图像

总装路线图（每个任务标注了它调用的前序课程）：

任务 1  每难度 AC 率        ← 第 4 课 聚合
任务 2  每人最近一次 AC     ← 第 7 课 窗口函数
任务 3  连续练习天数        ← 第 7 课 CTE + 窗口（面试经典）
任务 4  每日提交量补零      ← 第 7 课 递归 CTE + 第 4 课 JOIN
任务 5  行转列统计          ← 第 5 课 条件聚合
任务 6  事务导入 + 校验     ← 第 3 课 DML + 第 10 课 事务
任务 7  EXPLAIN 优化验证    ← 第 11 课 性能优化

跟着做

步骤 0：全量重置数据

-- 若第 9 课演示表未删，先清理
DROP TABLE IF EXISTS cf_comment, cf_feedback;

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
TRUNCATE TABLE cf_lesson;    -- cf_lesson 有指向 cf_track 的外键，先清子表
TRUNCATE TABLE cf_track;
-- 再执行第 1 课步骤 4 的四段 INSERT（6 用户、6 题、12 提交、9 练习记录）
-- 以及第 8 课步骤 1 的 cf_track / cf_lesson 两段 INSERT（1 个 track、12 节课）

以下所有「预期结果」都基于这份初始数据，对不上就先重置。

任务 1：每难度 AC 率报表（聚合）

SELECT
  p.difficulty,
  COUNT(*)                                            AS total_sub,
  SUM(s.status = 'accepted')                          AS ac_cnt,
  ROUND(SUM(s.status = 'accepted') / COUNT(*) * 100, 1) AS ac_rate
FROM cf_submission s
JOIN cf_problem p ON p.id = s.problem_id
GROUP BY p.difficulty
ORDER BY ac_rate DESC;

预期：hard 1/1/100.0、medium 5/3/60.0、easy 6/3/50.0。

任务 2：每人最近一次 AC 的题目（窗口）

SELECT username, title, submitted_at
FROM (
  SELECT
    u.username, p.title, s.submitted_at,
    ROW_NUMBER() OVER (PARTITION BY s.user_id ORDER BY s.submitted_at DESC) AS rn
  FROM cf_submission s
  JOIN cf_user u    ON u.id = s.user_id
  JOIN cf_problem p ON p.id = s.problem_id
  WHERE s.status = 'accepted'
) t
WHERE rn = 1
ORDER BY submitted_at;

预期四行：lin/LRU 缓存/06-02 10:00、zhao/数组中的第 K 大元素/06-04 09:00、wang/两数之和/06-05 11:50、zhou/有效的括号/06-07 09:00（sun 与 qian 无 AC）。

任务 3：连续练习天数（CTE + 窗口，面试必背）

WITH days AS (
  SELECT user_id, DATE(practiced_at) AS d
  FROM cf_practice_record
  GROUP BY user_id, DATE(practiced_at)
),
streaks AS (
  SELECT
    user_id,
    d,
    DATE_SUB(d, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) DAY) AS grp
  FROM days
)
SELECT
  user_id,
  MIN(d)    AS start_day,
  MAX(d)    AS end_day,
  COUNT(*)  AS streak_days
FROM streaks
GROUP BY user_id, grp
ORDER BY user_id, streak_days DESC;

预期：lin 06-0106-02 共 2 天、zhao 06-0306-04 共 2 天、wang 06-05 共 1 天、zhou 06-06~06-07 共 2 天。

原理：连续日期减去递增行号得到恒定差值（如 06-01 减 1、06-02 减 2 都是 06-00），差值相同的日期属于同一段连续区间——「日期 - 行号」分组法是连续性问题的万能钥匙。

任务 4：每日提交量（递归 CTE 补零天）

WITH RECURSIVE days AS (
  SELECT CAST('2025-06-01' AS DATE) AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days WHERE d < CAST('2025-06-07' AS DATE)
)
SELECT
  dy.d,
  COUNT(s.id) AS cnt
FROM days dy
LEFT JOIN cf_submission s ON DATE(s.submitted_at) = dy.d
GROUP BY dy.d
ORDER BY dy.d;

预期：06-01 至 06-07 依次 2、1、2、1、2、2、2——每一天都出现，即使当天没有提交（这就是 LEFT JOIN 日期序列的意义，INNER JOIN 会漏掉 0 天）。

任务 5：行转列——每人各难度提交数（条件聚合）

SELECT
  u.username,
  COALESCE(SUM(p.difficulty = 'easy'),   0) AS easy_cnt,
  COALESCE(SUM(p.difficulty = 'medium'), 0) AS medium_cnt,
  COALESCE(SUM(p.difficulty = 'hard'),   0) AS hard_cnt
FROM cf_user u
LEFT JOIN cf_submission s ON s.user_id = u.id
LEFT JOIN cf_problem p    ON p.id = s.problem_id
GROUP BY u.id, u.username
ORDER BY u.id;

预期：lin 1/2/0、zhao 0/2/1、wang 2/0/0、sun 1/0/0、zhou 2/1/0、qian 0/0/0（qian 的三个 0 全靠 COALESCE 兜底）。

任务 6：事务导入 + 中途校验

运营要给 qian 补录两条 06-08 的历史提交，同时导入流程可能混入脏数据——用 SAVEPOINT 做分段提交：

START TRANSACTION;

INSERT INTO cf_submission (user_id, problem_id, language, status, runtime_ms, submitted_at)
VALUES (6, 2, 'java', 'accepted', 30, '2025-06-08 10:00:00'),
       (6, 5, 'java', 'accepted', 15, '2025-06-08 10:03:00');

SAVEPOINT sp_check;

-- 模拟一条混入的脏数据（user_id=999 不存在）
INSERT INTO cf_submission (user_id, problem_id, language, status, runtime_ms, submitted_at)
VALUES (999, 1, 'java', 'accepted', 10, '2025-06-08 10:05:00');

-- 校验：所有提交的 user_id 必须能关联到真实用户
SELECT s.id, s.user_id
FROM cf_submission s
LEFT JOIN cf_user u ON u.id = s.user_id
WHERE u.id IS NULL;
-- 返回刚才那条 999 → 有脏数据，回滚到检查点

ROLLBACK TO sp_check;
COMMIT;

SELECT COUNT(*) FROM cf_submission;   -- 预期 14（12 + 干净的 2 条）

生产上这套「插入 → SAVEPOINT → 校验 → ROLLBACK TO / COMMIT」的节奏由应用代码驱动（校验不通过就整体 ROLLBACK），思想完全一致。

任务 7：优化验证（EXPLAIN 前后对比）

-- 先确认索引存在（第 11 课建的；TRUNCATE 不会删索引）
SHOW INDEX FROM cf_submission;
-- 若没有则补建：CREATE INDEX idx_user_status ON cf_submission (user_id, status);

EXPLAIN
SELECT user_id, status FROM cf_submission
WHERE user_id = 1 AND status = 'accepted';
-- 预期：type=ref、key=idx_user_status、Extra=Using index（覆盖）

EXPLAIN
SELECT * FROM cf_submission WHERE DATE(submitted_at) = '2025-06-01';
-- 预期：type=ALL（函数包裹失效），改写为范围条件后变 range

讲清楚要点

1. 综合任务的开发清单固定五步：建模（表结构）→ 数据校准（可复现的初始数据）→ 分析（聚合/窗口报表）→ 变更（事务包裹的 DML）→ 验证（EXPLAIN + 结果核对）。任何数据需求都套这个流程。
2. 每个任务都要「先小后大」：先用 LIMIT 看几行确认形状，再去掉 LIMIT 出全量；复杂 SQL 用 CTE 分步，每步单独 SELECT 验证后再组合。一步到位写出来的大 SQL 排错成本极高。
3. 报表永远要处理「空」：没人提交的用户（qian）、没提交的日期（补零天）、没命中的枚举（CASE 无 ELSE）——三类空场景各对应 LEFT JOIN、日期序列、COALESCE 三种补法。报表与预期差 1 行，八成是某个空场景没兜住。
4. 「日期 - 行号」分组法值得默写：连续天数、连续登录、连续打卡全是它。记住三段结构：去重日期（CTE days）→ 打行号求差值（CTE streaks）→ 按差值分组取 MIN/MAX/COUNT。
5. 数据变更必须带校验环：本课任务 6 的 SAVEPOINT 用法在生产等价于「应用层先查后插、异常整体回滚」。导入类需求的验收标准是「 COUNT 前后一致 + 校验查询为空」。
6. 写完报表顺手 EXPLAIN：新 SQL 上线前看一眼 type/key/Extra，养成习惯后，多数慢 SQL 根本没机会上生产——这是第 11 课五步法的前置化。

你可能会懵的地方

• ERROR 3636 Recursive query aborted 是什么？ 递归 CTE 超过默认 1000 层深度。检查终止条件是否收敛；确实需要更多层时 SET SESSION cte_max_recursion_depth = 100000;。任务 4 只递归 7 层，报这个错说明终止条件写错了。

• 跑出的结果和文中对不上？ 99% 是数据没重置（前面课程改过 level、插过提交）。回到步骤 0 全量 TRUNCATE + 重新 INSERT，再逐任务核对。

• EXPLAIN 结果和文中不完全一样？ rows 是估算值、不同小版本的优化器策略有差异。看 type/key/Extra 的「等级」是否一致即可，不必逐字节对齐。

• 复杂报表放数据库还是应用层算？ 过滤、聚合、排名这类集合运算数据库更快（数据在哪算在哪）；展示格式化、业务语义翻译放应用层。判断标准：中间结果集大小——结果集大就下推给 SQL（配索引），小就拉回应用层算。

• 为什么任务 4 不直接 GROUP BY 日期？ 直接 GROUP BY 只会出现「有提交的日期」，0 天会消失，报表趋势失真。日期序列 + LEFT JOIN 是所有「补零报表」（每日/每月/每小时）的标准骨架。

错法对照

错法	问题	正解
在生产库上练这些任务	一个忘 WHERE 就是事故	永远在本地练习库复现
不 TRUNCATE 就直接跑任务	与前课数据混染，结果全错	每次重置到初始数据再核对
窗口结果直接写进 WHERE	ERROR 3587	套一层派生表/CTE 再过滤（任务 2 姿势）
大批量导入不 SAVEPOINT 不校验	脏数据与正常数据一起落库	分段提交 + 校验查询（任务 6 姿势）
报表 SQL 无 LIMIT 无范围	数据涨了之后全表扫描	先加时间范围预过滤 + LIMIT 试跑

卡点急救

1. ERROR 3636：递归终止条件失效，检查 WHERE d < 边界 的比较列；或调大 cte_max_recursion_depth。
2. ERROR 1055 not in GROUP BY：综合 SQL 列多了之后容易漏。把 SELECT 里的裸列补进 GROUP BY（按主键分组可覆盖同表其他列）。
3. 结果行数对不上：按顺序执行 SELECT COUNT(*) 核对四张表（6/6/12/9），不对就重置；仍不对就检查是否漏跑了某段 INSERT。

面试常问

• SQL：求每个用户连续刷题的最长天数？ 就用任务 3 的「日期减行号」三段式：日期去重 → ROW_NUMBER 求差值 → 按差值分组 COUNT 取 MAX。能当场默写完整 SQL 并解释每一步，这道题就是满分。

• SQL：输出近 7 天每日提交量，没有数据的天显示 0？ 递归 CTE 生成日期序列 + LEFT JOIN 提交表 + COUNT——任务 4 原样就是答案。追问「为什么不用应用层补零」，答「补零逻辑放 SQL 让聚合语义完整，应用层只管渲染」。

• 给你一条 3 秒的慢 SQL，完整说一遍你的排查动作？ 串讲第 11 课五步法：确认慢在哪层 → EXPLAIN 看执行计划与索引命中 → 定位失效写法或缺失索引 → 改写/补索引/覆盖索引 → 复测验证。能按顺序讲清每一步「看什么、改什么」就是完整闭环。

自测三问

1. 「日期减行号」分组法为什么能识别连续区间？用 06-01、06-02、06-04 三天推一遍。
2. 任务 4 用 LEFT JOIN 的目的与 INNER JOIN 的差异是什么？
3. 任务 6 中 SAVEPOINT 的作用是什么？没有它，校验失败后你会怎么做？

小结一句话

进阶 SQL 的完整闭环 = 正确建模 + 可复现数据 + 聚合/窗口分析 + 事务保一致 + EXPLAIN 保速度——把 7 个任务跑通并核对，本 track 的能力就落袋了。

延伸

• MySQL 8.0 Reference Manual — Optimization
• 下一步：带着 sql_practice 练手库回到真实项目——在本站 Java/Spring 轨道里用 MyBatis 或 JPA 把这套 SQL 落进代码；或转向 PostgreSQL 专项，对比两个数据库的窗口函数与事务行为差异。面试前，把任务 3（连续天数）和第 11 课五步法各默写一遍。