窗口函数
窗口函数的语法：
function_name([expression [, expression ... ]]) OVER( [ PARTITION BY expr1 ] [ ORDER BY expr2 [ DESC ] ] )
- [ PARTITION BY expr1 ] 可选参数，partition by 会将查询的数据进行分组，窗口函数会独立的处理每一个分组。
- [ ORDER BY expr2 [ DESC ] ] 可选参数，根据expr2进行排序。
- [ DESC ] 可选参数，默认会使用正序排序，使用DESC参数后结果将会使用倒序排序。
本文的介绍中，会使用下表的数据做为示例数据进行说明。
| id | zh_name | director | prime_genre | runtime | rate_num | votes | stars | pubdate |
|---|---|---|---|---|---|---|---|---|
| 1 | 星际穿越 | 克里斯托弗·诺兰 | 剧情 | 169 | 7.75 | 416252 | 马修·麦康纳 | 2014-11-12 |
| 2 | 辛德勒的名单 | 史蒂文·斯皮尔伯格 | 剧情 | 195 | 7.97 | 329749 | 连姆·尼森 | 1993-11-30 |
| 3 | 唐伯虎点秋香 | 李力持 | 喜剧 | 102 | 4.25 | 260928 | 周星驰 | 1993-07-01 |
| 4 | 致命ID | 詹姆斯·曼高德 | 剧情 | 90 | 9.57 | 271970 | 约翰·库萨克 | 2003-04-25 |
| 5 | 指环王3：王者无敌 | 彼得·杰克逊 | 剧情 | 201 | 5.24 | 255167 | 维果·莫腾森 | 2004-03-12 |
| 6 | 城市之光 | 查理·卓别林 | 剧情 | 87 | 0.11 | 34216 | 查理·卓别林 | 1931-01-30 |
| 7 | 这个男人来自地球 | 理查德·沙因克曼 | 剧情 | 87 | 4.15 | 147835 | 大卫·李·史密斯 | 2007-11-13 |
| 8 | 上帝也疯狂 | 加美·尤伊斯 | 喜剧 | 109 | 2.67 | 83463 | Marius Weyers | 1980-09-10 |
| 9 | 大话西游之大圣娶亲 | 刘镇伟 | 喜剧 | 95 | 3.91 | 395680 | 周星驰 | 2014-10-24 |
| 10 | 放牛班的春天 | 克里斯托夫·巴拉蒂 | 剧情 | 97 | 9.92 | 396189 | 杰拉尔·朱诺 | 2004-10-16 |
dense_rank
| 语法 | dense_rank() OVER( [ PARTITION BY expr1 ] [ ORDER BY expr2 [ DESC ] ] ) |
|---|---|
| 参数说明 | 1. expr1：可选参数，用于分区的表达式，可选类型：任意类型； 2. expr2：可选参数，用于排序的表达式，可选类型：任意类型； 3. DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型 | 数字 |
| 描述 | 返回当前窗口的排名(不保持间隔)， 计数从 1 开始。dense_rank与rank的区别在于dense_rank在出现重复排名时，下一个排名会仅接其上的序号，而rank则会跳过因为重复而没有出现的序号。 |
- 示例
text
dense_rank() OVER (PARTITION BY {prime_genre} ORDER BY {runtime} ASC)结果如下：
| runtime | prime_genre | value |
|---|---|---|
| 169 | 剧情 | 4 |
| 195 | 剧情 | 5 |
| 102 | 喜剧 | 2 |
| 90 | 剧情 | 2 |
| 201 | 剧情 | 6 |
| 87 | 剧情 | 1 |
| 87 | 剧情 | 1 |
| 109 | 喜剧 | 3 |
| 95 | 喜剧 | 1 |
| 97 | 剧情 | 3 |
lag
| 语法 | lag(ARG, I) OVER( [ PARTITION BY expr1 ] [ ORDER BY expr2 [ DESC ] ] ) |
|---|---|
| 参数说明 | 1. ARG：窗口框架，可选类型：任意类型； 2. I：指定记录与当前记录的距离，可选类型：数字； 3. expr1：可选参数，用于分区的表达式，可选类型：任意类型； 4. expr2：可选参数，用于排序的表达式，可选类型：任意类型； 5. DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型 | 任意类型 |
| 描述 | 窗口函数，向前获得相对于当前记录指定i距离的那条记录的 arg 数据 |
- 示例
text
lag({zh_name}, 2) OVER (ORDER BY {runtime} ASC)结果如下：
| zh_name | runtime | value |
|---|---|---|
| 星际穿越 | 169 | 唐伯虎点秋香 |
| 辛德勒的名单 | 195 | 上帝也疯狂 |
| 唐伯虎点秋香 | 102 | 大话西游之大圣娶亲 |
| 致命ID | 90 | 城市之光 |
| 指环王3：王者无敌 | 201 | 星际穿越 |
| 城市之光 | 87 | null |
| 这个男人来自地球 | 87 | null |
| 上帝也疯狂 | 109 | 放牛班的春天 |
| 大话西游之大圣娶亲 | 95 | 这个男人来自地球 |
| 放牛班的春天 | 97 | 致命ID |
lead
| 语法 | lead(ARG, I) |
|---|---|
| 参数说明 | 1. 参与计算的参数，可选类型：任意类型； 2. 整数类型参数，可选类型：数字 |
| 返回值类型 | 任意类型 |
| 描述 | 窗口函数，向后获得相对于当前记录指定 i 距离的那条记录的 arg 数据 |
- 示例
text
lead({zh_name}, 2) OVER (ORDER BY {runtime} ASC)结果如下：
| zh_name | runtime | value |
|---|---|---|
| 星际穿越 | 169 | 指环王3：王者无敌 |
| 辛德勒的名单 | 195 | null |
| 唐伯虎点秋香 | 102 | 星际穿越 |
| 致命ID | 90 | 放牛班的春天 |
| 指环王3：王者无敌 | 201 | null |
| 城市之光 | 87 | 致命ID |
| 这个男人来自地球 | 87 | 大话西游之大圣娶亲 |
| 上帝也疯狂 | 109 | 辛德勒的名单 |
| 大话西游之大圣娶亲 | 95 | 唐伯虎点秋香 |
| 放牛班的春天 | 97 | 上帝也疯狂 |
nth_value
| 语法 | nth_value(ARG, I) OVER( [ PARTITION BY expr1 ] [ ORDER BY expr2 [ DESC ] ] ) |
|---|---|
| 参数说明 | 1. ARG：窗口框架，可选类型：任意类型； 2. I：想要获取的数据的行数，可选类型：数字； 3. expr1：可选参数，用于分区的表达式，可选类型：任意类型； 4. expr2：可选参数，用于排序的表达式，可选类型：任意类型； 5. DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型 | 任意类型 |
| 描述 | 获取窗口中第 i 个 arg 值 |
- 示例
text
NTH_VALUE({zh_name}, 2) OVER( PARTITION BY {prime_genre} ORDER BY {runtime} DESC )结果如下：
| zh_name | runtime | prime_genre | value |
|---|---|---|---|
| 星际穿越 | 169 | 剧情 | 辛德勒的名单 |
| 辛德勒的名单 | 195 | 剧情 | 辛德勒的名单 |
| 唐伯虎点秋香 | 102 | 喜剧 | 唐伯虎点秋香 |
| 致命ID | 90 | 剧情 | 辛德勒的名单 |
| 指环王3：王者无敌 | 201 | 剧情 | null |
| 城市之光 | 87 | 剧情 | 辛德勒的名单 |
| 这个男人来自地球 | 87 | 剧情 | 辛德勒的名单 |
| 上帝也疯狂 | 109 | 喜剧 | null |
| 大话西游之大圣娶亲 | 95 | 喜剧 | 唐伯虎点秋香 |
| 放牛班的春天 | 97 | 剧情 | 辛德勒的名单 |
rank
| 语法 | rank() OVER( [ PARTITION BY expr1 ] [ ORDER BY expr2 [ DESC ] ] ) |
|---|---|
| 参数说明 | 1. expr1：可选参数，用于分区的表达式，可选类型：任意类型； 2. expr2：可选参数，用于排序的表达式，可选类型：任意类型； 3. DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型 | 数字 |
| 描述 | 返回当前窗口的排名(保持间隔)，计数从 1 开始 |
- 示例
text
rank() OVER (PARTITION BY {prime_genre} ORDER BY {runtime} ASC)结果如下：
| runtime | prime_genre | value |
|---|---|---|
| 169 | 剧情 | 5 |
| 195 | 剧情 | 6 |
| 102 | 喜剧 | 2 |
| 90 | 剧情 | 3 |
| 201 | 剧情 | 7 |
| 87 | 剧情 | 1 |
| 87 | 剧情 | 1 |
| 109 | 喜剧 | 3 |
| 95 | 喜剧 | 1 |
| 97 | 剧情 | 4 |
row_number
| 语法 | row_number() OVER( [ PARTITION BY expr1 ] [ ORDER BY expr2 [ DESC ] ] ) |
|---|---|
| 参数说明 | 1. expr1：可选参数，用于分区的表达式，可选类型：任意类型； 2. expr2：可选参数，用于排序的表达式，可选类型：任意类型； 3. DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型 | 数字 |
| 描述 | 返回当前窗口的行数，计数从 1 开始 |
- 示例
text
ROW_NUMBER() OVER (PARTITION BY {prime_genre} ORDER BY {runtime} ASC)结果如下：
| runtime | prime_genre | value |
|---|---|---|
| 169 | 剧情 | 5 |
| 195 | 剧情 | 6 |
| 102 | 喜剧 | 2 |
| 90 | 剧情 | 3 |
| 201 | 剧情 | 7 |
| 87 | 剧情 | 2 |
| 87 | 剧情 | 1 |
| 109 | 喜剧 | 3 |
| 95 | 喜剧 | 1 |
| 97 | 剧情 | 4 |
支持窗口计算的聚合函数
部分聚合函数也能够作为窗口函数使用。仅当聚集函数调用后面跟着一个OVER子句时，聚合函数会进行窗口计算。函数列表如下：
| 函数名 | 描述 |
|---|---|
| SUM | 计算数值列的总和 |
| AVG | 计算数值列的平均值 |
| MAX | 计算数值列的最大值 |
| MIN | 计算数值列的最小值 |
| CORR | 计算两列数值的相关系数 |
| COVAR_POP | 计算两列数值的总体协方差 |
| COVAR_SAMP | 计算两列数值的样本协方差 |
| REGR_AVGX | 计算线性回归中自变量的平均值 |
| REGR_AVGY | 计算线性回归中因变量的平均值 |
| REGR_COUNT | 计算线性回归中非空数据点的数量 |
| REGR_INTERCEPT | 计算线性回归方程的截距 |
| REGR_R2 | 计算线性回归的决定系数（R²） |
| REGR_SLOPE | 计算线性回归方程的斜率 |
| REGR_SXX | 计算线性回归中自变量的平方和 |
| REGR_SXY | 计算线性回归中自变量与因变量的交叉乘积和 |
| REGR_SYY | 计算线性回归中因变量的平方和 |
| STDDEV_POP | 计算数值列的总体标准差 |
| STDDEV_SAMP | 计算数值列的样本标准差 |
| VAR_POP | 计算数值列的总体方差（总体标准差的平方） |
| VAR_SAMP | 计算数值列的样本方差（样本标准差的平方） |
| STRING_AGG | 将字符串列按指定方式聚合连接 |
| JSON_AGG | 将数据聚合为JSON格式数组 |
| COUNT | 计算数据的行数（可指定列，忽略NULL值；或用*包含所有行） |
order by 子句
提示
这里有一个与窗口函数相关的重要概念：对于每一行，在它的分区中的行集合被称为它的窗口帧。
一些窗口函数只作用在窗口帧中的行上，而不是整个分区。此类函数如果同时使用ORDER BY，则帧包括从分区开始到当前行的所有行，以及后续任何与当前行在ORDER BY子句上相等的行。
所有的聚合函数在使用OVER子句中的ORDER BY表达式时，最终的返回结果均会与不使用ORDER BY表达式会有各种各样的差异。
- 示例1
获取每个班学生的总人数。
COUNT(1) OVER (PARTITION BY {class})- 示例2
将各个班级的学生姓名按照成绩顺序拼接成一个JSON数组。
JSON_AGG({student_name}) OVER (PARTITION BY {class} ORDER BY {student_score})窗口函数注意事项
使用窗口函数做为图表指标时的注意事项:
- 窗口函数不是聚合函数，不能作为聚合函数所用。
- 图表中有维度列的时候，如果窗口函数使用了 OVER 子句[PARTITION BY expr1]/[ORDER BY expr2]，非聚合运算的 expr1 或者 expr2 都必须是图表的维度列，否则会报错。
- 假设图表有维度 a，b， 度量为 MAX(COUNT({书籍编号})) OVER (***)。计算的逻辑是:
- 以 a， b 分组，计算 COUNT({书籍编号}) 的值， 得到一个中间结果： （a, b, count_tmp）
- 度量是 MAX(COUNT({书籍编号})) OVER ()， 计算结果是：在 count_tmp 列中找出最大值
- 度量是 MAX(COUNT({书籍编号})) OVER (PARTITION BY a)， 计算结果是：从（a， tmp） 这个数组中，以a的值为划分依据，找出每个组内tmp的最大值
- 度量是 MAX(COUNT({书籍编号})) OVER (PARTITION BY b)， 计算结果是：从（b， tmp） 这个数组中，以b的值为划分依据，找出每个组内tmp的最大值