1. SQL窗口函数简介
SQL窗口函数,也叫OVER函数,是一种常用的SQL函数,它可以在结果集中进行计算并返回结果。可以认为SQL窗口函数就是一种在数据集结果范围内执行的聚合函数。
所谓OVER函数,就是在数据视图上划分分组(Partition By),并给分组排序(Order By),然后在分组结果上做聚合计算的一种函数。它与聚合函数GROUP BY有相似之处,但相比GROUP BY,它更加灵活。因此,OVER函数通常会用在复杂的数据分析场景中。
SQL窗口函数的基本语法:
OVER([PARTITION BY partition_expression, ... ]
[ORDER BY sort_expression [ASC|DESC], ... ]
[ROWS {UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING}])
OVER函数必须要有人来调用,如下:
SELECT column_list,
AGG_FUNCTION(column) OVER (PARTITION BY column1, column2,... ORDER BY column3,...) alias
FROM table_name;
其中AGG_FUNCTION是聚合函数,目前有4个函数可以作为聚合函数,分别为:AVG()、COUNT()、MAX()、SUM()。
2. 常用的SQL窗口函数
2.1 RANK()函数
RANK()函数用来计算一个分组中某个数据行的排名。
它的函数定义为:
RANK() OVER (ORDER BY column DESC)
RANK()函数根据ORDER BY子句中指定的列排序,并根据这个排序返回行的排名。
注意:RANK函数的结果中可能会存在两个连续的排名一致的情况,解决的方法是:
SELECT RANK() OVER (ORDER BY column DESC) AS "Rank",
column1, column2, ...
FROM table_name;
2.2 DENSE_RANK()函数
DENSE_RANK()函数与RANK()函数的区别在于,如果相同的数值被排名,则DENSE_RANK()函数会跳过下一个排名。
例如,如果有三个相同的数值(1,2,2),RANK()函数将分别返回1、2和2作为排名。但是,DENSE_RANK()函数将为第一个分配1,同时跳过第二个排名,在第三个位置分配2。
DENSE_RANK()函数的语法与RANK()函数相同:
DENSE_RANK() OVER (ORDER BY column DESC)
2.3 ROW_NUMBER()函数
ROW_NUMBER()函数用来为结果集中的每一行生成一个唯一的、连续的编号。注意,ROW_NUMBER()函数不是聚合函数。
它的函数定义为:
ROW_NUMBER() OVER (ORDER BY column DESC)
与RANK()函数类似,ROW_NUMBER()函数根据ORDER BY子句中指定的列排序来分配行号。
2.4 NTILE()函数
NTILE()函数用来将一个行集合划分为n个等份。它将结果集划分为n组,并为每一组分配一个组编号。
NTILE()函数的基本语法:
NTILE(n) OVER (ORDER BY column DESC)
n表示要将结果集划分成的组数。排序是可选的,但如果需要使用NTILE函数,必须使用ORDER BY子句。
注意:分析函数的执行顺序与SELECT语句中的顺序不同。在SELECT语句中,FROM优先级最高,然后是WHERE,GROUP BY,HAVING和SELECT。但是,在分析函数中,ORDER BY优先于PARTITION BY。
3. 总结
SQL窗口函数是一种可以在结果集中进行计算并返回结果的SQL函数。常用的SQL窗口函数包括RANK()、DENSE_RANK()、ROW_NUMBER()和NTILE()等。
这些函数可以在数据集结果范围内执行聚合计算,实现灵活的数据分析功能。