hive数据排序、和窗口函数

Order by会对输入做全局排序，因此只有一个Reducer（多个Reducer无法保证全局有序，即使设置了多个Reduce，程序依然会按照一个Reduce进行排序）。Order by全局排序，又一个缺点，就是只有一个Reducer，会导致数据规模较大是，消耗较长的计算时间。

案例演示：

sort by 不是全局排序，其在数据进行reducer前完成排序。使用sort by是可以指定执行的reduce的个数，对输出的数据再执行归并排序，即可得到全部结果。

案例演示：

distribute by是控制在map端如何拆分数据给reduce端的。hive会根据distribute by 后面列，对应reducer的个数进行并发，默认是采用hash算法。sort by为每个reduce产生一个排序文件。一般情况下，distribute by经常和sort by配合使用。

案例演示：

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒叙排序，不能指定排序规则。

GROUP BY用于分组

• Hive基本内置聚合函数与GROUP BY一起使用
• 如果没有指定GROUP BY子句，则默认聚合整个表
• 除聚合函数外，所选的其他列也必须包含在GROUP BY中
• GROUP BY支持使用CASE WHEN或表达式

HAVING：对GROUP BY聚合结果的条件过滤

• 可以避免在GROUP BY之后使用子查询
• HAVING之后可以使用表达式，但不建议使用，性能比较差

基础聚合

基础聚合函数

• max, min, count, sum, avg
  • max(distinct col1)、avg(col2)等
• collect_set, collect_list：返回每个组列中的对象集/列表

注意事项

• 一般与GROUP BY一起使用（不一起使用就会都查询显示出来）
• 可应用于列或表达式
• 对NULL的count聚合为0
  • select count(null) = 0

窗口函数是一组特殊函数

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

窗口函数语法

• PARTITION BY类似于GROUP BY，未指定则按整个结果集
• 只有指定ORDER BY子句之后才能进行窗口定义
• 可同时使用多个窗口函数
• 过滤窗口函数计算结果必须在外面一层

窗口函数功能划分

排序，聚合，分析

ROW_NUMBER()
对所有数值输出不同的序号，序号唯一连续

案例演示：

RANK()
对相同数值，输出相同的序号，下一个序号跳过（1,1,3）

DENSE_RANK()
对相同数值，输出相同的序号，下一个序号连续（1,1,2）

PERCENT_RANK()
(目前排名- 1)/(总行数- 1)，值相对于一组值的百分比排名

• COUNT()
  • 计数，可以和DISTINCT一起用（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函数不支持与窗口子句一起使用