工程学习台

主题

主要章节

SQL 手写：先确定粒度，再写 JOIN 与窗口 ​

SQL 笔试考的是“这张结果表的每一行代表什么”。先说明保留哪些实体、一次计数的单位、并列规则和时间口径，再画中间结果，最后落笔。本文以 MySQL 8.0.39 为目标版本；CTE 与窗口函数可用。MySQL 5.7 的替代写法、分布式跨库聚合、存储过程不在主线内。

高级必会是能独立写对七类题，并用空集、并列、NULL 和缺失日期构造反例；架构师进阶是区分查询正确、数据完整与执行成本，说明报表的快照、数据水位和汇总粒度。本篇不要求个人项目经历。B+ 树与计划的权威讲解见索引与 EXPLAIN，并发读的边界见MVCC 与锁。

JOIN 与聚合：先确定保留哪些行 ​

用一份小输入看清空集 ​

完整实验输入采用 pr_ 前缀。pr_departments 有平台、业务、无订单三个部门，pr_users 把用户映射到部门；pr_orders 的金额是整数分，status 的 1、2、3 分别表示支付、退款、取消，created_at 在夹具中是可按字典序比较的固定格式时间。生产模型应使用金额 DECIMAL、时间 DATETIME，不能据此把字符串存时间当成通用建议。

以下是 MySQL 8.0.39 的教学建表，非原始引擎源码；完整可执行夹具为兼容 SQLite 使用简化类型，没有模拟 InnoDB 的索引和外键行为。

