Software Systems Atlas

中文

English

中文

English

主题

第 12 卷 / 第 4 章 / 第 1 课

4.1 分析粒度、连接基数与指标分母：SQL 先防止算重 ​

分析型 SQL 的第一技能不是窗口函数，而是随时知道“当前结果一行代表什么”。

本课目标 ​

  • 为每个 CTE 写明结果粒度；
  • 预测一对一、一对多和多对多连接后的行数；
  • 正确使用 COUNT(*)、COUNT(col) 与条件聚合；
  • 为比率固定 cohort、分子、分母和时间归属。

1. 在查询开头声明粒度 ​

在新的 DuckDB 1.5 连接中运行以下准备代码，再执行本课查询。完整查询可直接运行，独立表达式另作片段说明；其他引擎可能需要调整语法。

sql
CREATE TEMP TABLE missions AS
SELECT * FROM (VALUES
 ('M1', 'survey', 'completed', TIMESTAMP '2026-08-01 10:00:00', 'S1', 10, 'U1'),
 ('M2', 'repair', 'failed', NULL, 'S1', 20, 'U1'),
 ('M3', 'survey', 'running', NULL, 'S2', 30, NULL)
) AS t(mission_id, mission_type, terminal_status, completed_at, system_id, cost, user_id);
CREATE TEMP TABLE mission_tags AS
SELECT * FROM (VALUES ('M1','hazardous'), ('M1','priority')) AS t(mission_id,tag);
CREATE TEMP TABLE mission_events AS
SELECT * FROM (VALUES ('M1','e1'), ('M1','e2'), ('M2','e3')) AS t(mission_id,event_id);
CREATE TEMP TABLE mission_costs AS
SELECT * FROM (VALUES ('M1',4), ('M1',6), ('M2',20)) AS t(mission_id,amount);
CREATE TEMP TABLE systems AS
SELECT * FROM (VALUES ('S1','north'), ('S2','south')) AS t(system_id,region);
sql
-- Grain: one row per mission_id
SELECT
    mission_id,
    mission_type,
    terminal_status,
    completed_at
FROM missions;

其他常见粒度：

text
one row per event_id
one row per mission_id per calendar day
one row per account per invoice
one row per experiment assignment

聚合和连接可能改变粒度；单独的窗口函数保留输入行，后续筛选才可能改变结果集合。每个 CTE 写明粒度，再验证键确实满足承诺，比最后发现数字翻倍更便宜。

2. 连接前预测基数 ​

假设：

text
missions：每个 mission_id 一行
mission_tags：每个 mission_id 可有多行
sql
SELECT m.mission_id, m.cost, t.tag
FROM missions AS m
JOIN mission_tags AS t USING (mission_id);

结果粒度变成“每个任务每个标签一行”。此时直接 SUM(m.cost) 会按标签数重复成本。

样本中 M1 有两个标签，内连接输出两行，成本被加成 20；无标签的 M2、M3 同时消失。下面先聚合再左连接，保留总成本 60、危险任务成本 10。算重和漏掉未匹配行，是两笔不同的错账。

如果目标是判断任务是否含危险标签，可先聚合到任务粒度：

sql
WITH tag_flags AS (
    -- Grain: one row per mission_id
    SELECT
        mission_id,
        MAX(CASE WHEN tag = 'hazardous' THEN 1 ELSE 0 END) AS is_hazardous
    FROM mission_tags
    GROUP BY mission_id
)
SELECT
    SUM(m.cost) AS total_cost,
    SUM(CASE WHEN f.is_hazardous = 1 THEN m.cost ELSE 0 END) AS hazardous_cost
FROM missions AS m
LEFT JOIN tag_flags AS f USING (mission_id);

3. 用唯一性检查验证假设 ​

sql
SELECT mission_id, COUNT(*) AS rows_per_key
FROM missions
GROUP BY mission_id
HAVING COUNT(*) > 1;

生产模型可在表约束或数据测试中声明 uniqueness。查询作者仍需确认连接键是否完整：只按 customer_id 连接一张按 (customer_id, valid_from) 版本化的维表，会产生多版本匹配。

重复键查询不能证明键非空：只有一个空键时，它也会通过。还需单独检查 mission_id IS NULL。同一个键左侧 m 行、右侧 n 行，等值连接会形成 m×n 个匹配对；列名相同或结果去重，都不能证明实体身份。

SELECT DISTINCT 不能修复未知重复来源。它可能掩盖错误 join，也可能错误合并两条数值恰好相同的合法事件。

4. COUNT 的空值语义 ​

sql
SELECT COUNT(*) AS rows,
       COUNT(completed_at) AS completion_times,
       COUNT(DISTINCT user_id) AS known_users
FROM missions;

要计算完成任务数，若完成时间可能晚到或格式错误，COUNT(completed_at) 与状态口径不一定一致。应依据指标契约明确使用哪个字段。

条件聚合的可移植写法：

