掌握 SQL 窗口函数：优化你的数据查询 – wiki大全

作者： /

掌握 SQL 窗口函数：优化你的数据查询

在数据分析和处理的领域，SQL（结构化查询语言）是不可或缺的工具。而随着数据规模和复杂性的增长，传统的 SQL 查询有时会显得力不从心。这时，SQL 窗口函数（Window Functions）便闪亮登场，它们是 SQL 中一项强大且灵活的功能，能够帮助你以前所未有的方式洞察数据，优化复杂的查询逻辑。

本文将深入探讨 SQL 窗口函数的核心概念、语法、常见类型及其在实际数据分析中的应用。

什么是 SQL 窗口函数？

简单来说，SQL 窗口函数允许你在不改变原始查询结果行数的前提下，对“窗口”（即与当前行相关的一组行）中的数据执行计算。与传统的聚合函数（如 SUM()、AVG()、COUNT() 等，它们通常会通过 GROUP BY 子句将多行聚合成一行）不同，窗口函数为结果集中的每一行都返回一个计算结果，保持了数据的粒度。

想象一下，你想要计算每个员工的薪资在其部门内的排名，或者计算某个产品在销售历史上的移动平均销量。这些场景，使用传统 SQL 往往需要复杂的子查询或自连接，而窗口函数则能 elegantly 地解决。

核心语法：OVER() 子句

所有窗口函数都必须紧随一个 OVER() 子句。这个 OVER() 子句正是定义“窗口”的关键，它决定了函数将在哪些行上进行计算。

基本语法结构如下：

sql
<窗口函数>() OVER (
[PARTITION BY <用于分组的列名>]
[ORDER BY <用于排序的列名>]
[ROWS 或 RANGE <窗口帧定义>]
)

接下来，我们详细解析 OVER() 子句的各个组成部分：

1. PARTITION BY 子句 (可选)

  • 作用： PARTITION BY 用于将查询结果集划分为若干个逻辑分区（或组）。窗口函数会独立地应用于每个分区，并在每个分区内重新开始计算。
  • 类比： 类似于 GROUP BY，但它不会折叠行。它只是定义了计算的边界。
  • 省略： 如果省略 PARTITION BY，则整个结果集将被视为一个单一的分区。

示例：
假设我们有一个 Sales 表，包含 Region（区域）、Salesperson（销售员）和 SaleAmount（销售额）等字段。

sql
SELECT
Salesperson,
Region,
SaleAmount,
SUM(SaleAmount) OVER (PARTITION BY Region) AS TotalRegionSales
FROM
Sales;
此查询会为 Sales 表中的每一行返回该行所在 Region 的总销售额。

2. ORDER BY 子句 (可选)

  • 作用： ORDER BY 定义了每个分区内行的逻辑顺序。这对于依赖于顺序的窗口函数（如排名函数、累积总和、LEAD/LAG）至关重要。
  • 省略： 如果省略 ORDER BY，窗口函数将对分区内的所有行进行操作，但其结果可能是不确定的，因为没有明确的顺序。

示例：
计算每个销售员的累计销售额：

sql
SELECT
Salesperson,
SaleDate,
SaleAmount,
SUM(SaleAmount) OVER (PARTITION BY Salesperson ORDER BY SaleDate) AS CumulativeSales
FROM
Sales;
CumulativeSales 列将显示每个销售员按销售日期排序的累计销售额。

3. ROWS 或 RANGE 子句 (窗口帧，可选)

  • 作用： 这个子句进一步限制了分区内用于计算的行子集，这个子集被称为“窗口帧”。它提供了对计算范围更精细的控制。
  • ROWS： 基于物理行数定义窗口帧。
  • RANGE： 基于 ORDER BY 表达式的值的逻辑范围定义窗口帧。

常用的窗口帧定义包括：
* UNBOUNDED PRECEDING: 从分区的第一行开始。
* CURRENT ROW: 当前行。
* UNBOUNDED FOLLOWING: 到分区的最后一行。
* N PRECEDING / N FOLLOWING: 当前行之前/之后的 N 行。
* BETWEEN <start_bound> AND <end_bound>: 定义窗口帧的起始和结束点。

默认窗口帧： 如果 ORDER BY 存在但未指定 ROWS 或 RANGE，默认的窗口帧通常是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这意味着计算将包括分区开始到当前行（以及与当前行具有相同 ORDER BY 值的行）的所有行。

示例：
计算每个销售员的 2 天移动平均销售额：

