Hive中window函数

Window函数就是在某个区间内做聚合操作

传统的关系型数据库中我们对SQL的聚合函数 Group By 都很熟悉，比如算一下销售部门这个月销售人员业绩的排行，一般来说一句SQL就可以搞定。对于Window函数来讲，相比聚合函数要难于理解一些，因为他把条件写到select中，其实通过几个例子理解一下并不复杂。简单的说，就是将查询结果进行分区，之后为对应的分区进行聚合，这里有个和聚合不一样的点Window函数并不会把相同的结果合并成一条记录。下面通过HiveQL来看一下Window函数和其对应的参数的作用，希望对大家有所启发。

Window函数是通过Over函数进行调用的，对数据的每一行进行分组聚合。

OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] )

Window函数支持以下功能：

Window 聚合函数
SUM(), MAX(), MIN(), AVG(). COUNT()，这些函数都是SQL标准聚合函数，定义都是一致的，这里不再多说。
Window 排行函数
RANK()：排行函数，如果数值相同就返回的同样的值，原先的值被废弃掉，比如结果有4行，2，3行的值是一样的，那么3就会被去掉，结果就是1，2，2，4。可以参考后面的例子理解一下。
DENSE_RANK()：紧凑的排行函数，我们继续拿Rank的例子来说，跟Rank不通，这次返回的是 1，2，2，3，中间的3不会被去掉，这也是紧凑这个词要表达的意思。
ROW_NUMBER()：按顺序显示行号
NTILE()：将结果集分组，结果机总数/NTILE
Window 值函数
值函数比较强大，以前我们要去取结果集中其他行的记录是比较复杂的，解决办法大多是Join或者自查询之类的，现在有了值函数还是比较方便的。
LAG()：取之前行的值
LEAD()：取之后的行的值
FIRST_VALUE()：取第一行的值
LAST_VALUE()：取第一行的值

下面我们来看一些例子，感受一下Window函数的用法

• 标准聚合函数

SELECT city, SUM(order_amount) total_order_amount
FROM orders GROUP BY city

• Window 聚合函数

SELECT order_id, order_date, customer_name, city, order_amount
 ,SUM(order_amount) OVER(PARTITION BY city) as grand_total 
FROM orders

• avg 函数

SELECT order_id, order_date, customer_name, city, order_amount
 ,AVG(order_amount) OVER(PARTITION BY city, MONTH(order_date)) as   average_order_amount 
FROM orders

• Rank函数
  Rank会舍掉中间重复的值，下面的例子4、6背去掉了。

SELECT order_id,order_date,customer_name,city, order_amount,
RANK() OVER(ORDER BY order_amount DESC)  as rank
FROM orders

• DENSE_RANK函数
  和Rank的结果比较可以看到明显的区别，相同值的排序号没有被跳过

SELECT order_id,order_date,customer_name,city, order_amount,
DENSE_RANK() OVER(ORDER BY order_amount DESC)  as rank
FROM orders

• ROW_NUMBER函数
  对相同值不做处理

SELECT order_id,order_date,customer_name,city, order_amount,
ROW_NUMBER() OVER(ORDER BY order_amount DESC)  as rank
FROM orders

• NTILE函数
  分组函数，11条记录分4组，做了4舍5入，最后剩下的为一组

SELECT order_id,order_date,customer_name,city, order_amount,
NTILE(4) OVER(ORDER BY order_amount DESC)  as rank
FROM orders

• LAG函数
  向前取一条

SELECT order_id,customer_name,city, order_amount,order_date,
 LAG(order_date,1) OVER(ORDER BY order_date) prev_order_date
FROM orders

• LEAD函数
  向后取一条

SELECT order_id,customer_name,city, order_amount,order_date,
LEAD(order_date,1) OVER(ORDER BY order_date) prev_order_date
FROM orders

• FIRST_VALUE函数和LAST_VALUE函数
  分别显示分组之后的第一条和最后一条记录

SELECT order_id,customer_name,city, order_amount,order_date,
FIRST_VALUE(order_date) OVER(PARTITION BY city ORDER BY city) f_order_date,
LAST_VALUE(order_date) OVER(PARTITION BY city ORDER BY city) l_order_date
FROM orders