窗口函数用法

news/2024/9/29 15:44:33/

窗口函数（Window Functions）是SQL中一种强大的工具，允许你在数据集的特定“窗口”或“分区”内执行计算，而不需要对数据进行分组汇总（即不会减少返回的行数）。这些计算可以包括行号、移动平均、排名等。窗口函数对于数据分析特别有用，尤其是在处理时间序列数据、进行复杂的数据排名或计算累积和时。

基本语法

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

sql"><窗口函数> OVER ( 
[PARTITION BY column1, column2, ...] 
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...] 
[frame_clause] 
)

窗口函数：如ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM(), AVG(), MIN(), MAX(), COUNT()等，但请注意，聚合函数（如SUM(), AVG()等）在窗口函数中的用法与它们在GROUP BY语句中的用法不同。
PARTITION BY：可选。指定分区列，将结果集分成多个分区，每个分区内的行独立计算窗口函数。如果省略，则整个结果集视为一个分区。
ORDER BY：在大多数窗口函数中都是必需的（但不是全部），用于指定分区内行的排序方式。排序对于计算如排名、移动平均等是必需的。
frame_clause：定义窗口框架，即指定窗口函数操作的行的范围。例如，计算当前行及其前N行的平均值。

示例

假设有一个销售数据表sales，包含sale_date（销售日期）、region（地区）和amount（销售额）。

示例1：计算每个地区的销售额排名

sql">SELECT 
region, 
sale_date, 
amount, 
RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region 
FROM 
sales;

这个查询会为每个地区内的销售额进行排名。

示例2：计算每个地区每天的累计销售额

sql">SELECT 
region, 
sale_date, 
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS cumulative_sales 
FROM 
sales;

这里，我们计算了每个地区按日期排序的累计销售额。

示例3：使用窗口框架

假设我们只想计算过去7天内的累计销售额：

sql">SELECT 
region, 
sale_date, 
SUM(amount) OVER ( 
PARTITION BY region 
ORDER BY sale_date 
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 
) AS rolling_7_day_sales 
FROM 
sales;

在这个查询中，ROWS BETWEEN 6 PRECEDING AND CURRENT ROW定义了窗口框架，表示从当前行之前的6行到当前行本身，共计7天的数据用于计算累计销售额。