sql
CREATE TABLE dept (
  id BIGINT UNSIGNED PRIMARY KEY, name VARCHAR(64) NOT NULL
);
CREATE TABLE users (
  id BIGINT UNSIGNED PRIMARY KEY, dept_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(64) NOT NULL, created_at DATETIME NOT NULL,
  KEY idx_dept (dept_id), KEY idx_created (created_at),
  CONSTRAINT fk_users_dept FOREIGN KEY (dept_id) REFERENCES dept(id)
);
CREATE TABLE orders (
  id BIGINT UNSIGNED PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_user_time (user_id, created_at),
  KEY idx_status_time (status, created_at),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE attendance (
  id BIGINT UNSIGNED PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
  attend_date DATE NOT NULL,
  KEY idx_user_date (user_id, attend_date)
);

attendance 是原始事件表，允许一天重复记录；若另建按用户与日期去重后的日活表，才加 UNIQUE(user_id, attend_date)。不能先依赖唯一键忽略真实日志的重复，再声称算法天然防重。主键确定行身份，复合索引提供候选访问路径，不保证优化器一定采用。

把 JOIN 想成匹配组合，而非真实执行时必须先生成笛卡尔积：无条件 CROSS JOIN 的行数是两表行数之积；INNER JOIN 只保留满足 ON 的组合；LEFT JOIN 在无匹配时仍为每条左行补一条右列全 NULL 的行。RIGHT JOIN 对称，交换两侧可改写为 LEFT JOIN。

左行右侧匹配INNER JOINLEFT JOIN
用户 11两笔已支付订单两行订单两行订单
用户 31没有订单无输出一行用户 + NULL 订单

MySQL 8.0 没有原生 FULL OUTER JOIN。模拟时可写 A LEFT JOIN B ... UNION ALL B LEFT JOIN A ... WHERE A.非空主键 IS NULL：第二支只补右侧未匹配行，避免把已匹配行输出两遍。不是把两种外连接直接 UNION ALL，也不是用 UNION 任意消除原始数据中的合法重复。

ON、WHERE 与 NULL 的三值逻辑 ​

sql
-- 所有用户，以及这些用户的已支付订单；无匹配用户仍保留。
SELECT u.id, o.id AS order_id, o.amount
FROM pr_users u
LEFT JOIN pr_orders o ON o.user_id = u.id AND o.status = 1;

若改成 LEFT JOIN ... ON o.user_id = u.id WHERE o.status = 1，无订单用户的 NULL = 1 是 UNKNOWN，并非 FALSE。WHERE 只保留 TRUE，FALSE 与 UNKNOWN 都被滤掉；这里的右表条件拒绝 NULL，于是失去保留无订单用户的效果。不是任意引用右列的 WHERE 都让外连接退化，例如 WHERE o.id IS NULL 正是在找没有匹配订单的用户。

表达式结果影响
NULL = 1、NULL <> 1、NULL = NULLUNKNOWN不能用 = NULL 找空值
NULL IS NULLTRUE显式检查空值
FALSE AND UNKNOWNFALSE三值逻辑不等于“含 NULL 都是 NULL”
TRUE OR UNKNOWNTRUEWHERE 仍可保留该行

左表筛选也要分清作用：WHERE u.dept_id = 1 筛掉其他部门用户；写在 ON 里只是让其他部门用户匹配不上右表，他们仍会被 LEFT JOIN 保留。

反连接可以写 LEFT JOIN ... WHERE o.id IS NULL，或 NOT EXISTS (SELECT 1 FROM ... WHERE ...)，应按相同匹配谓词比较。别直接换成 NOT IN：夹具黑名单为 (11, NULL)，用户 12 对 12 NOT IN (11, NULL) 的判断是 UNKNOWN，四个用户全部无法通过；NOT EXISTS 则正确返回 12、21、31。若左键允许 NULL，也必须明确希望保留还是排除它，不能泛称两种写法等价。

聚合前先说清“数订单还是数用户” ​

sql
SELECT d.id, COUNT(*) AS joined_rows,
       COUNT(o.id) AS paid_orders, SUM(o.amount) AS paid_amount
FROM pr_departments d
LEFT JOIN pr_users u ON u.dept_id = d.id
LEFT JOIN pr_orders o ON o.user_id = u.id AND o.status = 1
GROUP BY d.id
ORDER BY d.id;

夹具结果为 (1,5,5,37000)、(2,2,2,6000)、(3,1,0,NULL)。第三个部门的 COUNT(*)=1 数的是补 NULL 后的输出行，COUNT(o.id)=0 才是订单数。若该部门有两个无订单用户，补 NULL 行还会变成两行，不能把 COUNT(*) 当作订单数。

  • COUNT(*)、COUNT(1) 数行，结果相同；COUNT(col) 忽略 NULL；COUNT(DISTINCT col) 数非 NULL 的不同值。支付用户数应看 COUNT(DISTINCT o.user_id)，不是订单行数。
  • 无 GROUP BY 的空输入聚合仍输出一行：COUNT 为 0、SUM/AVG 为 NULL；带 GROUP BY 的空输入没有任何组，也就没有结果行。外连接制造的 NULL 行又是第三种情况。只有业务确认“无事实等于零”时才 COALESCE(SUM(...), 0)。
  • WHERE 筛聚合前的行；HAVING AVG(o.amount) > 50000 筛聚合后的组。窗口在这些逻辑步骤之后计算，不减少已有行数；要过滤窗口别名，在外层查询处理。逻辑求值顺序不是物理执行计划。

手画 分组键 → 原始金额集合 → COUNT/SUM/AVG。例如用户 11 的支付金额集合为 {9000,9000}，合计 18000；用户 31 无事实。JOIN 到两个一对多明细表时，可能把一个订单展开多次，先按同一业务粒度分别汇总再连接，不能事后用 DISTINCT amount 补救，因为两个真实订单允许同金额。

派生表、CTE、相关子查询、JOIN 和 EXISTS 各有可读性与语义边界。先聚合常能缩小中间结果，但不能从“用了子查询”推出一定慢、一定物化；是否合并、物化与采用什么访问路径看具体版本和实际计划。COUNT(1) 也不能凭语法证明比 COUNT(*) 快。

七类题一：部门 TopN 与高于均值 ​

旧闭卷题 A 要求每个部门取金额前三笔支付订单，无订单部门也出现。确定“前三笔”后，先给事实排名，再从部门维表左连；对排名结果的限制放 ON 里。

sql
WITH ranked AS (
  SELECT u.dept_id, o.id, o.amount,
         ROW_NUMBER() OVER (
           PARTITION BY u.dept_id ORDER BY o.amount DESC, o.id DESC
         ) AS rn
  FROM pr_orders o JOIN pr_users u ON u.id = o.user_id
  WHERE o.status = 1
)
SELECT d.id AS dept_id, r.id AS order_id, r.amount, r.rn
FROM pr_departments d
LEFT JOIN ranked r ON r.dept_id = d.id AND r.rn <= 3
ORDER BY d.id, r.rn;

部门 1 的结果是订单 102、101、104，部门 3 是 NULL 订单。id DESC 定义同金额的稳定次序，并不证明“较大 ID 必然较晚支付”。外层 ORDER BY 决定展示顺序；窗口里的 ORDER BY 只决定计算顺序。

若要求“金额最高的三个不同档位，保留并列”，使用 DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY amount DESC)，不能把 id 放进这个排序键，否则相同金额不再并列。部门 1 的五笔支付订单全部入选，金额档位是 9000、7000、5000；这是五行，不是三行。

