DuckDB 支持 窗口函数，这些函数可以使用多行来计算每行的值。 窗口函数是 阻塞操作符，即它们需要缓冲整个输入，这使得它们成为 SQL 中最消耗内存的操作符之一。
窗口函数自SQL:2003以来在SQL中可用，并且得到了主要SQL数据库系统的支持。
Examples
生成一个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;
Syntax
窗口函数只能在SELECT子句中使用。要在函数之间共享OVER规范，请使用语句的WINDOW子句，并使用OVER ⟨window-name⟩语法。
通用窗口函数
下表显示了可用的通用窗口函数。
| Name | Description |
|---|---|
cume_dist() |
累积分布：（当前行之前或与当前行同级的行数）/ 分区总行数。 |
dense_rank() |
当前行的排名没有间隔；此函数计算同级组。 |
first_value(expr[ IGNORE NULLS]) |
返回在窗口框架的第一行（如果设置了IGNORE NULLS，则为expr的非空值）处评估的expr。 |
lag(expr[, offset[, default]][ IGNORE NULLS]) |
返回在当前行的窗口框架中，位于当前行之前offset行的expr的值（如果设置了IGNORE NULLS，则仅在expr非空的行中计算）；如果没有这样的行，则返回default（必须与expr类型相同）。offset和default都是相对于当前行计算的。如果省略，offset默认为1，default默认为NULL。 |
last_value(expr[ IGNORE NULLS]) |
返回在窗口框架的最后一行（如果设置了IGNORE NULLS，则在expr的非空值行中）评估的expr。 |
lead(expr[, offset[, default]][ IGNORE NULLS]) |
返回在当前行之后的offset行处评估的expr（如果设置了IGNORE NULLS，则在expr非空值的行中）；如果没有这样的行，则返回default（必须与expr类型相同）。offset和default都是相对于当前行进行评估的。如果省略，offset默认为1，default默认为NULL。 |
nth_value(expr, nth[ IGNORE NULLS]) |
返回在窗口框架的第n行（如果设置了IGNORE NULLS，则在expr非空值的行中）评估的expr（从1开始计数）；如果没有这样的行，则返回NULL。 |
ntile(num_buckets) |
一个从1到num_buckets的整数，尽可能均等地划分分区。 |
percent_rank() |
当前行的相对排名：(rank() - 1) / (total partition rows - 1)。 |
rank_dense() |
当前行的排名，*没有间隔。 |
rank() |
当前行的排名有间隔; 与其第一个同行的row_number相同。 |
row_number() |
分区内当前行的编号，从1开始计数。 |
cume_dist()
| 描述 | 累积分布：（当前行之前或与当前行同级的分区行数）/ 总分区行数。 |
| 返回类型 | DOUBLE |
| 示例 | cume_dist() |
dense_rank()
| 描述 | 当前行的排名无间隔；此函数计算同级组。 |
| 返回类型 | BIGINT |
| 示例 | dense_rank() |
| 别名 | rank_dense() |
first_value(expr[ IGNORE NULLS])
| 描述 | 返回在窗口框架的第一行（如果设置了IGNORE NULLS，则为具有非空值的expr的第一行）处评估的expr。 |
| 返回类型 | 与 expr 相同的类型 |
| 示例 | first_value(column) |
lag(expr[, offset[, default]][ IGNORE NULLS])
| 描述 | 返回在当前行的窗口框架内，位于当前行之前offset行的expr的评估值（如果设置了IGNORE NULLS，则仅在expr值不为空的行中计算）；如果没有这样的行，则返回default（其类型必须与expr相同）。offset和default都是相对于当前行进行评估的。如果省略，offset默认为1，default默认为NULL。 |
| 返回类型 | 与 expr 相同的类型 |
| 别名 | lag(column, 3, 0) |
last_value(expr[ IGNORE NULLS])
| 描述 | 返回在窗口框架的最后一行（如果设置了IGNORE NULLS，则在具有非空expr值的行中）评估的expr。 |
| 返回类型 | 与 expr 相同的类型 |
| 示例 | last_value(column) |
lead(expr[, offset[, default]][ IGNORE NULLS])
| 描述 | 返回在当前行之后的offset行处评估的expr（如果设置了IGNORE NULLS，则在expr非空值的行中）；如果没有这样的行，则返回default（必须与expr类型相同）。offset和default都是相对于当前行进行评估的。如果省略，offset默认为1，default默认为NULL。 |
| Return Type | Same type as expr |
| 别名 | lead(column, 3, 0) |
nth_value(expr, nth[ IGNORE NULLS])
| 描述 | 返回在窗口框架的第n行（从1开始计数）评估的expr（如果设置了IGNORE NULLS，则在expr非空值的行中）；如果没有这样的行，则返回NULL。 |
| Return Type | Same type as expr |
| 别名 | nth_value(column, 2) |
ntile(num_buckets)
| 描述 | 一个从1到num_buckets的整数，尽可能均等地划分分区。 |
| Return Type | BIGINT |
| 示例 | ntile(4) |
percent_rank()
| 描述 | 当前行的相对排名：(rank() - 1) / (total partition rows - 1)。 |
| Return Type | DOUBLE |
| 示例 | percent_rank() |
rank_dense()
| 描述 | 当前行的排名无间隔。 |
| Return Type | BIGINT |
| 示例 | rank_dense() |
| 别名 | dense_rank() |
rank()
| 描述 | 当前行的排名有间隔；与row_number的第一个对等行相同。 |
| Return Type | BIGINT |
| 示例 | rank() |
row_number()
| 描述 | 分区内当前行的编号，从1开始计数。 |
| Return Type | BIGINT |
| 示例 | row_number() |
聚合窗口函数
所有聚合函数都可以在窗口上下文中使用，包括可选的FILTER子句。
first和last聚合函数被相应的通用窗口函数所遮蔽，导致的一个小后果是这些函数不支持FILTER子句，但支持IGNORE NULLS。
Nulls
所有通用窗口函数默认情况下都会接受IGNORE NULLS并尊重空值。这种默认行为可以通过RESPECT NULLS明确指定。
相比之下，所有的聚合窗口函数（除了list及其别名，它们可以通过FILTER来忽略空值）都会忽略空值，并且不接受RESPECT NULLS。例如，sum(column) OVER (ORDER BY time) AS cumulativeColumn计算一个累积和，其中column值为NULL的行与它们前面的行具有相同的cumulativeColumn值。
评估
窗口操作通过将关系分解为独立的分区， 对这些分区进行排序， 然后根据附近的值计算每行的新列。 一些窗口函数仅依赖于分区边界和排序， 但少数（包括所有聚合函数）还使用框架。 框架被指定为当前行两侧（前或后）的行数。 距离可以指定为行数或使用分区的排序值和距离的范围。
完整的语法显示在页面顶部的图表中，该图表直观地展示了计算环境：
分区和排序
分区将关系分解为独立的、不相关的部分。 分区是可选的，如果未指定，则整个关系被视为单个分区。 窗口函数无法访问其所在行所在分区之外的值。
排序也是可选的，但没有它，通用窗口函数和对顺序敏感的聚合函数的结果，以及框架的顺序将没有明确定义。 每个分区都使用相同的排序子句进行排序。
目前无法通过
OVER规范中的顺序来指定窗口函数的聚合顺序。特别是，无法使用与框架顺序不同的聚合顺序，例如last_value(x ORDER BY y) OVER (ORDER BY z)。
这里是一个发电数据表，可作为CSV文件使用（power-plant-generation-history.csv）。要加载数据，请运行：
CREATE TABLE "Generation History" AS
FROM 'power-plant-generation-history.csv';
按工厂分区并按日期排序后，它将具有以下布局：
| 植物 | 日期 | 兆瓦时 |
|---|---|---|
| 波士顿 | 2019-01-02 | 564337 |
| 波士顿 | 2019-01-03 | 507405 |
| 波士顿 | 2019-01-04 | 528523 |
| 波士顿 | 2019-01-05 | 469538 |
| 波士顿 | 2019-01-06 | 474163 |
| 波士顿 | 2019-01-07 | 507213 |
| 波士顿 | 2019-01-08 | 613040 |
| 波士顿 | 2019-01-09 | 582588 |
| 波士顿 | 2019-01-10 | 499506 |
| 波士顿 | 2019-01-11 | 482014 |
| 波士顿 | 2019-01-12 | 486134 |
| 波士顿 | 2019-01-13 | 531518 |
| 伍斯特 | 2019-01-02 | 118860 |
| 伍斯特 | 2019-01-03 | 101977 |
| 伍斯特 | 2019-01-04 | 106054 |
| 伍斯特 | 2019-01-05 | 92182 |
| 伍斯特 | 2019-01-06 | 94492 |
| 伍斯特 | 2019-01-07 | 99932 |
| 伍斯特 | 2019-01-08 | 118854 |
| 伍斯特 | 2019-01-09 | 113506 |
| 伍斯特 | 2019-01-10 | 96644 |
| 伍斯特 | 2019-01-11 | 93806 |
| 伍斯特 | 2019-01-12 | 98963 |
| 伍斯特 | 2019-01-13 | 107170 |
在接下来的内容中， 我们将使用这个表（或其小部分）来说明窗口函数评估的各个部分。
最简单的窗口函数是 row_number()。
这个函数只是使用查询在分区内计算基于1的行号：
SELECT
"Plant",
"Date",
row_number() OVER (PARTITION BY "Plant" ORDER BY "Date") AS "Row"
FROM "Generation History"
ORDER BY 1, 2;
结果将是以下内容：
| 植物 | 日期 | 行 |
|---|---|---|
| 波士顿 | 2019-01-02 | 1 |
| 波士顿 | 2019-01-03 | 2 |
| 波士顿 | 2019-01-04 | 3 |
| … | … | … |
| 伍斯特 | 2019-01-02 | 1 |
| 伍斯特 | 2019-01-03 | 2 |
| 伍斯特 | 2019-01-04 | 3 |
| … | … | … |
请注意，即使函数是通过ORDER BY子句计算的，
结果也不一定是有序的，
因此如果希望结果有序，SELECT也需要显式排序。
框架
框架指定了一组相对于每行的行，其中函数被评估。
与当前行的距离由表达式给出，该表达式在当前行之前或之后，顺序由OVER规范中的ORDER BY子句指定。
这个距离可以指定为ROWS的整数
或作为RANGE的增量表达式。
对于RANGE规范，必须只有一个排序表达式，
并且它必须支持加法和减法（即数字或INTERVAL）。
当没有ORDER BY子句时，默认框架是从UNBOUNDED PRECEDING到UNBOUNDED FOLLOWING，当存在ORDER BY子句时，是从UNBOUNDED PRECEDING到CURRENT ROW。
框架在结束之后开始是无效的。
使用EXCLUDE子句，可以排除当前行周围的行的框架。
ROW 框架
这是一个简单的ROW框架查询，使用了聚合函数：
SELECT points,
sum(points) OVER (
ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING) 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框架
（以处理任何缺失的天数）。
这是结果：
| 工厂 | 日期 | 7天移动平均发电量（MWh） |
|---|---|---|
| 波士顿 | 2019-01-02 | 517450.75 |
| 波士顿 | 2019-01-03 | 508793.20 |
| 波士顿 | 2019-01-04 | 508529.83 |
| … | … | … |
| 波士顿 | 2019-01-13 | 499793.00 |
| 伍斯特 | 2019-01-02 | 104768.25 |
| 伍斯特 | 2019-01-03 | 102713.00 |
| 伍斯特 | 2019-01-04 | 102249.50 |
| … | … | … |
EXCLUDE Clause
EXCLUDE 子句允许从当前行的周围排除行。它有以下选项：
EXCLUDE NO OTHERS: 不排除任何内容（默认）EXCLUDE CURRENT ROW: 从窗口框架中排除当前行EXCLUDE GROUP: 从窗口框架中排除当前行及其所有同级行（根据ORDER BY指定的列）EXCLUDE TIES: 仅从窗口框架中排除当前行的对等行
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等。对于更复杂的查询，您可以找到WINDOW子句在
SELECT statement的规范顺序中的位置。
使用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;