DuckDB 支持窗口函数，窗口函数可以使用多行来计算每行的值。窗口函数是阻塞操作符，即它们需要将整个输入缓冲起来，这使得它们成为 SQL 中内存消耗最大的操作符之一。

自SQL:2003以来，SQL 中提供了窗口函数，并且主流的 SQL 数据库系统都支持它们。

示例

生成一个 row_number 列来枚举行

SELECT row_number() OVER ()
FROM sales;

提示 如果你只需要表中每行的编号，可以使用rowid 伪列。

生成一个 row_number 列来枚举行，按 time 排序

SELECT row_number() OVER (ORDER BY time)
FROM sales;

生成一个 row_number 列来枚举行，按 time 排序并按 region 分区

SELECT row_number() OVER (PARTITION BY region ORDER BY time)
FROM sales;

计算当前 amount 与按 time 排列的前一个 amount 之间的差值

SELECT amount - lag(amount) OVER (ORDER BY time)
FROM sales;

计算每行销售总 amount 按 region 的百分比

SELECT amount / sum(amount) OVER (PARTITION BY region)
FROM sales;

语法

窗口函数只能在 SELECT 子句中使用。要在函数之间共享 OVER 规范，请使用语句的WINDOW 子句，并使用 OVER window_name 语法。

通用窗口函数

下表显示了可用的通用窗口函数。

cume_dist([ORDER BY ordering])

dense_rank()

first_value(expr[ ORDER BY ordering][ IGNORE NULLS])

lag(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])

last_value(expr[ ORDER BY ordering][ IGNORE NULLS])

lead(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])

nth_value(expr, nth[ ORDER BY ordering][ IGNORE NULLS])

ntile(num_buckets[ ORDER BY ordering])

percent_rank([ORDER BY ordering])

rank_dense()

rank([ORDER BY ordering])

row_number([ORDER BY ordering])

聚合窗口函数

所有聚合函数都可以在窗口上下文中使用，包括可选的FILTER 子句。first 和 last 聚合函数被相应的通用窗口函数所覆盖，其微小后果是 FILTER 子句对它们不可用，但 IGNORE NULLS 可用。

DISTINCT 参数

所有聚合窗口函数都支持对参数使用 DISTINCT 子句。当提供 DISTINCT 子句时，只有不重复的值才会被考虑在聚合计算中。这通常与 COUNT 聚合结合使用以获取不同元素的数量；但它可以与系统中的任何聚合函数一起使用。有些聚合对重复值不敏感（例如 min、max），对于它们，此子句会被解析但被忽略。

-- Count the number of distinct users at a given point in time
SELECT count(DISTINCT name) OVER (ORDER BY time) FROM sales;
-- Concatenate those distinct users into a list
SELECT list(DISTINCT name) OVER (ORDER BY time) FROM sales;

ORDER BY 参数

所有聚合窗口函数都支持使用与窗口排序不同的 ORDER BY 参数子句。当提供 ORDER BY 参数子句时，被聚合的值在应用函数之前会进行排序。通常这不重要，但有些对顺序敏感的聚合可能会有不确定的结果（例如 mode、list 和 string_agg）。通过对参数排序，这些结果可以变得确定。对于对顺序不敏感的聚合，此子句会被解析但被忽略。

-- Compute the modal value up to each time, breaking ties in favour of the most recent value.
SELECT mode(value ORDER BY time DESC) OVER (ORDER BY time) FROM sales;

SQL 标准不提供在通用窗口函数中使用 ORDER BY，但我们已扩展了所有这些函数（除了 dense_rank）以接受此语法，并使用帧来限制辅助排序的应用范围。

-- Compare each athlete's time in an event with the best time to date
SELECT event, date, athlete, time
    first_value(time ORDER BY time DESC) OVER w AS record_time,
    first_value(athlete ORDER BY time DESC) OVER w AS record_athlete,
FROM meet_results
WINDOW w AS (PARTITION BY event ORDER BY datetime)
ORDER BY ALL

请注意，参数与 ORDER BY 子句之间没有逗号分隔。

