SQL Server 开窗函数详解：数据分析必备的排名、聚合与偏移技巧

在日常的数据分析工作中，你是否遇到过这样的需求：需要对数据进行分组排序、计算累计值，或者比较当前行与前后行的数据？传统的 GROUP BY 聚合会合并行，导致原始数据明细丢失。这时，SQL 的开窗函数（Window Functions）就成了解决问题的利器。它能让你在不折叠行的前提下，完成复杂的分析计算。

今天，我们就以 SQL Server 为例，深入讲解常用的开窗函数，并通过清晰的代码示例帮助你快速掌握。

什么是开窗函数？

开窗函数与聚合函数类似，但不会将多行合并为一行，而是将函数的结果与每一行相关联。它的强大之处在于 OVER 子句，通过这个子句，你可以定义数据的分区、排序以及计算范围。

准备工作：创建示例数据

在开始之前，我们先创建一个销售数据表，并插入一些测试数据，方便后续演示。

-- 创建示例表
CREATE TABLE Sales (
    SalesID INT IDENTITY(1,1) PRIMARY KEY,
    SalesPerson NVARCHAR(50),
    Region NVARCHAR(50),
    SaleDate DATE,
    Amount DECIMAL(10,2)
);

-- 插入测试数据
INSERT INTO Sales (SalesPerson, Region, SaleDate, Amount) VALUES
('张三', '北京', '2024-01-10', 5000),
('张三', '北京', '2024-01-15', 8000),
('张三', '北京', '2024-02-05', 6000),
('李四', '上海', '2024-01-12', 7000),
('李四', '上海', '2024-02-08', 9000),
('王五', '北京', '2024-01-20', 5500),
('王五', '北京', '2024-02-10', 7500),
('赵六', '上海', '2024-01-25', 8500),
('赵六', '上海', '2024-02-15', 6500);

1. 排名函数：给数据排座次

排名函数主要用于为数据集中的行分配一个序号或排名。

ROW_NUMBER()

为每一行分配一个唯一的、连续的序号，从1开始。

-- 为每个销售员的销售额按时间排序编号
SELECT
    SalesPerson,
    SaleDate,
    Amount,
    ROW_NUMBER() OVER (PARTITION BY SalesPerson ORDER BY SaleDate) AS RowNum
FROM Sales;

结果示例：

张三    2024-01-10  5000.00  1
张三    2024-01-15  8000.00  2
张三    2024-02-05  6000.00  3
李四    2024-01-12  7000.00  1
...

RANK()

计算排名，相同值会获得相同的排名，并且后续排名序号会“跳跃”。例如，两个并列第一后，下一个是第三名。

-- 按销售额排名，相同销售额则并列
SELECT
    SalesPerson,
    SaleDate,
    Amount,
    RANK() OVER (ORDER BY Amount DESC) AS SalesRank
FROM Sales;

结果示例：

李四    2024-02-08  9000.00  1
赵六    2024-01-25  8500.00  2
张三    2024-01-15  8000.00  3
王五    2024-02-10  7500.00  4
...

DENSE_RANK()

与 RANK() 类似，相同值排名相同，但排名序号是连续的，不会跳跃。

-- 按销售额连续排名
SELECT
    SalesPerson,
    SaleDate,
    Amount,
    DENSE_RANK() OVER (ORDER BY Amount DESC) AS DenseRank
FROM Sales;

NTILE(n)

将有序分区中的行分配到指定数量的、大小大致相等的组中，并为每一行分配其所属的组号（从1开始）。

-- 将销售额分为4个等级
SELECT
    SalesPerson,
    SaleDate,
    Amount,
    NTILE(4) OVER (ORDER BY Amount DESC) AS SalesQuartile
FROM Sales;

2. 聚合开窗函数：灵活计算累计值

你可以在 OVER 子句中使用常见的聚合函数（如 SUM, AVG, MIN, MAX, COUNT），实现分区累计、移动平均等计算。

累计求和

-- 计算每个销售员的销售额累计和
SELECT
    SalesPerson,
    SaleDate,
    Amount,
    SUM(Amount) OVER (
        PARTITION BY SalesPerson
        ORDER BY SaleDate
    ) AS CumulativeAmount
FROM Sales;

结果示例：

张三    2024-01-10  5000.00  5000.00
张三    2024-01-15  8000.00  13000.00
张三    2024-02-05  6000.00  19000.00
...

移动平均

