当前位置：首页 > article >正文

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

article 2024/12/25 16:20:20

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

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

一、窗口函数简介

1. 窗口函数的定义

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

2. 基本语法

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

关键词解释：

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

二、常见窗口函数

窗口函数	作用说明	示例
ROW_NUMBER()	为每个分区内的记录生成唯一递增的编号	每个部门员工的排名
RANK()	生成排名，相同值时排名相同，跳过后续名次	产品销量排名
DENSE_RANK()	类似 RANK，不跳过名次	学生成绩排名
NTILE(n)	将分区内数据分为 n 份	将订单分为 4 个季度
SUM()	计算窗口内的累计和	销售额累计和
AVG()	计算窗口内的平均值	滑动窗口的平均分
LAG()	取当前行的前 N 行值	计算上一天的销售额
LEAD()	取当前行的后 N 行值	计算下一季度的销售目标

三、实战案例分析

案例 1：销售排名分析

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

表结构 sales：

sales_id	region	employee	amount
1	East	Alice	1000
2	East	Bob	1200
3	West	Carol	1500
4	East	David	1200
5	West	Eve	1500

SQL 实现：

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

结果：

region	employee	amount	rank
East	Bob	1200	1
East	David	1200	1
East	Alice	1000	2
West	Carol	1500	1
West	Eve	1500	1

解释：

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

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

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

表结构 sales_history：

sales_id	region	employee	amount	sale_date
1	East	Alice	1000	2023-01-01
2	East	Bob	1200	2023-01-05
3	West	Carol	1500	2023-01-10
4	East	David	500	2023-01-12
5	West	Eve	700	2023-01-15

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;

结果：

region	employee	amount	cumulative_sum
East	Alice	1000	1000
East	Bob	1200	2200
East	David	500	2700
West	Carol	1500	1500
West	Eve	700	2200

解释：

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;