Software Systems Atlas

中文

English

中文

English

主题

4.3 CTE、Grouping Sets 与查询验证：把分析 SQL 当作程序 ​

一张由五层 CTE 生成的报表通过了语法检查，总额却与账本对不上，预言厅决定把分析 SQL 当程序审查。

五层 CTE 能把复杂查询拆清楚，也能把同一个粒度错误传到五层之后。分析 SQL 和普通程序一样，需要接口、测试、执行计划和发布前对账。

本课目标 ​

  • 用 CTE 划分语义阶段而非堆叠别名；
  • 使用 GROUPING SETS、ROLLUP 和 CUBE；
  • 正确连接慢变维度；
  • 用不变量、对照查询和执行计划验证结果与成本。

1. 每个 CTE 只有一个职责 ​

sql
WITH terminal_missions AS (
    -- Grain: one row per terminal mission
    SELECT mission_id, fortress_id, terminal_status, completed_at
    FROM missions
    WHERE terminal_status IN ('completed', 'failed')
),
fortress_daily AS (
    -- Grain: one row per fortress per UTC date
    SELECT
        fortress_id,
        CAST(completed_at AS DATE) AS completed_date,
        COUNT(*) AS attempts,
        SUM(CASE WHEN terminal_status = 'completed' THEN 1 ELSE 0 END) AS successes
    FROM terminal_missions
    GROUP BY fortress_id, CAST(completed_at AS DATE)
),
scored AS (
    SELECT
        *,
        1.0 * successes / NULLIF(attempts, 0) AS completion_rate
    FROM fortress_daily
)
SELECT * FROM scored;

CTE 名称描述业务结果，注释声明粒度，字段显式列出。step1、temp2 和层层 SELECT * 会让 schema 变化悄悄传播。

2. CTE 是否物化取决于数据库 ​

有些优化器内联 CTE，有些版本或选项会物化，有些对递归/多次引用采用不同策略。CTE 首先是查询结构，不应假设它天然提高或降低性能。

查看实际执行计划与扫描字节、shuffle、spill 和运行时间。若中间结果需要多次复用或质量检查，可显式物化为版本化模型；同时承担存储、新鲜度与更新成本。

3. GROUPING SETS 一次表达多个粒度 ​

sql
SELECT
    mission_type,
    fortress_id,
    SUM(cost) AS total_cost,
    GROUPING(mission_type) AS all_mission_types,
    GROUPING(fortress_id) AS all_fortresses
FROM mission_facts
GROUP BY GROUPING SETS (
    (mission_type, fortress_id),
    (mission_type),
    (fortress_id),
    ()
);

ROLLUP(a,b) 通常生成层级组合 (a,b)、(a)、()；CUBE(a,b) 生成所有组合。具体语法和 GROUPING_ID 支持依方言而异。

汇总行用 NULL 占位时，必须用 GROUPING() 区分“所有类别”与源数据真实 NULL。直接 COALESCE(col,'ALL') 会把两者混在一起。

4. 版本化维度需要 as-of join ​

维度表：

text
fortress_id, region, valid_from, valid_to

事实应按事件发生时的版本连接：

sql
SELECT f.mission_id, d.region
FROM mission_facts AS f
JOIN fortress_history AS d
  ON f.fortress_id = d.fortress_id
 AND f.occurred_at >= d.valid_from
 AND f.occurred_at < COALESCE(d.valid_to, TIMESTAMP '9999-12-31 00:00:00');

维表有效区间必须对同一 key 不重叠，否则一条事实匹配多个版本。用测试检查 overlap 和 gap，并明确 valid_to 是包含还是不包含；半开区间更易拼接。

5. 查询测试从小型反例开始 ​

构造最小 fixtures：

  • 一个任务无标签；
  • 一个任务两个标签；
  • 两条同分排名；
  • 某天缺分区；
  • 分母为零；
  • 维度边界恰在 valid_to；
  • NULL key；
  • 迟到事件跨日期回补。

对每个案例手算期望结果。大数据集“数字看起来合理”不是测试。

6. 不变量与对照查询 ​

可验证：

text
聚合前后总成本守恒
成功数 ≤ 尝试数
完成率位于 [0,1]
输出业务键唯一
inner join 丢失数等于明确拒绝数
按分组求和等于总计（无重叠分组时）

为关键指标写一个慢而直白的参考查询，与优化版在样本和近期分区做差分。两者共享同一错误口径时仍可能同时错，因此还要与指标契约和源系统对账。

7. 读执行计划而不是猜性能 ​

关注：

  • 全表扫描还是分区裁剪；
  • join 算法与 build/probe 侧；
  • 实际与估计行数差异；
  • shuffle 数据量；
  • 排序与窗口 spill；
  • 重复扫描同一大表；
  • 过滤是否下推；
  • 数据倾斜 key。

WHERE DATE(timestamp_col)=... 可能阻止某些系统利用原始时间分区或索引。使用半开范围通常更明确：

sql
WHERE occurred_at >= TIMESTAMP '2026-08-01 00:00:00'
  AND occurred_at <  TIMESTAMP '2026-09-01 00:00:00'

是否真正裁剪仍需看具体平台计划。

8. 成本优化不能改变口径 ​

预聚合、近似 distinct、抽样和物化视图都能降成本，却改变新鲜度、精度或可下钻能力。报告应说明：

  • 数据截至时间；
  • 是否近似及误差保证；
  • 刷新频率；
  • 使用了哪个模型版本；
  • 哪些维度不能继续拆分。

不要为了扫描更少字节，悄悄把用户级 distinct 换成事件计数。

9. SQL 发布清单 ​

  1. 每个 CTE 的粒度已注明；
  2. join 基数已验证；
  3. 分子、分母和 NULL 语义已写入契约；
  4. 时间范围使用一致时区与半开边界；
  5. 窗口排序与 frame 明确；
  6. fixtures 和不变量通过；
  7. 与参考指标完成 reconciliation；
  8. 执行计划和资源在预算内；
  9. 输出 owner、新鲜度和版本可追踪。

常见误区 ​

  • CTE 会自动物化或自动优化：行为依数据库和版本。
  • ROLLUP 中的 NULL 都表示总计：源数据 NULL 需用 GROUPING 区分。
  • 维表按 key 等值连接即可：历史事实需要时间版本。
  • 查询更快就算优化成功：指标口径和精度不能悄悄改变。

练习 ​

  1. 把一个五层 CTE 的每层粒度写出来，找出第一次变化的位置。
  2. 用 GROUPING SETS 同时计算地区、类型和总计，并区分真实 NULL。
  3. 构造两个重叠维度版本，观察 as-of join 如何复制事实。
  4. 为完成率查询写最小 fixtures、守恒量和参考实现。

小结 ​

分析 SQL 是可部署的数据程序。CTE 划分语义步骤，grouping sets 表达多粒度，as-of join 维护历史语义；fixtures、不变量、对账与执行计划共同证明它既算对，也算得起。

下一章进入统计推断：从样本差异判断总体差异时，需要把抽样过程、不确定性和决策阈值一起写进结论。

Built with VitePress | Software Systems Atlas

Copyright 2026 Steven Yang