SQL 窗口函数 零基础教程 – wiki基地

作者： /

SQL 窗口函数零基础教程：告别 Group By 的烦恼，解锁更强大的数据分析能力

1. 什么是窗口函数？为什么我们需要它？

想象一下，你有一张销售订单表（orders），里面记录了每笔订单的销售金额、销售员、销售区域等等信息。

现在，你想要做一些分析：

  1. 计算每个销售员的总销售额。 -> 使用 GROUP BY 可以轻松搞定。
  2. 计算整个公司的总销售额。 -> 使用聚合函数 SUM() 即可。
  3. 计算每个销售区域的总销售额，并且在结果中同时显示每笔订单的详细信息以及该订单所在区域的总销售额。 -> 这时候 GROUP BY 就不好使了。GROUP BY 会将同一个区域的多行订单“折叠”成一行区域总计，而你想要的是 保留每一行原始订单数据，同时在旁边附带上区域总计信息。
  4. 计算每个销售员在所有销售员中的销售额排名。
  5. 计算每个销售员在其所在区域内的销售额排名。
  6. 计算每个订单相对于前一笔订单的销售额增长或下降。
  7. 计算某个销售员至今为止的累积销售额（Running Total）。

对于需求 3-7，传统的 GROUP BY 聚合函数就显得力不从心了。它们只能对分组后的结果进行汇总，而无法在保留原始行信息的同时进行跨行计算或分组计算。

窗口函数（Window Function） 应运而生，就是为了解决这类问题。

简单来说，窗口函数允许你在与当前行相关的 一组行（这个“组”就是“窗口”） 上执行计算，而 不会将这些行折叠成单个输出行。这意味着你可以在每一行原始数据旁边，看到基于其“窗口”计算出的结果。

它就像你在看一张详细的数据报表时，旁边有一列是该项目所属类别的总计、另一列是该项目在类别内的排名一样，既有明细，又有汇总/比较信息。

2. 窗口函数的基本语法

窗口函数的基本语法结构如下：

sql
窗口函数名 ( [参数列表] ) OVER ( [PARTITION BY 列名1, 列名2, ...] [ORDER BY 列名3 [ASC|DESC], ...] [窗口帧] )

我们来分解这个语法：

  • 窗口函数名: 你要使用的窗口函数，比如 SUM(), AVG(), COUNT(), MAX(), MIN() (作为窗口函数使用时), 或者专门的窗口函数如 ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTILE() 等。
  • ([参数列表]): 某些窗口函数需要参数，比如 SUM() 需要指定要求和的列，NTILE() 需要指定分组数。
  • OVER(): 这是定义窗口函数的核心关键字。 它的存在告诉数据库引擎：这是一个窗口函数，后面的括号定义了它的“窗口”。
    • 如果 OVER() 括号内为空，表示窗口是整个结果集。
    • 如果 OVER() 括号内有内容，则根据内容定义窗口的范围和排序。
  • [PARTITION BY 列名1, 列名2, ...]: 划分窗口。 这个子句类似于 GROUP BY，它将结果集划分成多个独立的“分区”（Partition）。窗口函数会分别在每个分区内独立计算。如果省略 PARTITION BY，则整个结果集被视为一个单一的分区。
  • [ORDER BY 列名3 [ASC|DESC], ...]: 指定窗口内行的顺序。 这个子句定义了在每个分区（或整个结果集）内部，行应该按照什么顺序排列。这对于一些窗口函数（如排名函数、累积函数、LAG/LEAD 函数）至关重要，因为它们的计算依赖于行的顺序。
  • [窗口帧]: 定义当前行计算的范围。 这是一个更高级的概念，用于指定窗口函数在当前分区内计算时，具体包含哪些行（比如，当前行以及它前面的2行）。最常见的窗口帧是 ROWS BETWEEN ... AND ... 或 RANGE BETWEEN ... AND ...。如果省略窗口帧，不同的窗口函数有不同的默认帧。对于像 RANK() 这样的排名函数，窗口帧通常不重要，但对于聚合函数作为窗口函数（如计算移动平均或累积和）则非常重要。我们稍后详细讲解。

