Hive 窗口函数与分析函数

关注

发布于: 2021 年 05 月 24 日

在 sql 中有一类函数叫做聚合函数,例如 sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。窗口函数又叫 OLAP 函数/分析函数，窗口函数兼具分组和排序功能。

窗口函数最重要的关键字是 partition by 和 order by。

具体语法如下：over (partition by xxx order by xxx)

sum、avg、min、max

准备数据

建表语句:create table itcast_t1(cookieid string,createtime string,   --day pv int) row format delimited fields terminated by ',';
加载数据：load data local inpath '/root/hivedata/itcast_t1.dat' into table itcast_t1;
cookie1,2018-04-10,1cookie1,2018-04-11,5cookie1,2018-04-12,7cookie1,2018-04-13,3cookie1,2018-04-14,2cookie1,2018-04-15,4cookie1,2018-04-16,4
开启智能本地模式SET hive.exec.mode.local.auto=true;

复制代码

SUM 函数和窗口函数的配合使用：结果和 ORDER BY 相关,默认为升序。

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as pv1 from itcast_t1;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2from itcast_t1;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid) as pv3from itcast_t1;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4from itcast_t1;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5from itcast_t1;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6from itcast_t1;

pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号pv2: 同pv1pv3: 分组内(cookie1)所有的pv累加pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号，                         13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，               14号=14号+15号+16号=2+4+4=10

复制代码

如果不指定 rows between,默认为从起点到当前行;

如果不指定 order by，则将分组内所有值累加;

关键是理解 rows between 含义,也叫做 window 子句：

preceding：往前

following：往后

current row：当前行

unbounded：起点

unbounded preceding 表示从前面的起点

unbounded following：表示到后面的终点

AVG，MIN，MAX，和 SUM 用法一样。

row_number、rank、dense_rank、ntile

准备数据

cookie1,2018-04-10,1cookie1,2018-04-11,5cookie1,2018-04-12,7cookie1,2018-04-13,3cookie1,2018-04-14,2cookie1,2018-04-15,4cookie1,2018-04-16,4cookie2,2018-04-10,2cookie2,2018-04-11,3cookie2,2018-04-12,5cookie2,2018-04-13,6cookie2,2018-04-14,3cookie2,2018-04-15,9cookie2,2018-04-16,7 CREATE TABLE itcast_t2 (cookieid string,createtime string,   --day pv INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile;  加载数据：load data local inpath '/root/hivedata/itcast_t2.dat' into table itcast_t2;

复制代码

ROW_NUMBER()使用
ROW_NUMBER()从 1 开始，按照顺序，生成分组内记录的序列。

SELECT cookieid,createtime,pv,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn FROM itcast_t2;

复制代码

RANK 和 DENSE_RANK 使用
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位。
DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。

SELECT cookieid,createtime,pv,RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 FROM itcast_t2 WHERE cookieid = 'cookie1';

复制代码

NTILE
有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE 函数即可以满足。
ntile 可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差 1。
然后可以根据桶号，选取前或后 n 分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

SELECT cookieid,createtime,pv,NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,NTILE(4) OVER(ORDER BY createtime) AS rn3FROM itcast_t2 ORDER BY cookieid,createtime;

复制代码

发布于: 2021 年 05 月 24 日阅读数: 40

原文链接:【http://xie.infoq.cn/article/6476be0d576901c4be0f89de4】。

大数据技术指南

关注

还未添加个人签名 2021.03.07 加入

还未添加个人简介

发布

暂无评论

创作场景

Hive 窗口函数与分析函数

sum、avg、min、max

row_number、rank、dense_rank、ntile

大数据技术指南

评论