跳到主要内容

SQL 窗口函数基础

SQL窗口函数(Window Functions)是一种强大的工具,允许你在不改变查询结果集的情况下,对数据进行分组、排序和计算。与聚合函数不同,窗口函数不会将多行合并为一行,而是为每一行返回一个计算结果。这使得窗口函数非常适合用于排名、累积计算、移动平均等场景。

什么是窗口函数?

窗口函数是一种特殊的SQL函数,它可以在一个“窗口”(即一组相关的行)上执行计算。这个窗口可以是一个分区(partition)、一个排序(order)或一个范围(range)。窗口函数的关键特点是,它不会减少查询结果的行数,而是为每一行返回一个计算结果。

基本语法

窗口函数的基本语法如下:

sql
<窗口函数> OVER (
[PARTITION BY <列名>]
[ORDER BY <列名>]
[ROWS BETWEEN <起始行> AND <结束行>]
)
  • PARTITION BY:将数据分成多个分区,窗口函数会在每个分区内独立计算。
  • ORDER BY:指定分区内的排序方式。
  • ROWS BETWEEN:定义窗口的范围,可以是当前行的前后几行,或者整个分区。

常见的窗口函数

以下是一些常见的窗口函数:

  1. ROW_NUMBER():为每一行分配一个唯一的序号。
  2. RANK():为每一行分配一个排名,相同值的行会得到相同的排名,后续排名会跳过。
  3. DENSE_RANK():与RANK()类似,但不会跳过后续排名。
  4. SUM()AVG()MIN()MAX():在窗口内计算聚合值。
  5. 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;

输出:

salespersonsale_dateamountcumulative_sales
Alice2023-01-01100.00100.00
Alice2023-01-02150.00250.00
Bob2023-01-01200.00200.00
Bob2023-01-03300.00500.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;

输出:

salespersonsale_dateamountsales_rank
Alice2023-01-02150.001
Alice2023-01-01100.002
Bob2023-01-03300.001
Bob2023-01-01200.002

在这个例子中,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 BYORDER BYROWS BETWEEN等子句,你可以灵活地定义窗口的范围和排序方式,从而实现排名、累积计算、移动平均等功能。

提示

提示:窗口函数在处理时间序列数据、排名和累积计算时非常有用。熟练掌握窗口函数可以大大提升你的SQL数据分析能力。

附加资源与练习

  1. 练习1:在sales表中,使用DENSE_RANK()函数为每个销售人员的销售额进行排名,并观察与RANK()的区别。
  2. 练习2:尝试使用LEAD()LAG()函数,查找每个销售人员的下一个和前一个销售额。
  3. 资源:参考SQL窗口函数官方文档以了解更多高级用法。

通过不断练习和探索,你将能够更好地掌握SQL窗口函数,并在实际工作中灵活运用它们。