空值

所有接受 IGNORE NULLS 的通用窗口函数默认都尊重空值。此默认行为可通过 RESPECT NULLS 可选地明确指定。

相反，所有聚合窗口函数（除了 list 及其别名，它们可以通过 FILTER 忽略空值）都忽略空值，并且不接受 RESPECT NULLS。例如，sum(column) OVER (ORDER BY time) AS cumulativeColumn 计算一个累积和，其中 column 值为 NULL 的行与它们前面的行具有相同的 cumulativeColumn 值。

评估

窗口操作通过将关系分解为独立的分区、对这些分区进行排序，然后根据附近的值为每行计算一个新列来实现。有些窗口函数只依赖于分区边界和排序，但少数（包括所有聚合函数）也使用帧。帧被指定为当前行两侧（之前或之后）的行数。距离可以指定为行数，或作为使用分区排序值和距离的值范围，或作为组数（具有相同排序值的行集）。

完整的语法显示在页面顶部的图中，此图直观地说明了计算环境

分区与排序

分区将关系分解为独立的、不相关的部分。分区是可选的，如果未指定，则整个关系被视为一个单一分区。窗口函数无法访问包含正在评估行的分区之外的值。

排序也是可选的，但如果没有排序，通用窗口函数和对顺序敏感的聚合函数的结果以及帧的顺序将不明确。每个分区都使用相同的排序子句进行排序。

这是一个发电数据表，以 CSV 文件形式提供（power-plant-generation-history.csv）。要加载数据，请运行

CREATE TABLE "Generation History" AS
    FROM 'power-plant-generation-history.csv';

按电厂分区并按日期排序后，它将具有以下布局

在下文中，我们将使用此表（或其小部分）来演示窗口函数评估的各个部分。

最简单的窗口函数是 row_number()。此函数仅使用查询计算分区内基于 1 的行号

SELECT
    "Plant",
    "Date",
    row_number() OVER (PARTITION BY "Plant" ORDER BY "Date") AS "Row"
FROM "Generation History"
ORDER BY 1, 2;

结果如下

请注意，即使函数是使用 ORDER BY 子句计算的，结果也不一定已排序，因此如果需要排序，SELECT 也需要显式排序。

帧

帧指定了相对于每行（在其中评估函数）的一组行。与当前行的距离表示为一个表达式，即在 OVER 规范的 ORDER BY 子句中指定的顺序中，当前行的 PRECEDING（之前）或 FOLLOWING（之后）。此距离可以指定为整数个 ROWS（行）或 GROUPS（组），或作为 RANGE（范围）增量表达式。帧在结束之后开始是无效的。对于 RANGE 规范，必须只有一个排序表达式，并且它必须支持减法，除非仅使用边界值 UNBOUNDED PRECEDING / UNBOUNDED FOLLOWING / CURRENT ROW。使用EXCLUDE 子句，在指定排序表达式中与当前行比较相等的行（即所谓的对等行）可以从帧中排除。

当没有 ORDER BY 子句时，默认帧是无边界的（即整个分区）；当存在 ORDER BY 子句时，默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。默认情况下，CURRENT ROW 边界值（但不是 EXCLUDE 子句中的 CURRENT ROW）在使用 RANGE 或 GROUP 帧时表示当前行及其所有对等行，但在使用 ROWS 帧时仅表示当前行。

ROWS 帧

这是一个简单的 ROW 帧查询，使用聚合函数

SELECT points,
    sum(points) OVER (
        ROWS BETWEEN 1 PRECEDING
                 AND 1 FOLLOWING) AS we
FROM results;

此查询计算每个点及其两侧点的 sum

请注意，在分区边缘，只有两个值相加。这是因为帧被裁剪到分区的边缘。

RANGE 帧

回到电力数据，假设数据有噪声。我们可能希望计算每个电厂的 7 天移动平均值以平滑噪声。为此，我们可以使用此窗口查询

SELECT "Plant", "Date",
    avg("MWh") OVER (
        PARTITION BY "Plant"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
                  AND INTERVAL 3 DAYS FOLLOWING)
        AS "MWh 7-day Moving Average"
