10 窗口函数

SQL 窗口函数（Window Functions）是一种在查询结果集的特定窗口（行集）上进行计算的函数。

它为每行数据提供了一个聚合值，而不需要像传统聚合函数那样将多行数据合并为一行，在不影响原表行数的情况下，进行各种统计分析。

一、语法结构

<窗口函数> OVER (
    [PARTITION BY <列名1>[, <列名2> ...]]
    [ORDER BY <列名1> [ASC|DESC][, <列名2> [ASC|DESC] ...]]
    [窗口子句]
)

• 窗口函数：

  • 常见的有聚合函数（如 SUM、AVG、COUNT 等）
  • 排名函数（如 RANK、DENSE_RANK、ROW_NUMBER 等）
  • 分析函数（如 LEAD、LAG 等）

• PARTITION BY 子句：可选，用于将结果集划分为多个分区，窗口函数会在每个分区内独立计算。类似于 GROUP BY，但不会像 GROUP BY 那样合并行。

• ORDER BY 子句：可选，用于指定分区内的排序规则，决定了窗口函数处理行的顺序。

• 窗口子句：可选，进一步定义窗口的范围，例如指定当前行前后的行作为窗口范围。

---

二、常见窗口函数类型及示例

1. 聚合窗口函数

用于在窗口内进行聚合计算，如求和、求平均值等。

-- 假设存在一个 sales 表，包含 product_id、sale_date 和 amount 字段
SELECT 
    product_id,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS cumulative_amount
FROM 
    sales;

在上述示例中，SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) 会为每个产品（按 product_id 分区）计算截至当前销售日期的累计销售金额。

2. 排名窗口函数

用于为结果集中的行进行排名。

• ROW_NUMBER()：为每行分配一个唯一的连续整数，从 1 开始，按照 ORDER BY 子句指定的顺序排列。

SELECT 
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM 
    employees;

此示例会为 employees 表中的每行按照薪资降序分配一个唯一的行号。

• RANK()：排名函数，如果有相同的值，会赋予相同的排名，下一个排名会跳过相应的数字。

SELECT 
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM 
    employees;

若有两个员工薪资相同且排名为第 2，则下一个员工的排名为第 4。

• DENSE_RANK()：也是排名函数，但即使有相同的值，下一个排名也不会跳过，是连续的。

SELECT 
    employee_id,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM 
    employees;

若有两个员工薪资相同且排名为第 2，则下一个员工的排名为第 3。

3. 分析窗口函数

用于获取当前行前后的行的数据。

• LAG()：用于获取当前行之前某一行的值

SELECT 
    sale_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sale_date) AS previous_amount
FROM 
    sales;

此示例会获取每行销售金额的前一行销售金额。

• LEAD()：用于获取当前行之后某一行的值。

SELECT 
    sale_date,
    amount,
    LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount
FROM 
    sales;

该示例会获取每行销售金额的后一行销售金额。

---

三、窗口函数的优势

• 灵活性：可以在不使用复杂的子查询或连接的情况下，在同一查询中进行多种计算。
• 性能优化：在处理大数据集时，窗口函数通常比子查询更高效。
• 可读性：使 SQL 查询更简洁易读，尤其是在需要进行复杂分析时。