SQL Window Functions 教程：快速入门与进阶 – wiki词典

作者： /

SQL Window Functions 教程：快速入门与进阶

SQL Window Functions（窗口函数）是现代SQL中一项强大且不可或缺的特性，它允许你在不减少行数的情况下，对与当前行相关的一组行进行计算。与传统的GROUP BY聚合函数不同，窗口函数会为查询结果的每一行返回一个值，而不会将行折叠成单个聚合结果。这使得它们在执行复杂的分析任务时表现出色，例如计算运行总计、排名、移动平均或比较相邻行。

一、快速入门：理解核心概念

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

sql
<窗口函数> OVER ([PARTITION BY <列名>] [ORDER BY <列名>] [ROWS/RANGE BETWEEN ...])

让我们分解这些关键组成部分：

  1. OVER() 子句：

    • 这是将一个函数转换为窗口函数的关键。空括号OVER()意味着整个结果集被视为一个窗口。
    • 例如，COUNT(*) OVER() 将返回表中所有行的总数，并为每一行重复这个总数。

  2. PARTITION BY 子句 (可选)：

    • PARTITION BY 用于将结果集分成多个独立的“窗口”或“分区”。窗口函数将独立地应用于每个分区。
    • 它类似于GROUP BY，但不同之处在于，PARTITION BY 不会折叠行，而是为每个分区内的每一行返回一个值。
    • 示例：如果你想计算每个部门的员工总数，你可以使用COUNT(*) OVER (PARTITION BY department_id)。

  3. ORDER BY 子句 (可选)：

    • ORDER BY 用于定义窗口内行的逻辑顺序。这对于排名函数和涉及相邻行的函数（如LAG、LEAD）至关重要。
    • 它决定了窗口函数在计算时行的处理顺序。
    • 示例：如果你想计算每个部门员工的累积工资，你需要按工资或入职日期在每个部门内进行排序：SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date)。

常用聚合函数作为窗口函数

几乎所有的聚合函数（如SUM, AVG, COUNT, MIN, MAX）都可以作为窗口函数使用。

基本示例：计算总销售额与每笔订单的比例

假设你有一个orders表：

order_id customer_id amount
1 101 100
2 102 200
3 101 150
4 103 300

sql
SELECT
order_id,
customer_id,
amount,
SUM(amount) OVER() AS total_sales,
(amount * 1.0 / SUM(amount) OVER()) * 100 AS percentage_of_total_sales
FROM
orders;

结果：

order_id customer_id amount total_sales percentage_of_total_sales
1 101 100 750 13.33
2 102 200 750 26.67
3 101 150 750 20.00
4 103 300 750 40.00

二、进阶应用：特定窗口函数与帧子句

除了聚合函数外，SQL还提供了一系列专门的窗口函数，以及更精细控制窗口范围的“帧子句”。

1. 排名函数

这些函数用于为分区内的行分配一个排名。

  • ROW_NUMBER()：为分区内的每一行分配一个唯一的连续整数，从1开始。
    sql
    SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
    FROM employees;

  • RANK()：为分区内的行分配排名。如果存在相同的值，它们将获得相同的排名，下一个不同的值将跳过一个或多个排名（例如：1, 2, 2, 4）。
    sql
    SELECT *, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rk
    FROM employees;

  • DENSE_RANK()：与RANK()类似，但如果存在相同的值，它们将获得相同的排名，下一个不同的值将获得紧随其后的排名，不会跳过（例如：1, 2, 2, 3）。
    sql
    SELECT *, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as drk
    FROM employees;

  • NTILE(N)：将分区中的行分成N个大致相等的分组，并为每行分配一个组号（1到N）。
    sql
    SELECT *, NTILE(4) OVER (ORDER BY score DESC) as quartile
    FROM student_scores;

2. 分析函数（Value Functions）

这些函数用于访问窗口中的特定行。

  • LAG(expression, offset, default_value)：返回当前行之前offset行expression的值。default_value是可选的，当没有前一行时返回。
    sql
    -- 计算每个订单与前一个订单的金额差异
    SELECT
    order_id,
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) as previous_order_amount,
    amount - LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) as amount_difference
    FROM
    customer_orders;

  • LEAD(expression, offset, default_value)：返回当前行之后offset行expression的值。default_value是可选的，当没有后一行时返回。
    sql
    -- 查找每个员工的下一个入职的同事
    SELECT
    employee_id,
    hire_date,
    LEAD(employee_id, 1, NULL) OVER (ORDER BY hire_date) as next_hired_employee
    FROM
    employees;

  • FIRST_VALUE(expression)：返回窗口中第一行expression的值。
    sql
    -- 查找每个部门工资最高的员工姓名
    SELECT
    department_id,
    employee_name,
    salary,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department_id ORDER BY salary DESC) as highest_paid_in_dept
    FROM
    employees;

  • LAST_VALUE(expression)：返回窗口中最后一行expression的值。

    • 注意：LAST_VALUE的行为受默认帧（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）的影响。通常需要配合帧子句使用。
      sql
      -- 查找每个部门工资最低的员工姓名 (需要明确的帧定义)
      SELECT
      department_id,
      employee_name,
      salary,
      LAST_VALUE(employee_name) OVER (PARTITION BY department_id ORDER BY salary ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_paid_in_dept
      FROM
      employees;

  • NTH_VALUE(expression, N)：返回窗口中第N行expression的值。
    sql
    -- 查找每个部门工资排名第二的员工姓名
    SELECT
    department_id,
    employee_name,
    salary,
    NTH_VALUE(employee_name, 2) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as second_highest_paid_in_dept
    FROM
    employees;

3. 帧子句 (Frame Clause)

帧子句用于进一步精确定义窗口函数操作的“当前行”周围的行集。它紧跟在ORDER BY子句之后。

语法：

sql
ROWS BETWEEN <起点> AND <终点>
RANGE BETWEEN <起点> AND <终点>

起点/终点选项：

  • UNBOUNDED PRECEDING：从分区的开始到当前行。
  • N PRECEDING：当前行之前的N行。
  • CURRENT ROW：仅当前行。
  • N FOLLOWING：当前行之后的N行。
  • UNBOUNDED FOLLOWING：从当前行到分区的结束。

默认帧：
当ORDER BY存在时，默认帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
当ORDER BY不存在时，默认帧是ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

ROWS vs RANGE：

  • ROWS：基于物理行数进行计数。例如，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指的是当前行、前一行和后一行。
  • RANGE：基于逻辑值进行计数。例如，RANGE BETWEEN 10 PRECEDING AND CURRENT ROW 指的是所有值在当前行值减10到当前行值之间的行。RANGE通常用于日期或数值类型。

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

sql
SELECT
trade_date,
price,
AVG(price) OVER (ORDER BY trade_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_day_moving_avg
FROM
stock_prices;
这将计算包括当前日期和前两个日期在内的3天的平均价格。

示例：计算累积总和 (Running Total)

sql
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_sales
FROM
daily_sales;
这将计算从销售开始到当前日期的累积销售总额。

三、实际应用场景

  • 数据排名：找出每个类别中的Top N项（例如，每个部门工资最高的3名员工）。
  • 趋势分析：计算移动平均值或累积总和，以平滑数据或观察长期趋势。
  • 时间序列分析：比较当前值与前一个或后一个时间点的值（例如，每日销售额与前一天的比较）。
  • 差距和岛屿问题：识别数据中的连续序列或不连续点（例如，连续登录天数、缺失的数据点）。
  • 百分比计算：计算某个值在总数或分组总数中的百分比。
  • 去重：配合ROW_NUMBER()和子查询，可以有效地删除重复记录，只保留每组的第一条。

总结

SQL窗口函数极大地扩展了SQL的分析能力，使得数据分析师和开发者能够以更简洁、高效的方式处理复杂的业务逻辑，而无需借助子查询或应用程序层逻辑。掌握窗口函数是提升SQL技能的关键一步，它将帮助你从数据中提取更有价值的洞察。通过不断地练习和尝试不同的函数组合，你将能更好地驾驭数据的力量。

滚动至顶部