Hive窗口函数

概述

• 窗口函数是一组特殊函数
  • 扫描多个输入行来计算每个输出值，为每行数据生成一行结果
  • 可以通过窗口函数来实现复杂的计算和聚合
• 语法

  • Function (arg1,..., arg n) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_clause>])

  • PARTITION BY类似于GROUP BY，未指定则按整个结果集

  • 只有指定ORDER BY子句之后才能进行窗口定义

  • 可同时使用多个窗口函数

  • 过滤窗口函数计算结果必须在外面一层

• 按功能可划分为：排序，聚合，分析

排序

• ROW_NUMBER()
  • 对所有数值输出不同的序号，序号唯一连续
• RANK()
  • 对相同数值，输出相同的序号，下一个序号跳过（1,1,3）
• DENSE_RANK()
  • 对相同数值，输出相同的序号，下一个序号连续（1,1,2）
• NTILE(n)
  • 将有序的数据集合平均分配到n个桶中, 将桶号分配给每一行，根据桶号，选取前或后 n分之几的数据
• PERCENT_RANK()
  • (目前排名- 1)/(总行数- 1)，值相对于一组值的百分比排名
    案例：

SELECT 
name, dept_num, salary, 
ROW_NUMBER() OVER () AS row_num, 
RANK() OVER (PARTITION BY dept_num ORDER BY salary) AS rank, 
DENSE_RANK() OVER (PARTITION BY dept_num ORDER BY salary) AS dense_rank,
PERCENT_RANK() OVER(PARTITION BY dept_num ORDER BY salary) AS percent_rank, 
NTILE(2) OVER(PARTITION BY dept_num ORDER BY salary) AS ntile 
FROM employee_contract 
ORDER BY dept_num, salary;

聚合

• count()：计数，可以和distinct一起用
• sum()：求和
• avg()：平均值
• max()/min()： 最大/小值
• 从Hive 2.1.0开始在over子句中支持聚合函数

分析

• cume_dist
  小于等于当前值的行数/分组内总行数
• lead／lag(col,n)
  某一列进行往前/后第n行值(n可选，默认为1)
• first_value
  对该列到目前为止的首个值
• last_value
  到目前行为止的最后一个值

窗口定义

• 窗口定义由[<window_clause>]子句描述
  • 用于进一步细分结果并应用分析函数
• 支持两类窗口定义
  • 行类型窗口
  • 范围类型窗口
• rank、ntile、dense_rank、cume_dist、percent_rank、lead、lag和row_number函数不支持与窗口子句一起使用
• 行窗口：根据当前行之前或之后的行号确定的窗口
  • rows between <start_expr> and <end_expr>
  • <start_expr>可以为下列值
    • unbounded preceding: 窗口起始位置（分组第一行）
    • current row：当前行
    • n preceding/following：当前行之前/之后n行
  • <end_expr>可以为下列值
    • unbounded following : 窗口结束位置（分组最后一行）
    • current row：当前行
    • n preceding/following：当前行之前/之后n行