Posted Updated a few seconds read (About 0 words)
开窗函数(Window Functions)是SQL中一种强大的功能,用于在一组相关行(窗口)上执行聚合计算,同时保留每行的详细信息。与传统的聚合函数(如 SUM
, AVG
, COUNT
等)不同,开窗函数不会将结果聚合成单一的行,而是为每一行返回一个计算结果。
基本语法
开窗函数的基本语法如下:1
2
3
4
5function_name(expression) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression [ASC | DESC]]
[ROWS BETWEEN start AND end]
)
主要组成部分
- 函数名:常见的开窗函数包括
ROW_NUMBER()
,RANK()
,DENSE_RANK()
,LEAD()
,LAG()
,SUM()
,AVG()
,MIN()
,MAX()
等。 - OVER子句:定义窗口的范围。
- PARTITION BY:将数据分成多个分区,每个分区独立计算。
- ORDER BY:在每个分区内对数据进行排序。
- ROWS BETWEEN:定义窗口的行范围。
常见的开窗函数
1. 行号函数
ROW_NUMBER():为每个分区内的行分配一个唯一的行号。
1
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)
RANK():为每个分区内的行分配一个排名,如果有相同的值,排名会跳过。
1
RANK() OVER (PARTITION BY column1 ORDER BY column2)
DENSE_RANK():为每个分区内的行分配一个排名,如果有相同的值,排名不会跳过。
1
DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2)
2. 前后行函数
LEAD():获取当前行之后的某一行的值。
1
LEAD(column, offset, default) OVER (PARTITION BY column1 ORDER BY column2)
LAG():获取当前行之前的某一行的值。
1
LAG(column, offset, default) OVER (PARTITION BY column1 ORDER BY column2)
3. 聚合函数
SUM():计算窗口内的总和。
1
SUM(column) OVER (PARTITION BY column1 ORDER BY column2)
AVG():计算窗口内的平均值。
1
AVG(column) OVER (PARTITION BY column1 ORDER BY column2)
MIN():计算窗口内的最小值。
1
MIN(column) OVER (PARTITION BY column1 ORDER BY column2)
MAX():计算窗口内的最大值。
1
MAX(column) OVER (PARTITION BY column1 ORDER BY column2)
示例
假设有一个销售数据表 sales
,包含以下字段:id
, product_id
, sale_date
, amount
。
1. 行号函数示例
1 | SELECT id, product_id, sale_date, amount, |
这个查询为每个产品的销售记录按日期排序,并分配一个行号。
2. 排名函数示例
1 | SELECT id, product_id, sale_date, amount, |
这个查询为每个产品的销售记录按金额降序排序,并分配一个排名。
3. 前后行函数示例
1 | SELECT id, product_id, sale_date, amount, |
这个查询为每个产品的销售记录按日期排序,并获取前一行和后一行的金额。
4. 聚合函数示例
1 | SELECT id, product_id, sale_date, amount, |
这个查询为每个产品的销售记录按日期排序,并计算累计销售额。
窗口范围
- ROWS BETWEEN:定义窗口的行范围。这个查询计算每个产品销售记录按日期排序时,当前行及其前后两行的总和。
1
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)