窗口函数

发表于   |   分类于

先看一个栗子：

1
2
3
4
5
6
7
8
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

想得到上述表格里1号部门里薪水最高的员工名和对应的薪水，如果用聚合函数来做，需要建立子查询，因为按照DepartmentId聚合，只能得到一个统计值，即部门内最高的薪水。然后再关联全表获得员工其他信息。

所以聚合函数有个缺点，即普通的聚合函数用group by分组，每个分组只能返回一行统计值。而窗口函数采用partition by分组，并且每组每行都可以返回一个统计值。

上述问题可以通过一个典型的窗口函数写法来实现组内的排序，且不丢失原表的信息：

1
ROW_NUMBER() OVER (PARTITION BY departmentId ORDER BY salary desc BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

开窗函数

开窗函数即over()函数。窗口则是由over子句定义的记录范围，通常包含多行记录。分析函数就作用于窗口内的数据。

开窗有以下两种方式

1
2
OVER (DISTRIBUTE BY ... SORT BY ...) 
OVER (PARTITION BY ... ORDER BY ...)

其中，PARTITION BY是按照一个一个reduce去处理数据的，所以要使用全局排序ORDER BY。DISTRIBUTE BY是按照多个reduce去处理数据的，所以对应的排序是局部排序SORT BY。

下边我们只讨论 OVER (PARTITION BY ... ORDER BY ...)

窗口

窗口大小也称为WINDOW子句，写在ORDER BY语句之后。用于限制窗口的大小，默认窗口大小是从起始行到当前行。

窗口大小的写法如下：

1
2
3
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

其中，
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点
UNBOUNDED PRECEDING：从前面的起点
UNBOUNDED FOLLOWING：到后面的终点

举几个栗子：

1
2
3
4
5
6
7
8
9
10
11
# 从起始行到当前行（默认）
rows between unbounded preceding and current row

# 从当前行到之前三行
rows between 3 preceding and current row

# 当前行的前三行到之后的一行
rows between 3 preceding and 1 following

# 当前行的前三行到之后的所有行
rows between 3 preceding and unbounded following

分析函数

分析函数会对窗口中的每一行数据输出一个结果，即会对查询的结果多出一列，这一列可以是聚合结果，也可以是排序结果。

分析函数的使用一定要注意窗口的范围，因为窗口的范围限定了分析函数的作用范围。

排序函数

排序函数不支持WINDOW子句，即不支持自定义窗口大小。
ASC时，默认nulls last；desc时，默认nulls first

>
ROW_NUMBER()
不并列，数值相同也会进行连续排名，例如1 2 3 4

>
RANK()
相同的两名是并列，空出并列所占的名次，例如1 2 2 4

>
DENSE_RANK()
相同的两名是并列，空出并列所占的名次，例如1 2 2 3

>
NTILE(n)
将分组数据按照顺序切分成n片，返回当前记录所在的切片值。
如果切片不均匀，默认增加第一个切片的分布。
通常用于取前百分数比例的记录后计算统计值，例如前50%用户的均值

聚合函数

CUME_DIST()
小于等于当前值的行数/分组总行数

>
PERCENT_RANK()
分组内当前行的RANK值-1/分组内总行数-1
RANK从1开始，相同值RANK相同

LEAD(col,n,DEFAULT)
窗口内往下第n行值。第一个参数为列名，第二个参数为当前行往下第n行（可选，默认为1，不含当前行），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

LAG(col,n,DEFAULT)
窗口内往上第n行值。第一个参数为列名，第二个参数为当前行往上第n行（可选，默认为1，不含当前行），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

FIRST_VALUE(col)
分组内排序后，截止到当前行，第一个值
first_value(col1) over (partition by col2 order by col3)取分组内排序后，截止到当前行，第一个值

LAST_VALUE(col)
分组内排序后，截止到当前行，最后一个值
last_value(col1) over (partition by col2 order by col3)取分组内排序后，截止到当前行，最后一个值

以下述表格为例：

1
2
3
4
5
6
7
+----+-------+------------+
| Id | Name  | Dept_code  |
+----+-------+------------+
| 1  | Joe   | AA         |
| 2  | Henry | BB         |
| 3  | Sam   | CC         | 
+----+-------+------------+

>
CUME_DIST() OVER (ORDER BY id)
输出：0.33, 0.66, 1

PERCENT_RANK() OVER (ORDER BY id)
输出：0, 0.5, 1

LEAD(dept_code,2) OVER (ORDER BY id)
输出：CC, NULL, NULL

LAG(dept_code,2) OVER (ORDER BY id)
输出：NULL, NULL, AA

FIRST_VALUE(dept_code) OVER (ORDER BY id)
输出：AA, AA, AA

LAST_VALUE(dept_code) OVER (ORDER BY id)
输出：AA, BB, CC