本页讨论的窗口函数是一种强大的扩展功能,区别于传统的单行计算函数。窗口函数的结果不是基于单行计算,而是基于一组行(类似于聚合函数和 GROUP BY 子句的结合)。关键点在于,这组行会“滑动”或“移动”到通过 WHERE 子句确定的所有行上。这种“滑动窗口”被称为帧(frame),或者按照 SQL 标准的术语,称为“窗口帧”(window frame)。

示例

  1. 一个简单的滑动窗口可以包括前一行、当前行和下一行。
  2. 窗口函数常用于分析任意时间序列数据。例如,通过股票的时间序列数据,计算过去 n 天的移动平均值。
  3. 窗口函数广泛应用于数据仓库和 OLAP 分析中。例如,对于跨多个地区和多个时间段的产品销售数据,可以通过窗口函数计算销售收入的统计指标。这种评估比单纯的 GROUP BY 子句更加强大。

GROUP BY 不同,窗口函数保留了结果集中每一行的标识,因此每行都会显示在最终结果中。


语法

窗口函数出现在 SELECTFROM 之间,与普通函数和列名并列。它们包含关键字 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>         := 定义窗口帧,详见下文

整体描述

使用窗口函数时,需要清晰地理解以下概念和步骤。这些步骤大致反映了窗口函数的执行顺序。前七步的目标是确定当前的窗口帧,第八步是在窗口帧上执行操作。

  1. WHERE 子句:返回符合条件的所有行,构成结果集。
  2. ORDER BY 子句:对结果集重新排序,形成特定的顺序。
  3. 当前行:排序后的结果集逐行传递给 SELECT 子句,当前处理的行称为当前行(current row)。
  4. PARTITION BY 子句:将结果集分为多个分区(partition)。如果未指定 PARTITION BY,则整个结果集视为一个分区。类似 GROUP BY,分区之间互不重叠,每行只属于一个分区。
  5. WINDOW ORDER 子句:对每个分区中的行进行排序,这可能与 ORDER BY 的顺序不同。
  6. WINDOW FRAME 子句:定义当前分区中哪些行属于当前窗口帧(frame)。对于结果集中的每一行,根据定义的上下边界,确定其窗口帧的范围。帧是“滑动窗口”的实例,其行按 WINDOW ORDER 子句的顺序排列。
  7. 默认窗口帧
    • 如果未指定 WINDOW FRAME,默认的窗口帧从分区的第一行开始,到当前行结束。
    • 如果既未指定 WINDOW FRAME 又未指定 WINDOW ORDER,默认窗口帧扩展到分区的最后一行。
  8. 窗口函数操作:窗口函数在当前窗口帧上的所有行上执行计算。

示例

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 和 LEADPREV_ROWNEXT_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 应用程序的核心工具。

示例解析:三种窗口框架的比较

示例说明

  1. 当前帧中的行号: 使用 ROW_NUMBER() 函数显示当前行在帧中的位置。
  2. 帧中的第二行数据: 使用 NTH_VALUE(column, 2) 获取当前帧中第二行的列值。如果帧的行数不足两行,结果为 NULL
  3. 当前行后两行数据: 使用 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 子句定义帧的上下边界,范围包括:

  1. 帧下界(Lower Boundary):起始行,可以是 UNBOUNDED PRECEDINGn PRECEDING
  2. 帧上界(Upper Boundary):结束行,可以是 UNBOUNDED FOLLOWINGn 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 的所有行。
  • 对于数值型或日期型数据特别有用。

注意事项

  1. 跨系统兼容性
    • 并非所有数据库系统都完全支持 SQL 标准的窗口函数。例如,GROUPSRANGE 的行为可能在不同数据库中表现不同。
  2. 测试环境
    • 在实际使用窗口函数之前,需测试数据库对各种窗口帧的支持,确保符合预期。
  3. 性能影响
    • 窗口函数在大数据集上的计算成本较高,建议优化查询条件以提升效率。

窗口函数为复杂的数据分析提供了强大的工具,适用于时间序列、OLAP 分析和分组统计等场景。

最后修改: 2025年01月28日 星期二 14:23