3. 理解 OVER()、PARTITION BY 和 ORDER BY

理解这三个部分是掌握窗口函数的关键。我们通过一个简单的例子来说明。

假设我们有一张 employees 表：

“`sql
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(101, ‘Alice’, ‘Sales’, 50000),
(102, ‘Bob’, ‘Sales’, 60000),
(103, ‘Charlie’, ‘IT’, 70000),
(104, ‘David’, ‘IT’, 75000),
(105, ‘Eve’, ‘Sales’, 55000),
(106, ‘Frank’, ‘IT’, 65000),
(107, ‘Grace’, ‘Sales’, 62000);
“`

例子 1: 最简单的 OVER() – 计算总平均工资

sql
SELECT
employee_name,
department,
salary,
AVG(salary) OVER() AS total_average_salary -- OVER()为空，窗口是整个结果集
FROM
employees;

结果可能类似：

employee_name | department | salary | total_average_salary
--------------|------------|---------|----------------------
Alice | Sales | 50000.00 | 62428.5714...
Bob | Sales | 60000.00 | 62428.5714...
Charlie | IT | 70000.00 | 62428.5714...
David | IT | 75000.00 | 62428.5714...
Eve | Sales | 55000.00 | 62428.5714...
Frank | IT | 65000.00 | 62428.5714...
Grace | Sales | 62000.00 | 62428.5714...
解释：AVG(salary) OVER() 计算了所有员工工资的平均值，并将这个平均值显示在每一行旁边。注意，原始的每一行数据都得到了保留。

例子 2: 使用 PARTITION BY – 计算每个部门的平均工资

sql
SELECT
employee_name,
department,
salary,
AVG(salary) OVER(PARTITION BY department) AS department_average_salary
FROM
employees;

结果可能类似：

employee_name | department | salary | department_average_salary
--------------|------------|---------|---------------------------
Alice | Sales | 50000.00 | 56750.0000... -- Sales部门平均工资
Bob | Sales | 60000.00 | 56750.0000...
Eve | Sales | 55000.00 | 56750.0000...
Grace | Sales | 62000.00 | 56750.0000...
Charlie | IT | 70000.00 | 70000.0000... -- IT部门平均工资
David | IT | 75000.00 | 70000.0000...
Frank | IT | 65000.00 | 70000.0000...
解释：PARTITION BY department 将数据分成了 ‘Sales’ 和 ‘IT’ 两个分区。AVG(salary) 分别在每个分区内计算。所以 Sales 部门的员工行显示的是 Sales 部门的平均工资，IT 部门的员工行显示的是 IT 部门的平均工资。

例子 3: 使用 ORDER BY – 为所有员工排序并编号

这里我们介绍一个排名函数 ROW_NUMBER()。它为窗口内的每一行分配一个唯一的、连续的序号。

sql
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER(ORDER BY salary DESC) AS overall_rank
FROM
employees
ORDER BY
overall_rank; -- 通常我们会按排名排序结果

结果可能类似：

employee_name | department | salary | overall_rank
--------------|------------|---------|--------------
David | IT | 75000.00 | 1
Charlie | IT | 70000.00 | 2
Frank | IT | 65000.00 | 3
Grace | Sales | 62000.00 | 4
Bob | Sales | 60000.00 | 5
Eve | Sales | 55000.00 | 6
Alice | Sales | 50000.00 | 7
解释：ORDER BY salary DESC 指定了窗口内的排序规则（按工资降序）。ROW_NUMBER() OVER(...) 则根据这个顺序给每一行编上号，从1开始。因为没有 PARTITION BY，窗口是整个结果集，所以这是所有员工的工资排名。

