SQL窗口函数
介绍
SQL窗口函数（Window Functions）是一种强大的工具，允许你在不改变查询结果集的情况下，对数据进行复杂的计算和分析。与普通的聚合函数（如 SUM、AVG）不同，窗口函数不会将多行合并为一行，而是为每一行返回一个计算结果。这使得窗口函数非常适合用于排名、累积计算、移动平均等场景。
窗口函数的核心概念是“窗口”（Window），它定义了函数计算的范围。窗口可以是整个表、分组后的数据，或者基于行的滑动窗口。
基本语法
窗口函数的基本语法如下：
sql
<窗口函数> OVER (
[PARTITION BY <列名>]
[ORDER BY <列名>]
[ROWS BETWEEN <起始行> AND <结束行>]
)
- PARTITION BY：将数据分组，类似于
GROUP BY，但不会合并行。 - ORDER BY：定义窗口内数据的排序方式。
- ROWS BETWEEN：定义窗口的范围，可以是当前行的前后几行，或者整个分区。
常用窗口函数
以下是一些常用的窗口函数：
- ROW_NUMBER()：为每一行分配一个唯一的序号。
- RANK()：为每一行分配一个排名，相同值会有相同的排名，后续排名会跳过。
- DENSE_RANK()：与
RANK()类似，但不会跳过后续排名。 - SUM()、AVG()、MIN()、MAX()：在窗口内计算聚合值。
- LEAD() 和 LAG()：访问当前行之前或之后的行的值。
示例
假设我们有一个销售数据表 sales，结构如下：
sql
CREATE TABLE sales (
id INT,
salesperson VARCHAR(50),
sale_date DATE,
amount DECIMAL(10, 2)
);
示例1：计算每个销售人员的累计销售额
sql
SELECT
salesperson,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS cumulative_sales
FROM
sales;
输出：
| salesperson | sale_date | amount | cumulative_sales |
|---|---|---|---|
| Alice | 2023-01-01 | 100.00 | 100.00 |
| Alice | 2023-01-02 | 200.00 | 300.00 |
| Bob | 2023-01-01 | 150.00 | 150.00 |
| Bob | 2023-01-03 | 250.00 | 400.00 |
示例2：计算每个销售人员的销售额排名
sql
SELECT
salesperson,
sale_date,
amount,
RANK() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS sales_rank
FROM
sales;
输出：
| salesperson | sale_date | amount | sales_rank |
|---|---|---|---|
| Alice | 2023-01-02 | 200.00 | 1 |
| Alice | 2023-01-01 | 100.00 | 2 |
| Bob | 2023-01-03 | 250.00 | 1 |
| Bob | 2023-01-01 | 150.00 | 2 |
实际应用场景
场景1：计算移动平均
假设你想计算每个销售人员的3天移动平均销售额：
sql
SELECT
salesperson,
sale_date,
amount,
AVG(amount) OVER (
PARTITION BY salesperson
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM
sales;
场景2：查找每个销售人员的最大销售额
sql
SELECT
salesperson,
sale_date,
amount,
MAX(amount) OVER (PARTITION BY salesperson) AS max_sale
FROM
sales;
总结
SQL窗口函数为数据分析提供了强大的工具，允许你在不改变数据行的情况下进行复杂的计算。通过 PARTITION BY、ORDER BY 和 ROWS BETWEEN，你可以灵活地定义窗口的范围和排序方式。掌握窗口函数将极大地提升你在SQL中的数据处理能力。
附加资源与练习
- 练习1：尝试使用
LEAD()和LAG()函数，计算每个销售人员的销售额与前一天的差异。 - 练习2：使用
DENSE_RANK()函数，为每个销售人员的销售额分配排名，并观察与RANK()的区别。
提示
如果你对窗口函数的使用还有疑问，建议多尝试不同的查询，并结合实际数据进行练习。实践是掌握SQL窗口函数的最佳方式！