海南澄迈住房和城乡建设厅网站,电影宣传网站开发设计,看怀集app下载,seo网站优化方法Hive窗口函数 文章目录Hive窗口函数语法规则窗口聚合函数窗口表达式窗口排序函数 - ROW NUMBER窗口排序函数 - NTILE窗口分析函数窗口函数也叫开窗函数、OLAP函数其最大特点#xff1a;输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。如果函数具有OVER子句输入值是从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[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;