例子 4: 结合 PARTITION BY 和 ORDER BY – 计算部门内员工排名

这是窗口函数最常见的用法模式之一。

sql
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS department_rank
FROM
employees
ORDER BY
department, department_rank; -- 通常按部门和部门内排名排序

结果可能类似：

“`
employee_name | department | salary | department_rank
————–|————|———|—————–
Charlie | IT | 70000.00 | 1 — IT 部门排名
David | IT | 75000.00 | 2 — Oops! salary 75000 应该排第一。
Frank | IT | 65000.00 | 3 — Let’s fix the sample data order for clarity.
— Re-inserting data in a more predictable order for illustration
— (In reality, order in query results is not guaranteed without ORDER BY)
— Let’s re-run and imagine a better sorted output:

— (After re-sorting the conceptual output)
department | employee_name | salary | department_rank
————|—————|———|—————–
IT | David | 75000.00 | 1 — IT 部门排名
IT | Charlie | 70000.00 | 2
IT | Frank | 65000.00 | 3
Sales | Grace | 62000.00 | 1 — Sales 部门排名
Sales | Bob | 60000.00 | 2
Sales | Eve | 55000.00 | 3
Sales | Alice | 50000.00 | 4
``
解释：PARTITION BY department将数据按部门分成 IT 和 Sales 两个组。ORDER BY salary DESC在每个组内按工资降序排序。ROW_NUMBER()` 分别在 IT 组内和 Sales 组内进行编号，互不影响。所以 David 是 IT 部门的第一名，Grace 是 Sales 部门的第一名。

4. 常见的窗口函数

窗口函数可以分为几类：

4.1 排名函数 (Ranking Functions)

这类函数用于在窗口内为每一行分配一个排名。它们都依赖于 OVER() 子句中的 ORDER BY。

  • ROW_NUMBER(): 为分区内的每一行分配一个唯一的、连续的整数排名，从1开始。即使值相同，排名也不同。
  • RANK(): 为分区内的每一行分配一个排名。如果有多行具有相同的值，它们将获得相同的排名，下一个排名会跳过相应的数字。
  • DENSE_RANK(): 为分区内的每一行分配一个排名。如果有多行具有相同的值，它们将获得相同的排名，下一个排名是紧挨着的下一个整数，不会跳过数字。
  • NTILE(n): 将分区内的行分成 n 个大致相等的部分，并为每一行分配一个从1到 n 的整数，表示它属于哪一部分（例如，用于将数据分成四分位数）。

继续使用 employees 表，看排名函数的区别：

“`sql
— 假设我们有两名员工工资相同，看看排名函数的区别
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(108, ‘Diana’, ‘IT’, 70000); — 与Charlie工资相同

SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS rnk_row_number,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rnk_rank,
DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rnk_dense_rank
FROM
employees
ORDER BY
department, salary DESC;
“`

结果可能类似（关注IT部门，Charlie和Diana工资相同）：

employee_name | department | salary | rnk_row_number | rnk_rank | rnk_dense_rank
--------------|------------|---------|----------------|----------|----------------
David | IT | 75000.00 | 1 | 1 | 1
Charlie | IT | 70000.00 | 2 | 2 | 2
Diana | IT | 70000.00 | 3 | 2 | 2
Frank | IT | 65000.00 | 4 | 4 | 3 -- 注意：rank跳过了3，dense_rank没有
Grace | Sales | 62000.00 | 1 | 1 | 1
Bob | Sales | 60000.00 | 2 | 2 | 2
Eve | Sales | 55000.00 | 3 | 3 | 3
Alice | Sales | 50000.00 | 4 | 4 | 4
解释：
* ROW_NUMBER(): Charlie是IT部门工资第二高的，Diana是第三高的，即使他们工资一样，排名也不同。
* RANK(): Charlie和Diana工资一样，都是并列第2名。因为占用了第2和第3的位置，下一个Frank的排名是第4名（跳过了3）。
* DENSE_RANK(): Charlie和Diana工资一样，都是并列第2名。下一个Frank的排名是第3名（没有跳过数字）。

