SQL窗口函数使用指南

SQL 中的窗口函数允许你在与当前行相关的一组行（称为"窗口"）上执行计算，而不会将结果集折叠成更少的行（这与使用 GROUP BY 的聚合函数不同）。它们在数据分析、排名、累积计算等方面非常强大。

以下是常用的窗口函数类别及举例说明：

📌 一、核心概念

• OVER() 子句： 这是定义窗口的关键。它指定了：
  • PARTITION BY: 将数据分成多个组（分区），窗口函数在每个分区内独立计算。
  • ORDER BY: 定义分区内行的排序顺序，这对排名、累积和等函数至关重要。
  • ROWS/RANGE BETWEEN: 定义窗口帧（window frame），即相对于当前行，计算包含哪些行（如前N行、后N行、所有行到当前行等）。默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

📊 二、常用窗口函数分类与举例

假设我们有一个销售表 sales_data：

sales_id	employee_id	sale_date	amount	region
1	101	2023-10-01	100	East
2	101	2023-10-05	150	East
3	102	2023-10-02	200	West
4	103	2023-10-03	120	East
5	102	2023-10-04	180	West
6	101	2023-10-06	90	East
7	103	2023-10-07	110	East

🧮 1. 聚合窗口函数

• 在窗口上执行标准聚合（如 SUM, AVG, COUNT, MIN, MAX）。
• SUM(column) OVER (...)
  • 示例1 (分区累计)： 计算每个销售员 (employee_id) 的累计销售额。

    SELECT sales_id, employee_id, sale_date, amount,
           SUM(amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total
    FROM sales_data;
    

    结果片段：

    sales_id	employee_id	sale_date	amount	running_total
    1	101	2023-10-01	100	100
    2	101	2023-10-05	150	250
    6	101	2023-10-06	90	340
    3	102	2023-10-02	200	200
    5	102	2023-10-04	180	380
    …	…	…	…	…

• AVG(column) OVER (...)
  • 示例2 (移动平均)： 计算每个销售员最近2笔交易（按日期）的平均销售额。

    SELECT sales_id, employee_id, sale_date, amount,
           AVG(amount) OVER (PARTITION BY employee_id ORDER BY sale_date
                             ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS last2_avg
    FROM sales_data;
    

    结果片段 (只看101号员工)：

    sales_id	employee_id	sale_date	amount	last2_avg
    1	101	2023-10-01	100	100.00
    2	101	2023-10-05	150	125.00
    6	101	2023-10-06	90	120.00

🥇 2. 排名窗口函数

• 根据指定的排序为分区内的行分配排名。
• ROW_NUMBER() OVER (...)
  • 为分区内的每一行分配一个唯一的连续整数序号（即使值相同）。
  • 示例3： 按销售额从高到低为每个区域 (region) 内的交易排名。

    SELECT sales_id, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
    FROM sales_data;
    

    结果片段 (只看East区域)：

    sales_id	region	amount	rank_in_region
    2	East	150	1
    4	East	120	2
    1	East	100	3
    7	East	110	4
    6	East	90	5

• RANK() OVER (...)
  • 分配排名。值相同的行获得相同排名，下一个排名会跳过并列占用的位置。
  • 示例4： 按销售额为所有销售员排名（全局排名）。

    SELECT sales_id, employee_id, amount,
           RANK() OVER (ORDER BY amount DESC) AS global_rank
    FROM sales_data;
    

    结果片段：

    sales_id	employee_id	amount	global_rank
    3	102	200	1
    5	102	180	2
    2	101	150	3
    4	103	120	4
    7	103	110	5
    1	101	100	6
    6	101	90	7

• DENSE_RANK() OVER (...)
  • 分配排名。值相同的行获得相同排名，下一个排名是连续的（不跳过）。
  • 示例5： 按销售额为所有销售员排名（全局排名），使用密集排名。

    SELECT sales_id, employee_id, amount,
           DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_global_rank
    FROM sales_data;
    

    结果片段： (假设数据不变)

    sales_id	employee_id	amount	dense_global_rank
    3	102	200	1
    5	102	180	2
    2	101	150	3
    4	103	120	4
    7	103	110	5
    1	101	100	6
    6	101	90	7

• NTILE(n) OVER (...)
  • 将分区内的行尽可能均匀地分成 n 个桶，并为每行分配桶号 (1 到 n)。
  • 示例6： 将每个区域 (region) 的销售额分成 2 个等级（高/低）。

    SELECT sales_id, region, amount,
           NTILE(2) OVER (PARTITION BY region ORDER BY amount DESC) AS ntile_group
    FROM sales_data;
    

    结果片段 (East区域有5行)：

    sales_id	region	amount	ntile_group
    2	East	150	1
    4	East	120	1
    7	East	110	1
    1	East	100	2
    6	East	90	2

⏪ 3. 偏移窗口函数

• 访问窗口中相对于当前行的其他行（前一行、后一行）。
• LAG(column [, offset [, default]]) OVER (...)
  • 返回分区内当前行之前 offset 行的 column 值。offset 默认为1。default 用于指定当没有前一行时返回的值（默认为 NULL）。
  • 示例7： 查看每个销售员每一笔交易与前一笔交易的销售额差异。

    SELECT sales_id, employee_id, sale_date, amount,
           LAG(amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS prev_amount,
           amount - LAG(amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS diff_from_prev
    FROM sales_data;
    

    结果片段 (只看101号员工)：

    sales_id	employee_id	sale_date	amount	prev_amount	diff_from_prev
    1	101	2023-10-01	100	NULL	NULL
    2	101	2023-10-05	150	100	50
    6	101	2023-10-06	90	150	-60

• LEAD(column [, offset [, default]]) OVER (...)
  • 返回分区内当前行之后 offset 行的 column 值。offset 默认为1。default 用于指定当没有后一行时返回的值（默认为 NULL）。
  • 示例8： 查看每个销售员每一笔交易与下一笔交易的日期间隔（近似）。

    SELECT sales_id, employee_id, sale_date, amount,
           LEAD(sale_date) OVER (PARTITION BY employee_id ORDER BY sale_date) AS next_sale_date,
           LEAD(sale_date) OVER (...) - sale_date AS days_to_next_sale
    FROM sales_data;
    

    结果片段 (只看102号员工)：

    sales_id	employee_id	sale_date	amount	next_sale_date	days_to_next_sale
    3	102	2023-10-02	200	2023-10-04	2
    5	102	2023-10-04	180	NULL	NULL

🔍 4. 首尾值窗口函数

• 访问窗口帧中的第一个或最后一个值。
• FIRST_VALUE(column) OVER (...)
  • 返回窗口帧中的第一个 column 值。
  • 示例9： 找出每个区域 (region) 内最高销售额是多少，并显示在每一行。

    SELECT sales_id, region, amount,
           FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount DESC) AS highest_in_region
    FROM sales_data;
    

    结果片段 (West区域)：

    sales_id	region	amount	highest_in_region
    3	West	200	200
    5	West	180	200

• LAST_VALUE(column) OVER (...)
  • 返回窗口帧中的最后一个 column 值。注意默认窗口帧 (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 会导致它返回当前行的值，通常需要显式指定窗口帧（如 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING）来获得分区内最后一个值。
  • 示例10： 找出每个销售员 (employee_id) 最近一笔交易（按日期）的销售额，并显示在每一行。

    SELECT sales_id, employee_id, sale_date, amount,
           LAST_VALUE(amount) OVER (PARTITION BY employee_id ORDER BY sale_date
                                  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS latest_sale_amount
    FROM sales_data;
    

    结果片段 (101号员工)：

    sales_id	employee_id	sale_date	amount	latest_sale_amount
    1	101	2023-10-01	100	90
    2	101	2023-10-05	150	90
    6	101	2023-10-06	90	90

💡 关键总结

1. OVER() 是灵魂： 所有窗口函数都通过 OVER() 子句定义计算窗口。
2. PARTITION BY： 分组，类似 GROUP BY，但结果不折叠。
3. ORDER BY： 定义分区内顺序，对排名、累积计算、偏移至关重要。
4. 窗口帧 (ROWS/RANGE BETWEEN): 精确控制计算范围（相对于当前行）。
5. 不折叠行： 窗口函数计算结果会附加到每一行后面，原始行数不变。
6. 执行顺序： 窗口函数在 WHERE, GROUP BY, HAVING 子句之后执行，在 ORDER BY 之前执行。不能在 WHERE 或 HAVING 中直接使用窗口函数结果（通常需要用子查询或 CTE）。

掌握这些常用窗口函数及其灵活应用，能让你在 SQL 中进行复杂的数据分析和处理变得更加高效和优雅。它们在报表、时间序列分析、排名计算、数据对比等场景中极为有用。