Software Systems Atlas

中文

English

中文

English

主题

第 12 卷 / 第 4 章 / 第 3 课

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

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

本课目标 ​

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

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

以下样本在新的 DuckDB 1.5 连接中运行。terminal_at 为成功与失败共有的带时区终态时间；事实表和维表中不带时区的时间戳在本例明确约定为 UTC。

sql
CREATE TEMP TABLE missions AS
SELECT * FROM (VALUES
 ('M1','S1','completed', TIMESTAMPTZ '2026-08-01 23:30:00+00'),
 ('M2','S1','failed', TIMESTAMPTZ '2026-08-01 23:40:00+00'),
 ('M3','S1','completed', TIMESTAMPTZ '2026-08-02 00:00:00+00')
) AS t(mission_id,system_id,terminal_status,terminal_at);
CREATE TEMP TABLE mission_facts AS
SELECT * FROM (VALUES
 ('M1','survey','S1',10,TIMESTAMP '2026-08-01 23:30:00'),
 ('M2',NULL,'S1',20,TIMESTAMP '2026-08-01 23:40:00'),
 ('M3','survey','S1',30,TIMESTAMP '2026-08-02 00:00:00')
) AS t(mission_id,mission_type,system_id,cost,occurred_at);
CREATE TEMP TABLE system_history AS
SELECT * FROM (VALUES
 ('S1','north',TIMESTAMP '2026-08-01 00:00:00',TIMESTAMP '2026-08-02 00:00:00'),
 ('S1','south',TIMESTAMP '2026-08-02 00:00:00',NULL)
) AS t(system_id,region,valid_from,valid_to);
sql
WITH terminal_missions AS (
    -- Grain: one row per terminal mission; terminal_at is required
    SELECT mission_id, system_id, terminal_status, terminal_at
    FROM missions
    WHERE terminal_status IN ('completed', 'failed')
),
system_daily AS (
    -- Grain: one row per system per UTC terminal date
    SELECT
        system_id,
        CAST(terminal_at AT TIME ZONE 'UTC' AS DATE) AS terminal_date,
        COUNT(*) AS attempts,
        SUM(CASE WHEN terminal_status = 'completed' THEN 1 ELSE 0 END) AS successes
    FROM terminal_missions
    GROUP BY system_id, CAST(terminal_at AT TIME ZONE 'UTC' AS DATE)
),
scored AS (
    SELECT system_id, terminal_date, attempts, successes,
           1.0 * successes / NULLIF(attempts, 0) AS completion_rate
    FROM system_daily
)
SELECT system_id, terminal_date, attempts, successes, completion_rate
FROM scored
ORDER BY system_id, terminal_date;

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

结果为 8 月 1 日两次终态、一成功，8 月 2 日一次终态、一成功。成功专用的完成时间无法正确归属失败记录；终态时间缺失应触发质量规则，不能靠过滤悄悄消失。带时区时间戳先转换到指标时区再取日期，避免会话设置把事件挪到另一天。

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

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

例如 PostgreSQL CTE 文档区分无副作用的非递归查询、多次引用以及 MATERIALIZED / NOT MATERIALIZED。这些选项和默认行为属于具体引擎，需结合版本查看计划。

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

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

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

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

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

样本中真实空任务类型的小计为 20，GROUPING(mission_type)=0；总计为 60，两个 grouping 标记均为 1。把所有输出行再相加，会把多个汇总层级一起算入。k 个维度的 CUBE 可包含 2^k 个 grouping sets，输出与成本会很快增长。参见 DuckDB 分组汇总文档。

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

维度表：

text
system_id, region, valid_from, valid_to

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

sql
SELECT f.mission_id, d.region
FROM mission_facts AS f
JOIN system_history AS d
  ON f.system_id = d.system_id
 AND f.occurred_at >= d.valid_from
 AND (d.valid_to IS NULL OR f.occurred_at < d.valid_to);

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

M3 恰好发生在边界时刻，只匹配新的 south 版本；开放结束时间用 IS NULL 表达，不依赖遥远未来的哨兵日期。这个内连接会丢掉没有有效维度版本的事实，需要另行对账，或改为左连接并保留匹配标记。有效时间回答当时适用哪个版本；若要重建当时系统已知的信息，还需要记录时间的历史。

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'

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

EXPLAIN 展示计划，EXPLAIN ANALYZE 会实际执行查询以收集运行数据。小型本地样本验证的是语义，不能证明生产环境的分区裁剪和分布式资源成本。

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