第4章笔记：SQL 进阶：连接、聚合与分析

本章不是七个并列名词

用连接、聚合、子查询、CTE 和窗口函数表达真实分析问题，并核对重复与 NULL。 学习顺序从《内连接与外连接的保留侧》开始，到《综合 SQL：先定义粒度再编码》闭合。每一节都要留下下一节能直接使用的对象：模式、关系、查询结果、页、计划、事务状态、日志记录或部署证据。若你只能逐条背定义，却说不清前一节输出怎样成为后一节输入，这一章还没有真正连起来。

七节依赖与例题

1. 内连接与外连接的保留侧

要解决的问题： 内连接只保留匹配对，外连接保留指定一侧的未匹配行；把右表过滤写进 WHERE 可能把左连接悄悄变回内连接。

跟着做： 列出所有课程及选课人数时用 course LEFT JOIN enrollment，并在 COUNT 中数 enrollment 的非空键。

验收规则： 外连接过滤右表条件通常放 ON；COUNT(*) 与 COUNT(right.id) 在空匹配组上不同。

2. 多表连接与重复放大

要解决的问题： 一对多再连接另一张一对多表会产生乘法放大；聚合前必须明确结果粒度并先分别汇总。

跟着做： 订单同时连接订单项和支付记录会把金额重复累加，应先把 item 按 order 汇总、payment 按 order 汇总，再回连订单。

验收规则： 若 A 对 B 平均 b 条、对 C 平均 c 条，直接三表连接约产生 |A|·b·c 行。

3. GROUP BY 与 HAVING

要解决的问题： GROUP BY 定义结果粒度，聚合函数压缩每组；WHERE 过滤原始行，HAVING 过滤聚合后的组。

跟着做： 找至少选 3 门且平均分≥85 的学生：WHERE 排除无效成绩，按 student_id 分组，再 HAVING COUNT 与 AVG。

验收规则： SELECT 中非聚合列必须由分组键函数决定；先写一句‘一行代表什么’。

4. 相关子查询与 EXISTS

要解决的问题： EXISTS 关心证据是否存在而非返回多少列；相关子查询对外层每个候选绑定参数，优化器常可改写为半连接。

跟着做： 找至少有一门挂科的学生用 EXISTS；找从未挂科用 NOT EXISTS，能自然避开 NOT IN 遇 NULL 的陷阱。

验收规则： EXISTS 子查询的 SELECT 列无语义作用；NOT EXISTS 是可靠的反存在表达。

5. CTE 与递归查询

要解决的问题： CTE 为复杂查询命名中间关系，递归 CTE 由锚点和递归步组成，并必须保证终止或限制深度。

跟着做： 沿组织树查某经理全部下属：锚点选直接对象，递归步连接 parent_id，额外维护 path 检测脏数据环。

验收规则： R_0 为锚点，R_{k+1}=step(R_k)；到新增集合为空时达到不动点。

6. 窗口函数保留明细粒度

要解决的问题： 窗口函数在不折叠行的前提下计算分区排名、累计和与前后值；PARTITION BY 与 ORDER BY 分别定义组和序。

跟着做： 求每个部门薪资前 3 名用 DENSE_RANK；若并列都应保留，不能用任意 LIMIT 3。

验收规则： 窗口值 = f(当前行所属分区及窗口框架)，ROWS 与 RANGE 的同值行边界不同。

7. 综合 SQL：先定义粒度再编码

要解决的问题： 复杂 SQL 应分阶段写出候选集、事实汇总、资格判断和最终展示，每层都有可单独运行的断言。

跟着做： 生成月度留存表：先定义 cohort 月，再定义活跃月，计算月份差并按 cohort 聚合；不要直接在事件明细上盲目 COUNT。

验收规则： 每个 CTE 写清主键候选、行代表的对象与预期行数，最终再格式化比例。

章内共同推理方法

先写“一行或一个状态代表什么”，再写它必须满足的键、约束、顺序或故障假设。遇到 SQL，先定结果粒度和重复/NULL 语义，再编码；遇到存储与优化，先估算页数、基数和 I/O，再看真实执行计划；遇到事务与分布式，先画时间线和允许历史，再讨论隔离级别、日志或共识。任何公式都要带单位、数据分布和适用边界。

可复现练习

从本章七个例题中任选两个，用 SQLite 或课程给定模型从空环境重做。保存建表/输入、执行步骤、实际输出和断言；随后故意加入一个重复键、NULL、并发交错、崩溃点、倾斜分布或网络分区，记录第一个被破坏的不变量。只截成功界面、只贴 SQL 或只报告耗时不算完成。

闭卷验收

用十分钟画出本章七节箭头图；任选一条箭头解释传递的具体字段、状态或证据。再为《综合 SQL：先定义粒度再编码》写一个最小失败案例，并追溯它需要《内连接与外连接的保留侧》中的哪条定义才能修复。最后列出三道题：一道唯一答案判断、一道多条件选择、一道必须计算或写 SQL/状态轨迹的问题，且每题都写清为什么其他答案错。