Hive窗口函数
- 窗口函数也叫开窗函数、OLAP函数
- 其最大特点:输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。
- 如果函数具有OVER子句,则它是窗口函数。
- 窗口函数可以简单地解释为类似于聚合函数的计算函数,但通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行,最终输出一行;窗口函数聚合后还可以访问当中的各个行,并且可以将这些行中的某些属性添加到结果集。
语法规则
- Function(arg1,…,argn)可以是:
- 聚合函数:比如sum max avg等
- 排序函数:比如rank row_number等
- 分析函数:比如lead lag first_value等
- OVER [PARTITION BY <…>] 类似于group by,用于指定分组,每个分组都可以看成窗口。如果没有指定PARTITION BY则所有行为一组。
- [ORDER BY <…>]类似于group by,用于指定每个分组内数据排序的规则。支持ASC、DESC
- 若order by同时用sum()求和,则每行会返回积累求和的结果。
- [<window_expression>] 用于指定每个窗口中操作的数据范围,默认是窗口中所有行。
Function(arg1,...,argn) over ([PARTITION BY <...>] [ORDER BY<...>] [<window_expression>])
窗口聚合函数
- 即sum、max、min、avg这样的聚合函数在窗口中的使用。
- 以sum为例:
- 常规聚合操作:
SELECT cookieid,SUM(pv) AS total_pv FROM website_pv_into GROUP BY cookieid;
- 求出网站总的pv数,即所有用户的所有访问求和
SELECT cookieid,createtime,pv,SUM(pc)OVER() AS total_pv
FROM website_pv_info;
- 分别求每个用户pv数
SELECT cookieid,createtime,pv,SUM(pv) OVER(PARTITION BY cookieid) AS total_pv
FROM website_pv_info;
- 求每个用户截至当天,积累的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,包括下面几个选项:
- preceding:往前
- following:往后
- current row:当前行
- unbounded:边界
- unbounded preceding:表示前面的起点
- unbounded following:表示后面的终点
- 例:
- 第一行到当前行:
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;
- 向前三行至当前行:
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;
- 向前三行至向后一行:
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;