DuckDB 支持窗口函数，它可以使用多行数据为每一行计算出一个值。窗口函数是阻塞算子，这意味着它们需要缓冲全部输入数据，因此它们是 SQL 中最耗费内存的算子之一。

窗口函数自 SQL:2003 标准起引入，并被主流 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;

计算当前行与按 time 排序的前一行之间的 amount 差值

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

为每一行计算该行所属 region 的总 amount 销售额占比

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

语法

窗口函数只能在 SELECT 子句中使用。若要在多个函数间共享 OVER 定义，请使用语句的 WINDOW 子句并使用 OVER window_name 语法。

通用窗口函数

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

cume_dist([ORDER BY ordering])

dense_rank()

fill(expr[ ORDER BY ordering])

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([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 favor of the most recent value.
SELECT mode(value ORDER BY time DESC) OVER (ORDER BY time) FROM sales;

SQL 标准并未规定通用窗口函数可以使用 ORDER BY，但我们扩展了所有这些函数（除了 dense_rank）以支持此语法，并利用框架（framing）来限制次级排序应用的范围。

-- 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 子句之间没有逗号分隔。

空值 (Nulls)

所有接受 IGNORE NULLS 的通用窗口函数默认都尊重（respect）空值。这种默认行为可以通过 RESPECT NULLS 显式指定。

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

执行机制

窗口函数的工作原理是将关系划分为独立的分区，对这些分区进行排序，然后根据附近的值为每一行计算一个新列。一些窗口函数仅依赖于分区边界和顺序，但少数（包括所有聚合函数）还会使用框架（frame）。框架指定了当前行两侧（前导/preceding 或 跟随/following）的若干行。距离可以指定为行数，或使用分区的排序值和距离作为范围（range），或者指定为组数（groups）（具有相同排序值的行集）。

完整语法如页面顶部的图所示，该图直观地说明了计算环境。

分区和排序

分区将关系划分为独立的、不相关的部分。分区是可选的，如果不指定分区，则整个关系被视为单个分区。窗口函数无法访问当前所在分区之外的值。

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

这是一个电力发电数据表，可作为 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 语句也需要显式排序。

框架 (Framing)

框架指定了函数计算时相对于当前行的一组行。距离当前行的距离表现为 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。默认情况下，使用 RANGE 或 GROUP 框架时，CURRENT ROW 边界值（不包括 EXCLUDE 子句中的 CURRENT ROW）意味着当前行及其所有并列行；而使用 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 对每个分区排序（以将能量测量值相邻放置），并对 avg 使用了以当前日期前后三天的 RANGE 框架（以处理日期缺失情况）。结果如下：

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;

上述查询没有使用 select 语句中常见的许多子句，如 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;