HIVE 窗口及分析函数

窗口函数应用场景：

（1）用于分区排序

（2）动态Group By

（3）Top N

（4）累计计算

（5）层次查询

一、分析函数

用于等级、百分点、n分片等。

函数 说明

RANK() 返回数据项在分组中的排名，排名相等会在名次中留下空位

DENSE_RANK() 返回数据项在分组中的排名，排名相等会在名次中不会留下空位

NTILE() 返回n分片后的值

ROW_NUMBER() 为每条记录返回一个数字

Rank、DENSE_RANK

RANK（）在出现等级相同的元素时预留为空，DENSE_RANK()不会。

Eg：某产品类型有两个并列第一

RANK()：第一二为1，第三位3

DENSE_RANK()：第一二为1，第三位2

Sql代码 收藏代码

SELECT

column_name,

RANK() OVER （ORDER BY column_name DESC） AS rank,

DENSE_RANK() OVER （ORDER BY SUM(column_name) DESC） AS dense_rank

FROM table_name

OVER 需要，括号内为编号顺序

注意：order by 时，desc NULL 值排在首位，ASC时NULL值排在末尾

可以通过NULLS LAST、NULLS FIRST 控制

Java代码 收藏代码

RANK() OVER (ORDER BY column_name DESC NULLS LAST)

PARTITION BY 分组排列顺序

Java代码 收藏代码

RANK() OVER(PARTITION BY month ORDER BY column_name DESC)

这样，就会按照month 来分，即所需要排列的信息先以month 的值来分组，在分组中排序，各个分组间不干涉

CUBE,ROLLUP,GROUPING SETS() 详见：HIVE增强的聚合，也可以结合RANK()使用实现具体逻辑。

NTILE

按层次查询，如一年中，统计出工资前1/5之的人员的名单，使用NTILE分析函数,把所有工资分为5份，为1的哪一份就是我们想要的结果：

Sql代码 收藏代码

select empno,ename,sum(sal),ntile(5) over (order by sum(sal) desc nulls last) til from emp group by empno,ename;

ROW_NUMBER

ROW_NUMBER()从1开始，为每条记录返回一个数字

Sql代码 收藏代码

SELECT

ROW_NUMBER() OVER (ORDER BY column_name DESC)AS row_name

FROM table_name;

二、窗口函数

可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。

可以结合聚集函数SUM() 、AVG() 等使用。

可以结合FIRST_VALUE() 和LAST_VALUE()，返回窗口的第一个和最后一个值

（1）计算累计和

eg：统计1-12月的累积销量，即1月为1月份的值，2月为1.2月份值的和，3月为123月份的和，12月为1-12月份值的和

Java代码 收藏代码

SELECT

month,SUM(amount) month_amount,

SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount

FROM table_name

GROUP BY month

ORDER BY month;

其中：

SUM( SUM(amount)) 内部的SUM(amount)为需要累加的值，在上述可以换为 month_amount

ORDER BY month 按月份对查询读取的记录进行排序，就是窗口范围内的排序

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点，UNBOUNDED PRECEDING 为起点，表明从第一行开始, CURRENT ROW为默认值，就是这一句等价于：

ROWS UNBOUNDED PRECEDING

PRECEDING：在前 N 行的意思。

FOLLOWING：在后 N 行的意思。

计算前3个月之间的和

Sql代码 收藏代码

SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_amount

也可以

Java代码 收藏代码

SUM( SUM(amount)) OVER (ORDER BY month 3 PRECENDING) AS cumulative_amount

前后一个月之间的和

Sql代码 收藏代码

SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cumulative_amount

窗体第一条和最后一条的值

Java代码 收藏代码

FIRST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;

LAST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;

三、LAG、LEAD

获得相对于当前记录指定距离的那条记录的数据

LAG()为向前、LEAD()为向后

Sql代码 收藏代码

LAG(column_name1,1) OVER(ORDER BY column_name2)

LEAG(column_name1,1) OVER(ORDER BY column_name2)

这样就获得前一条、后一条的数据

四、FIRST、LAST

获得一个排序分组中的第一个值和组后一个值。可以与分组函数结合

Java代码 收藏代码

SELECT

MIN(month) KEEP(DENSE_RANK FIRST ORDER BY SUM(amount)) AS highest_sales_month,

MIN(month) KEEP(DENSE_RANK LAST ORDER BY SUM(amount)) AS lows_sales_month

FROM table_name

GROUP BY month

ORDER BY month;

这样就可以求得一年中销量最高和最低的月份。

输出的是月份，但是用SUM(amount)来判断。

示例

PARTITION BY with one partitioning column, no ORDER BY or window specification

SELECT a, COUNT(b) OVER (PARTITION BY c) FROM T;

根据c分派任务, 选择a和每个c下b的个数

PARTITION BY with two partitioning columns, no ORDER BY or window specification

SELECT a, COUNT(b) OVER (PARTITION BY c, d) FROM T;

根据c, d分派任务, 选择a和每个c, d下b的个数

PARTITION BY with one partitioning column, one ORDER BY column, and no window specification

SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d) FROM T;

根据c分派任务, 每个任务组依据d排序, 选择a和每个c下字段b的和

PARTITION BY with two partitioning columns, two ORDER BY columns, and no window specification

SELECT a, SUM(b) OVER (PARTITION BY c, d ORDER BY e, f) FROM T;

根据c,d分派任务, 每个任务组依据e,f排序, 选择a和每个c, d下字段的b的和

PARTITION BY with partitioning, ORDER BY, and window specification

SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T;

根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 从最开始到当前列的字段b的和

SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) FROM T;

根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 从当前列之前3个到当前列的字段b的平均数

SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) FROM T;

根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 当前列前后各3列的字段b的平均数

SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM T;

根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 当前列到最后一列的字段b的平均数

WINDOW clause

SELECT a, SUM(b) OVER w FROM T; WINDOW w AS (PARTITION BY c ORDER BY d ROWS UNBOUNDED PRECEDING)

根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 第一列到当前列字段b的和

LEAD using default 1 row lead and not specifying default value

SELECT a, LEAD(a) OVER (PARTITION BY b ORDER BY C ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM T;

根据b分派任务, 每个任务组依据c排序, 选择a和下一条记录的a

LAG specifying a lag of 3 rows and default value of 0

SELECT a, LAG(a, 3, 0) OVER (PARTITION BY b ORDER BY C ROWS 3 PRECEDING) FROM T;

根据b分派任务, 每个任务组依据c排序, 选择a和前面第三条记录的a, 如果超出窗口, 返回0

本文转自：http://yugouai.iteye.com/blog/1908121