窗口函数最新进展

Richard Wesley

2025年2月10日 · 8 分钟

背景

最初，关系数据处理模型完全围绕集合展开。这是 Codd 的伟大创见，多年来，关系处理很少关注数据排序。

但事实证明，许多分析操作都与排序有关。例如，在传统 SQL 查询中平滑时间序列中的噪声非常困难——因为它涉及带有不等式条件的自连接！因此，在1990年代后期，数据库厂商开始添加窗口操作。通过明确用户的意图，这些操作可以更高效地实现，这些操作最终被 添加到了 SQL:2003 标准中。

DuckDB 从早期就支持窗口函数，但如果您是新手，您可能希望从我早前关于 DuckDB 中的窗口函数 和 快速移动整体聚合 的博客文章开始，或者直接查阅 窗口函数文档。在本文中，我将首先介绍最近添加的功能。在后续文章中，我将深入探讨其内部机制，讨论一些性能和扩展性改进。

本文中的示例将主要使用一张名为 results 的运动成绩表

GROUPS 帧定义

除了 ROWS 和 RANGE 帧边界类型（我们已支持一段时间）之外，标准还定义了 GROUPS 作为一种边界类型。ROWS 相当简单：它只计算行数。RANGE 则更复杂：它将其计数视为与当前行 ORDER BY 表达式值的距离。这意味着只能有一个这样的表达式，并且您必须能够对其进行算术运算。

GROUPS 介于两者之间。标准语言中的“组”是指一行所有“对等行”，即所有与当前行 ORDER BY 表达式值相同的行。在最初的窗口代码中，这实现起来并不容易，但经过几年的工作，基础设施已经发展，截至 v1.2.0，我们现在支持这种最新类型的帧定义。

帧排除

2003 年规范中缺失的另一个部分是 EXCLUDE 子句。感谢一位社区成员的工作，我们从 v0.10.0 版本开始支持此功能，但我们不知何故从未在博客文章中提及它！

EXCLUDE 是帧子句的一个可选修饰符，用于排除 CURRENT ROW 周围的行。当您想计算附近行的某个聚合值，以查看当前行与其相比如何时，这非常有用。在此示例中，我们想知道运动员在某项赛事中的时间与该赛事在 ±10 天内所有记录时间的平均值相比如何

SELECT
    event,
    date,
    athlete,
    avg(time) OVER w AS recent,
FROM results
WINDOW w AS (
    PARTITION BY event
    ORDER BY date
    RANGE BETWEEN INTERVAL 10 DAYS PRECEDING AND INTERVAL 10 DAYS FOLLOWING
        EXCLUDE CURRENT ROW
)
ORDER BY event, date, athlete;

EXCLUDE 有四个选项，用于指定如何处理当前行：

• CURRENT ROW – 仅排除当前行
• GROUP – 排除当前行及其所有“对等行”（即 ORDER BY 值相同的行）
• TIES – 排除所有对等行，但**不**排除当前行（这会在两侧创建空洞）
• NO OTHERS – 不排除任何内容（默认）

排除功能已在窗口聚合以及 first、last 和 nth_value 函数中实现。

QUALIFY 子句

可能并非显而易见，但 SQL 语言对各种表达式的计算顺序有规定。例如，聚合函数（如 sum）是在行级表达式（如 +）计算之后进行的。这就是为什么 SQL 有两个过滤子句：WHERE 和 HAVING：WHERE 用于行级计算，而 HAVING 则在 GROUP BY 之后应用。

引入窗口函数时，它又增加了一层计算：窗口函数在聚合之后计算。这很棒，但如何过滤 OVER 函数的结果呢？最初，您必须将查询放入一个公共表表达式（CTE）中，该表达式由 WITH 子句定义。

-- Find the third fastest times in each event
WITH windowed AS (
    SELECT
        event,
        athlete,
        time,
        row_number() OVER w AS r
    FROM results
    WINDOW w AS (
        PARTITION BY event
        ORDER BY time
    )
)
SELECT event, athlete, time
FROM windowed
WHERE r = 3;

