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;
(这里,根据天和月进行聚合,和根据天聚合结果一样,因为有父子关系,如果是其他维度组合的话,就会不一样)

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

hive窗口函数/分析函数详细剖析的更多相关文章

  1. [Hive_10] Hive 的分析函数

    0. 说明 Hive 的分析函数 窗口函数  | 排名函数 | 最大值 | 分层次 | lead && lag 统计活跃用户 | cume_dist 1. 窗口函数(开窗函数) ove ...

  2. linux动态库编译和使用详细剖析 - 后续

    引言 - 也许是修行 很久以前写过关于动态库科普文章, 废话反正是说了好多. 核心就是在 linux 上面玩了一下 dlopen : ) linux动态库编译和使用详细剖析 - https://www ...

  3. Hive 窗口函数sum() over()求当前行和前面n条数据的和

    前几天遇到一个这样的需求:销售总占比加起来超过75%的top分类.具体需求是这样的:商品一级分类标签下面有许多商品标签,例如运动户外一级标签,下面可能存在361°,CBA,Nike,Adidas... ...

  4. Hive 窗口函数、分析函数

    1 分析函数:用于等级.百分点.n分片等 Ntile 是Hive很强大的一个分析函数. 可以看成是:它把有序的数据集合 平均分配 到 指定的数量(num)个桶中, 将桶号分配给每一行.如果不能平均分配 ...

  5. Hive 窗口分析函数

    1.窗口函数 1.LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值 ...

  6. Hive 窗口函数LEAD LAG FIRST_VALUE LAST_VALUE

    窗口函数(window functions)对多行进行操作,并为查询中的每一行返回一个值. OVER()子句能将窗口函数与其他分析函数(analytical functions)和报告函数(repor ...

  7. hive中分析函数window子句

    hive中有些分析函数功能确实很强大,在和sum,max等聚合函数结合起来能实现不少功能. 直接上代码演示吧 原始数据 channel1 2016-11-10 1 channel1 2016-11-1 ...

  8. linux动态库编译和使用详细剖析

    引言 重点讲述linux上使用gcc编译动态库的一些操作.并且对其深入的案例分析.最后介绍一下动态库插件技术, 让代码向后兼容.关于linux上使用gcc基础编译, 预编译,编译,生成机械码最后链接输 ...

  9. Hadoop Hive sql 语法详细解释

    Hive 是基于Hadoop 构建的一套数据仓库分析系统.它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据,能够将结构 化的数据文件映射为一张数据库表,并提供完整的SQL查 ...

  10. hive参数配置详细

    hive.exec.mode.local.auto 决定 Hive 是否应该自动地根据输入文件大小,在本地运行(在GateWay运行) true hive.exec.mode.local.auto.i ...

随机推荐

  1. 【转】linux内核中writesb(), writesw(), writesl() 宏函数

    writesb(), writesw(), writesl() 宏函数 功能 : writesb()    I/O 上写入 8 位数据流数据 (1字节) writesw()   I/O  上写入 16 ...

  2. 常用API——日期型函数Date

    上图 ·声明 var myDate = new Date(); //系统当前时间 var myDate = new Date(yyyy, mm, dd, hh, mm, ss); var myDate ...

  3. json loads No JSON object could be decoded 问题解决

    今天自己用json的 dumps生成的 json 文本: f2=open(filename,'w')f2.write(json.dumps(c) f=open(filename,'r')r= f.re ...

  4. AD域修改组策略

    如果我们的计算机加入AD域之后,修改安全策略时不能用本地策略来修改.具体修改方法: .Start(开始)–Programs(程序)–Administrative Tools(管理工具)–Group P ...

  5. Keepalived高可用集群介绍

    1.Keepalived服务介绍 Keepalived起初是专为LVS设计的,专门用来监控LVS集群系统中各个服务节点的状态,后来又加入了VRRP的功能,因此除了配合LVS服务外,也可以为其他服务(n ...

  6. Nuget程序包 使用log4net

    Nuget程序包不用细介绍,网上资源很多,有个项目使用了log4net,为项目打log,功能很多,足够一般使用. 使用时候需要在配置文件中对其进行相关配置,我自己的配置文件放在App.config文件 ...

  7. hibernate_validator_03

    约束继承 如果要验证的对象继承于某个父类或者实现了某个接口,那么定义在父类或者接口中的约束会在验证这个对象的时候被自动加载,如同这些约束定义在这个对象所在的类中一样. 让我们来看看下面的示例: pac ...

  8. 10_9 java笔记

    java中所有的关键字都是小写的注意main虽然被编译器识别,但是它并不是关键字包:(名字小写) 单级包:liyi 多级包:cn.itcast path 和classpath的区别:path环境变量里 ...

  9. drupal中使用drush命令,快速批量的开启和关闭模块

    方法一: drush pml --no-core --type=module --status=enabled --pipe > modules.txt  xargs -a modules.tx ...

  10. 机器学习实战笔记(1)——k-近邻算法

    机器学习实战笔记(1) 1. 写在前面 近来感觉机器学习,深度学习神马的是越来越火了,从AlphaGo到Master,所谓的人工智能越来越NB,而我又是一个热爱新潮事物的人,于是也来凑个热闹学习学习. ...