对降序金额 [9000,9000,7000,7000,5000]：

函数名次适用含义
ROW_NUMBER（加唯一次键）1、2、3、4、5最多 N 行
RANK（只按金额）1、1、3、3、5竞赛名次，<=3 为四行
DENSE_RANK（只按金额）1、1、2、2、3前 N 个不同金额档位

“工资高于本部门平均”是另一种窗口形态，映射成订单金额可直接比较每一行与同部门均值：

sql
WITH compared AS (
  SELECT u.dept_id, o.id, o.amount,
         AVG(o.amount) OVER (PARTITION BY u.dept_id) AS avg_amount
  FROM pr_orders o JOIN pr_users u ON u.id = o.user_id
  WHERE o.status = 1
)
SELECT dept_id, id, amount FROM compared
WHERE amount > avg_amount;

平台部门均值 7400 分，只有 101、102 超过；业务部门均值 3000 分，只有 201 超过。等于均值的行不入选。这里平均的是支付订单，不是先算每人平均再平均；部门没有支付事实时也没有可比较的订单。等价替代是按部门 GROUP BY 得到 AVG，再按部门 JOIN 回原订单，不改变平均的粒度。

七类题二：连续日期先去重 ​

旧题 B 的“连续三天登录”按用户和自然日判断，不按事件条数。先按 (user_id, day_ordinal) 去重，再排序编号。夹具的日历维表把相邻自然日映射为连续整数；MySQL 真实日期可改用 DATE_SUB(attend_date, INTERVAL rn DAY) 作为段键。

sql
WITH days AS (
  SELECT DISTINCT user_id, day_ordinal FROM pr_attendance
), numbered AS (
  SELECT user_id, day_ordinal,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day_ordinal) AS rn
  FROM days
)
SELECT n.user_id, MIN(d.attend_date) AS seg_start,
       MAX(d.attend_date) AS seg_end, COUNT(*) AS seg_days
FROM numbered n JOIN pr_days d ON d.day_ordinal = n.day_ordinal
GROUP BY n.user_id, n.day_ordinal - n.rn
HAVING COUNT(*) >= 3
ORDER BY n.user_id, seg_start;

用户 11 的自然日为 [1,2,3,5,8]，序号为 [1,2,3,4,5]，相减得到 [0,0,0,1,3]，前三天形成一段。用户 11 的第 1 天有两条原始事件；去重前编号会让同一天占用两个序号，破坏不变量。实际结果为用户 11 的 10 月 1–3 日、用户 21 的 10 月 2–4 日，各三天。只要用户集合，可在结果外包一层 SELECT DISTINCT user_id；要最长连续天数，就先保留各段长度再按用户 MAX。

MySQL 业务时间戳要先约定时区再投影自然日。连续工作日、排班日与自然日不同，应使用相应业务日历的连续序号；不能把节假日跳过后的业务连续性塞进普通日期差公式。

七类题三：留存必须有观测窗口 ​

旧题 C 在这里定义为日活跃用户在第 1/7 天恰好再次活跃，不是注册 cohort 留存，也不是“七天内至少回来一次”。分母是当日去重日活用户数；分子是同一用户在精确目标日的去重存在性。

设事件已完整采集到 2026-10-10（日序号 10）。10 月 3 日的 D+7 可观察，10 月 4 日的 D+7 尚不可观察；后者必须返回 NULL，不能把未来未到当作流失。水位是采集完整性的保证，不应简单取 MAX(event_date)：某天恰好没人活跃，也可能没有任何日志。