这有点笨拙，所以最终提出了 QUALIFY 子句用于过滤窗口函数。DuckDB 支持此功能，从而更容易地过滤窗口函数的结果。

-- Find the third fastest times in each event
SELECT event, athlete, time
FROM results
WINDOW w AS (
    PARTITION BY event
    ORDER BY time
)
QUALIFY row_number() OVER w = 3;

聚合修饰符

普通聚合函数有几个修饰符（FILTER、DISTINCT 和作为参数的 ORDER BY），它们不是 SQL:2003 窗口标准的组成部分，但在窗口上下文中也很有用。FILTER 相当简单（DuckDB 已经支持了一段时间），但其他修饰符不易高效实现。

当然，它们可以被天真地（学术上称为“慢”）实现，只需独立计算每一行：

• 重新读取所有值，
• 过滤掉我们不想要的值，
• 将它们放入哈希表以删除重复项，
• 对结果进行排序，
• 将其发送给聚合函数以获取结果。

我们有一个实现可以做到这一点（您可以通过关闭优化器来访问它），我们用它来检查更高级的实现，但它非常慢。

幸运的是，后两种修饰符在过去 10 年中一直是 研究 和 发表 的主题，我们现在已将这些算法添加到窗口聚合中。然后我们可以使用 DISTINCT 修饰符来排除帧中的重复项。

-- Count the number of distinct athletes at a given point in time
SELECT count(DISTINCT athlete) OVER (ORDER BY date) FROM results;
-- Concatenate those distinct athletes into a list
SELECT list(DISTINCT athlete) OVER (ORDER BY date) FROM results;

我们还可以将 ORDER BY 修饰符与顺序敏感的聚合函数一起使用，以获取排序后的结果。

-- Return an alphabetized list of athletes who made or beat a time
SELECT list(athlete ORDER BY athlete) OVER (
    PARTITION BY event, date
    ORDER BY time DESC
)
FROM results;

我应该提到，对这些扩展的研究仍在进行中，将它们组合使用通常会迫使我们使用朴素的实现。例如，如果我们要排除上一个示例中创造时间的运动员：

-- Return an alphabetized list athletes who beat the each time
SELECT list(athlete ORDER BY athlete) OVER (
    PARTITION BY event, date
    ORDER BY time DESC
    EXCLUDE CURRENT ROW
)
FROM results;

DuckDB 仍然会为您计算，但这可能会非常慢。

函数修饰符

ORDER BY 修饰符对于一些非聚合窗口函数也很有意义，特别是如果我们允许它们与帧定义一起使用时。

-- Compute the current world record holder over time for each event
SELECT
    event,
    date,
    first_value(time ORDER BY time DESC) OVER w AS record_time,
    first_value(athlete ORDER BY time DESC) OVER w AS record_holder,
FROM results
WINDOW w AS (
    PARTITION BY event
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY event, date;

所有非聚合窗口函数（除了 dense_rank）现在都支持排序参数，并且在提供排序参数时，将使用帧而不是整个分区。

提示 如果您希望在排序参数中使用整个帧，则需要明确指定并使用 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

注意 如果您希望将帧排序和帧边界与非聚合函数一起使用，您需要指定 ORDER BY 两次（一次在帧规范中，一次在参数列表中）。这尚未优化，但在 v1.3.0 版本中会进行优化。

结论

窗口函数是一种非常自然的方式来思考依赖于顺序的分析，但它与传统的无序查询处理有所冲突。尽管如此，自 2003 年以来，SQL 语言提供了语法来表达各种此类查询。近年来，社区还考虑了对语言进行进一步扩展（例如 QUALIFY 以及 DISTINCT 和 ORDER BY 等参数修饰符）以提高表达能力。在 DuckDB，我们很高兴能将这种表达能力作为我们“友好 SQL”工作的一部分提供。可能不太明显的是，当我们让用户更自然地表达他们的问题时，这有助于我们提供性能更优的解决方案！在我的下一篇文章中，我将更深入地探讨窗口函数在性能和资源利用方面的最新改进。