目录
- 一、简介
- 二、使用教程
- 1. 场景
- 2. 功能
- 3. 语法
- 4. 窗口子句
- 5. Windowing Functions：取值
- 6. Aggregates Functions：聚合
- 7. Analytics Functions：排序、序列
一、简介
差别说明：窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果
二、使用教程
1. 场景
- 分组排序：如取某年级每个班学习成绩排名前10的学生。
- 分组聚合
2. 功能
聚合、取值、排名、序列
3. 语法
<窗口函数>()
OVER (
PARTITION BY <col>
ORDER BY <col>
ROWS/RANGE #范围 ）- 函数子句：指明具体操作，如sum-求和，first_value-取第一个值
- partition by子句：指明分区字段，如果没有，则将所有数据作为一个分区
- order by子句：指明了每个分区排序的字段和方式,也是可选的，没有就是按照表中的顺序
- 窗口子句：指明相对当前记录的计算范围，可以向上（preceding），可以向下（following）,也可以使用between指明，上下边界的值，没有的话默认为当前分区
4. 窗口子句
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）
UNBOUNDED PRECEDING ：表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）
比如说：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
#表示从起点到当前行
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING
#表示往前2行到往后1行
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW
#表示往前2行到当前行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
#表示当前行到终点示例：
SELECT
userId,
price,
FIRST_VALUE(price) OVER (PARTITION BY userId ORDER BY price DESC) AS firvue_num,
FIRST_VALUE(price)
OVER (PARTITION BY userId ORDER BY price DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
AS firvue1_num
FROM
orderInfo运行结果：
不适用于：rank, ntile, dense_rank, cum_dist, persent_rank, lead, lag
5. Windowing Functions：取值
（1）LEAD(col, n, DEFAULT)： 用于统计窗口内往下第n行值，第一个参数为列名，第二个参数为往下第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
（2）LAG(col,n,DEFAULT)： 用于统计窗口内往上第n行值，第一个参数为列名，第二个参数为往上第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
SELECT
userId,
price,
LEAD(price, 2) OVER (PARTITION BY userId ORDER BY price DESC) AS lead_num,
LAG(price, 2) OVER (PARTITION BY userId ORDER BY price DESC) AS lag_num
FROM
orderInfo运行结果：
（3）FIRST_VALUE（col, false/ true)：取分组内排序后，截止到当前行，第一个值，这最多需要两个参数。第一个参数是您想要第一个值的列，第二个（可选）参数必须是false默认为布尔值的布尔值。如果设置为true，则跳过空值
(4) LAST_VALUE(col, false/ true)：取分组内排序后，截止到当前行,最后一个值，这最多需要两个参数。第一个参数是您想要第一个值的列，第二个（可选）参数必须是false默认为布尔值的布尔值。如果设置为true，则跳过空值
SELECT
userId,
price,
FIRST_VALUE(price) OVER (PARTITION BY userId ORDER BY price DESC) AS firvue_num,
LAST_VALUE(price) OVER (PARTITION BY userId ORDER BY price DESC) AS lastvue_num
FROM
orderInfo运行结果：
6. Aggregates Functions：聚合
(1). COUNT(col)
(2). SUM(col)
(3). MIN(col)
(4). MAX(col)
(5). AVG(col)
注释：默认情况就是从起点到当前行，不带order by语句其实就是表示窗口内全部行都参与聚合处理
示例：
SELECT
userId,
price,
AVG( price ) OVER ( PARTITION BY userId ORDER BY price DESC ) AS avg_num, -- 默认范围
AVG( price ) OVER (
PARTITION BY userId
ORDER BY price DESC
ROWS BETWEEN 3 preceding AND CURRENT ROW ) AS avg1_num, -- 指定范围
FROM
orderInfo运行结果：
7. Analytics Functions：排序、序列
(1). ROW_NUMBER：从1开始，按照顺序，生成分组内记录的序列，row_number()的值不会存在重复，当排序的值相同时，按照表中记录的顺序进行排列
(2). RANK：生成数据项在分组中的排名，排名相等会在名次中留下空位
(3). DENSE_RANK：生成数据项在分组中的排名，排名相等会在名次中不会留下空位
示例：
SELECT
userId,
price,
ROW_NUMBER() over ( PARTITION BY userId ORDER BY price DESC ) AS row_num,
RANK() over ( PARTITION BY userId ORDER BY price DESC ) AS rank_num,
DENSE_RANK() over ( PARTITION BY userId ORDER BY price DESC ) AS dense_num
FROM
orderInfo运行结果：
(4). CUME_DIST：小于等于当前值的行数/分组内总行数，统计小于等于当前薪水的人数，所占总人数的比例
(5). PERCENT_RANK：计算给定行的百分比排名，分组内当前行的RANK值-1/分组内总行数-1，用来计算超过了百分之多少的人
(6). NTILE：NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布
示例：
SELECT
userId,
price,
CUME_DIST() over ( PARTITION BY userId ORDER BY price DESC ) AS cume_num,
PERCENT_RANK() over ( PARTITION BY userId ORDER BY price DESC ) AS per_num,
NTILE( 4 ) over ( PARTITION BY userId ORDER BY price DESC ) AS ntile_num -- 每组分成4份，返回在第几份
FROM
orderInfo运行结果：