sql
WITH act AS (
  SELECT DISTINCT user_id, day_ordinal FROM pr_attendance
)
SELECT c.day_ordinal, COUNT(a.user_id) AS active_users,
       CASE WHEN c.day_ordinal + 1 <= p.complete_through
            THEN COUNT(b1.user_id) END AS retained_1d,
       CASE WHEN c.day_ordinal + 1 <= p.complete_through
            THEN ROUND(1.0 * COUNT(b1.user_id) / NULLIF(COUNT(a.user_id), 0), 4)
       END AS retention_1d,
       CASE WHEN c.day_ordinal + 7 <= p.complete_through
            THEN COUNT(b7.user_id) END AS retained_7d,
       CASE WHEN c.day_ordinal + 7 <= p.complete_through
            THEN ROUND(1.0 * COUNT(b7.user_id) / NULLIF(COUNT(a.user_id), 0), 4)
       END AS retention_7d
FROM pr_days c CROSS JOIN pr_observation p
LEFT JOIN act a ON a.day_ordinal = c.day_ordinal
LEFT JOIN act b1 ON b1.user_id = a.user_id AND b1.day_ordinal = a.day_ordinal + 1
LEFT JOIN act b7 ON b7.user_id = a.user_id AND b7.day_ordinal = a.day_ordinal + 7
GROUP BY c.day_ordinal, p.complete_through
ORDER BY c.day_ordinal;

日历表保留零日活日期，pr_observation 恰好一行。比率显式 ROUND(..., 4) 到四位小数；数学上的 1/3 在公开输出中为 0.3333，不使用未说明的近似容差掩盖 MySQL DECIMAL 与 SQLite 浮点差异。先去重使两次 JOIN 各至多匹配一行，避免两个回访集合相乘；只给外层 COUNT 加 DISTINCT 可能隐藏巨大中间结果。MySQL DATE 版把整数加法替换为 DATE_ADD(d, INTERVAL 1 DAY) / INTERVAL 7 DAY。

cohort 日序号日活D+1 人数 / 比率D+7 人数 / 比率原因
121 / 0.52 / 1.0两人都在第 8 日回访
222 / 1.00 / 0第 9 日已完整可观察但无回访
331 / 1/31 / 1/3只有用户 12 在第 10 日回访
410 / 0NULL / NULL第 11 日未到观测水位
600 / NULLNULL / NULL零分母；D+7 又不可观察
101NULL / NULLNULL / NULL两个目标日均不可观察

LEAD/LAG 偏移的是记录行而非日期。用户日期 [1,3,8] 的下一行是 3，不能据此说日 1 次日留存；需要显式比较日期，或用上面的精确日期自连接。注册留存应另取注册用户作 cohort，再按目标日匹配日活；滚动七日留存则匹配区间，口径改变后分子也要重新去重。

七类题四：同比环比先补齐月份 ​

旧题 D 只在事实月份上 LAG 会跳过空月。先明确每个日历月一行，再 LAG(1)/LAG(12)，它们才分别代表上一月与去年同月。不要先筛选“今年”再计算同比，否则去年数据已经被删掉。

夹具月序号 1–14 覆盖 2025-01 至 2026-02，事实只有 1 月 100、3 月 50、次年 1 月 200、次年 2 月 0。这里假定源数据完整，没事实的月份代表销售为零；若是采集失败，必须保留“未知”，不能随意 COALESCE 成零。

sql
WITH monthly AS (
  SELECT c.month_ordinal, COALESCE(SUM(s.amount), 0) AS amount
  FROM pr_months c
  LEFT JOIN pr_month_sales s ON s.month_ordinal = c.month_ordinal
  GROUP BY c.month_ordinal
), compared AS (
  SELECT month_ordinal, amount,
         LAG(amount) OVER (ORDER BY month_ordinal) AS mom_prev,
         LAG(amount, 12) OVER (ORDER BY month_ordinal) AS yoy_prev
  FROM monthly
)
SELECT month_ordinal, amount, mom_prev, yoy_prev,
       ROUND(1.0 * amount / NULLIF(mom_prev, 0), 4) AS mom_ratio,
       ROUND(1.0 * (amount - mom_prev) / NULLIF(mom_prev, 0), 4) AS mom_growth,
       ROUND(1.0 * amount / NULLIF(yoy_prev, 0), 4) AS yoy_ratio,
       ROUND(1.0 * (amount - yoy_prev) / NULLIF(yoy_prev, 0), 4) AS yoy_growth
FROM compared
ORDER BY month_ordinal;

增长率是 (本期−基期)/基期，倍数是 本期/基期，不能把两者都叫增长率。2026-01 对 2025-01 的同比为 2 倍、增长 100%；2025-03 的环比基期为补齐后的 2 月 0，结果是 NULL，而不是错拿 1 月 100 算成 −50%。2026-02 金额 0，相对 2026-01 的环比增长率 −100%；同比基期也为 0，仍是 NULL。首 12 个月同比缺少历史，NULL 与零增长不同。

