学习 Hive（十）：窗口函数

窗口函数语法：

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

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

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

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

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

窗口函数可以分为三类：

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

样例数据：

聚合函数

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

查询 SQL：

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

查询结果：

排序函数

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

查询 SQL：

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

查询结果：

分析函数

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

查询 SQL：

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

查询结果：

参考

• Structured Query Language/Window functions
• LanguageManual WindowingAndAnalytics
• Introducing Window Functions in Spark SQL
• Hive分析窗口函数(一) SUM,AVG,MIN,MAX
• Hive分析窗口函数(二) NTILE,ROW NUMBER,RANK,DENSE RANK
• Hive分析窗口函数(三) CUME DIST,PERCENT RANK
• Hive分析窗口函数(四) LAG,LEAD,FIRST VALUE,LAST VALUE
• Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP