结构化查询语言(Structured Query Language)
本页讨论的窗口函数是一种强大的扩展功能,区别于传统的单行计算函数。窗口函数的结果不是基于单行计算,而是基于一组行(类似于聚合函数和 GROUP BY 子句的结合)。关键点在于,这组行会“滑动”或“移动”到通过 WHERE 子句确定的所有行上。这种“滑动窗口”被称为帧(frame),或者按照 SQL 标准的术语,称为“窗口帧”(window frame)。
示例
- 一个简单的滑动窗口可以包括前一行、当前行和下一行。
- 窗口函数常用于分析任意时间序列数据。例如,通过股票的时间序列数据,计算过去 n 天的移动平均值。
- 窗口函数广泛应用于数据仓库和 OLAP 分析中。例如,对于跨多个地区和多个时间段的产品销售数据,可以通过窗口函数计算销售收入的统计指标。这种评估比单纯的 GROUP BY 子句更加强大。
与 GROUP BY 不同,窗口函数保留了结果集中每一行的标识,因此每行都会显示在最终结果中。
语法
窗口函数出现在 SELECT 和 FROM 之间,与普通函数和列名并列。它们包含关键字 OVER。
-- 窗口函数出现在 SELECT 和 FROM 之间
SELECT ...,
<window_function>,
...
FROM <tablename>
...
;
-- 窗口函数由以下三部分组成:
-- 1. 函数类型(函数名)
-- 2. 关键字 'OVER'
-- 3. 窗口规范(定义滑动窗口的分区、排序和帧)
<window_function> := <window_function_type> OVER <window_specification>
<window_function_type> := ROW_NUMBER() | RANK() | LEAD(<column>) | LAG(<column>) |
FIRST_VALUE(<column>) | LAST_VALUE(<column>) | NTH_VALUE(<column>, <n>) |
SUM(<column>) | MIN(<column>) | MAX(<column>) | AVG(<column>) | COUNT(<column>)
<window_specification> := [ <window_partition> ] [ <window_order> ] [ <window_frame> ]
<window_partition> := PARTITION BY <column>
<window_order> := ORDER BY <column>
<window_frame> := 定义窗口帧,详见下文
整体描述
使用窗口函数时,需要清晰地理解以下概念和步骤。这些步骤大致反映了窗口函数的执行顺序。前七步的目标是确定当前的窗口帧,第八步是在窗口帧上执行操作。
- WHERE 子句:返回符合条件的所有行,构成结果集。
- ORDER BY 子句:对结果集重新排序,形成特定的顺序。
- 当前行:排序后的结果集逐行传递给 SELECT 子句,当前处理的行称为当前行(current row)。
- PARTITION BY 子句:将结果集分为多个分区(partition)。如果未指定 PARTITION BY,则整个结果集视为一个分区。类似 GROUP BY,分区之间互不重叠,每行只属于一个分区。
- WINDOW ORDER 子句:对每个分区中的行进行排序,这可能与 ORDER BY 的顺序不同。
- WINDOW FRAME 子句:定义当前分区中哪些行属于当前窗口帧(frame)。对于结果集中的每一行,根据定义的上下边界,确定其窗口帧的范围。帧是“滑动窗口”的实例,其行按 WINDOW ORDER 子句的顺序排列。
- 默认窗口帧:
- 如果未指定 WINDOW FRAME,默认的窗口帧从分区的第一行开始,到当前行结束。
- 如果既未指定 WINDOW FRAME 又未指定 WINDOW ORDER,默认窗口帧扩展到分区的最后一行。
- 窗口函数操作:窗口函数在当前窗口帧上的所有行上执行计算。
示例
PARTITION BY 和 ORDER BY 示例
SELECT
producer,
model,
SUM(counter) OVER (PARTITION BY producer ORDER BY model) AS cumulative_sum
FROM
car_pool;
- PARTITION BY:按生产商(producer)分区。
- ORDER BY:在每个分区内按车型(model)排序。
- SUM(counter):计算累计和。
窗口帧示例
SELECT
producer,
model,
ROW_NUMBER() OVER (PARTITION BY producer ORDER BY counter DESC) AS rank
FROM
car_pool;
- ROW_NUMBER():为每个分区中的行按排序顺序分配唯一编号。
- ORDER BY counter DESC:在分区内按倒序排序。
总结
窗口函数提供了一种强大的工具,可以在不丢失每行数据的情况下执行高级分析。与 GROUP BY 不同,它允许保留每行的完整性,同时支持灵活的分组和滑动计算。
示例表
我们通过以下表来演示窗口函数的使用:
CREATE TABLE employee (
id DECIMAL PRIMARY KEY,
emp_name VARCHAR(20) NOT NULL,
dep_name VARCHAR(20) NOT NULL,
salary DECIMAL(7,2) NOT NULL,
age DECIMAL(3,0) NOT NULL,
CONSTRAINT employee_uk UNIQUE (emp_name, dep_name)
);
INSERT INTO employee VALUES ( 1, 'Matthew', 'Management', 4500, 55);
INSERT INTO employee VALUES ( 2, 'Olivia', 'Management', 4400, 61);
INSERT INTO employee VALUES ( 3, 'Grace', 'Management', 4000, 42);
INSERT INTO employee VALUES ( 4, 'Jim', 'Production', 3700, 35);
INSERT INTO employee VALUES ( 5, 'Alice', 'Production', 3500, 24);
INSERT INTO employee VALUES ( 6, 'Michael', 'Production', 3600, 28);
INSERT INTO employee VALUES ( 7, 'Tom', 'Production', 3800, 35);
INSERT INTO employee VALUES ( 8, 'Kevin', 'Production', 4000, 52);
INSERT INTO employee VALUES ( 9, 'Elvis', 'Service', 4100, 40);
INSERT INTO employee VALUES (10, 'Sophia', 'Sales', 4300, 36);
INSERT INTO employee VALUES (11, 'Samantha','Sales', 4100, 38);
COMMIT;
初步查询示例
以下示例演示窗口帧如何“滑动”并创建每行的帧。这些帧属于分区,分区属于结果集,结果集属于整个表。
SELECT id,
emp_name,
dep_name,
FIRST_VALUE(id) OVER (PARTITION BY dep_name ORDER BY id) AS frame_first_row,
LAST_VALUE(id) OVER (PARTITION BY dep_name ORDER BY id) AS frame_last_row,
COUNT(*) OVER (PARTITION BY dep_name ORDER BY id) AS frame_count,
LAG(id) OVER (PARTITION BY dep_name ORDER BY id) AS prev_row,
LEAD(id) OVER (PARTITION BY dep_name ORDER BY id) AS next_row
FROM employee;
查询结果
ID | EMP_NAME | DEP_NAME | FRAME_FIRST_ROW | FRAME_LAST_ROW | FRAME_COUNT | PREV_ROW | NEXT_ROW |
---|---|---|---|---|---|---|---|
1 | Matthew | Management | 1 | 1 | 1 | NULL | 2 |
2 | Olivia | Management | 1 | 2 | 2 | 1 | 3 |
3 | Grace | Management | 1 | 3 | 3 | 2 | NULL |
4 | Jim | Production | 4 | 4 | 1 | NULL | 5 |
5 | Alice | Production | 4 | 5 | 2 | 4 | 6 |
6 | Michael | Production | 4 | 6 | 3 | 5 | 7 |
7 | Tom | Production | 4 | 7 | 4 | 6 | 8 |
8 | Kevin | Production | 4 | 8 | 5 | 7 | NULL |
10 | Sophia | Sales | 10 | 10 | 1 | NULL | 11 |
11 | Samantha | Sales | 10 | 11 | 2 | 10 | NULL |
9 | Elvis | Service | 9 | 9 | 1 | NULL | NULL |
分析
- 分区:通过
PARTITION BY dep_name
将结果集分为 'Management'、'Production'、'Sales' 和 'Service' 4 个分区。 - 帧定义:未指定窗口帧,因此每帧从分区的第一行开始,到当前行结束。
- 帧计数:
FRAME_COUNT
表示帧中行数,从 1 增加到分区总行数,然后在新分区重新开始计数。 - LAG 和 LEAD:
PREV_ROW
和NEXT_ROW
显示分区中当前行的前一行和后一行。如果当前行是分区的第一行或最后一行,则返回NULL
。
窗口函数说明
以下是常用窗口函数及其含义:
函数 | 作用范围 | 返回值含义 |
---|---|---|
FIRST_VALUE(column) | 当前帧 | 帧中第一行的列值 |
LAST_VALUE(column) | 当前帧 | 帧中最后一行的列值 |
LAG(column) | 当前分区 | 当前行前一行的列值 |
LEAD(column) | 当前分区 | 当前行后一行的列值 |
ROW_NUMBER() | 当前帧 | 当前行在帧中的序号 |
RANK() | 当前帧 | 当前行的排名,指定排序值相同的行排名相同 |
NTH_VALUE(column, n) | 当前帧 | 帧中第 n 行的列值 |
SUM(column) | 当前帧 | 对列值求和 |
MIN(column) | 当前帧 | 返回列值最小值 |
MAX(column) | 当前帧 | 返回列值最大值 |
AVG(column) | 当前帧 | 对列值求平均值 |
COUNT(column) | 当前帧 | 计算帧中的行数 |
更多示例
查询示例
SELECT id,
emp_name,
dep_name,
ROW_NUMBER() OVER (PARTITION BY dep_name ORDER BY id) AS row_number_in_frame,
NTH_VALUE(emp_name, 2) OVER (PARTITION BY dep_name ORDER BY id) AS second_row_in_frame,
LEAD(emp_name, 2) OVER (PARTITION BY dep_name ORDER BY id) AS two_rows_ahead
FROM employee;
查询结果
ID | EMP_NAME | DEP_NAME | ROW_NUMBER_IN_FRAME | SECOND_ROW_IN_FRAME | TWO_ROWS_AHEAD |
---|---|---|---|---|---|
1 | Matthew | Management | 1 | NULL | Grace |
2 | Olivia | Management | 2 | Olivia | NULL |
3 | Grace | Management | 3 | Olivia | NULL |
4 | Jim | Production | 1 | NULL | Michael |
5 | Alice | Production | 2 | Alice | Tom |
6 | Michael | Production | 3 | Alice | Kevin |
7 | Tom | Production | 4 | Alice | NULL |
8 | Kevin | Production | 5 | Alice | NULL |
10 | Sophia | Sales | 1 | NULL | NULL |
11 | Samantha | Sales | 2 | Samantha | NULL |
9 | Elvis | Service | 1 | NULL | NULL |
总结
窗口函数以灵活且强大的方式支持分区、排序和帧的定义,使得对复杂数据分析更加直观。它们是处理大数据和 OLAP 应用程序的核心工具。
示例解析:三种窗口框架的比较
示例说明
- 当前帧中的行号: 使用
ROW_NUMBER()
函数显示当前行在帧中的位置。 - 帧中的第二行数据: 使用
NTH_VALUE(column, 2)
获取当前帧中第二行的列值。如果帧的行数不足两行,结果为NULL
。 - 当前行后两行数据: 使用
LEAD(column, 2)
获取分区中比当前行靠后两行的值。如果后续行不足两行,结果为NULL
。
例如:
SELECT id,
emp_name,
dep_name,
ROW_NUMBER() OVER (PARTITION BY dep_name ORDER BY id) AS row_number,
NTH_VALUE(emp_name, 2) OVER (PARTITION BY dep_name ORDER BY id) AS second_row,
LEAD(emp_name, 2) OVER (PARTITION BY dep_name ORDER BY id) AS two_rows_ahead
FROM employee;
分区与排序
分区(Partition)
通过 PARTITION BY dep_name
将数据按照部门分组,每个分区独立处理。
排序(Order)
通过 ORDER BY id
指定分区内的排序方式,这种顺序决定了帧的动态变化。
定义窗口帧
窗口帧通过 WINDOW FRAME
子句定义帧的上下边界,范围包括:
- 帧下界(Lower Boundary):起始行,可以是
UNBOUNDED PRECEDING
或n PRECEDING
。 - 帧上界(Upper Boundary):结束行,可以是
UNBOUNDED FOLLOWING
或n FOLLOWING
。
帧的类型可以通过以下三种模式定义:
- ROWS:按具体行数定义帧的范围。
- GROUPS:按唯一分组值的数量定义帧的范围。
- RANGE:按数值或日期区间定义帧的范围。
默认值
- 如果省略
WINDOW FRAME
子句,则默认范围是:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
。 - 如果省略
ORDER BY
,所有分区中的行合并为一个帧。
帧类型对比
1. ROWS:基于行的数量
按行数定义帧范围,例如:
SELECT id, dep_name, salary,
SUM(salary) OVER (PARTITION BY dep_name ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_rows
FROM employee;
解析:
- 帧范围:从当前行前两行开始,到当前行结束。
- 每帧包含的最大行数为 3(当前行 + 最多两行)。
2. GROUPS:基于唯一值分组
按排序列的唯一值分组,例如:
SELECT id, dep_name, salary,
SUM(salary) OVER (PARTITION BY dep_name ORDER BY salary GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_groups
FROM employee;
解析:
- 帧范围:当前行所在组的前一组、当前组和后一组的所有行。
- 适用于有重复值的列,能够处理按分组计算的需求。
3. RANGE:基于数值范围
按数值或日期区间定义帧范围,例如:
SELECT id, dep_name, salary,
SUM(salary) OVER (PARTITION BY dep_name ORDER BY salary RANGE BETWEEN 100 PRECEDING AND 50 FOLLOWING) AS sum_range
FROM employee;
解析:
- 帧范围:包含当前行,以及数值范围为当前行减去 100 到当前行加上 50 的所有行。
- 对于数值型或日期型数据特别有用。
注意事项
- 跨系统兼容性:
- 并非所有数据库系统都完全支持 SQL 标准的窗口函数。例如,
GROUPS
和RANGE
的行为可能在不同数据库中表现不同。
- 并非所有数据库系统都完全支持 SQL 标准的窗口函数。例如,
- 测试环境:
- 在实际使用窗口函数之前,需测试数据库对各种窗口帧的支持,确保符合预期。
- 性能影响:
- 窗口函数在大数据集上的计算成本较高,建议优化查询条件以提升效率。
窗口函数为复杂的数据分析提供了强大的工具,适用于时间序列、OLAP 分析和分组统计等场景。