生产中可用日期维表或受控递归 CTE 生成月份，用 created_at >= 月初 AND created_at < 下月月初 归属事实，明确时区及完整月份的截止时间。维度还包含部门时，先生成“部门 × 月”骨架，再按部门 PARTITION BY；只有全局补月不能保证每个部门都不缺月。

七类题五：每组最近一行 ​

旧题 E 的“最近支付订单”应先过滤支付状态，再排序取行。夹具用户 11 的退款与取消事件时间更晚；若先给全部订单排名再过滤 status=1，该用户就被误删。

sql
WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM pr_orders o WHERE status = 1
)
SELECT user_id, id, amount, created_at
FROM ranked WHERE rn = 1
ORDER BY user_id;

实际选择 11→102、12→105、21→202，用户 31 没有支付订单所以不出现；要保留全部用户，改从用户维表 LEFT JOIN ranked，并将 rn=1 放 ON。

MAX(created_at) 后 JOIN 回原表会保留同时间全部订单，不能当作“恰好一行”。也不能在同一 GROUP BY 里分别取 MAX(created_at) 与 MAX(id) 再拼接，因为两个最大值可能来自不同行。禁止窗口函数时，可写反连接：

sql
SELECT o.user_id, o.id
FROM pr_orders o
WHERE o.status = 1
  AND NOT EXISTS (
    SELECT 1 FROM pr_orders n
    WHERE n.user_id = o.user_id AND n.status = 1
      AND (n.created_at > o.created_at
           OR (n.created_at = o.created_at AND n.id > o.id))
  );

它表达“不存在排序键更大的支付行”，因此稳定保留一行。另一种两阶段聚合必须先取每组最大时间，再仅在该时间的候选行中取最大 ID。时间列为空时还需另定 NULL 排序，本文教学数据为 NOT NULL。哪种实现更快仍由实际索引、分布和计划决定。

七类题六：行列转换 ​

部门状态报表的行转列是条件聚合，不是给每个状态写一条会放大的 JOIN。保留无订单部门时从部门表出发：

sql
SELECT d.id,
       COUNT(CASE WHEN o.status = 1 THEN 1 END) AS paid_cnt,
       COUNT(CASE WHEN o.status = 2 THEN 1 END) AS refund_cnt,
       COUNT(CASE WHEN o.status = 3 THEN 1 END) AS cancel_cnt
FROM pr_departments d
LEFT JOIN pr_users u ON u.dept_id = d.id
LEFT JOIN pr_orders o ON o.user_id = u.id
GROUP BY d.id ORDER BY d.id;

结果为平台 (5,1,1)、业务 (2,0,0)、无订单部门 (0,0,0)。COUNT(CASE WHEN ... THEN 1 ELSE 0 END) 错在 0 也非 NULL，所有行都会被数入。若用 SUM，写 SUM(CASE WHEN ... THEN 1 ELSE 0 END)，这时才需要 ELSE 0。

MySQL 8.0 无通用 UNPIVOT；列转行用 UNION ALL。以下 agg 指上面的聚合结果，可放进同一条 WITH 中，完整 SQL 见实验的 unpivot_counts：

sql
SELECT id, 'paid' AS metric, paid_cnt AS metric_value FROM agg
UNION ALL
SELECT id, 'refund', refund_cnt FROM agg
UNION ALL
SELECT id, 'cancel', cancel_cnt FROM agg;

这里使用 UNION ALL 保留每个指标行。结果应有“部门数 × 三种指标”行，包括零值；如果只要非零指标，再在结果外显式筛选。状态种类动态增长时，固定三列的报表契约也要修改，可考虑保留长表给展示层转换。

七类题七：累计阈值与窗口实现 ​

默认 RANGE 会把并列时间一起纳入 ​

OVER (PARTITION BY ... ORDER BY ... frame) 分别确定计算分区、顺序与取数范围。聚合窗口有 ORDER BY 且省略 frame 时，默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，CURRENT ROW 包括与当前行所有窗口排序键相同的 peers。无 ORDER BY 的默认范围覆盖整个分区。ROW_NUMBER、RANK、DENSE_RANK、LAG/LEAD 等看排序或行偏移，不靠给它们增加 frame 来改变名次/偏移。

