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

Gulp4.0入门和实战

hive窗口函数/分析函数

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

窗口函数最重要的关键字是 partition byorder 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,1
cookie1,2018-04-11,5
cookie1,2018-04-12,7
cookie1,2018-04-13,3
cookie1,2018-04-14,2
cookie1,2018-04-15,4
cookie1,2018-04-16,4

开启智能本地模式
SET hive.exec.mode.local.auto=true;

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

#pv1
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1 
from bigdata_t1;

#pv2
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from bigdata_t1;

#pv3
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as pv3
from bigdata_t1;

#pv4
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from bigdata_t1;

#pv5
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from bigdata_t1;

#pv6
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from bigdata_t1;


pv1: 分组内从起点到当前行的pv累积,如,11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
pv2: 同pv1
pv3: 分组内(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=21
pv6: 分组内当前行+往后所有行,如,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,1
cookie1,2018-04-11,5
cookie1,2018-04-12,7
cookie1,2018-04-13,3
cookie1,2018-04-14,2
cookie1,2018-04-15,4
cookie1,2018-04-16,4
cookie2,2018-04-10,2
cookie2,2018-04-11,3
cookie2,2018-04-12,5
cookie2,2018-04-13,6
cookie2,2018-04-14,3
cookie2,2018-04-15,9
cookie2,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 rn3
FROM 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;

cume_dist,percent_rank 函数

这两个序列分析函数不是很常用,注意: 序列函数不支持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;
  (这里,根据天和月进行聚合,和根据天聚合结果一样,因为有父子关系,如果是其他维度组合的话,就会不一样)

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

Redis 实战 —— 02. Redis 简单实践 – 文章投票

给TA买糖
共{{data.count}}人
人已赞赏
经验教程

day128:MySQL进阶:MySQL安装&用户/权限/连接/配置管理&MySQL的体系结构&SQL&MySQL索引和执行计划

2021-1-21 21:29:00

经验教程

Gulp4.0入门和实战

2021-1-21 21:53:00

⚠️
免责声明:根据《计算机软件保护条例》第十七条规定“为了学习和研究软件内含的设计思想和原理,通过安装、显示、传输或者存储软件等方式使用软件的,可以不经软件著作权人许可,不向其支付报酬。”您需知晓本站所有内容资源均来源于网络,仅供用户交流学习与研究使用,版权归属原版权方所有,版权争议与本站无关,用户本人下载后不能用作商业或非法用途,需在24个小时之内从您的电脑中彻底删除上述内容,否则后果均由用户承担责任;如果您访问和下载此文件,表示您同意只将此文件用于参考、学习而非其他用途,否则一切后果请您自行承担,如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。 本站为个人博客非盈利性站点,所有软件信息均来自网络,所有资源仅供学习参考研究目的,并不贩卖软件,不存在任何商业目的及用途,网站会员捐赠是您喜欢本站而产生的赞助支持行为,仅为维持服务器的开支与维护,全凭自愿无任何强求。本站部份代码及教程来源于互联网,仅供网友学习交流,若您喜欢本文可附上原文链接随意转载。
无意侵害您的权益,请发送邮件至 momeis6@qq.com 或点击右侧 私信:momeis 反馈,我们将尽快处理。
0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
今日签到
有新私信 私信列表
搜索