Software Systems Atlas

中文

English

中文

English

主题

4.2 窗口函数与时间序列 SQL：排序、Frame 与缺失日期 ​

窗口函数保留明细行，同时在相关行集合上计算排名、累计和或滞后值。危险也正在这里：查询结果看起来每行都合理，但一个未写明的默认 frame 或并列顺序会悄悄改变数字。

以下示例采用接近标准 SQL 的语法；日期函数和 QUALIFY 等能力需按具体数据库调整。

本课目标 ​

  • 区分 partition、order 与 frame；
  • 正确使用排名、累计、移动窗口与 LAG；
  • 处理并列排序和缺失日期；
  • 避免平均比率与重复窗口表达式造成错误。

1. 三个维度分别决定什么 ​

sql
SUM(resources_used) OVER (
    PARTITION BY mission_id
    ORDER BY log_date, log_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
  • PARTITION BY：哪些行互相可见；
  • ORDER BY：分区内先后顺序；
  • frame：对当前行究竟取排序后的哪些行。

忘记 partition 会跨所有任务累计。忘记唯一 tie-breaker，则同日期多行顺序可能不确定。

2. 累计和显式写 frame ​

sql
SELECT
    mission_id,
    log_date,
    log_id,
    resources_used,
    SUM(resources_used) OVER (
        PARTITION BY mission_id
        ORDER BY log_date, log_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM mission_logs;

带 ORDER BY 时默认 frame 依方言和类型而异，常涉及 RANGE 与 peer rows。为了可读和跨版本稳定，累计、移动计算显式写 ROWS 或所需 frame。

3. ROWS、RANGE 与 GROUPS ​

  • ROWS 按物理排序行数取窗口；
  • RANGE 按排序值范围和 peers 处理；
  • GROUPS 按相同排序键的 peer group 计数。
sql
AVG(daily_total) OVER (
    ORDER BY calendar_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

表示当前行和前六行，不必然是七个日历日。如果数据缺少日期，它只是七个有记录的日期。

真正七日窗口可先补齐日历表，或使用数据库支持的日期 RANGE 语法。不同引擎对 interval frame 支持不同。

4. 建日历骨架区分缺失与零 ​

sql
WITH calendar AS (
    -- 使用平台日历表或对应方言的日期生成函数
    SELECT calendar_date
    FROM dim_calendar
    WHERE calendar_date BETWEEN DATE '2026-07-01' AND DATE '2026-07-31'
),
daily AS (
    SELECT log_date, SUM(resources_used) AS total_resources
    FROM mission_logs
    GROUP BY log_date
)
SELECT
    c.calendar_date,
    d.total_resources
FROM calendar c
LEFT JOIN daily d ON d.log_date = c.calendar_date
ORDER BY c.calendar_date;

是否把 NULL 转成 0 要看：无行代表零使用，还是管道缺数。可与分区完整性表连接，只有确认数据已完整到达时才填零。

5. 排名函数处理并列不同 ​

sql
ROW_NUMBER() -- 每行唯一序号，并列也强行区分
RANK()       -- 并列同名次，后续有跳号
DENSE_RANK() -- 并列同名次，后续不跳号
sql
ROW_NUMBER() OVER (
    PARTITION BY fortress_id
    ORDER BY score DESC, mission_id
)

若要求确定地选每组一行，添加稳定 tie-breaker。若业务要求并列冠军，则使用 RANK 并保留全部 rank=1，不能用任意 ROW_NUMBER 偷选一个。

6. LAG 看的是前一行 ​

sql
LAG(resources_used) OVER (
    PARTITION BY mission_id
    ORDER BY log_date
)

得到上一条排序记录，不保证是前一天。日期缺口、同日多行和迟到回补都会改变含义。

先聚合到每日粒度并补齐日历，再 LAG，才能定义“昨日变化”。还应防止前值为零：

sql
(current_value - previous_value) / NULLIF(previous_value, 0)

首行没有前值，应保持 NULL；把它填 0 会制造虚假的增长。

7. 移动比率不要平均每日比率 ​

错误倾向：

sql
AVG(daily_success_rate) OVER (...)

若每天分母不同，这会给每一天相同权重。七日总体成功率应分别滚动分子和分母：

sql
SUM(successes) OVER (
    ORDER BY calendar_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
/
NULLIF(
    SUM(attempts) OVER (
        ORDER BY calendar_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ),
    0
)

宏平均也可能是正确目标，例如每个团队等权。关键是明确权重，而不是默认对 rate 求平均。

8. 重用窗口定义 ​

某些方言支持命名窗口：

sql
SELECT
    resources_used,
    LAG(resources_used) OVER mission_order AS previous,
    SUM(resources_used) OVER mission_order AS running_total
FROM mission_logs
WINDOW mission_order AS (
    PARTITION BY mission_id
    ORDER BY log_date, log_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

但 LAG 对 frame 的处理可能与聚合窗口不同，方言行为要确认。为避免复制表达式，可先在 CTE 计算 previous，外层再计算 change。

9. 窗口结果过滤 ​

标准逻辑顺序中，窗口结果晚于 WHERE，常用子查询：

sql
WITH ranked AS (
    SELECT
        m.*,
        ROW_NUMBER() OVER (
            PARTITION BY fortress_id
            ORDER BY score DESC, mission_id
        ) AS row_num
    FROM missions m
)
SELECT *
FROM ranked
WHERE row_num = 1;

部分系统提供 QUALIFY，但迁移查询时不要假设所有方言支持。

常见误区 ​

  • 窗口 ORDER BY 自动稳定：并列键需要业务 tie-breaker。
  • ROWS 6 PRECEDING 就是过去七天：它计算七行。
  • LAG 一定是昨日值：它只是排序后的上一行。
  • 移动平均率可直接平均各日率：分母不同会改变权重。

练习 ​

  1. 构造同日两条记录，比较有无 tie-breaker 的 ROW_NUMBER。
  2. 在缺两个日期的数据上比较七行窗口与七日窗口。
  3. 写出 RANK 与 DENSE_RANK 对 [100,100,90] 的结果。
  4. 用滚动分子分母重写七日成功率。

小结 ​

窗口函数的结果由分区、排序和 frame 共同决定。显式 frame、唯一排序和日历骨架能消除大量“结果看起来对”的隐性错误。

下一课把多步查询组织成可测试模型，并讨论 grouping sets、版本化维表、执行计划和查询质量门禁。

Built with VitePress | Software Systems Atlas

Copyright 2026 Steven Yang