SQL 窗口函数基础
SQL窗口函数(Window Functions)是一种强大的工具,允许你在不改变查询结果集的情况下,对数据进行分组、排序和计算。与聚合函数不同,窗口函数不会将多行合并为一行,而是为每一行返回一个计算结果。这使得窗口函数非常适合用于排名、累积计算、移动平均等场景。
什么是窗口函数?
窗口函数是一种特殊的SQL函数,它可以在一个“窗口”(即一组相关的行)上执行计算。这个窗口可以是一个分区(partition)、一个排序(order)或一个范围(range)。窗口函数的关键特点是,它不会减少查询结果的行数,而是为每一行返回一个计算结果。
基本语法
窗口函数的基本语法如下:
sql
<窗口函数> OVER (
[PARTITION BY <列名>]
[ORDER BY <列名>]
[ROWS BETWEEN <起始行> AND <结束行>]
)
PARTITION 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 | 150.00 | 250.00 |
Bob | 2023-01-01 | 200.00 | 200.00 |
Bob | 2023-01-03 | 300.00 | 500.00 |
在这个例子中,SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date)
计算了每个销售人员的累计销售额。
示例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 | 150.00 | 1 |
Alice | 2023-01-01 | 100.00 | 2 |
Bob | 2023-01-03 | 300.00 | 1 |
Bob | 2023-01-01 | 200.00 | 2 |
在这个例子中,RANK()
函数根据销售额对每个销售人员进行排名。
实际应用场景
场景1:计算移动平均
假设你想计算每个销售人员的7天移动平均销售额:
sql
SELECT
salesperson,
sale_date,
amount,
AVG(amount) OVER (
PARTITION BY salesperson
ORDER BY sale_date
ROWS BETWEEN 6 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:在
sales
表中,使用DENSE_RANK()
函数为每个销售人员的销售额进行排名,并观察与RANK()
的区别。 - 练习2:尝试使用
LEAD()
和LAG()
函数,查找每个销售人员的下一个和前一个销售额。 - 资源:参考SQL窗口函数官方文档以了解更多高级用法。
通过不断练习和探索,你将能够更好地掌握SQL窗口函数,并在实际工作中灵活运用它们。