20201006【hive窗口函数】

---

一、简介

差别说明：窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果
引用来源：https://www.jianshu.com/p/12eaf61cf6e1
https://baijiahao.baidu.com/s?id=1673921653655594205&wfr=spider&for=pc
感谢

二、使用教程

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

运行结果：