窗口函数
“窗口函数”是聚合函数的一种变体。聚合函数（如 SUM() 和 AVG()）接受 n 个输入并返回单个值，而窗口函数则返回 n 个值。
例如，考虑定义为以下的简单表格：
CREATE TABLE test(i int);
INSERT INTO test VALUES(1),(2),(3),(4),(5);SUM() 和 AVG() 聚合函数返回以下值：
SELECT SUM(i), AVG(i) FROM test;
#1 #2
--------------------------------------------------------------------------
15 3
Selected records: 1在 SQL 的 SELECT 语句中，OVER 关键字表示前面的函数将应用于一个窗口，该窗口代表相对于查询结果集中当前行的一组行。例如，窗口函数 SUM() 和 AVG() 将返回以下值：
SELECT i, SUM(i) OVER(), AVG(i) OVER() FROM test;
i #2 #3
--------------------------------------------------------------------------
1 1 1
2 3 1.5
3 6 2
4 10 2.5
5 15 3
Selected records: 5窗口函数的输出依赖于多个输入值（如函数参数、其他行的计算结果），因此不包括按元素操作的函数，如 + 或 ROUND()。窗口函数涵盖聚合函数（如 SUM()、AVG()、MIN()、MAX()）、排名函数（如 RANK()）和偏移函数（如 LEAD()、LAG())。
请注意，SQL 窗口函数与基于向量的分析库中的“Window_Agg”函数不同。“Window_Agg”仅适用于序列字段，并在固定数量的元素上进行计算。
窗口函数语法
窗口函数调用包含直接跟在函数名称和参数后的 OVER 子句，这是它与常规或聚合函数的主要区别。OVER 子句决定了查询行如何分组以供窗口函数处理。PARTITION BY 列表用于将行划分为具有相同值的分区。对于每一行，窗口函数会在同一分区内的行上进行计算。
对于给定窗口，可以指定：
- 行的顺序（ORDER BY 子句）
- 操作的子集（PARTITION BY 子句）
- 使用 ROWS BETWEEN 或 RANGE BETWEEN 定义帧范围，包括：
- 有界：如 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW（前两行与当前行之间）
- 部分有界：如 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（当前行与后续无界行之间的行）
- 无界：如 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING（在无界先序和无界后序之间的行）
支持的窗口函数包括：
- 聚合函数
- count - 累计行数
- row_number - 行号
- min - 最小值
- max - 最大值
- sum - 当前行及之前所有行的累计和
- avg - 当前行及之前所有行的平均值
- median - 当前行及之前所有行的中位数
- 对于有序行
- first_value - 第一个值
- last_value - 最后一个值
- nth_value - 指定的第 n 个值
- lead - 访问当前行之后指定偏移量的行
- lag - 访问当前行之前指定偏移量的行
- rank - 当前行值的排名（排名序列中可能存在间隔）
- dense_rank - 当前行值的排名（排名序列中没有间隔）
- percent_rank - 用于计算当前行的百分位数
- ntile - 将行分配到指定数量的组中
注意：有序行函数需要在 OVER 子句中包含 ORDER BY。
指定窗口帧
结果集每一行的窗口帧通过指定 BETWEEN 界限来确定。这些 BETWEEN 界限可以通过 ROWS 或 RANGE 选项来指定。ROWS 选项定义了当前行之前和之后的固定行数；并且窗口帧所包含的行将由 ORDER BY 子句确定。如果未明确指定界限，则默认窗口帧是从第一行（UNBOUNDED PRECEDING）到当前行。
请注意，BETWEEN 的边界值必须使用明确的常量值指定，而不能使用参数。例如，以下语句是无效的：
SELECT stamp, SUM(price*quantity) OVER w AS pq1, SUM(quantity) OVER w AS vol, pq1/vol AS VWAP
FROM Quotes
WINDOW w AS (PARTITION BY pkid ORDER BY stamp RANGE BETWEEN ? PRECEDING AND 0 FOLLOWING)在此，参数替换“BETWEEN ? PRECEDING”是不允许的。 相反，使用常量值 5 的以下语句是有效的：
SELECT stamp, SUM(price*quantity) OVER w AS pq1, SUM(quantity) OVER w AS vol, pq1/vol AS VWAP
FROM Quotes
WINDOW w AS (PARTITION BY pkid ORDER BY stamp RANGE BETWEEN 5 PRECEDING AND 0 FOLLOWING)如果使用了 RANGE 选项，那么在当前行的帧内具有相同 ORDER BY 值的所有行都将包含在该行的函数计算中。为了说明物理 ROWS BETWEEN 帧和逻辑 RANGE BETWEEN 帧之间的区别，我们将向示例表 test 中添加一列，并插入如下值：
ALTER TABLE test ADD val int;
UPDATE test SET val=100 WHERE i=1;
UPDATE test SET val=200 WHERE i=2;
UPDATE test SET val=200 WHERE i=3;
UPDATE test SET val=200 WHERE i=4;
UPDATE test SET val=300 WHERE i=5;
SELECT * FROM test;
i val
------------------------------------------------------------------------------
1 100
2 200
3 200
4 200
5 300
Selected records: 5现在，要计算物理框架中“UNBOUNDED PRECEDING 到 CURRENT ROW 之间行的总和”（默认框架），我们可以执行如下查询：
SELECT i, val, SUM(val) OVER(ORDER BY val ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW) AS Row_Sum FROM test;
i val Row_Sum
------------------------------------------------------------------------------
1 100 100
2 200 300
3 200 500
4 200 700
5 300 1000
Selected records: 5请注意，SUM(val) 是将当前行的值 val 与其之前所有行的值相加计算得出的。要计算逻辑框架为“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”的总和，我们可以执行如下查询：
SELECT i, val, SUM(val) OVER(ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW) AS Range_Sum FROM test;
i val Range_Sum
------------------------------------------------------------------------------
1 100 100
2 200 700
3 200 700
4 200 700
5 300 1000
Selected records: 5请注意，对于第 2 行、第 3 行和第 4 行，ORDER BY 列 val 的值相同；因此，此处 SUM(val) 对这些行的计算结果为 100 + 200 + 200 + 200 = 700。
示例
以下示例使用了华盛顿特区 Capital Bikeshare 计划的数据，该计划在其网站上发布了详细的行程级别历史数据。每一行代表一次骑行。以下导入脚本用于创建名为 bikes 的表、用于加快按 bike_num 查找速度的索引 bike_num_idx，然后插入示例数据：
CREATE TABLE bikes(duration int, start_date string, end_date string, start_st_num int,
start_st string, end_st_num int, end_st string, bike_num string,
member_type string);
INSERT INTO bikes SELECT * from foreign table(path='2016-Q1-Trips-History-Data.csv', delimiter=',', skip=1) AS bikes;
CREATE INDEX bike_num_idx ON bikes(bike_num);窗口函数的一个简单实用示例是计算运行总计。例如，以下查询创建了一个聚合（running_total），作为字段 duration 的累积和（请注意，这里没有使用 GROUP BY 子句）：
SELECT duration, start_date, SUM(duration) OVER (ORDER BY start_date) AS running_total
FROM bikes WHERE bike_num = 'W22196' AND start_date >= '3/31/2016';上述聚合的第一部分 SUM(duration) 看起来与任何其他聚合类似。添加 OVER 关键字将其指定为窗口函数。因此，上述聚合可以理解为“按照 start_date 的顺序，对整个结果集中的 duration 求和”。这里，为了输出简洁，我们指定了一个 bike_num（W22196）和单个日期（2016 年 3 月 31 日）以及有限数量的行程。结果集展示了 running_total 如何显示累积和：
duration start_date running_total
--------------------------------------------------------------------------
456465 3/31/2016 0:05 456465
300889 3/31/2016 14:25 757354
1001144 3/31/2016 23:51 1758498
577035 3/31/2016 8:20 2335533
Selected records: 4以下查询展示了如何使用 PARTITION BY 子句来计算按起始终端编号 start_st_num 分组且按 start_date 排序的结果集行块的累计和：
SELECT start_st_num, duration, start_date, SUM(duration)
OVER (PARTITION BY start_st_num ORDER BY start_date) AS running_total
FROM bikes WHERE bike_num = 'W22196' AND start_st_num BETWEEN 31610 and 31625;
start_st_num duration start_date running_total
--------------------------------------------------------------------------
31624 1375776 1/12/2016 18:28 1375776
31624 396755 1/21/2016 8:47 1772531
31624 988750 1/7/2016 19:38 2761281
31624 203844 2/29/2016 10:26 2965125
31624 987070 3/11/2016 16:30 3952195
31624 372239 3/11/2016 18:09 4324434
31623 602551 1/14/2016 7:02 602551
31623 691224 1/16/2016 21:03 1293775
31623 372176 1/18/2016 12:40 1665951
31623 278196 1/20/2016 13:48 1944147
31623 389189 1/7/2016 7:42 2333336
31623 387578 3/29/2016 18:44 2720914
31622 382357 1/18/2016 8:24 382357
31622 797607 1/19/2016 8:36 1179964
31622 508194 3/30/2016 9:09 1688158
31621 837364 1/13/2016 17:37 837364
31621 143947 3/1/2016 8:52 981311
31620 188661 3/27/2016 12:22 188661
31620 300889 3/31/2016 14:25 489550
31618 3336939 3/30/2016 14:09 3336939
31615 216014 1/6/2016 16:36 216014
31615 577035 3/31/2016 8:20 793049
31613 1493051 3/30/2016 10:18 1493051
Selected records: 23此查询按 start_st_num 对结果进行分组。请注意，在每个 start_st_num 的值内，运行总计会将当前行以及该窗口内所有前一行的 duration 相加。这是 PARTITION BY 子句的效果。另外请注意，可选的 ORDER BY 子句只是按照指定的列以与 SELECT 语句中的 ORDER BY 子句相同的方式进行排序，只是它会分别处理每个分区。
请注意，您不能在同一查询中使用窗口函数和标准聚合函数。更具体地说，您不能在 GROUP BY 子句中包含窗口函数。
与 SUM() 函数计算累计和类似，COUNT() 和 AVG() 函数分别计算分区中累计的行数及其平均值。例如：
SELECT start_st_num, duration, start_date,
SUM(duration) OVER (PARTITION BY start_st_num ORDER BY start_date) AS running_total,
COUNT(duration) OVER (PARTITION BY start_st_num ORDER BY start_date) AS running_count,
AVG(duration) OVER (PARTITION BY start_st_num ORDER BY start_date) AS running_avg
FROM bikes WHERE bike_num = 'W22196' and start_st_num BETWEEN 31610 and 31625;
start_st_num duration start_date running_total running_count running_avg
--------------------------------------------------------------------------
31624 1375776 1/12/2016 18:28 1375776 1 1375776
31624 396755 1/21/2016 8:47 1772531 2 886265.5
31624 988750 1/7/2016 19:38 2761281 3 920427
31624 203844 2/29/2016 10:26 2965125 4 741281.25
31624 987070 3/11/2016 16:30 3952195 5 790439
31624 372239 3/11/2016 18:09 4324434 6 720739
31623 602551 1/14/2016 7:02 602551 1 602551
31623 691224 1/16/2016 21:03 1293775 2 646887.5
31623 372176 1/18/2016 12:40 1665951 3 555317
31623 278196 1/20/2016 13:48 1944147 4 486036.75
31623 389189 1/7/2016 7:42 2333336 5 466667.2
31623 387578 3/29/2016 18:44 2720914 6 453485.666666667
31622 382357 1/18/2016 8:24 382357 1 382357
31622 797607 1/19/2016 8:36 1179964 2 589982
31622 508194 3/30/2016 9:09 1688158 3 562719.333333333
31621 837364 1/13/2016 17:37 837364 1 837364
31621 143947 3/1/2016 8:52 981311 2 490655.5
31620 188661 3/27/2016 12:22 188661 1 188661
31620 300889 3/31/2016 14:25 489550 2 244775
31618 3336939 3/30/2016 14:09 3336939 1 3336939
31615 216014 1/6/2016 16:36 216014 1 216014
31615 577035 3/31/2016 8:20 793049 2 396524.5
31613 1493051 3/30/2016 10:18 1493051 1 1493051
Selected records: 23为了演示下一组函数，我们将使用由以下脚本创建的一个简单的表和数据集：
CREATE TABLE test(i int);
INSERT INTO test VALUES(1),(2),(3),(4),(5);LAG() 和 LEAD() 函数分别返回当前行之前或之后指定行的值：
SELECT i,
LAG(i,1) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
LEAD(i,1) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM test;
i #2 #3
--------------------------------------------------------------------------
1 null 2
2 1 3
3 2 4
4 3 5
5 4 null
Selected records: 5MIN() 和 MAX() 函数分别返回指定帧中的最小值和最大值。例如，在使用默认范围（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）时，MIN() 和 MAX() 是由不断增加的帧来确定的。例如：
SELECT i, MIN(i) OVER (ORDER BY i), MAX(i) OVER (ORDER BY i) FROM test;
i #2 #3
--------------------------------------------------------------------------
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
Selected records: 5但是，当指定无界的帧（ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING）时，最小值和最大值是针对整个窗口而言的：
SELECT i,
MIN(i) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
MAX(i) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM test;
i #2 #3
--------------------------------------------------------------------------
1 1 5
2 1 5
3 1 5
4 1 5
5 1 5
Selected records: 5在未指定边界且按字段 i 排序的情况下，LAST_VALUE()、FIRST_VALUE() 和 NTH_VALUE() 函数将生成 5 个帧，并且 LAST_VALUE() 的值将是当前行中 i 的值。这是因为窗口函数的默认帧是“UNBOUNDED PRECEDING AND CURRENT ROW”。因此，如果不显式指定帧，FIRST_VALUE() 将返回表中第一行的值，而 LAST_VALUE() 将返回当前行的值。NTH_VALUE() 函数返回当前帧中第 n 行（从零开始）的值，如果当前帧中不存在第 n 行，则返回 NULL。例如：
SELECT i, FIRST_VALUE(i) OVER (ORDER BY i), LAST_VALUE(i) OVER (ORDER BY i),
NTH_VALUE(i, 1) OVER (ORDER BY i) FROM test;
i #2 #3 #4
--------------------------------------------------------------------------
1 1 1 null
2 1 2 2
3 1 3 2
4 1 4 2
5 1 5 2
Selected records: 5如果我们指定一个无界的窗口，LAST_VALUE() 的值将是整个窗口中的最后一个值：
SELECT i,
FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
NTH_VALUE(i, 1) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM test;
i #2 #3 #4
------------------------------------------------------------------------------
1 1 5 2
2 1 5 2
3 1 5 2
4 1 5 2
5 1 5 2
Selected records: 5并且，如果我们指定了一个有界的窗口，那么 LAST_VALUE() 的值将是指定范围（行的范围）内的最后一个值：
SELECT i, FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
NTH_VALUE(i, 2) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM test;
i #2 #3 #4
------------------------------------------------------------------------------
1 1 2 null
2 1 3 3
3 2 4 4
4 3 5 5
5 4 5 null
Selected records: 5为了演示排名窗口函数，我们将使用另一个简单的表和数据集，该数据集由以下脚本创建：
CREATE TABLE t(v string);
INSERT INTO t(v) VALUES ('a'),('a'),('a'),('b'),('c'),('c'),('d'),('e');ROW_NUMBER() 函数会为分区内的每一行分配一个唯一的编号。例如：
SELECT v, ROW_NUMBER() OVER(ORDER BY v) FROM t;
v #2
------------------------------------------------------------------------------
a 1
a 2
a 3
b 4
c 5
c 6
d 7
e 8
Selected records: 8ROW_NUMBER() 函数会为分区内的每一行分配一个唯一的编号。例如：
SELECT v, RANK() OVER(ORDER BY v) FROM t;
v #2
------------------------------------------------------------------------------
a 1
a 1
a 1
b 4
c 5
c 5
d 7
e 8
Selected records: 8DENSE_RANK() 函数计算一个没有间隔的排名，即“密集型”排名。例如：
SELECT v, DENSE_RANK() OVER(ORDER BY v) FROM t;
v #2
------------------------------------------------------------------------------
a 1
a 1
a 1
b 2
c 3
c 3
d 4
e 5
Selected records: 8PERCENT_RANK() 函数计算分区中当前行相对于其他行的相对排名。此函数用于统计分析，以确定一组值的百分位数。用于计算百分位数排名的实际公式为：
PERCENT_RANK() = ( RANK() – 1 ) / ( TotalRows - 1 )为了证明这一点，我们使用以下脚本创建另一个表：
CREATE TABLE salaries(DepartmentID int, Salary int);
INSERT INTO salaries VALUES (1, 15000), (1, 18000), (1, 23000), (1, 23000), (1, 25000);在以下查询中，RANK() 和 PERCENT_RANK() 函数应用于“Salary”（薪资）列：
SELECT DepartmentID, Salary,
ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING) AS RowNumber,
RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING) AS RowRank,
PERCENT_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING) AS PercentRank
FROM salaries;
DepartmentID Salary RowNumber RowRank PercentRank
------------------------------------------------------------------------------
1 15000 1 1 0
1 18000 2 2 0.25
1 23000 3 3 0.5
1 23000 4 3 0.5
1 25000 5 5 1
Selected records: 5这里我们有 5 行数据，其中包含 4 个不同的薪资值。PERCENT_RANK() 函数返回的值计算方式如下：
1. RANK = 1 so PERCENT_RANK = ( 1 – 1 ) / 4 = 0
2. RANK = 2 so PERCENT_RANK = ( 2 – 1 ) / 4 = 0.25
3. RANK = 3 so PERCENT_RANK = ( 3 – 1 ) / 4 = 0.5
4. RANK = 3 so PERCENT_RANK = ( 3 – 1 ) / 4 = 0.5
5. RANK = 5 so PERCENT_RANK = ( 5 – 1 ) / 4 = 1NTILE() 函数将结果集中的行分配到指定数量的组中。为了演示，我们将使用以下脚本创建另一个表：
CREATE TABLE students(StudentID char(2), Marks integer);
INSERT INTO students VALUES('S1', 75);
INSERT INTO students VALUES('S2', 83);
INSERT INTO students VALUES('S3', 91);
INSERT INTO students VALUES('S4', 83);
INSERT INTO students VALUES('S5', 93);我们可以使用如下脚本将 NTILE() 函数应用于此表：
SELECT StudentID, Marks, NTILE(2) OVER(ORDER BY Marks ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) FROM students;
StudentID Marks #3
------------------------------------------------------------------------------
S1 75 1
S2 83 1
S4 83 1
S3 91 2
S5 93 2
Selected records: 5请注意，第 1 组有 3 行，而第 2 组有 2 行，这仅仅是因为结果行数是奇数。