-- 计算每个销售员的3期移动平均销售额
SELECT
    SalesPerson,
    SaleDate,
    Amount,
    AVG(Amount) OVER (
        PARTITION BY SalesPerson
        ORDER BY SaleDate
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg
FROM Sales;

分区聚合

-- 计算每个区域的平均销售额，并与个人销售额比较
SELECT
    SalesPerson,
    Region,
    Amount,
    AVG(Amount) OVER (PARTITION BY Region) AS RegionAvg,
    Amount - AVG(Amount) OVER (PARTITION BY Region) AS DiffFromAvg
FROM Sales;

3. 偏移函数：访问“邻居”数据

这类函数允许你访问当前行之前或之后的行数据，非常适合用于计算环比、同比。

LAG() 和 LEAD()

LAG() 访问当前行之前某一行的值，LEAD() 则访问之后某一行的值。

-- 查看每个销售员的上次和下次销售额
SELECT
    SalesPerson,
    SaleDate,
    Amount,
    LAG(Amount) OVER (
        PARTITION BY SalesPerson
        ORDER BY SaleDate
    ) AS PreviousAmount,
    LEAD(Amount) OVER (
        PARTITION BY SalesPerson
        ORDER BY SaleDate
    ) AS NextAmount
FROM Sales;

结果示例：

张三    2024-01-10  5000.00  NULL      8000.00
张三    2024-01-15  8000.00  5000.00   6000.00
张三    2024-02-05  6000.00  8000.00   NULL

FIRST_VALUE() 和 LAST_VALUE()

返回窗口内第一行和最后一行的值。使用 LAST_VALUE() 时通常需要指定完整的窗口范围。

-- 查看每个销售员的首次和最后销售额
SELECT
    SalesPerson,
    SaleDate,
    Amount,
    FIRST_VALUE(Amount) OVER (
        PARTITION BY SalesPerson
        ORDER BY SaleDate
    ) AS FirstAmount,
    LAST_VALUE(Amount) OVER (
        PARTITION BY SalesPerson
        ORDER BY SaleDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LastAmount
FROM Sales;

4. 高级技巧与业务场景应用

掌握了基础函数，我们来看看如何组合它们解决更复杂的业务问题。

场景1：找出每个区域的销售冠军

WITH RankedSales AS (
    SELECT
        SalesPerson,
        Region,
        SUM(Amount) AS TotalSales,
        ROW_NUMBER() OVER (
            PARTITION BY Region
            ORDER BY SUM(Amount) DESC
        ) AS RegionRank
    FROM Sales
    GROUP BY SalesPerson, Region
)
SELECT
    SalesPerson,
    Region,
    TotalSales
FROM RankedSales
WHERE RegionRank = 1;

场景2：计算月度增长率

WITH MonthlySales AS (
    SELECT
        SalesPerson,
        YEAR(SaleDate) AS SaleYear,
        MONTH(SaleDate) AS SaleMonth,
        SUM(Amount) AS MonthlyAmount,
        LAG(SUM(Amount)) OVER (
            PARTITION BY SalesPerson
            ORDER BY YEAR(SaleDate), MONTH(SaleDate)
        ) AS PreviousMonthAmount
    FROM Sales
    GROUP BY SalesPerson, YEAR(SaleDate), MONTH(SaleDate)
)
SELECT
    SalesPerson,
    SaleYear,
    SaleMonth,
    MonthlyAmount,
    PreviousMonthAmount,
    CASE
        WHEN PreviousMonthAmount IS NULL THEN NULL
        ELSE ROUND(
            (MonthlyAmount - PreviousMonthAmount) * 100.0 / PreviousMonthAmount, 2
        )
    END AS GrowthRate
FROM MonthlySales;

性能优化建议

开窗函数虽然强大，但在大数据集上使用不当可能影响性能。这里有几个小建议：

1. 合理使用索引：为 OVER 子句中 ORDER BY 使用的列创建索引。
2. 避免不必要的排序：如果业务逻辑允许，尽量减少 ORDER BY 的使用。
3. 明确窗口帧：使用 ROWS 或 RANGE 子句明确指定计算范围，避免默认行为可能带来的性能开销。
4. 慎选分区字段：选择基数（不同值的数量）适中的字段作为 PARTITION BY 的键。

总结

开窗函数是 SQL 中用于数据分析的强大工具，它完美弥补了 GROUP BY 聚合会丢失数据明细的不足。通过 PARTITION BY 定义数据分区、ORDER BY 定义分区内排序、以及窗口帧定义计算范围，你可以灵活实现：

• 复杂的数据排名和分组
• 累计值、移动平均的计算
• 访问前后行数据进行趋势分析

无论是处理 MySQL、PostgreSQL 还是 SQL Server，掌握开窗函数都能让你的数据分析能力提升一个档次。希望本文的讲解和示例能帮助你更好地理解和应用这一功能。如果你在实践中有更多心得，欢迎在技术社区交流探讨。