窗口函数与 CTE

这一课你将能做到

1. 用 ROW_NUMBER/RANK/DENSE_RANK 排名，并用「套一层过滤 rn=1」的固定套路取每人 Top-N
2. 用 LAG/LEAD 做行间比较、用 SUM(...) OVER 算累计值，并解释 ROWS 与 RANGE 帧的差异
3. 用 CTE 把多层逻辑写成命名步骤，用 WITH RECURSIVE 生成序列

为什么要学

「每人最快的一次 AC」「按 AC 数排名」「相邻两次提交间隔了多久」——这些需求 GROUP BY 做不到（它会把明细行折叠掉），子查询写得又臭又长。窗口函数是 MySQL 8 最重磅的新能力：保留每一行明细，同时在旁边挂上组内计算结果。它也是现在面试的分水岭题：「找出每个部门工资前三名」没有窗口函数基本写不出优雅解。

人话：GROUP BY 是「把 12 行压成 4 行」，窗口函数是「12 行还在，每行多挂一个数」。想看明细+排行，只能靠它。

先建立图像

GROUP BY：      12 行提交 ──折叠──► 4 行（每人一行）      明细没了
窗口函数：      12 行提交 ──保留──► 12 行，每行多一列      明细还在

         user_id │ runtime_ms │ rn(组内按时间编号)
         ────────┼────────────┼─────
         lin     │ 12         │ 1
         lin     │ 30         │ 2   ← 行没合并，只是"多了个编号"
         lin     │ 25         │ 3
         zhao    │ 48         │ 1
         zhao    │ 2000       │ 2
         ...

CTE 则是把长 SQL 从「嵌套洋葱」改成「带名字的流水线」：

WITH step1 AS (...)   ← 先算每人 AC 数
   , step2 AS (...)   ← 再筛 AC 率
SELECT ... FROM step2 ← 最后出结果

跟着做

步骤 0：数据准备

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 执行第 1 课步骤 4 的四段 INSERT，恢复初始数据

步骤 1：ROW_NUMBER——每人按提交时间编号

SELECT
  user_id,
  runtime_ms,
  submitted_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY submitted_at) AS rn
FROM cf_submission;

预期：每人组内 1、2、3 顺次编号（lin 3 行、zhao 3 行、wang 2 行、zhou 3 行、sun 1 行）。PARTITION BY 是「分组但不合并行」，ORDER BY 决定组内序号怎么排。

步骤 2：RANK vs DENSE_RANK——按 AC 数排名

WITH ac AS (
  SELECT user_id, SUM(status = 'accepted') AS ac_cnt
  FROM cf_submission GROUP BY user_id
)
SELECT
  u.username,
  a.ac_cnt,
  ROW_NUMBER() OVER (ORDER BY a.ac_cnt DESC, u.id) AS rn,
  RANK()       OVER (ORDER BY a.ac_cnt DESC)       AS rk,
  DENSE_RANK() OVER (ORDER BY a.ac_cnt DESC)       AS drk
FROM ac a JOIN cf_user u ON u.id = a.user_id
ORDER BY rk, u.id;

预期结果（lin/zhao/zhou 各 2 AC 并列，wang 1，sun 0）：