sql
SELECT
Salesperson,
SaleDate,
SaleAmount,
AVG(SaleAmount) OVER (
PARTITION BY Salesperson
ORDER BY SaleDate
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS MovingAvg2Days
FROM
Sales;
MovingAvg2Days 将计算当前销售额和前一天的销售额的平均值。

窗口函数的类型

SQL 窗口函数大致可以分为三类：

1. 聚合窗口函数 (Aggregate Window Functions)

这些是大家熟知的聚合函数，如 SUM()、AVG()、COUNT()、MIN()、MAX()，但它们与 OVER() 子句一起使用时，便成了窗口函数。它们在定义的窗口内执行聚合计算，但不会折叠行，而是为每一行返回聚合结果。

常见用途： 运行总计 (running totals)、移动平均 (moving averages)、百分比计算等。

示例：
计算总销售额和每个区域的累计销售额：

sql
SELECT
SaleID,
Region,
Salesperson,
SaleAmount,
SaleDate,
SUM(SaleAmount) OVER (ORDER BY SaleDate) AS TotalRunningSales, -- 所有销售的累计总额
SUM(SaleAmount) OVER (PARTITION BY Region ORDER BY SaleDate) AS RegionRunningSales -- 每个区域的累计总额
FROM
Sales;

2. 排名窗口函数 (Ranking Window Functions)

这类函数用于为分区内的行分配排名，非常适合找出前 N 名、后 N 名或进行分层分析。

  • ROW_NUMBER(): 为分区内的每一行分配一个唯一的、连续的整数。
  • RANK(): 为分区内的每一行分配一个排名。如果存在相同的值，它们将获得相同的排名，但下一个排名会跳过相应数量的数字（有间隙）。
  • DENSE_RANK(): 类似于 RANK()，但当存在相同的值时，下一个排名不会跳过数字（无间隙）。
  • NTILE(n): 将分区内的行分成 n 个大致相等的组，并为每行分配其所属组的编号。

示例：

sql
SELECT
SaleID,
Region,
Salesperson,
SaleAmount,
ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SaleAmount DESC) AS rn,
RANK() OVER (PARTITION BY Region ORDER BY SaleAmount DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY Region ORDER BY SaleAmount DESC) AS drk,
NTILE(2) OVER (PARTITION BY Region ORDER BY SaleAmount DESC) AS quartile -- 分为2组 (二分位数)
FROM
Sales;
此查询将展示不同排名函数在每个区域内的销售额排名上的差异。

3. 分析/值窗口函数 (Analytic/Value Window Functions)

这些函数允许你访问窗口内其他行的值，通常用于行与行之间的比较。

  • LEAD(column, offset, default): 获取当前行之后第 offset 行的 column 值。
  • LAG(column, offset, default): 获取当前行之前第 offset 行的 column 值。
  • FIRST_VALUE(column): 返回窗口帧中第一行的 column 值。
  • LAST_VALUE(column): 返回窗口帧中最后一行的 column 值。
  • NTH_VALUE(column, n): 返回窗口帧中第 n 行的 column 值。

示例：

sql
SELECT
Salesperson,
SaleDate,
SaleAmount,
LAG(SaleAmount, 1, 0) OVER (PARTITION BY Salesperson ORDER BY SaleDate) AS PreviousSaleAmount,
LEAD(SaleAmount, 1, 0) OVER (PARTITION BY Salesperson ORDER BY SaleDate) AS NextSaleAmount,
FIRST_VALUE(SaleAmount) OVER (PARTITION BY Salesperson ORDER BY SaleDate) AS FirstSaleInPeriod,
LAST_VALUE(SaleAmount) OVER (PARTITION BY Salesperson ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSaleInPeriod
FROM
Sales;
此查询将显示每个销售员的前一笔、后一笔销售额，以及其在整个销售期间的第一笔和最后一笔销售额。注意 LAST_VALUE 结合窗口帧的使用，以确保它考虑整个分区。

总结

SQL 窗口函数是现代数据分析师和数据库开发者工具箱中不可或缺的一部分。通过熟练掌握 OVER() 子句及其 PARTITION BY、ORDER BY 和窗口帧定义，你可以灵活地对数据进行分组、排序和范围限制，从而实现复杂的排名、累计计算、移动平均、行间比较等高级分析功能。它们不仅能极大地简化你的 SQL 查询，还能提升数据处理的效率和洞察力。

开始实践这些强大的函数吧，你将发现数据查询的世界变得更加广阔和有趣！

滚动至顶部