Hive的窗口函数

hive的窗口函数说的其实是over子句的用法而已，over可以实现逐条处理等效果，下面我列举一些实用的开窗函数

根据提供的分组以及排序策略，从第一条记录到当前行，逐行做sum
SUM(xx) OVER(PARTITION BY xx ORDER BY xx) AS xx

从起点到当前行，结果同上，只是加了范围控制 ROWS BETWEEN是控制计算范围的关键字，UNBOUNDED原意是无边际这里做起始行的指定 ，PRECEDING指定从之前第几行起计算所用的关键字 ，AND 范围连接-至 ，CURRENT ROW 当前行
SUM(xx) OVER(PARTITION BY xx ORDER BY xx ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS xx

逐行做sum操作，但是每行只计算前三行到当前行
SUM(xx) OVER(PARTITION BY xx ORDER BY xx ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS xx  

逐行做sum操作，但是每行只计算前三行到当前行的后一行，FOLLOWING指定后几行时所用的关键字
SUM(xx) OVER(PARTITION BY xx ORDER BY xx ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS xx

逐行做sum操作，但是每行只计算当前行以及之后的每一行，UNBOUNDED原意无边界这里做末尾行
SUM(xx) OVER(PARTITION BY xx ORDER BY xx ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS xx 

上面的是over子句的用法，看似少，但大家只要掌握了over子句的用法，那么开窗函数就简单了，而相对的over字句跟随着那个函数其实就不是那么重要了，比如上面的例子中over跟随着sum函数，但是其实完全可以是其他的函数avg也好，rank也好，都是同样的道理

下面介绍一些其他的常用开窗函数，这里注意，我前面说了开窗函数就是over子句的应用，理论上任何函数只要符合逻辑都可以作为开窗函数，所以导致开窗函数其实相当多，多到可能有些都不会用到，因此我下面只列举常用的，而其他比较偏门的就需要大家用的时候自己试试了

ROW_NUMBER() 按照给定的分组策略(不指定时所有数据集算一个组)以及依据字段做排序操作，从结果来说不会有同排序数据，因为凡是同排名的数据，该函数都会按照数据出现的先后继续排，结果为正常的1 2 3 4 5........

RANK() 按照给定的分组策略(不指定时所有数据集算一个组)以及依据字段做排序操作，从结果上讲，会有同排名出现，但重复的排名会占用本该有的排名，导致排名会变成1 1 3 3 5 5 5 8....的样子

DENSE_RANK() 按照给定的分组策略(不指定时所有数据集算一个组)以及依据字段做排序操作，从结果上讲，也会有同排名出现，但重复的排名不会占用本该有的排名，结果会正常顺序显示，如1 1 2 2 2 3 3 4 4 5 6......

NTILE(n) 用户对查询结果从新分为n片，并返回片值

其他的还有很多但是不常用，大家有需要可以找找，我这里找到一个非常不错的博文，和大家分享一下https://blog.youkuaiyun.com/Abysscarry/article/details/81408265

！！最后使用开窗函数，一定要注意是否处于同一窗口对结果的影响，这也就是为什么我上面要介绍RANK、DENSE_RANK这些排序函数的原因，就是当数据按照设定的策略分窗口时，最终进入同一窗口的数据，他们的计算结果是相同的，且往往是这个窗口闭合时的结果，下面给大家举个例子就明白了

比如说，现在需要逐行计算数据的sum值，使用开窗函数则有如下sql

select t1,sum(t2) over(partition by t1 order by t2) as sums from t

按照预期，理想状态下的结果，应该是从起始值累加到最终值，但如果计算数据存在如下格式

t1,t2
(1,2)
(2,1)
(2,2)
(2,2)
(2,3)

按照t1字段分组，t2字段排序，计算sum累加值，那么当t1的值为2，t2的两个为2数据就会进入同一窗口，此时结果就会变成如下的样子

+---+----+
| t1|sums|
+---+----+
|  1|   2|
|  2|   1|
|  2|   5|
|  2|   5|
|  2|   8|
+---+----+

从结果来看就会发现，t2为2的两条记录，他们的累加结果是整个窗口闭合时的结果，因此想要避免这种问题一定要想办法让数据不在一个窗口中，你可以先用ROW_NUMBER排序，计算sum累加时用ROW_NUMBER的结果做排序的条件