掌握 SQL 窗口函数：综合指南

SQL 窗口函数是分析师和开发人员可用的最强大、最灵活的工具之一。它们允许跨数据行进行复杂计算，同时保留各个行，这使得它们非常适合各种用例，从排名和累计总和到移动平均值和比较。

这篇博文以 Employees 表为示例，详细概述了所有主要窗口函数。

-- Create the table
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    DivisionID INT,
    ManagerID INT,
    Salary DECIMAL(10, 2)
);

-- Insert data into the Employees table
INSERT INTO Employees (ID, Name, DivisionID, ManagerID, Salary)
VALUES
(356, 'Daniel Smith', 100, 133, 40000),
(122, 'Arnold Sully', 101, NULL, 60000),
(467, 'Lisa Roberts', 100, NULL, 80000),
(112, 'Mary Dial', 105, 467, 65000),
(775, 'Dennis Front', 103, NULL, 90000),
(111, 'Larry Weis', 104, 35534, 75000),
(222, 'Mark Red', 102, 133, 86000),
(577, 'Robert Night', 105, 12353, 76000),
(133, 'Susan Wall', 105, 577, 110000);

什么是 SQL 窗口函数？

#

SQL 窗口函数对由 OVER() 子句定义的一组行（称为“窗口”）进行操作。与诸如SUM()或COUNT()之类的聚合函数不同，窗口函数会为数据集中的每一行返回一个结果，这使得它们对于需要单独行级计算和组级操作的详细分析非常有用。

语法概述

#

窗口函数的一般语法如下：

<window_function>() OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
)

• <window_function>: 窗口函数 (例如 ROW_NUMBER(), SUM(), RANK()等等)。
• PARTITION BY: 将数据集划分为多个分区（可选）。
• ORDER BY: 指定每个分区内的行的顺序（可选）。
• OVER(): 定义函数的行窗口。

关键 SQL 窗口函数

#

1. 排名功能

#

当您想要为分区中的每一行分配一个排名时，排名函数非常有用。它们通常用于排行榜生成或员工薪资排名等场景。

ROW_NUMBER()

#

为分区中的每一行生成一个唯一的编号。示例：

SELECT
    DivisionID,
    Name,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

此查询按工资降序对每个部门的员工进行排序。

RANK()

#

为每行分配一个排名，但当出现平局时会留下空位。对于具有相同值的行，将跳过下一个排名。示例：

SELECT
    DivisionID,
    Name,
    Salary,
    RANK() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

如果两名员工的工资相同，则他们的级别相同，并且会跳过下一个级别。

DENSE_RANK()

#

与RANK()类似，但排名没有差距。所有具有相同值的行都获得相同的排名，但不会跳过后续排名。示例：

SELECT
    DivisionID,
    Name,
    Salary,
    DENSE_RANK() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

查询结果相同，如下所示：

2. 聚合函数（窗口）

#

窗口函数可以与聚合函数结合使用，这样您就可以计算诸如运行总计或平均值之类的值，而无需将行折叠为单个结果。

SUM()

#

计算行窗口的累计和。示例：

SELECT employee_id, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS cumulative_salary
FROM employees;

此查询计算每个部门每个员工的累计工资。

AVG()

#

计算各部门员工的平均工资。示例：

SELECT
    DivisionID,
    Name,
    Salary,
    AVG(Salary) OVER (PARTITION BY DivisionID) AS AvgSalary
FROM Employees;

COUNT()

#

计算窗口中的行数。例如：

SELECT
    DivisionID,
    Name,
    COUNT(*) OVER (PARTITION BY DivisionID) AS EmployeeCount
FROM Employees;

MAX() / MIN()

#

返回窗口中的最大值或最小值。示例：查找每个部门中的最高工资。

SELECT
    DivisionID,
    Name,
    Salary,
    MAX(Salary) OVER (PARTITION BY DivisionID) AS MaxSalary
FROM Employees;

3. 基于窗口的行导航

#

这些函数允许您引用前一行或后一行的值，这对于计算差异、移动平均值或基于时间的比较特别有用。

LAG()

#

返回同一分区中上一行中指定列的值。示例：查找每个部门以前的工资。

SELECT
    DivisionID,
    Name,
    Salary,
    LAG(Salary) OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS PreviousSalary
FROM Employees;

LEAD()

#

返回同一分区中后续行中指定列的值。示例：查找每个部门的下一个工资。

SELECT
    DivisionID,
    Name,
    Salary,
    LEAD(Salary) OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS NextSalary
FROM Employees;

4. 第一个值和最后一个值函数

#

这些函数返回窗口中的第一个或最后一个值。

FIRST_VALUE()

#

返回窗口中的第一个值。示例：查找每个部门中薪水最高的员工。

SELECT
    DivisionID,
    Name,
    Salary,
    FIRST_VALUE(Name) OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS TopEarner
FROM Employees;

LAST_VALUE()

#

返回窗口中的最后一个值。示例：查找每个部门中薪水最低的员工。

SELECT
    DivisionID,
    Name,
    Salary,
    LAST_VALUE(Name) OVER (PARTITION BY DivisionID ORDER BY Salary ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LowestEarner
FROM Employees;

5. 窗口条款 (ROWS 和 RANGE)

#

在 SQL 中，ROWS 和 RANGE子句允许您指定组成窗口的确切行集。这对于时间序列分析或比较特定范围的行时特别有用。

ROWS 例子

#

根据物理行（例如，当前行和前两行）定义窗口。示例：计算每个部门的工资移动平均值。

SELECT
    DivisionID,
    Name,
    Salary,
    AVG(Salary) OVER (PARTITION BY DivisionID ORDER BY Salary ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Employees;

窗口函数的常见用例

#

1. 累计计算

#

您可以计算一组行累计的总数、平均值或其他聚合值。

2. 排名和排序

#

对员工、产品或销售数据进行排名是窗口函数的常见用例，尤其是在处理平局和前 N 个查询时。

3. 时间序列分析

#

对于涉及时间序列数据的应用，像LAG()和LEAD()这样的窗口函数对于计算随时间变化的差异（例如，月环比增长）至关重要。

4. 比较分析

#

窗口函数支持行之间的比较，例如将每个员工的工资与同一部门中之前或之后的员工的工资进行比较。

结论

#

SQL 窗口函数是一种非常强大的工具，可以在不破坏数据集的情况下实现复杂的数据分析。无论您需要对项目进行排名、计算累计总数还是比较分区内的行，窗口函数都提供了一种灵活而高效的方式来处理这些任务。