窗口函数(Window Functions)是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:计算每个地区的销售额排名
SELECT
region,
sale_date,
amount,
RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM
sales;
这个查询会为每个地区内的销售额进行排名。
示例2:计算每个地区每天的累计销售额
SELECT
region,
sale_date,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS cumulative_sales
FROM
sales;
这里,我们计算了每个地区按日期排序的累计销售额。
示例3:使用窗口框架
假设我们只想计算过去7天内的累计销售额:
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天的数据用于计算累计销售额。
总结
窗口函数是SQL中非常强大的工具,允许你在保持数据行独立性的同时,对数据集中的行进行复杂的计算。通过合理使用分区、排序和窗口框架,你可以解决许多复杂的数据分析问题。