选择哪个排名函数取决于你处理并列排名时的具体需求。

NTILE(n) 示例：将所有员工按工资分成3组。

sql
SELECT
employee_name,
department,
salary,
NTILE(3) OVER(ORDER BY salary DESC) AS salary_group
FROM
employees
ORDER BY
salary_group, salary DESC;

结果会将员工大致平均分配到1、2、3组中。

4.2 值函数 (Value Functions)

这类函数用于获取窗口内特定行的值。它们通常依赖于 OVER() 子句中的 ORDER BY。

  • LAG(列名, offset, default): 获取当前行 之前 偏移量为 offset 的行的 列名 的值。offset 默认为 1。如果指定偏移量的行不存在，返回 default 值，default 默认为 NULL。
  • LEAD(列名, offset, default): 获取当前行 之后 偏移量为 offset 的行的 列名 的值。用法同 LAG()。
  • FIRST_VALUE(列名): 获取窗口内第一行的 列名 的值。
  • LAST_VALUE(列名): 获取窗口内最后一行的 列名 的值。注意：LAST_VALUE 的默认窗口帧只到当前行，可能需要显式指定窗口帧才能获取分区内的最后一个值。

LAG 和 LEAD 示例：计算员工工资与上一位/下一位员工的工资差。

sql
SELECT
employee_name,
department,
salary,
LAG(salary, 1, 0) OVER(PARTITION BY department ORDER BY salary ASC) AS previous_salary, -- 按工资升序排，看前一个人的工资
salary - LAG(salary, 1, 0) OVER(PARTITION BY department ORDER BY salary ASC) AS salary_increase,
LEAD(salary, 1, 0) OVER(PARTITION BY department ORDER BY salary ASC) AS next_salary
FROM
employees
ORDER BY
department, salary ASC;
解释：我们在每个部门内按工资升序排列。LAG(salary, 1, 0) 获取同一部门内前一位员工的工资（如果当前行是部门第一个，则返回0）。LEAD(salary, 1, 0) 获取后一位员工的工资（如果当前行是部门最后一个，则返回0）。

FIRST_VALUE 示例：获取每个部门工资最高的员工的工资。

sql
SELECT
employee_name,
department,
salary,
FIRST_VALUE(salary) OVER(PARTITION BY department ORDER BY salary DESC) AS highest_department_salary
FROM
employees
ORDER BY
department, salary DESC;
解释：在每个部门内按工资降序排序。FIRST_VALUE(salary) 获取每个部门分区内的第一行（即工资最高的那一行）的工资值。

LAST_VALUE 示例：获取每个部门工资最低的员工的工资。

sql
SELECT
employee_name,
department,
salary,
LAST_VALUE(salary) OVER(PARTITION BY department ORDER BY salary ASC) AS lowest_department_salary -- 按工资升序排，最后一行是最低工资
FROM
employees
ORDER BY
department, salary ASC;
解释：在每个部门内按工资升序排序。LAST_VALUE(salary) 默认的窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 或 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这意味着它只看到分区开始到当前行的范围。为了看到分区内的最后一行，我们需要修改窗口帧。

5. 窗口帧 (Window Frame)

窗口帧定义了在当前分区内，窗口函数计算时具体包含哪些行。它通常在 OVER() 子句的 ORDER BY 之后使用。

语法：
sql
ROWS BETWEEN 起始点 AND 结束点
RANGE BETWEEN 起始点 AND 结束点

  • ROWS: 基于物理行偏移量定义帧。例如，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示当前行以及它前面和后面各一行。
  • RANGE: 基于当前行某个列的值的范围定义帧。例如，RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW 可能表示当前行以及它之前7天内的所有行（基于日期列）。RANGE 需要 ORDER BY 子句，且 ORDER BY 的列通常用于定义范围。

