SQL 实战：窗口函数的妙用 – 分析排名与分组聚合

在复杂的数据分析和查询场景中，SQL 窗口函数（Window Functions）是提升性能和代码可读性的重要工具。窗口函数可以轻松实现排名、分组聚合、滑动平均等复杂计算，避免使用嵌套子查询或冗余的多次表扫描。

本文将通过实战案例，深入剖析窗口函数的应用场景，重点讲解如何进行排名和分组聚合操作。

一、窗口函数简介

1. 窗口函数的定义

窗口函数在 SQL 查询中，允许在返回的结果集中基于当前行的前后多行进行计算。窗口函数不会折叠行，而是返回每一行数据，同时增加一个聚合结果列。

2. 基本语法

SELECT 列名, 窗口函数() OVER (
    PARTITION BY 分区列 
    ORDER BY 排序列
) AS 新列名
FROM 表名;

关键词解释：

• OVER：指定窗口函数的作用范围。
• PARTITION BY：对数据进行分区，每个分区独立计算窗口函数。
• ORDER BY：指定窗口内的排序方式。

二、常见窗口函数

三、实战案例分析

案例 1：销售排名分析

需求：获取每个地区的销售员销售额排名，排名相同销售额相等，且不跳名次。

表结构 sales：

SQL 实现：

SELECT 
    region,
    employee,
    amount,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM sales;

结果：

解释：

• DENSE_RANK() 在相同金额时，给予相同的排名，不会跳过排名。
• 每个分区（region）内独立计算排名，便于细粒度的数据分析。

案例 2：计算累计销售额（滑动窗口）

需求：计算每个地区销售额的累计和（按销售日期顺序）。

表结构 sales_history：

SQL 实现：

SELECT 
    region,
    employee,
    amount,
    SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM sales_history;

结果：

解释：

• SUM() OVER 计算累计和，UNBOUNDED PRECEDING 表示从窗口的第一行到当前行进行累加。
• 每个分区（region）内，销售额随着日期递增进行累计。

案例 3：比较当前销售额与上一期销售额

需求：在销售记录表中，计算每个销售员与上一笔订单的销售额差异。

SQL 实现：

SELECT 
    employee,
    amount,
    LAG(amount, 1, 0) OVER (PARTITION BY employee ORDER BY sale_date) AS previous_amount,
    amount - LAG(amount, 1, 0) OVER (PARTITION BY employee ORDER BY sale_date) AS amount_diff
FROM sales_history;

结果：

解释：

• LAG() 返回当前行的前一行数据，1 表示上一行，0 表示如果没有数据则返回默认值 0。
• 计算当前销售额与上一笔订单的差异，方便追踪销售趋势。

四、窗口函数的应用场景

1. 排名分析：计算每个部门或地区内员工的销售排名。
2. 分组累计和：按地区或类别计算累计销量或累计收入。
3. 同比环比分析：计算每个季度或月份与上期的差异。
4. 移动平均与滑动窗口：计算滑动平均分或销售额，平滑波动数据。

五、总结

• 窗口函数在 SQL 查询中极大简化了复杂的分组和排名计算，避免了繁琐的嵌套子查询。
• 合理使用窗口函数可以显著提升 SQL 查询性能，减少冗余计算，提升数据分析效率。
• 在日常开发中，掌握窗口函数的妙用，能让复杂的业务需求实现更加优雅、高效。