Hive窗口函数语法规则、窗口聚合函数、窗口表达式、窗口排序函数 - ROW NUMBER 、口排序函数 - NTILE、窗口分析函数

Hive窗口函数

• 窗口函数也叫开窗函数、OLAP函数
• 其最大特点：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。
• 如果函数具有OVER子句，则它是窗口函数。
• 窗口函数可以简单地解释为类似于聚合函数的计算函数，但通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行；窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集。

语法规则

• Function(arg1,…,argn)可以是：
  1. 聚合函数：比如sum max avg等
  2. 排序函数：比如rank row_number等
  3. 分析函数：比如lead lag first_value等
• OVER [PARTITION BY <…>] 类似于group by，用于指定分组，每个分组都可以看成窗口。如果没有指定PARTITION BY则所有行为一组。
• [ORDER BY <…>]类似于group by，用于指定每个分组内数据排序的规则，并在每个分组内累积求和。支持ASC、DESC
• [<window_expression>] 用于指定每个窗口中操作的数据范围，默认是窗口中所有行。

Function(arg1,...,argn) over ([PARTITION BY <...>] [ORDER BY<...>] [<window_expression>])

窗口聚合函数

• 即sum、max、min、avg这样的聚合函数在窗口中的使用。
• 以sum为例：

1. 常规聚合操作：

SELECT cookieid,SUM(pv) AS total_pv FROM website_pv_into GROUP BY cookieid;

2. 求出网站总的pv数，即所有用户的所有访问求和

SELECT cookieid,createtime,pv,SUM(pc)OVER() AS total_pv
FROM website_pv_info;

3. 分别求每个用户pv数

SELECT cookieid,createtime,pv,SUM(pv) OVER(PARTITION BY cookieid) AS total_pv
FROM website_pv_info;

4. 求每个用户截至当天，积累的pv数（order by在每个分组内累积求和）

SELECT cookieid,createtime,pv,SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS total_pv
FROM website_pv_info;

窗口表达式

• 在sum(…) over(partition by … order by …)语法完整的情况下，进行累积聚合操作的默认聚合行为是从第一行聚合到当前行。
• Window expression窗口表达式给我们提供了一种控制行范围的能力，如向前两行，向后三行。
• 关键字是ROWS BETWEEN，包括下面几个选项：
  1. preceding：往前
  2. following：往后
  3. current row：当前行
  4. unbounded：边界
  5. unbounded preceding：表示前面的起点
  6. unbounded following：表示后面的终点
• 例：

1. 第一行到当前行：

SELECT cookieid,createtime,pv,SUM(pv) 
OVER
(
PARTITION BY cookieid 
ORDER BY createtime 
ROW BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS total_pv
FROM website_pv_info;

2. 向前三行至当前行：

SELECT cookieid,createtime,pv,SUM(pv) 
OVER
(
PARTITION BY cookieid 
ORDER BY createtime 
ROW BETWEEN 3 PRECEDING AND CURRENT ROW
) AS total_pv
FROM website_pv_info;

3. 向前三行至向后一行：

SELECT cookieid,createtime,pv,SUM(pv) 
OVER
(
PARTITION BY cookieid 
ORDER BY createtime 
ROW BETWEEN 3 PRECEDING AND 1 FOLLOWING
) AS total_pv
FROM website_pv_info;

窗口排序函数 - ROW NUMBER

• row_number：在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑数据的重复；
• rank：在每个分组中，为每行分配一个从1开始的序列号，考虑数据的重复，挤占后续位置；
• dense_rank：在每个分组中，为每行分配一个从1开始的序列号，考虑数据的重复，不挤占后续位置；
• 适合Top N业务分析。

SELECT cookieid,createtime,pv,SUM(pv),RANK()
OVER
(
PARTITION BY cookieid 
ORDER BY createtime 
ROW BETWEEN 3 PRECEDING AND 1 FOLLOWING
) AS rank
FROM website_pv_info;

窗口排序函数 - NTILE

• 将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每个桶分配一个桶编号。
• 如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1.
• NTILE可以取出指定的一部分数据。

SELECT cookieid,createtime,pv,NTILE(3)
OVER
(
PARTITION BY cookieid 
ORDER BY createtime 
) AS rank
FROM website_pv_info
ORDER BY cookieid,createtime;

窗口分析函数

• LAG(col,n,DEFAULT)：用于统计窗口内往上第n行值
  第一个参数为列名；第二个参数为往上第n行；第三个参数为默认值，即当往上第n行为NULL时，取默认值
• LEAD(col,n,DEFAULT)：用于统计窗口内往下第n行的值
  第一个参数为列名；第二个参数为往下第n行；第三个参数为默认值，即当往下第n行为NULL时，取默认值
• FIRST_VALUE：取分组内排序后，截至至当行，第一个值
• LAST_VALUE：取分组内排序后，截至至当行，最后一个值
• 例：

SELECT cookieid,createtime,pv,LAG(createtime,1,'1970-01-01')
OVER
(
PARTITION BY cookieid 
ORDER BY createtime 
) AS last_time
FROM website_pv_info
ORDER BY cookieid,createtime;