hive 窗口函数 应用场景与实战详解

hive中的窗口函数，功能非常强大，使用也比较方便，可以给我们的查询提供相当多的遍历。下面就结合具体的需求与实例，一一为大家讲解窗口函数的使用方法。

1.数据准备

先在hive数据库中建一张表，表的结构如下：

hive (tmp)> desc phone_test;OKcalling_num             stringcalled_num              string

准备测试文件：

vim phone130,131130,131130,131130,131130,131130,131130,132130,132130,133130,133130,134132,130132,130132,130132,130132,130132,131132,131132,131132,133132,133132,133134,135134,135134,135134,135134,136134,136134,136138,137138,137138,137138,136138,136135,130135,130135,130135,130135,132135,132

将文件put到hdfs中hive表对应的位置：

hadoop fs -put phone /data/hive/warehouse/tmp.db/phone_test

至此，数据准备工作完毕。

2.row_num()方法，最有用的窗口函数，或许没有之一

表phone_test的第一列为主叫电话，第二列为被叫电话，我们经常有这种需求：对于同一主叫电话，按通话次数的多少（即被叫电话）进行排序。这个时候row_num()方法就派上了用场。请看：

select a.calling_num,called_num,count,row_number() OVER (distribute BY calling_num sort BY count DESC) rnfrom(select calling_num,called_num,count(*) as countfrom phone_test group by calling_num,called_num)a130 131 6   1130 133 2   2130 132 2   3130 134 1   4132 130 5   1132 133 3   2132 131 3   3134 135 4   1134 136 3   2138 137 3   1138 136 2   2135 130 4   1135 132 2   2

最后一列，就是在同一主叫电话中，被叫电话与主叫电话通话次数的排序。怎么样，很方便吧。
如果要取通话最多的那个，在此基础上稍作改动：

select b.calling_num,called_num,countfrom(select a.calling_num,called_num,count,row_number() OVER (distribute BY calling_num sort BY count DESC) rnfrom(select calling_num,called_num,count(*) as countfrom phone_test group by calling_num,called_num)a)bwhere b.rn = 1130 131 6132 130 5134 135 4138 137 3135 130 4

这样就把每组中rn=1的那一行给选了出来。

换一种写法，还可以这么写：

select b.calling_num,called_num,countfrom(select a.calling_num,called_num,count,row_number() OVER (partition BY calling_num order BY count DESC) rnfrom(select calling_num,called_num,count(*) as countfrom phone_test group by calling_num,called_num)a)bwhere b.rn = 1130 131 6132 130 5134 135 4138 137 3135 130 4

效果与前面是一样一样滴！

3.rank() dense_rank()

rank，顾名思义，就是排序啦。这个比排序更高级一点的是，返回的数据项是在分组中的排名，排名相等的会在名词中留下对应的空位。而dense_rank与rank唯一的不同，就是排名相等的时候不会留下对应的空位。
看例子：

select a.calling_num,called_num,count,rank() over (partition by calling_num order by count desc) rank,dense_rank() over (partition by calling_num order by count desc) drank,row_number() OVER (distribute BY calling_num sort BY count DESC) rnfrom(select calling_num,called_num,count(*) as countfrom phone_test group by calling_num,called_num)a对应结果的列为：calling_num called_num count rank drank rn130 131 6   1   1   1130 133 2   2   2   2130 132 2   2   2   3130 134 1   4   3   4132 130 5   1   1   1132 133 3   2   2   2132 131 3   2   2   3134 135 4   1   1   1134 136 3   2   2   2138 137 3   1   1   1138 136 2   2   2   2135 130 4   1   1   1135 132 2   2   2   2

聪明的你，是不是已经明白这两个函数的用法了？

4.ntile()

ntile是按层次查询。其作用是将数据分成几部分，例如我们想把数据总共分为十份，我们想取前10%来做分析。请看:

select calling_num,called_num,count(*),ntile(5) over(order by count(*) desc) tilfrom phone_test group by calling_num,called_num130 131 6   1132 130 5   1135 130 4   1134 135 4   2138 137 3   2132 133 3   2132 131 3   3134 136 3   3138 136 2   3130 132 2   4135 132 2   4130 133 2   5130 134 1   5

我们先将所有数据分为了5部分。如果只想查看其中某一部分：

select a.calling_num,a.called_num,count,til from(select calling_num,called_num,count(*) count,ntile(5) over(order by count(*) desc) tilfrom phone_test group by calling_num,called_num)awhere til = 1130 131 6   1132 130 5   1135 130 4   1

5.一定范围内的聚合

实际应用场景中，我们有各种数据聚合的要求，而且还比一般的计算要复杂。例如对与电商来说，经常需要看当月与今年之前所有月的累计订单量。对于销售人员来说，也经常需要看当月与今年之前或这个季度之前的累计销售额。对于本文中的例子，我们想计算当前号码的通话总和，以及与之前所有号码通话总和的累计：

select calling_num,sum(num),sum(sum(num)) over(order by calling_num ROWS between unbounded preceding and current row) as cumulative_sumfrom(select calling_num,called_num,count(*) as numfrom phone_test group by calling_num,called_num)agroup by calling_num130 11  11132 11  22134 7   29135 6   35138 5   40

sum(sum(num))这种写法中，里面的sum(num)表示需要累加的和。
重点看下over里面的内容：
order by calling_num表示按主叫电话排序
ROWS between unbounded preceding and current row 肯定就是表示聚合的起始位置与终止位置了。unbounded preceding是起点，表示从第一行开始；current row为默认值，表示到当前行。
以下写法，能达到同样的效果：

select calling_num,sum(num),sum( sum(num)) over(order by calling_num ROWS unbounded preceding) as cumulative_sumfrom(select calling_num,called_num,count(*) as numfrom phone_test group by calling_num,called_num)agroup by calling_num130 11  11132 11  22134 7   29135 6   35138 5   40

如果只想在当前行对前两行聚合，总共计算前两行+当前行=3行的值，可以这样写：

select calling_num,sum(num),sum( sum(num)) over(order by calling_num ROWS between 2 preceding and current row) as cumulative_sumfrom(select calling_num,called_num,count(*) as numfrom phone_test group by calling_num,called_num)agroup by calling_num130 11  11132 11  22134 7   29135 6   24138 5   18

也可以这么写：

select calling_num,sum(num),sum( sum(num)) over(order by calling_num ROWS 2 preceding) as cumulative_sumfrom(select calling_num,called_num,count(*) as numfrom phone_test group by calling_num,called_num)agroup by calling_num130 11  11132 11  22134 7   29135 6   24138 5   18

如果想对之前一行后面一行进行聚合，总共前面一行+当前行+后面一行=3行计算结果，可以这么写：

select calling_num,sum(num),sum(sum(num)) over(order by calling_num ROWS between 1 preceding and 1 following) as cumulative_sumfrom(select calling_num,called_num,count(*) as numfrom phone_test group by calling_num,called_num)agroup by calling_num130 11  22132 11  29134 7   24135 6   18138 5   11