学习 Hive（十）：窗口函数

窗口函数语法：

Function() OVER ([PARTITION BY <...>] [ORDER BY <...>] [Window Specification])  

PARTITION BY 定义窗口分区，控制哪些数据行和当前数据行在同一个分区。

ORDER BY 定义窗口排序，控制分区内的排序方式，决定了当前行在分内的位置。

Window Specification 定义窗口帧，根据当前行的位置声明哪些行将包含在帧中。更多

表、数据集、分区、帧和行的关系如下图所示：

窗口函数可以分为三类：

• 聚合函数，通常的聚合函数，如 SUM 和 MAX 等。
• 排序函数，排序数据函数，如 RANK 和 ROW_NUMBER 等。
• 分析函数，统计和对比函数，如 LEAD、LAG 和 FIRST_VALUE 等。

样例数据：

name	department	salary
Tom	A	5500
Jerry	A	5000
Lily	B	7000
Lucy	A	8000
Tony	B	3800
Micheal	B	4000

聚合函数

需求：计算每个雇员所在部门的总工资额。

查询 SQL：

SELECT  
  name,
  department,
  salary,
  SUM(salary) OVER (PARTITION BY department) AS sum
FROM employee  

查询结果：

name	department	salary	sum
Tom	A	5500	18500
Jerry	A	5000	18500
Lily	B	7000	14800
Lucy	A	8000	18500
Tony	B	3800	14800
Micheal	B	4000	14800

排序函数

需求：计算每个雇员所在部门的工资排名。

查询 SQL：

SELECT  
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department SORT BY salary DESC) AS rank
FROM employee  

查询结果：

name	department	salary	rank
Tom	A	5500	2
Jerry	A	5000	3
Lily	B	7000	1
Lucy	A	8000	1
Tony	B	3800	3
Micheal	B	4000	2

分析函数

需求：计算每个雇员所在部门的最高工资。

查询 SQL：

SELECT  
  name,
  department,
  salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department SORT BY salary DESC) AS first
FROM employee  

查询结果：

name	department	salary	rank
Tom	A	5500	8000
Jerry	A	5000	8000
Lily	B	7000	7000
Lucy	A	8000	8000
Tony	B	3800	7000
Micheal	B	4000	7000

参考

• Structured Query Language/Window functions
• LanguageManual WindowingAndAnalytics
• Introducing Window Functions in Spark SQL
• Hive分析窗口函数(一) SUM,AVG,MIN,MAX
• Hive分析窗口函数(二) NTILE,ROWNUMBER,RANK,DENSERANK
• Hive分析窗口函数(三) CUMEDIST,PERCENTRANK
• Hive分析窗口函数(四) LAG,LEAD,FIRSTVALUE,LASTVALUE
• Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP