全部标签
写点什么
开源云计算行业资讯玩转写作平台算法Python人工智能区块链Linux前端个人成长面试架构师编程企业动态新基建敏捷安全读书笔记高效工作团队管理创业活动专区生涯规划知识管理运维产品经理 查看更多

hive 窗口函数 / 分析函数详细剖析

用户头像
五分钟学大数据
关注
发布于: 2021 年 01 月 21 日
hive窗口函数/分析函数详细剖析

hive 窗口函数/分析函数

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


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


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


sum,avg,min,max 函数


准备数据


建表语句:create table bigdata_t1(cookieid string,createtime string,   --day pv int) row format delimited fields terminated by ',';
加载数据：load data local inpath '/root/hivedata/bigdata_t1.dat' into table bigdata_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 相关,默认为升序。


#pv1select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as pv1 from bigdata_t1;
#pv2select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2from bigdata_t1;
#pv3select cookieid,createtime,pv,sum(pv) over(partition by cookieid) as pv3from bigdata_t1;
#pv4select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4from bigdata_t1;
#pv5select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5from bigdata_t1;
#pv6select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6from bigdata_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 用法一样。


rownumber,rank,denserank,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 bigdata_t2 (cookieid string,createtime string,   --day pv INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile;  加载数据：load data local inpath '/root/hivedata/bigdata_t2.dat' into table bigdata_t2;
复制代码


  • ROW_NUMBER()使用


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


SELECT cookieid,createtime,pv,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn FROM bigdata_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 bigdata_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 bigdata_t2 ORDER BY cookieid,createtime;
复制代码


其他一些窗口函数

lag,lead,first\value,last\value 函数

  • LAG

LAG(col,n,DEFAULT) 用于统计窗口内往上第 n 行值第一个参数为列名，第二个参数为往上第 n 行（可选，默认为 1），第三个参数为默认值（当往上第 n 行为 NULL 时候，取默认值，如不指定，则为 NULL）


  SELECT cookieid,  createtime,  url,  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,  LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time   FROM bigdata_t4;      last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00'                 			 cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00               			 cookie1第三行，往上1行值为第二行值，2015-04-10 10:00:02               			 cookie1第六行，往上1行值为第五行值，2015-04-10 10:50:01  last_2_time: 指定了往上第2行的值，为指定默认值  						 cookie1第一行，往上2行为NULL  						 cookie1第二行，往上2行为NULL  						 cookie1第四行，往上2行为第二行值，2015-04-10 10:00:02  						 cookie1第七行，往上2行为第五行值，2015-04-10 10:50:01
复制代码


  • LEAD


与 LAG 相反

LEAD(col,n,DEFAULT) 用于统计窗口内往下第 n 行值

第一个参数为列名，第二个参数为往下第 n 行（可选，默认为 1），第三个参数为默认值（当往下第 n 行为 NULL 时候，取默认值，如不指定，则为 NULL）


  SELECT cookieid,  createtime,  url,  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,  LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time   FROM bigdata_t4;
复制代码


  • FIRST_VALUE


取分组内排序后，截止到当前行，第一个值


  SELECT cookieid,  createtime,  url,  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1   FROM bigdata_t4;
复制代码


  • LAST_VALUE


取分组内排序后，截止到当前行，最后一个值


  SELECT cookieid,  createtime,  url,  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1   FROM bigdata_t4;
复制代码


如果想要取分组内排序后最后一个值，则需要变通一下：


  SELECT cookieid,  createtime,  url,  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,  FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2   FROM bigdata_t4   ORDER BY cookieid,createtime;
复制代码


特别注意 order by


如果不指定 ORDER BY，则进行排序混乱，会出现错误的结果


  SELECT cookieid,  createtime,  url,  FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2    FROM bigdata_t4;
复制代码


cumedist,percentrank 函数


这两个序列分析函数不是很常用，注意： 序列函数不支持 WINDOW 子句


  • 数据准备


  d1,user1,1000  d1,user2,2000  d1,user3,3000  d2,user4,4000  d2,user5,5000     CREATE EXTERNAL TABLE bigdata_t3 (  dept STRING,  userid string,  sal INT  ) ROW FORMAT DELIMITED   FIELDS TERMINATED BY ','   stored as textfile;    加载数据：  load data local inpath '/root/hivedata/bigdata_t3.dat' into table bigdata_t3;
复制代码


  • CUME_DIST 和 order by 的排序顺序有关系


CUME_DIST 小于等于当前值的行数/分组内总行数 order 默认顺序 正序 升序

比如，统计小于等于当前薪水的人数，所占总人数的比例


  SELECT   dept,  userid,  sal,  CUME_DIST() OVER(ORDER BY sal) AS rn1,  CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2   FROM bigdata_t3;    rn1: 没有partition,所有数据均为1组，总行数为5，       第一行：小于等于1000的行数为1，因此，1/5=0.2       第三行：小于等于3000的行数为3，因此，3/5=0.6  rn2: 按照部门分组，dpet=d1的行数为3,       第二行：小于等于2000的行数为2，因此，2/3=0.6666666666666666
复制代码


  • PERCENT_RANK


PERCENT_RANK 分组内当前行的 RANK 值-1/分组内总行数-1


  SELECT   dept,  userid,  sal,  PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   --分组内  RANK() OVER(ORDER BY sal) AS rn11,          --分组内RANK值  SUM(1) OVER(PARTITION BY NULL) AS rn12,     --分组内总行数  PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2   FROM bigdata_t3;    rn1: rn1 = (rn11-1) / (rn12-1)   	   第一行,(1-1)/(5-1)=0/4=0  	   第二行,(2-1)/(5-1)=1/4=0.25  	   第四行,(4-1)/(5-1)=3/4=0.75  rn2: 按照dept分组，       dept=d1的总行数为3       第一行，(1-1)/(3-1)=0       第三行，(3-1)/(3-1)=1
复制代码


grouping sets,grouping__id,cube,rollup 函数


这几个分析函数通常用于 OLAP 中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的 UV 数。


  • 数据准备


  2018-03,2018-03-10,cookie1  2018-03,2018-03-10,cookie5  2018-03,2018-03-12,cookie7  2018-04,2018-04-12,cookie3  2018-04,2018-04-13,cookie2  2018-04,2018-04-13,cookie4  2018-04,2018-04-16,cookie4  2018-03,2018-03-10,cookie2  2018-03,2018-03-10,cookie3  2018-04,2018-04-12,cookie5  2018-04,2018-04-13,cookie6  2018-04,2018-04-15,cookie3  2018-04,2018-04-15,cookie2  2018-04,2018-04-16,cookie1     CREATE TABLE bigdata_t5 (  month STRING,  day STRING,   cookieid STRING   ) ROW FORMAT DELIMITED   FIELDS TERMINATED BY ','   stored as textfile;    加载数据：  load data local inpath '/root/hivedata/bigdata_t5.dat' into table bigdata_t5;
复制代码


  • GROUPING SETS


grouping sets 是一种将多个 group by 逻辑写在一个 sql 语句中的便利写法。


等价于将不同维度的 GROUP BY 结果集进行 UNION ALL。


GROUPING__ID，表示结果属于哪一个分组集合。


  SELECT   month,  day,  COUNT(DISTINCT cookieid) AS uv,  GROUPING__ID   FROM bigdata_t5   GROUP BY month,day   GROUPING SETS (month,day)   ORDER BY GROUPING__ID;    grouping_id表示这一组结果属于哪个分组集合，  根据grouping sets中的分组条件month，day，1是代表month，2是代表day    等价于   SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month UNION ALL   SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day;
复制代码


再如：


  SELECT   month,  day,  COUNT(DISTINCT cookieid) AS uv,  GROUPING__ID   FROM bigdata_t5   GROUP BY month,day   GROUPING SETS (month,day,(month,day))   ORDER BY GROUPING__ID;    等价于  SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month   UNION ALL   SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day  UNION ALL   SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;
复制代码


  • CUBE


根据 GROUP BY 的维度的所有组合进行聚合。


  SELECT   month,  day,  COUNT(DISTINCT cookieid) AS uv,  GROUPING__ID   FROM bigdata_t5   GROUP BY month,day   WITH CUBE   ORDER BY GROUPING__ID;    等价于  SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM bigdata_t5  UNION ALL   SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month   UNION ALL   SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day  UNION ALL   SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;
复制代码


  • ROLLUP


是 CUBE 的子集，以最左侧的维度为主，从该维度进行层级聚合。


  比如，以month维度进行层级聚合：  SELECT   month,  day,  COUNT(DISTINCT cookieid) AS uv,  GROUPING__ID    FROM bigdata_t5   GROUP BY month,day  WITH ROLLUP   ORDER BY GROUPING__ID;    --把month和day调换顺序，则以day维度进行层级聚合：     SELECT   day,  month,  COUNT(DISTINCT cookieid) AS uv,  GROUPING__ID    FROM bigdata_t5   GROUP BY day,month   WITH ROLLUP   ORDER BY GROUPING__ID;  （这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）
复制代码

搜索公众号：五分钟学大数据，获取大数据学习秘籍，你的大数据能力将实现质的飞跃


划线
评论
复制
发布于: 2021 年 01 月 21 日阅读数: 22

版权声明: 本文为 InfoQ 作者【五分钟学大数据】的原创文章。

原文链接:【http://xie.infoq.cn/article/358051cf1f6681562da58d8f3】。

本文遵守【CC-BY 4.0】协议，转载请保留原文出处及本版权声明。

Hive SQL
用户头像

五分钟学大数据

关注

公众号：五分钟学大数据 2020.11.10 加入

大数据领域原创技术号，专注于大数据技术

评论

发布
暂无评论
Copyright © 2021, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号
hive窗口函数/分析函数详细剖析