文章目录
一、简介
二、概念
三、数据准备
四、聚合函数+over()
五、partition by 子句
六、order by 子句
七、window 子句
八、窗口函数中的序列函数
ntile
row_number
rank
dense_rank
lag 和 lead
first_value 和 last_value
一、简介
本文主要介绍Hive中的窗口函数，Hive中的窗口函数和SQL中的窗口函数相类似,都是用来做一些数据分析类的工作，一般用于olap分析（在线分析处理）。
二、概念
我们都知道在sql中有一类函数叫做聚合函数，例如sum()、avg()、max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的，但是有时我们想要既显示聚集前的数据，又要显示聚集后的数据，这时我们便引入了窗口函数。
在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。
三、数据准备
我们准备一张order表，字段分别为name，orderdate，cost，数据内容如下：
jack 2015-04-03 23 jack 2015-01-01 10 tony 2015-01-02 15 jack 2015-02-03 23 tony 2015-01-04 29 jack 2015-01-05 46 jack 2015-04-06 42 tony 2015-01-07 50 jack 2015-01-08 55 mart 2015-04-08 62 mart 2015-04-09 68 neil 2015-05-10 12 mart 2015-04-11 75 neil 2015-06-12 80 mart 2015-04-13 94
在hive中建立一张表order,将数据插入进去。
四、聚合函数+over()
假如说我们想要查询在2015年4月份购买过的顾客及总人数，我们便可以使用窗口函数去去实现。
select name,count(*) over() from order where substring(orderdate,1,7)='2015-04';
得到结果如下：
mart 6 mart 6 mart 6 mart 6 jack 6 jack 6
可见其实在2015年4月一共有5次购买记录，mart购买了4次，jack购买了2次。事实上，大多数情况下，我们是只看去重后的结果的。针对于这种情况，我们想对代码改进，进行去重，该怎么办呢 😉
第一种方式： group by
select j.name,j.e from (select name,count(*) over() as e from order where substring(orderdate,1,7)='2015-04') j group by j.name,j.e;
结果如下：
jack 6 mart 6
第二种方式：distinct
select distinct name,count(*) over() from order where substring(orderdate,1,7)='2015-04';
结果如下：
jack 2 mart 2
五、partition by 子句
Over子句之后第一个提到的就是Partition By。Partition By子句也可以称为查询分区子句，非常类似于Group By，都是将数据按照边界值分组，而Over之前的函数在每一个分组之内进行，如果超出了分组，则函数会重新计算。
实例
我们想要去看顾客的购买明细及月购买总额，可以执行如下的sql。
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from order
结果如下：
tony 2015-01-07 50 205 jack 2015-01-01 10 205 jack 2015-01-05 46 205 tony 2015-01-04 29 205 tony 2015-01-02 15 205 jack 2015-01-08 55 205 jack 2015-02-03 23 23 mart 2015-04-13 94 364 mart 2015-04-11 75 364 mart 2015-04-09 68 364 mart 2015-04-08 62 364 jack 2015-04-06 42 364 jack 2015-04-03 23 364 neil 2015-05-10 12 12 neil 2015-06-12 80 80
这里我们可以看到数据已经完全按照月份进行聚合。
六、order by 子句
上述的场景，假如我们想要将cost按照月进行累加，这时我们引入order by子句。
order by子句会让输入的数据强制排序（窗口函数是SQL语句最后执行的函数，因此可以把SQL结果集想象成输入数据）。Order By子句对于诸如Row_Number()，Lead()，LAG()等函数是必须的，因为如果数据无序，这些函数的结果就没有任何意义。因此如果有了Order By子句，则Count()，Min()等计算出来的结果就没有任何意义。
我们在上面的代码中加入order by
select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate) from order;
结果如下：
jack 2015-01-01 10 10 // 10 tony 2015-01-02 15 25 // 10+15 tony 2015-01-04 29 54 // 25+29 jack 2015-01-05 46 100 tony 2015-01-07 50 150 jack 2015-01-08 55 205 jack 2015-02-03 23 23 jack 2015-04-03 23 23 jack 2015-04-06 42 65 mart 2015-04-08 62 127 mart 2015-04-09 68 195 mart 2015-04-11 75 270 mart 2015-04-13 94 364 neil 2015-05-10 12 12 neil 2015-06-12 80 80