FROM "Generation History"
ORDER BY 1, 2;

此查询按 Plant 分区数据（以使不同电厂的数据分开），按 Date 排序每个电厂的分区（以使能量测量值相邻），并使用每个日期两侧三天的 RANGE 帧来计算 avg（以处理任何缺失的日期）。结果如下

GROUPS 帧

第三种帧类型计算相对于当前行的行组。在此帧中，组是具有相同 ORDER BY 值的集合。如果我们假设每天都在发电，我们可以使用 GROUPS 帧来计算系统中所有发电量的移动平均值，而无需进行日期算术运算

SELECT "Date", "Plant",
    avg("MWh") OVER (
        ORDER BY "Date" ASC
        GROUPS BETWEEN 3 PRECEDING
                   AND 3 FOLLOWING)
        AS "MWh 7-day Moving Average"
FROM "Generation History"
ORDER BY 1, 2;

请注意，每个日期的值都是相同的。

EXCLUDE 子句

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 函数实现。

WINDOW 子句

在同一个 SELECT 中可以指定多个不同的 OVER 子句，并且每个子句都会单独计算。然而，通常我们希望为多个窗口函数使用相同的布局。WINDOW 子句可用于定义一个命名窗口，该窗口可以在多个窗口函数之间共享

SELECT "Plant", "Date",
    min("MWh") OVER seven AS "MWh 7-day Moving Minimum",
    avg("MWh") OVER seven AS "MWh 7-day Moving Average",
    max("MWh") OVER seven AS "MWh 7-day Moving Maximum"
FROM "Generation History"
WINDOW seven AS (
    PARTITION BY "Plant"
    ORDER BY "Date" ASC
    RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
              AND INTERVAL 3 DAYS FOLLOWING)
ORDER BY 1, 2;

这三个窗口函数也将共享数据布局，这将提高性能。

可以在同一个 WINDOW 子句中通过逗号分隔来定义多个窗口

SELECT "Plant", "Date",
    min("MWh") OVER seven AS "MWh 7-day Moving Minimum",
    avg("MWh") OVER seven AS "MWh 7-day Moving Average",
    max("MWh") OVER seven AS "MWh 7-day Moving Maximum",
    min("MWh") OVER three AS "MWh 3-day Moving Minimum",
    avg("MWh") OVER three AS "MWh 3-day Moving Average",
    max("MWh") OVER three AS "MWh 3-day Moving Maximum"
FROM "Generation History"
WINDOW
    seven AS (
        PARTITION BY "Plant"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
                  AND INTERVAL 3 DAYS FOLLOWING),
    three AS (
        PARTITION BY "Plant"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 1 DAYS PRECEDING
        AND INTERVAL 1 DAYS FOLLOWING)
ORDER BY 1, 2;

上述查询没有使用选择语句中常见的许多子句，如 WHERE、GROUP BY 等。对于更复杂的查询，你可以在SELECT 语句的规范顺序中找到 WINDOW 子句的位置。

使用 QUALIFY 过滤窗口函数的结果

窗口函数在 WHERE 和 HAVING 子句已评估后执行，因此无法使用这些子句来过滤窗口函数的结果。QUALIFY 子句避免了使用子查询或WITH 子句来执行此过滤的需要。

箱线图查询

所有聚合都可以用作窗口函数，包括复杂的统计函数。这些函数实现已针对窗口操作进行了优化，我们可以使用窗口语法编写查询来生成移动箱线图的数据

SELECT "Plant", "Date",
    min("MWh") OVER seven AS "MWh 7-day Moving Minimum",
    quantile_cont("MWh", [0.25, 0.5, 0.75]) OVER seven
        AS "MWh 7-day Moving IQR",
    max("MWh") OVER seven AS "MWh 7-day Moving Maximum",
FROM "Generation History"
WINDOW seven AS (
    PARTITION BY "Plant"
    ORDER BY "Date" ASC
    RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
              AND INTERVAL 3 DAYS FOLLOWING)
ORDER BY 1, 2;