可能的起始点和结束点：
* UNBOUNDED PRECEDING: 分区的开始。
* n PRECEDING: 当前行之前的 n 行/值。
* CURRENT ROW: 当前行本身。
* n FOLLOWING: 当前行之后的 n 行/值。
* UNBOUNDED FOLLOWING: 分区的结束。

示例：计算累积工资 (Running Total)

“`sql
— 假设我们有订单表 sales，记录了每日销售额
CREATE TABLE daily_sales (
sale_date DATE,
amount DECIMAL(10, 2)
);

INSERT INTO daily_sales (sale_date, amount) VALUES
(‘2023-01-01’, 100.00),
(‘2023-01-02’, 150.00),
(‘2023-01-03’, 120.00),
(‘2023-01-04’, 200.00),
(‘2023-01-05’, 180.00);

SELECT
sale_date,
amount,
SUM(amount) OVER(ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM
daily_sales
ORDER BY
sale_date;
“`

结果：

sale_date | amount | running_total
------------|--------|---------------
2023-01-01 | 100.00 | 100.00 -- 只有第一行
2023-01-02 | 150.00 | 250.00 -- 100 + 150
2023-01-03 | 120.00 | 370.00 -- 100 + 150 + 120
2023-01-04 | 200.00 | 570.00 -- ...
2023-01-05 | 180.00 | 750.00 -- ...
解释：ORDER BY sale_date 确保了按日期顺序计算。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义了窗口帧：从分区开始（这里没有分区，就是整个结果集的开始）到当前行。SUM(amount) 在这个帧内求和，就得到了累积和。

示例：计算移动平均 (Moving Average)

计算每个日期及其前2天的3天移动平均销售额：