sql
SELECT SUM(CASE WHEN terminal_status = 'completed' THEN 1 ELSE 0 END) AS completed
FROM missions;

某些方言支持 COUNT(*) FILTER (WHERE ...)，更简洁但不是所有系统都有。

样本的三个计数依次为 3、1、1。无 GROUP BY 的空输入上，COUNT 返回 0，SUM 返回 NULL；有 GROUP BY 时，输入中不存在的组连一行都不会产生。若报表要求每个系统都有行，需要完整的系统清单。

5. 比率先计算分子和分母 ​

sql
WITH terminal_missions AS (
    -- Grain: one row per terminal mission
    SELECT mission_id, system_id, terminal_status
    FROM missions
    WHERE terminal_status IN ('completed', 'failed', 'cancelled')
)
SELECT
    system_id,
    SUM(CASE WHEN terminal_status = 'completed' THEN 1 ELSE 0 END) AS completed,
    COUNT(*) AS terminal,
    1.0 * SUM(CASE WHEN terminal_status = 'completed' THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0) AS completion_rate
FROM terminal_missions
GROUP BY system_id;

保留 completed 与 terminal，不要只输出 rate。NULLIF 避免零分母错误；返回 NULL 表示无可定义比率，比默认为 0 更准确。

是否把 cancelled 放进分母属于业务定义，不是 SQL 技巧。

此查询统计所给快照中的终态任务：S1 为 1/2=50%，S2 没有终态组，因此不出现。它不是按创建批次计算的完成率。生产指标还要声明观察截止时间、时区、迟到终态的处理，以及时间范围按创建时间还是终态时间筛选。

6. 先聚合再连接或先连接再聚合 ​

两种顺序不总等价。若两张事实表分别含任务事件与成本明细，直接按 mission 连接会形成 m×n 行。

sql
WITH event_by_mission AS (
    SELECT mission_id, COUNT(*) AS event_count
    FROM mission_events
    GROUP BY mission_id
),
cost_by_mission AS (
    SELECT mission_id, SUM(amount) AS total_cost
    FROM mission_costs
    GROUP BY mission_id
)
SELECT *
FROM event_by_mission
JOIN cost_by_mission USING (mission_id);

先把两边都聚合到共同粒度，再连接，避免扇出。

M1 的两条事件直接连接两条成本明细，会得到四行并把成本加成 20；预聚合后为 event_count=2、total_cost=10。这里的内连接仅保留两侧都有数据的任务；若要保留全部任务，应从 missions 开始做左连接。

7. NULL 在连接中需要显式解释 ​

NULL = NULL 的结果是 unknown，不是 true 或 false，因此普通等值连接不会匹配两个空键。IS NOT DISTINCT FROM 可提供把空值视为相等的比较，但未知客户不应因此全部连成同一个客户。PostgreSQL 比较运算文档说明了这种三值逻辑。

LEFT JOIN 后在 WHERE 过滤右表列：

sql
SELECT m.mission_id, f.region
FROM missions AS m
LEFT JOIN systems AS f ON m.system_id = f.system_id
WHERE f.region = 'north';

会删除未匹配行，效果接近 inner join。若目标是保留所有任务并只连接北区维度，应把条件放在 ON，或显式处理 NULL。

对这个谓词，结果具有内连接语义。把 f.region = 'north' 移到 ON，M3 会保留但维度字段为空；在 WHERE 中补 OR f.region IS NULL 通常不等价，因为 M3 已经匹配 south 行，仍会被删除。

8. 逻辑执行顺序帮助读查询 ​

概念顺序：

text
FROM / JOIN
→ WHERE
→ GROUP BY
→ HAVING
→ window functions
→ SELECT
→ ORDER BY
→ LIMIT

具体优化器可重排执行，但必须保持语义。理解逻辑顺序可解释为什么大多数方言不能在同层 WHERE 中引用窗口结果，也为什么聚合前后的过滤位置不同。

常见误区 ​

  • join key 名字相同就能安全连接：还需确认唯一性和时间版本。
  • DISTINCT 能修复重复：它常掩盖粒度错误。
  • COUNT(col) 等于行数：NULL 不计入。
  • 成功率只需要一个除法：cohort、状态和时间口径决定分子分母。

练习 ​

  1. 为任务、标签、成本三张表分别写粒度，并预测直接连接行数。
  2. 构造一条 LEFT JOIN + WHERE right.col 意外丢行的查询并修复。
  3. 比较 COUNT(*)、COUNT(status) 和 COUNT(DISTINCT mission_id)。
  4. 为完成率查询写至少五项口径说明。

小结 ​

分析 SQL 的正确性从粒度开始。连接会改变行身份，聚合会改变分母，NULL 会改变匹配与计数。只有把这些变化写清楚，窗口函数才是在正确事实之上计算。

下一课专门处理窗口：排序是否唯一、frame 包含哪些行，以及“前一行”为何未必是“前一天”。

Built with VitePress | Software Systems Atlas

Copyright 2026 Steven Yang