sql
SELECT user_id, id, amount,
       SUM(amount) OVER (
         PARTITION BY user_id ORDER BY created_at, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS rows_sum,
       SUM(amount) OVER (
         PARTITION BY user_id ORDER BY created_at
       ) AS default_range_sum
FROM pr_orders WHERE status = 1
ORDER BY user_id, created_at, id;
用户 / 订单金额显式 ROWS（时间、ID）默认 RANGE（只有时间）
11 / 1019000900018000
11 / 102，同一时刻90001800018000
21 / 201400040006000
21 / 202，同一时刻200060006000

RANGE 的差异来自 peer 边界，不是随机累加错误；如果业务想按时间点批量累计，这就是正确语义。如果业务想知道“第几笔订单跨阈值”，显式 ROWS 加唯一次键同时定义行次序与 frame；只有 ROWS 而无唯一排序仍不足以确定同时间谁先。

sql
WITH running AS (
  SELECT user_id, id, created_at,
         SUM(amount) OVER (
           PARTITION BY user_id ORDER BY created_at, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS running_sum
  FROM pr_orders WHERE status = 1
), crossed AS (
  SELECT running.*,
         ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY created_at, id
         ) AS rn
  FROM running WHERE running_sum >= 15000
)
SELECT user_id, id, running_sum
FROM crossed WHERE rn = 1 ORDER BY user_id;

用户 11 首次达标的是 102（18000），用户 12 是 105（19000），用户 21 未达标所以无行。旧题的全站累计只需去掉两个 PARTITION BY，但跨用户金额汇总是不同业务口径。题目说“严格超过”用 >，说“达到”用 >=；阈值相等的输入必须能区分二者。

若只要首次达标时刻，在累计结果上 MIN(created_at) 可行；若还要整笔订单，不能随意 MIN(id) 拼接。存在退款负金额时累计会下降，不能用“已达阈值后永远达标”做增量推断；上面从所有达标行里再取最早的查询仍表达首次达到，但阈值事件是否撤销需另外定义。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 是最近三条，不是最近三天；日期有缺口时要改用日期边界或日历补齐。

MySQL 8.0.39 怎样交付一行窗口结果 ​

以下连续原码均来自 mysql/mysql-server 的 mysql-8.0.39，不把教学 SQL 冒充引擎实现。缓冲路径为 BufferingWindowIterator::Read 读取输入、检查分区并缓冲，随后 ReadBufferedRow 调用 process_buffered_windowing_record；后者围绕 Window 保存的 frame、当前输出行、缓存行与 peer 状态计算。这是需要缓冲的窗口路径，不能外推为所有窗口都要缓存整个分区。

原码连续摘录，仅截取局部、未改写：mysql/mysql-server · mysql-8.0.39 · sql/iterators/window_iterators.cc · L1682–L1702。

原码cpp
int BufferingWindowIterator::ReadBufferedRow(bool new_partition_or_eof) {
  bool output_row_ready;
  if (process_buffered_windowing_record(
          thd(), m_temp_table_param, new_partition_or_eof, &output_row_ready)) {
    return 1;
  }
  if (thd()->killed) {
    thd()->send_kill_message();
    return 1;
  }
  if (output_row_ready) {
    // Return the buffered row, and there are possibly more.
    // These will be checked on the next call to Read().
    m_possibly_buffered_rows = true;
    return 0;
  } else {
    // No more buffered rows.
    m_possibly_buffered_rows = false;
    return -1;
  }
}

output_row_ready 为真才向上层返回一行，m_possibly_buffered_rows 提醒下次优先消费现有缓冲；无就绪行返回 −1，由调用者继续读输入。计算错误返回 1；查询被 kill 时发出终止消息，不能仍把缓冲中的“成功结果”向上传递。

原码连续摘录，仅截取局部、未改写：mysql/mysql-server · mysql-8.0.39 · sql/iterators/window_iterators.cc · L770–L785。

原码cpp

  /* Compute lower_limit, upper_limit and possibly unbounded_following */
  if (f->m_query_expression == WFU_RANGE) {
    lower_limit = w.first_rowno_in_range_frame();
    /*
      For RANGE frame, we first buffer all the rows in the partition due to the
      need to find last peer before first can be processed. This can be
      optimized,
      FIXME.
    */
    upper_limit = INT64_MAX;
  } else {
    assert(f->m_query_expression == WFU_ROWS);
    bool lower_within_limits = true;
    // Determine lower border, handle wraparound for unsigned value:
    int64 border =

RANGE 分支记录按值边界的帧位置，源码明确该缓冲实现为了定位最后一个 peer 先缓存分区；ROWS 则进入基于物理行序号的边界计算。这里可推导同一分区较大时可能出现缓冲成本，不能直接推导当前查询落盘、耗时或一定 filesort。Window 中的行号与 frame 状态会随着分区切换重置，防止上一用户的累计串到下一用户。

原码连续摘录，仅截取局部、未改写：mysql/mysql-server · mysql-8.0.39 · sql/iterators/window_iterators.cc · L1234–L1248。

原码cpp
    const int64 prev_last_rowno_in_frame = w.last_rowno_in_range_frame();
    const int64 prev_first_rowno_in_frame = w.first_rowno_in_range_frame();

    /*
      As an optimization, if:
      - RANGE frame specification ends at CURRENT ROW and
      - current_row belongs to frame of previous row,
      then both rows are peers, so have the same frame: nothing changes.
    */
    if (range_to_current_row && current_row >= prev_first_rowno_in_frame &&
        current_row <= prev_last_rowno_in_frame) {
      // Peer set should already have been determined:
      assert(w.last_rowno_in_peerset() >= current_row);
      have_peers_current_row = true;
    } else {

这支优化识别“当前行位于上一帧且上界为 CURRENT ROW”，于是复用同一 peer set 的 frame。它解释为什么同时间 peers 可以得到相同 RANGE 累计值；如果忽略 peer 边界，每行只累到自己，默认 RANGE 就被错误实现成 ROWS。删掉优化并正确重算同一 frame 可以保持结果，却增加计算成本；不能把优化分支与 SQL 语义本身混为一谈。

从手写正确到报表可用 ​

每道题落笔前先说明输入粒度和方言，画分组或排序后的中间表；写完依次检查：空实体是否保留、并列取几行、NULL 表示零还是未知、日期是否完整、JOIN 是否放大。七类 SQL 都应能给出小输入、预期结果和一个使错误写法暴露的反例。

技术案例：月报在 10 月 10 日发布“七日留存”。查询只看实时日志最大日期会把仍在迟到的 10 月 3 日 cohort 当作完整，再把 10 月 4 日以后的 cohort 算成 0。正确发布流程由采集系统给出可靠完整水位，按水位标记可观察 cohort，再用同一数据快照计算分子分母；迟到修订应保留报表版本。若允许较低延迟的预览，应同时标记暂估与完整状态。SQL 本身无法凭空证明日志完整。

数据变大后，比较原始事件 JOIN、预先日活去重表、部门月汇总三种方案：去重表减少重复读取但要处理迟到修订，汇总表减少扫描却可能丢失明细解释能力；跨分片 TopN 要先局部候选再全局合并，跨分片均值要保留 SUM 与 COUNT，不能平均各分片 AVG。容量、快照和维护成本需要慢 SQL 治理中的真实计划证据，本文夹具只证明给定数据上的结果。

写法层的索引自查 ​

题面写法更可验证的改写 / 说明
DATE(created_at)='2026-10-01'、YEAR(created_at)=2026普通原列索引难以直接构造函数值范围；改为半开区间 >= '2026-10-01' AND < '2026-10-02'，或年度 [2026-01-01,2027-01-01)。确有函数索引/生成列索引时另行判断。
联合索引 (user_id,created_at)，只限制时间缺首列通常不能形成普通连续前缀范围；MySQL 特定条件可能用 skip scan，不能断言绝不访问索引。根据实际过滤与排序选择索引。
VARCHAR 手机号与数字常量比较隐式数值转换改变语义且可能使字符串索引无法有效定位；按列类型绑定字符串参数，不把自动转换当优化。
name LIKE '%ao'普通 B+ 树缺少可用前缀范围，但仍可能被选作全索引扫描；LIKE 'gao%' 可形成候选前缀范围。后缀/全文检索另选匹配语义与索引。

这些是检查候选路径的线索，不是“索引失效免检清单”。是否扫描索引、形成范围、覆盖、排序或回表是不同问题；写 EXPLAIN / EXPLAIN ANALYZE 验证，不凭 SQL 外形宣布快慢。BETWEEN 两端都包含，时间上界用 <= 当天23:59:59 会漏微秒或混入下个边界，优先半开区间。

公开实验：实际执行的结果与限制 ​

在仓库根目录运行，不依赖 MySQL 服务：

实验代码bash
python3 labs/mysql/verify_practice.py

校验入口将同一份 SQL放入内存 SQLite 执行，把 15 个命名结果与独立手算的字面量逐行比较，比率先由 SQL 统一舍入四位小数，再规范 MySQL Decimal 返回类型并严格比较，同时检查 NULL、顺序和行数，不使用宽泛容差。它不重写查询、不返回预置结果、不使用数据库 mock。自定义输入可用 --sql /path/to/candidate.sql；结果不符、SQL 错误、查询缺失均非零退出。

2026-10-23 已运行：最初入口缺失退出 2，随后 SQL 输入缺失退出 1；按反例补齐后，SQLite 3.54.0 的 15 项全部 PASS。下面是实际汇总，不是 MySQL 执行输出：

text
PASS window_frames (7 rows)
PASS first_threshold (2 rows)
PASS null_logic (1 rows)
PASS not_in_null (0 rows)
PASS not_exists_null (3 rows)
PASS consecutive_days (2 rows)
PASS retention (10 rows)
PASS monthly_change (14 rows)
PASS top_three_rows (6 rows)
PASS top_three_prices (8 rows)
PASS above_average (3 rows)
PASS latest_paid (3 rows)
PASS pivot_counts (3 rows)
PASS unpivot_counts (9 rows)
PASS join_counts (3 rows)
PASS 15 checks; SQLite 3.54.0 semantics only; MySQL 8.0.39 execution and optimizer plans NOT verified

上面的默认 CLI 是 SQLite 语义夹具。整数分、日期/月序号刻意避开日期函数、DECIMAL 精度及类型转换差异；SQLite 的通过不能证明 MySQL 语法、ONLY_FULL_GROUP_BY、字符排序规则、执行计划、锁、隔离级别或性能。

同日另通过本机真库入口实际连接用户提供的 MySQL 8.4.7：/tmp/t16-mysql-client/bin/python labs/mysql/run_local.py --mode practice > output/t16-local-practice.json。它调用同一 verify_mysql(connection)，在新建的 study_t16_20261023_56c95f27_practice 中执行同一份 practice.sql，15/15 项全部 PASS，并在 finally 删除该临时库；输出记录的 cleaned_schemas 与该库一致。没有清空既有 study 或业务数据，也没有安装/修改数据库服务。复跑环境及可选客户端依赖见实验说明。

MySQL 实测与 SQLite 均使用四位小数契约，不通过放大浮点容差让两端勉强相等。这份结果只证明 8.4.7、该夹具、所列 SQL 的输出符合独立预期，不代表 8.0.39 已实测，也不是 SQL 性能、并发隔离或优化器的验收。本机 Docker daemon 不可用，固定基线 MySQL 8.0.39 尚未运行；本篇窗口源码仍固定到 8.0.39，版本边界保留。

有隔离的 MySQL 8.0.39 测试库后，可把 practice.sql 交给该版本 mysql CLI 执行；它会重建 pr_* 夹具表，不能指向业务数据库。先记录 SELECT VERSION() 与 @@sql_mode，再保存全部结果并与同一独立预期对照；这仍不同于 10 万行索引实验的计划验证。固定镜像命令 cd labs/mysql && ./run.sh 的 8.0.39 容器路径仍待可用环境执行；本机 8.4.7 的独立计划/事务实验见实验说明，各自的结果不互相替代。

练习的可检查产物是：表与约束说明、JOIN 三种中间结果、七类最终 SQL、各自反例和实际输出。可选口述时回答六个追问：COUNT(*) 与 COUNT(1) 能否从语法比快慢；左表条件放 ON 会保留谁；窗口排序能否凭代码证明免排序；RANK 与 DENSE_RANK 的排名契约；LEAD 为什么不是自然日偏移；重复签到如何破坏日期减序号。能写对但解释不了反例，仍需要回查；阅读完成与自评都不等于客观掌握。

闭卷验收 ​

本页 8 道闭卷题收进了下方自测面板：先回忆，再展开参考答案与评分点，最后给三档自评。

来源与相关讲解 ​

工程学习台　阅读讲解，学后自主自测。记录保存在本机浏览器。