sql
SELECT
sale_date,
amount,
AVG(amount) OVER(ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average_3days
FROM
daily_sales
ORDER BY
sale_date;

结果：

sale_date | amount | moving_average_3days
------------|--------|----------------------
2023-01-01 | 100.00 | 100.00 -- (100)/1
2023-01-02 | 150.00 | 125.00 -- (100+150)/2
2023-01-03 | 120.00 | 123.33 -- (100+150+120)/3
2023-01-04 | 200.00 | 156.67 -- (150+120+200)/3
2023-01-05 | 180.00 | 166.67 -- (120+200+180)/3
解释：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义窗口帧：从当前行之前的第2行开始，直到当前行结束。AVG(amount) 在这个3行的窗口帧内计算平均值。

默认窗口帧：
* 如果 OVER() 子句中 没有 ORDER BY，则默认窗口帧是整个分区：RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (或者 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)。聚合函数在这种情况下计算的是整个分区（或整个结果集）的聚合值，并显示在每一行。这解释了我们前面例子1和例子2的行为。
* 如果 OVER() 子句中 有 ORDER BY，则默认窗口帧取决于使用的函数：
* 对于排名函数 (ROW_NUMBER, RANK, DENSE_RANK, NTILE)，窗口帧通常不影响结果，可以认为是整个分区。
* 对于聚合函数 (SUM, AVG, etc.)，默认窗口帧通常是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (或等效的 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)。这解释了为什么 SUM() OVER(ORDER BY ...) 会计算累积和。
* 对于 FIRST_VALUE, LAST_VALUE, LAG, LEAD 等函数，默认帧的行为可能略有不同，但通常与 ORDER BY 有关。LAST_VALUE 是一个特别需要注意的，它的默认帧可能只到 CURRENT ROW，导致它返回当前行的值，而不是分区内的最后一个值。显式指定 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 可以解决这个问题。

掌握窗口帧的概念可以让你更灵活地控制聚合函数作为窗口函数时的计算范围，实现更复杂的分析，如移动总和、移动平均、百分比等。

6. 窗口函数的常见应用场景总结

  • 排名 (Ranking): 在一个分组内（或整个数据集）对数据进行排序并获取排名（ROW_NUMBER, RANK, DENSE_RANK）。
  • 分块/分位数 (Quantiles): 将数据分成 N 个大致相等的组（NTILE）。
  • 前后比较 (Lag/Lead Analysis): 比较当前行的值与前一行或后一行的值（LAG, LEAD）。常用于计算增长率、差值等。
  • 聚合计算不折叠行 (Aggregates without Grouping): 在保留原始行的同时，计算分组（分区）的总和、平均值、最大/最小值等（SUM() OVER(...), AVG() OVER(...) 等）。
  • 累积计算 (Running Totals): 计算到当前行为止的累积值（SUM() OVER(ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)）。
  • 移动计算 (Moving Averages/Sums): 在一个滑动窗口内计算平均值或总和（AVG() OVER(ORDER BY ... ROWS BETWEEN n PRECEDING AND m FOLLOWING)）。
  • 获取边界值 (Boundary Values): 获取分区内的第一个或最后一个值（FIRST_VALUE, LAST_VALUE）。
  • 计算百分比 (Percentage of Total/Group): 结合聚合窗口函数计算某个值占总值或分组总值的百分比。例如：salary * 100.0 / SUM(salary) OVER(PARTITION BY department) 计算每个员工工资占部门总工资的百分比。

7. 窗口函数与 GROUP BY 的区别

再次强调两者的核心区别：

  • GROUP BY + 聚合函数: 将多行聚合成 一行，丢失原始行的明细信息。
  • 窗口函数 + 聚合函数: 在多行上进行计算，但 保留所有原始行，并在每行旁边显示计算结果。

举例来说：

使用 GROUP BY 计算部门总工资：

sql
SELECT
department,
SUM(salary) AS department_total_salary
FROM
employees
GROUP BY
department;
结果只有两行（IT和Sales部门各自的总计），原始员工信息消失。

使用窗口函数计算部门总工资（并显示在每行）：

sql
SELECT
employee_name,
department,
salary,
SUM(salary) OVER(PARTITION BY department) AS department_total_salary -- 注意这里是 OVER 而不是 GROUP BY
FROM
employees;
结果是所有员工的列表，每一行都带有该员工的工资和他们部门的总工资。

选择哪个取决于你的分析目的：是只需要汇总结果，还是需要汇总结果与明细信息一起查看。

8. 总结与实践

窗口函数是 SQL 中一个非常强大且常用的特性，它极大地扩展了我们进行数据分析的能力，特别是在需要进行跨行计算、排名、累积或前后比较的场景。

本教程从零开始，介绍了窗口函数的基本概念、语法 (OVER(), PARTITION BY, ORDER BY)，常见的函数类型及其用法，以及窗口帧的概念和作用。通过示例，你应该能理解窗口函数如何让你在不丢失原始数据的情况下进行复杂的分析。

实践建议：

  1. 找到一个支持窗口函数的数据库系统（如 PostgreSQL, MySQL 8.0+, SQL Server, Oracle, SQLite 3.33.0+ 等）。
  2. 创建本文中的示例表 employees 或 daily_sales。
  3. 运行文中的每一个查询示例，观察并理解输出结果。
  4. 尝试修改示例查询，比如改变 PARTITION BY 的列、ORDER BY 的顺序、LAG/LEAD 的偏移量、NTILE 的分组数，或者调整窗口帧，看看结果如何变化。
  5. 尝试在自己的数据集上应用窗口函数解决实际问题，比如：
    • 计算每个产品的销售排名。
    • 计算每个客户的累积消费金额。
    • 计算每个月比上一个月的销售额变化。
    • 查找每个类别中销售额最高的前 N 个产品。

熟练掌握窗口函数将是你在数据分析和处理领域迈出的重要一步。多练习，多尝试，你就能体会到它的强大之处。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部