hive窗口函数

窗口分析

窗口函数——聚合「sum\max\min\avg」

以SUM为例子：（max、min、avg同理）

sum(求和字段) over (partition by 分组字段 order by 排序字段 rows between unbounded preceding and current row) as pv1

关键字	说明
如果不指定 rows between	默认为从起点到当前行;
如果不指定 order by	则将分组内所有值累加;

关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：

关键字	说明
preceding：往前	3 preceding（前三行）
following：往后	1 following（后一行）
current row：当前行	current row（当前行）

关键字	说明
unbounded preceding.	(表示从前面的起点)
unbounded following.	(表示到后面的终点)

窗口帧

作用是用于调整窗口大小

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ( CURRENT ROW ）| (UNBOUNDED | [num]) FOLLOWING)

UNBOUNDED ：表示没有限制

num：具体行值或数据值

PRECEDING: 往前取

CURRENT ROW : 表示当前行

FOLLOWING：往后取值

-- 方式：
-- 1.按行限制窗口大小
--  ROWS BETWEEN num PRECEDING AND CURRENT ROW =>      往前取 num 行到当前行结束  
--  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW => 往前不限制行数 到 当前行结束
--  ROWS BETWEEN num PRECEDING AND num FOLLOWING => 往前 num行 往后 num 行 

-- 需求：对于各班级学生成绩按倒序排序，再取出前3名和后3名学生的平均分

SELECT
*
,avg(sum_score) over(partition by clazz order by sum_score ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) as  avg_sum_score
FROM student_score

-- 2.按值限制窗口大小
-- 对于range来说必须要使用 order by 并对order by中的列计算其范围 

-- 需求：对于各班级学生成绩按倒序排序,再取出比当前学生分数多10分和少10分的学生人数个数

SELECT
*
,count(*) over(partition by clazz order by sum_score RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING) as  avg_sum_score
FROM student_score

窗口取行

• lag/lead

LAG(col,n,default)：往前第n行数据,default如果参数不给，那么默认返回NULL
LEAD(col,n,default)：往后第n行数据

• FIRST_VALUE /LAST_VALUE

FIRST_VALUE：取分组内排序后，截止到当前行，第一个值
LAST_VALUE：取分组内排序后，截止到当前行，最后一个值，对于并列的排名，取最后一个

窗口函数

非常重要

排名函数

• row_number函数
• rank/dense_rank
• percent_rank/ cume_dist/NTILE

– 三者的不同点
① row_number() 排名连续 不重复
② dense_rank() 排名连续 且重复
③ rank() 排名不连续 且重复

hive中排名函数：https://blog.youkuaiyun.com/cc__cc___/article/details/134975068