Hive是基于Hadoop的数据仓库解决方案,简单来说,Hive就是在Hadoop上架了一层SQL接口,可以将SQL翻译成MapReduce去Hadoop上执行,这样就使得数据开发和分析人员很方便地使用SQL来完成海量数据的统计和分析,而不必使用编程语言开发MapReduce那么麻烦。Hive可以使用HQL(Hive SQL)很方便地完成对海量数据的统计汇总,即席查询和分析。Hive中提供了越来越多的分析函数,用于完成复杂的统计分析,其中Window Function,又称窗口函数或分析函数,极大地方便了大数据的复杂统计分析,本文将介绍一下常用的Window Function。

Window子句

理解窗口函数,关键是要理解Window子句,Window子句通过ROWS/RANGE指定了一个窗口的边界,它的语法格式如下:

其中:

PRECEDING往前

FOLLOWING往后

CURRENT ROW当前行

UNBOUNDED PRECEDING起点

UNBOUNDED FOLLOWING终点

或者:

其中:

在ASC时,PRECEDING表示减,FOLLOWING表示加,而在DESC时,PRECEDING表示加,

FOLLOWING表示减。

这里需要注意的是,Window子句在限定窗口上下限时,包括但不仅仅是以上两种格式中提供的形式,这在下面的实验中就可以看得到。

ROWS BETWEEN AND控制物理窗口,从行数上控制窗口尺寸,RANGE BETWEEN AND控制逻辑窗口,以当前行为基准,通过行值划定窗口尺寸。

逻辑窗口可以理解为数值的区间控制,物理窗口含义如下图所示:

数据准备

在详细介绍之前,先做数据准备。

window_test表结构如下:

window_test表数据如下:

窗口函数

本文将分四组介绍窗口函数,第一组介绍较为基础的SUM、AVG、MIN、MAX,第二组介绍序列函数NTILE、ROW_NUMBER、RANK、DENSE_RANK、CUME_DIST、PERCENT_RANK,第三组介绍LAG、LEAD、FIRST_VALUE、LAST_VALUE,第四组介绍分组相关的GROUPING SETS、GROUPING_ID、CUBE、ROLLUP。本文将以最基础的SUM为例来详细介绍Window子句的使用。

第一组:SUM、AVG、MIN、MAX

SUM:求和

执行结果:

由上述运行结果来看,当over()语句置空时,窗口默认返回所有行,一般情况下这对我们的统计没有实际用处,直接使用聚集函数即可。

实验二:

执行结果:

由上述结果来看,当over()语句中加上partition by子句而忽略order by和Window子句时,窗口默认返回分组内所有行,同理,一般情况下这对我们的统计没有实际用处,分组后直接使用聚集函数即可。

接下来我们仅加上order by子句,进行一系列的统计。

实验三:

执行结果:

我们发现实验三和实验一、二在Window子句置空的情况下统计方式发生了变化,产生上述结果的原因是窗口函数没有指定orderby子句,也就不存在 ROWS/RANGE 窗口的计算,离开了order by子句的Window子句是没有意义的,而当指定了orderby子句时,窗口默认返回从起点到当前行的所有行,这也是实验三和实验一、二统计方法完全不同的原因。

明白了order by子句的重要性之后,我们加上partition by子句来进行统计。

实验四:

这里需要注意的是,当FOLLOWING子句出现在AND之前时,取值范围为>而不再是>=,另外,PRECEDING子句不能出现在AND之后。

至此,Window子句的用法和SUM的用法已经介绍了,对于AVG、MIN、MAX,分别是在窗口返回的数据集上取平均值、最小值和最大值,在这里不多做介绍。

第二组:NTILE、ROW_NUMBER、RANK、DENSE_RANK、CUME_DIST、PERCENT_RANK

本组函数不支持Window子句。

NTILE(N):分组数据按照order by子句顺序切分成N片,返回当前行切片值,如果切片不均匀,默认增加第一个切片的分布。

实验六:

执行结果:

ROW_NUMBER():分组数据按照order by子句顺序,生成分组内记录的序列。

实验七:

执行结果:

RANK():分组数据按照order by子句顺序,生成数据项在分组中的排名,排名相等会在名次中留下空位。

DENSE_RANK():分组数据按照order by子句顺序,生成数据项在分组中的排名,排名相等不会在名次中留下空位。

实验八:

CUME_DIST():分组数据按照order by子句顺序,生成(分组内小于等于当前值的行数) / (分组内总行数)的值。

PERCENT_RANK():分组数据按照order by子句顺序,生成(分组内当前行的RANK值-1) / (分组内总行数-1)。

这里使用round函数使CUME_DIST()和PERCENT_RANK()的值保留两位小数。

第三组:LAG、LEAD、FIRST_VALUE、LAST_VALUE

本组函数不支持Window子句。

LAG(col,n,DEFAULT):分组数据按照order by子句顺序,用于统计窗口内向上数第n行的col列值, DEFAULT为默认值(当向上数第n行为NULL时,取默认值,如不指定,则为NULL),行数值以ROW_NUMBER()为标准。

LEAD(col,n,DEFAULT):与LAG相反,LEAD用于统计窗口内向下数第n行的col列值,DEFAULT为默认值(当向下数第n行为NULL时,取默认值,如不指定,则为NULL),行数值以ROW_NUMBER()为标准。

FIRST_VALUE(col):返回分组内部截止到当前行的第一个值。

LAST_VALUE(col):返回分组内部截止到当前行的最后一个值。

这里需要注意的是,FIRST_VALUE()和LAST_VALUE()在使用时,若不指定排序方式,则按照文件中的偏移量进行排序,会出现错误的结果。应用时需要注意的是,因FIRST_VALUE()和LAST_VALUE()都是取截止到当前行的极值,所以若要取分组内的第一个值,可以使用FIRST _VALUE() OVER(PARTITION BY column1 ORDER BY column2),如果要取分组内的最后一个值,不能使用LAST _VALUE() OVER(PARTITION BY column1 ORDER BY column2),该方法返回的是当前行的值,应该使用FIRST _VALUE() OVER(PARTITION BY column1 ORDER BY column2 DESC)。

第四组:GROUPING SETS、GROUPING_ID、CUBE、ROLLUP

在一个GROUP BY查询中,根据不同的维度组合进行聚合,等价于将不同维度的GROUP BY结果集进行UNION ALL,如果需要多种维度的聚合,单纯使用GROUP BY的形式会大大增加代码量,此时我们可以根据应用场景的不同选择本组内的窗口函数进行分组聚合。

GROUPING__ID:用于标示结果属于哪一个分组,分组序号遵从CUBE中的分组序号,注意这里’_’是两个。

GROUPING SETS:提供分组的集合,查询结果即为集合中不同维度分组的聚合,格式为GROUP BYcol_list GROUPING SETS (col1,col2,...(col3,col4)...(col5,col6,...)...),其中,col_list是col1、col2、col3…元素的并集,查询结果是以col1分组、col2分组、col3和col4分组、col5和col6分组等进行分组的结果并集。

CUBE:与GROUPING SETS只对集合中的维度进行分组不同,CUBE是对GROUP BY维度的所有组合进行聚合。

ROLLUP:ROLLUP的结果集是CUBE的子集,以最左侧的维度为主,从该维度进行层级聚合,包括全维度。

本文较为基础地介绍了窗口函数的使用,在具体的应用场景下,完成复杂的统计需求还是需要灵活地组合使用窗口函数。