结构化查询语言/窗口函数
本页面讨论的窗口函数是对“传统”函数的一种特殊且非常强大的扩展。它们不是对单个行进行计算，而是对一组行进行计算（类似于聚合函数与GROUP BY 语句一起使用）。这组行 - 关键点是 - 在所有由WHERE 语句确定的行上“移动”或“滑动”。这个“滑动窗口”被称为框架或 - 根据官方 SQL 标准 - “窗口框架”。
以下是一些示例
- 一个简单的例子是“滑动窗口”，它包含前一行、当前行和下一行。
- 窗口函数的一个典型应用领域是对任意时间序列的评估。如果你有股票市场价格的时间序列，你可以很容易地计算出过去 n 天的移动平均值。
- 窗口函数通常用于数据仓库和其他OLAP 应用。如果你有关于大量区域内所有产品在大量时期内的销售数据，你可以计算收入的统计指标。这种评估比简单的
GROUP BY 语句更强大。
与GROUP BY 语句形成对比的是，GROUP BY 语句每个组只有一行输出，而窗口函数保留结果集中所有行的标识并显示它们。
语法
[edit | edit source]窗口函数列在SELECT和FROM这两个关键字之间，与通常的函数和列位于同一位置。它们包含关键字OVER。
-- Window functions appear between the key words SELECT and FROM
SELECT ...,
<window_function>,
...
FROM <tablename>
...
;
-- They consist of three main parts:
-- 1. function type (which is the name of the function)
-- 2. key word 'OVER'
-- 3. specification, which rows constitute the 'sliding window' (partition, order and frame)
<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> := see below
总体描述
[edit | edit source]
关于窗口函数，有一些类似的概念。为了区分这些概念，有必要使用精确的术语。这些术语将在接下来的八段中介绍，它们也大致反映了执行顺序。前七步的目的是确定实际的框架，而第八步则对其实施操作。
WHERE 语句返回一定数量的行。它们构成结果集。ORDER BY 语句（在语法上位于WHERE 语句之后）将结果集重新排序成特定顺序。- 此顺序决定了将行传递给
SELECT 语句的顺序。实际传递给SELECT 语句的行被称为当前行。 WINDOW PARTITION 语句将结果集划分为窗口分区（我们将使用更短的术语分区，因为在我们的网站环境中，不会有混淆的风险）。如果没有WINDOW PARTITION 语句，结果集的所有行都构成一个分区。（这些分区等同于GROUP BY 语句创建的组）。分区彼此独立：没有重叠，因为结果集的每一行都只属于一个分区。WINDOW ORDER 语句对每个分区的行进行排序（可能与ORDER BY 语句不同）。WINDOW FRAME 语句定义实际分区中哪些行属于实际窗口框架（我们将使用更短的术语框架）。该语句为结果集的每一行定义一个框架。这是通过确定受影响行的上下边界来实现的。因此，结果集中行数有多少个（通常不同的）框架。上下边界会随着结果集的每一行而重新确定！单个行可能属于多个框架。实际框架是“滑动窗口”的实例化。它的行根据WINDOW ORDER 语句排序。- 如果没有
WINDOW FRAME 语句，则实际分区的行构成框架，具有以下默认边界：实际分区的第一行是它们的低边界，当前行是它们的上下边界。如果没有WINDOW FRAME 语句和WINDOW ORDER 语句，则上下边界将切换到实际分区的最后一行。下面我们将解释如何更改此默认行为。 - <window_function_type>s 对实际框架的行进行操作。
示例表
[edit | edit source]我们使用下表来演示窗口函数。
CREATE TABLE employee (
-- define columns (name / type / default value / column constraint)
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,
-- define table constraints (it's merely an example table)
CONSTRAINT empoyee_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;
第一个查询
[edit | edit source]此示例演示了边界如何在结果集中“滑动”。这样做，它们创建了一个接一个的框架，每个结果集行一个。这些框架是分区的一部分，分区是结果集的一部分，结果集是表的一部分。
SELECT id,
emp_name,
dep_name,
-- The functions FIRST_VALUE() and LAST_VALUE() explain themselves by their name. They act within the actual frame.
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,
-- The functions LAG() and LEAD() explain themselves by their name. They act within the actual partition.
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;
-- For simplification, we use the same PARTITION and ORDER definitions for all window functions.
-- This is not necessary, you can use divergent definitions.
请注意低边界（FRAME_FIRST_ROW）和上边界（FRAME_LAST_ROW）如何随行而变化。
| ID | EMP_NAME | DEP_NAME | FRAME_FIRST_ROW | FRAME_LAST_ROW | FRAME_COUNT | PREV_ROW | NEXT_ROW |
|---|---|---|---|---|---|---|---|
| 1 | Matthew | Management | 1 | 1 | 1 | - | 2 |
| 2 | Olivia | Management | 1 | 2 | 2 | 1 | 3 |
| 3 | Grace | Management | 1 | 3 | 3 | 2 | - |
| 4 | Jim | Production | 4 | 4 | 1 | - | 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 | - |
| 10 | Sophia | Sales | 10 | 10 | 1 | - | 11 |
| 11 | Samantha | Sales | 10 | 11 | 2 | 10 | - |
| 9 | Elvis | Service | 9 | 9 | 1 | - | - |
该查询没有WHERE 语句。因此，表的所有行都是结果集的一部分。根据WINDOW PARTITION 语句，即“PARTITION BY dep_name”，结果集被划分为 4 个分区：“Management”、“Production”、“Sales”和“Service”。框架在这些分区内运行。由于没有WINDOW FRAME 语句，因此框架从实际分区的首行开始，一直运行到当前行。
你可以看到框架内的实际行数（FRAME_COUNT 列）从 1 开始增长到分区内所有行的总和。当分区切换到下一个分区时，该数字将从 1 重新开始。
PREV_ROW 和 NEXT_ROW 列显示实际分区内前一行和下一行的 ID。由于首行没有前驱，因此显示了空指示器。对于最后一行及其后继，也相应适用。
基本窗口函数
[edit | edit source]我们介绍一些<window_function_type> 函数及其含义。标准以及大多数实现都包含其他函数和重载变体。
| 签名 | 范围 | 含义/返回值 |
|---|---|---|
| FIRST_VALUE(<column>) | 实际框架 | 框架内首行的列值。 |
| LAST_VALUE(<column>) | 实际框架 | 框架内最后一行是列的值。 |
| LAG(<column>) | 实际分区 | 前一行（当前行之前的行）的列值。 |
| LAG(<column>, <n>) | 实际分区 | 当前行之前第 n 行的列值。 |
| LEAD(<column>) | 实际分区 | 后继行的列值（当前行之后的行）。 |
| LEAD(<column>, <n>) | 实际分区 | 当前行之后第 n 行的列值。 |
| 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 | - | Grace |
| 2 | Olivia | Management | 2 | Olivia | - |
| 3 | Grace | Management | 3 | Olivia | - |
| 4 | Jim | Production | 1 | - | Michael |
| 5 | Alice | Production | 2 | Alice | Tom |
| 6 | Michael | Production | 3 | Alice | Kevin |
| 7 | Tom | Production | 4 | Alice | - |
| 8 | Kevin | Production | 5 | Alice | - |
| 10 | Sophia | Sales | 1 | - | - |
| 11 | Samantha | Sales | 2 | Samantha | - |
| 9 | Elvis | Service | 1 | - | - |
这三个例子展示了
- 实际帧内的行号。
- 实际帧内第二行的员工姓名。这并非所有情况下都可行。a) 每个分区帧系列中的第一个帧仅包含 1 行。b) 最后一个分区及其唯一的帧仅包含一行。
- 领先当前行两行的员工姓名。与上一列类似，这并非所有情况下都可行。
- 请注意第一行最后两列的区别。SECOND_ROW_IN_FRAME 列包含 NULL 指示符。与该行关联的帧仅包含 1 行（从第一行到当前行） - 并且 nth_value() 函数的作用域为“帧”。相反，TWO_ROW_AHEAD 列包含值“Grace”。该值由 lead() 函数评估，该函数的作用域为分区！该分区包含 3 行：部门“管理”中的所有行。只有在第二行和第三行中，才能无法向前移动 2 行。
如上例所示，WINDOW PARTITION 子句 使用关键字 PARTITION BY 来定义分区，WINDOW ORDER 子句 使用关键字 ORDER BY 来定义分区内行的顺序。
帧由 WINDOW FRAME 子句 定义，该子句可以选择跟在 WINDOW PARTITION 子句 和 WINDOW ORDER 子句 之后。
除了作用域为实际分区的 lead() 和 lag() 函数之外，所有其他窗口函数都作用于实际帧。因此，一个基本决定是哪些行应构成帧。这是通过建立上下边界（在 WINDOW ORDER 子句 的意义上）来完成的。这两个边界内的所有行都构成实际帧。因此，WINDOW FRAME 子句 主要由两个边界的定义组成 - 以四种方式之一
- 在当前行之前和之后定义一定数量的 行。这会导致帧系列中包含恒定的行数 - 有一些例外情况出现在上下边界附近，以及使用“UNBOUNDED”关键字的例外情况。
- 在当前行之前和之后定义一定数量的 组。这些组由之前和之后行的唯一值构建 - 与
SELECT DISTINCT ...或GROUP BY相同。生成的帧涵盖所有值属于其中一个组的行。由于每个组都可能由多行（具有相同值）组成，因此每个帧的行数并不恒定。 - 通过指定一个固定的数值来定义某个列的值的 范围，例如：1.000（对于薪资）或 30 天（对于时间序列）。定义的范围从当前值与定义值的差值运行到当前值（FOLLOWING 情况构建总和，而不是差值）。分区中所有列值落在该范围内的行都构成帧。相应地，帧内的行数可能因帧而异 - 与 行 技术相反。
- 省略子句并使用默认值。
根据这些不同的策略，有三个关键字“ROWS”、“GROUPS”和“RANGE”，它们会导致不同的行为。
WINDOW FRAME 子句 使用一些关键字来修改或指定分区的有序行在何处可视化。
Rows in a partition and the related keywords - <-- UNBOUNDED PRECEDING (first row) ... - <-- 2 PRECEDING - <-- 1 PRECEDING - <-- CURRENT ROW - <-- 1 FOLLOWING - <-- 2 FOLLOWING ... - <-- UNBOUNDED FOLLOWING (last row)
术语 UNBOUNDED PRECEDING 表示分区中的第一行，而 UNBOUNDED FOLLOWING 表示最后一行。从 CURRENT ROW 开始计算，有 <n> PRECEDING 行和 <n> FOLLOWING 行。显然，这种 PRECEDING/FOLLOWING 术语仅在存在 WINDOW ORDER 子句 时有效，该子句创建了一个明确的序列。
WINDOW FRAME 子句 的（简化）语法为
<window_frame> := [ROWS | GROUPS | RANGE ] BETWEEN
[ UNBOUNDED PRECEDING | <n> PRECEDING | CURRENT ROW ] AND
[ UNBOUNDED FOLLOWING | <n> FOLLOWING | CURRENT ROW ]
包含 WINDOW FRAME 子句 的完整窗口函数示例为
...
SUM(salary) OVER (PARTITION BY dep_name ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as growing_sum,
...
在这种情况下，WINDOW FRAME 子句 以关键字“ROWS”开头。它将下边界定义为分区的第一个行，并将上边界定义为实际行。这意味着帧系列从帧到帧增长一行，直到处理完分区中的所有行。之后，下一个分区从一个 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_over_1or2or3_rows
FROM employee;
该示例作用于特定数量的行，即当前行之前的两行（如果存在于分区内）和当前行。没有一种情况是帧内存在超过三行的。窗口函数计算这最多三行的薪资总和。
在每个新分区中，总和将重置为零，在本例中即为部门。这也适用于 GROUPS 和 RANGE 语法。
当人们对特定数量行的平均值或两行之间的距离感兴趣时，ROWS 语法经常使用。
GROUPS 语法的语义与 ROWS 语法类似 - 只有一个例外：WINDOW ORDER 子句 列中具有相同值的行计为 1 行。GROUPS 语法计算不同值的个数，而不是行的个数。
-- Hint: The syntax 'GROUPS' (Feature T620) is not supported by Oracle 11
SELECT id, dep_name, salary,
SUM(salary) OVER (PARTITION BY dep_name ORDER BY salary
GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_over_groups
FROM employee;
该示例以关键字 GROUPS 开头，并定义它希望对“salary”列的三个不同值进行操作。可能有多于三行满足这些条件 - 与等效的 ROWS 策略相反。
当人们在审查期间具有变化的行数时，GROUPS 语法是合适的策略，例如：人们每天具有变化的测量值个数，并且对一周或一个月内方差的平均值感兴趣。
乍一看，RANGE 语法与 ROWS 和 GROUPS 语法类似。但语义却大不相同！此语法中给出的数字 <n> 未指定任何计数器。它们指定了当前行中的值与下边界或上边界之间的 距离。因此，ORDER BY 列的类型应为 NUMERIC、DATE 或 INTERVAL。
SELECT id, dep_name, salary,
SUM(salary) OVER (PARTITION BY dep_name ORDER BY salary
RANGE BETWEEN 100 PRECEDING AND 50 FOLLOWING) AS sum_over_range
FROM employee;
此定义会导致对所有薪资低于当前行 100 并高于当前行 50 的行进行求和。在我们的示例表中，此条件在某些情况下适用于多于 1 行。
RANGE 策略的典型用例是人们分析宽数值范围并期望在此范围内仅遇到几行的情况，例如：稀疏矩阵。
如果省略了 WINDOW FRAME 子句，则其默认值为：'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'。这会导致从分区的第一个行到当前行加上所有具有与当前行相同值的行的范围 - 因为应用了 RANGE 语法。
如果省略了 WINDOW ORDER 子句，则不允许使用 WINDOW FRAME 子句，并且分区的全部行都构成帧。
如果省略了 PARTITION BY 子句，则结果集的所有行都构成唯一的分区。
尽管 SQL 标准 2003 及其后续版本对窗口函数定义了非常明确的规则，但一些实现并没有遵循这些规则。一些供应商只实现了标准的一部分——这是他们自己的责任——但其他供应商似乎以一种奇特的方式解释了标准。
据我们所知，ROWS 语法在实现时符合标准。但似乎 RANGE 语法有时实现了 SQL 标准中 GROUPS 语法所需的功能。（也许这是一个误解，只是各种实现的公开可用描述没有反映出细节。）所以：小心，测试你的系统，并给我们反馈在讨论页面上。
显示 id、emp_name、dep_name、salary 和部门内的平均工资。
--
-- To get the average of the department, every frame must be build by ALL rows of the department.
--
SELECT id, emp_name, dep_name, salary,
AVG(salary) OVER (PARTITION BY dep_name ORDER BY dep_name
-- all rows of partition (=department)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as avg_salary
FROM employee;
--
-- It's possible to omit the 'window order' clause. Thereby the frames include ALL rows of the actual partition.
-- See: 'Defaults' above.
--
SELECT id, emp_name, dep_name, salary,
AVG(salary) OVER (PARTITION BY dep_name) as avg_salary
FROM employee;
--
-- The following statements lead to different results as the frames are composed of a growing number of rows.
--
SELECT id, emp_name, dep_name, salary,
AVG(salary) OVER (PARTITION BY dep_name ORDER BY salary) as avg_salary
FROM employee;
--
-- It's possible to sort the result set by arbitrary rows (test the emp_name, it's interesting)
--
SELECT id, emp_name, dep_name, salary,
AVG(salary) OVER (PARTITION BY dep_name) as avg_salary
FROM employee
ORDER BY dep_name, salary;
老年人赚的钱比年轻人多吗？
要给出答案，请显示 id、emp_name、salary、age 和 3（或 5）个年龄相近的人的平均工资。
SELECT id, emp_name, salary, age,
AVG(salary) OVER ( ORDER BY age ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS mean_over_3,
AVG(salary) OVER ( ORDER BY age ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS mean_over_5
FROM employee;
-- As there is no restriction to any other criterion than the age (department or something else), there is
-- no need for any PARTITION definition. Averages are computed without any interruption.
扩展上述问题及其解决方案，以显示四个部门的结果。
SELECT id, emp_name, salary, age, dep_name,
AVG(salary) OVER (PARTITION BY dep_name ORDER BY age ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS mean_over_3,
AVG(salary) OVER (PARTITION BY dep_name ORDER BY age ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS mean_over_5
FROM employee;
-- Averages are computed WITHIN departments.
显示 id、emp_name、salary 和与之前人员（按 ID 顺序）薪资的差额。
-- For mathematician: This is a very first approximation to first derivate.
SELECT id, emp_name, salary,
salary - LAG(salary) OVER (ORDER BY id) as diff_salary
FROM employee;
-- And the difference of differences:
SELECT id, emp_name, salary,
(LAG(salary) OVER (ORDER BY id) - salary) AS diff_salary_1,
(LAG(salary) OVER (ORDER BY id) - salary) -
(LAG(salary, 2) OVER (ORDER BY id) - LAG(salary) OVER (ORDER BY id)) AS diff_salary_2
FROM employee;
显示值的“周围环境”：按 emp_name 排序的所有人员的 id 和 emp_name。在每行中补充前两个 emp_name 和实际 emp_name 后面的两个（按通常的字母顺序）。
SELECT id,
LAG(emp_name, 2) OVER (ORDER BY emp_name) AS before_prev,
LAG(emp_name) OVER (ORDER BY emp_name) AS prev,
emp_name AS act,
LEAD(emp_name) OVER (ORDER BY emp_name) AS follower,
LEAD(emp_name, 2) OVER (ORDER BY emp_name) AS behind_follower
FROM employee
ORDER BY emp_name;