- 浏览: 108669 次
- 性别:
- 来自: 深圳
文章分类
最新评论
-
土豆蛋儿:
我想读取一个外部文件,以什么方式好了? 文件内容经常编辑
flume 自定义source -
土豆蛋儿:
大神,您好。
flume 自定义source
HIVE 窗口及分析函数 使用场景
www.MyException.Cn 发布于:2013-07-20 11:07:48 浏览:49次 1
HIVE 窗口及分析函数 应用场景
窗口函数应用场景:
(1)用于分区排序
(2)动态Group By
(3)Top N
(4)累计计算
(5)层次查询
一、分析函数
用于等级、百分点、n分片等。
函数 说明
RANK() 返回数据项在分组中的排名,排名相等会在名次中留下空位
DENSE_RANK() 返回数据项在分组中的排名,排名相等会在名次中不会留下空位
NTILE() 返回n分片后的值
ROW_NUMBER() 为每条记录返回一个数字
Rank、DENSE_RANK
RANK()在出现等级相同的元素时预留为空,DENSE_RANK()不会。
Eg:某产品类型有两个并列第一
RANK():第一二为1,第三位3
DENSE_RANK():第一二为1,第三位2
SELECT
column_name,
RANK() OVER (ORDER BY column_name DESC) AS rank,
DENSE_RANK() OVER (ORDER BY SUM(column_name) DESC) AS dense_rank
FROM table_name
OVER 需要,括号内为编号顺序
over()表示当前查询的结果集对象,括号里面的语句则表示对这个结果集进行处理。
注意:order by 时,desc NULL 值排在首位,ASC时NULL值排在末尾
可以通过NULLS LAST、NULLS FIRST 控制
RANK() OVER (ORDER BY column_name DESC NULLS LAST)
PARTITION BY 分组排列顺序
RANK() OVER(PARTITION BY month ORDER BY column_name DESC)
这样,就会按照month 来分,即所需要排列的信息先以month 的值来分组,在分组中排序,各个分组间不干涉
CUBE,ROLLUP,GROUPING SETS() 详见:HIVE增强的聚合,也可以结合RANK()使用实现具体逻辑。
NTILE
按层次查询,如一年中,统计出工资前1/5之的人员的名单,使用NTILE分析函数,把所有工资分为5份,为1的哪一份就是我们想要的结果:
select empno,ename,sum(sal),ntile(5) over (order by sum(sal) desc nulls last) til from emp group by empno,ename;
ROW_NUMBER
ROW_NUMBER()从1开始,为每条记录返回一个数字
SELECT
ROW_NUMBER() OVER (ORDER BY column_name DESC)AS row_name
FROM table_name;
二、窗口函数
可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。
可以结合聚集函数SUM() 、AVG() 等使用。
可以结合FIRST_VALUE() 和LAST_VALUE(),返回窗口的第一个和最后一个值
(1)计算累计和
eg:统计1-12月的累积销量,即1月为1月份的值,2月为1.2月份值的和,3月为123月份的和,12月为1-12月份值的和
SELECT
month,SUM(amount) month_amount,
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM table_name
GROUP BY month
ORDER BY month;
其中:
SUM( SUM(amount)) 内部的SUM(amount)为需要累加的值,在上述可以换为 month_amount
ORDER BY month 按月份对查询读取的记录进行排序,就是窗口范围内的排序
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点,UNBOUNDED PRECEDING 为起点,表明从第一行开始, CURRENT ROW为默认值,就是这一句等价于:
ROWS UNBOUNDED PRECEDING
PRECEDING:在前 N 行的意思。
FOLLOWING:在后 N 行的意思。
计算前3个月之间的和
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_amount
也可以
SUM( SUM(amount)) OVER (ORDER BY month 3 PRECENDING) AS cumulative_amount
前后一个月之间的和
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cumulative_amount
窗体第一条和最后一条的值
FIRST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;
LAST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;
三、LAG、LEAD
获得相对于当前记录指定距离的那条记录的数据
LAG()为向前、LEAD()为向后
LAG(column_name1,1) OVER(ORDER BY column_name2)
LEAG(column_name1,1) OVER(ORDER BY column_name2)
这样就获得前一条、后一条的数据
四、FIRST、LAST
获得一个排序分组中的第一个值和组后一个值。可以与分组函数结合
SELECT
MIN(month) KEEP(DENSE_RANK FIRST ORDER BY SUM(amount)) AS highest_sales_month,
MIN(month) KEEP(DENSE_RANK LAST ORDER BY SUM(amount)) AS lows_sales_month
FROM table_name
GROUP BY month
ORDER BY month;
这样就可以求得一年中销量最高和最低的月份。
输出的是月份,但是用SUM(amount)来判断。
www.MyException.Cn 发布于:2013-07-20 11:07:48 浏览:49次 1
HIVE 窗口及分析函数 应用场景
窗口函数应用场景:
(1)用于分区排序
(2)动态Group By
(3)Top N
(4)累计计算
(5)层次查询
一、分析函数
用于等级、百分点、n分片等。
函数 说明
RANK() 返回数据项在分组中的排名,排名相等会在名次中留下空位
DENSE_RANK() 返回数据项在分组中的排名,排名相等会在名次中不会留下空位
NTILE() 返回n分片后的值
ROW_NUMBER() 为每条记录返回一个数字
Rank、DENSE_RANK
RANK()在出现等级相同的元素时预留为空,DENSE_RANK()不会。
Eg:某产品类型有两个并列第一
RANK():第一二为1,第三位3
DENSE_RANK():第一二为1,第三位2
SELECT
column_name,
RANK() OVER (ORDER BY column_name DESC) AS rank,
DENSE_RANK() OVER (ORDER BY SUM(column_name) DESC) AS dense_rank
FROM table_name
OVER 需要,括号内为编号顺序
over()表示当前查询的结果集对象,括号里面的语句则表示对这个结果集进行处理。
注意:order by 时,desc NULL 值排在首位,ASC时NULL值排在末尾
可以通过NULLS LAST、NULLS FIRST 控制
RANK() OVER (ORDER BY column_name DESC NULLS LAST)
PARTITION BY 分组排列顺序
RANK() OVER(PARTITION BY month ORDER BY column_name DESC)
这样,就会按照month 来分,即所需要排列的信息先以month 的值来分组,在分组中排序,各个分组间不干涉
CUBE,ROLLUP,GROUPING SETS() 详见:HIVE增强的聚合,也可以结合RANK()使用实现具体逻辑。
NTILE
按层次查询,如一年中,统计出工资前1/5之的人员的名单,使用NTILE分析函数,把所有工资分为5份,为1的哪一份就是我们想要的结果:
select empno,ename,sum(sal),ntile(5) over (order by sum(sal) desc nulls last) til from emp group by empno,ename;
ROW_NUMBER
ROW_NUMBER()从1开始,为每条记录返回一个数字
SELECT
ROW_NUMBER() OVER (ORDER BY column_name DESC)AS row_name
FROM table_name;
二、窗口函数
可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。
可以结合聚集函数SUM() 、AVG() 等使用。
可以结合FIRST_VALUE() 和LAST_VALUE(),返回窗口的第一个和最后一个值
(1)计算累计和
eg:统计1-12月的累积销量,即1月为1月份的值,2月为1.2月份值的和,3月为123月份的和,12月为1-12月份值的和
SELECT
month,SUM(amount) month_amount,
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM table_name
GROUP BY month
ORDER BY month;
其中:
SUM( SUM(amount)) 内部的SUM(amount)为需要累加的值,在上述可以换为 month_amount
ORDER BY month 按月份对查询读取的记录进行排序,就是窗口范围内的排序
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点,UNBOUNDED PRECEDING 为起点,表明从第一行开始, CURRENT ROW为默认值,就是这一句等价于:
ROWS UNBOUNDED PRECEDING
PRECEDING:在前 N 行的意思。
FOLLOWING:在后 N 行的意思。
计算前3个月之间的和
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_amount
也可以
SUM( SUM(amount)) OVER (ORDER BY month 3 PRECENDING) AS cumulative_amount
前后一个月之间的和
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cumulative_amount
窗体第一条和最后一条的值
FIRST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;
LAST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;
三、LAG、LEAD
获得相对于当前记录指定距离的那条记录的数据
LAG()为向前、LEAD()为向后
LAG(column_name1,1) OVER(ORDER BY column_name2)
LEAG(column_name1,1) OVER(ORDER BY column_name2)
这样就获得前一条、后一条的数据
四、FIRST、LAST
获得一个排序分组中的第一个值和组后一个值。可以与分组函数结合
SELECT
MIN(month) KEEP(DENSE_RANK FIRST ORDER BY SUM(amount)) AS highest_sales_month,
MIN(month) KEEP(DENSE_RANK LAST ORDER BY SUM(amount)) AS lows_sales_month
FROM table_name
GROUP BY month
ORDER BY month;
这样就可以求得一年中销量最高和最低的月份。
输出的是月份,但是用SUM(amount)来判断。
发表评论
-
hive + hbase
2015-01-04 10:42 733环境配置: hadoop-2.0.0-cdh4.3.0 (4 ... -
hive 数据倾斜
2014-08-27 09:03 644链接:http://www.alidata.org/archi ... -
hive 分通总结
2014-08-27 08:42 543总结分析: 1. 定义了桶,但要生成桶的数据,只能是由其他表 ... -
深入了解Hive Index具体实现
2014-08-25 08:51 702索引是标准的数据库技术,hive 0.7版本之后支持索引。hi ... -
explain hive index
2014-08-24 16:44 1119设置索引: 使用聚合索引优化groupby操作 hive> ... -
Hive 中内部表与外部表的区别与创建方法
2014-08-15 17:11 725分类: Hive 2013-12-07 11:56 ... -
hive map和reduce的控制
2014-08-15 16:14 597一、 控制hive任务中的map数: 1. 通 ... -
hive 压缩策略
2014-08-15 15:16 1727Hive使用的是Hadoop的文件 ... -
hive 在mysql中创建备用数据库
2014-08-15 09:21 840修改hive-site.xml <property> ... -
hive 内置函数
2014-08-11 09:06 30261.sort_array(): sort_array(arra ... -
hive lateral view
2014-08-09 14:59 1988通过Lateral view可以方便的将UDTF得到的行转列的 ... -
hive数据的导出
2014-07-28 21:53 419在本博客的《Hive几种数据导入方式》文章中,谈到了Hive中 ... -
hive udaf
2014-07-25 16:11 715package com.lwz.udaf; import o ... -
hive自定义InputFormat
2014-07-25 09:13 819自定义分隔符 package com.lwz.inputf; ... -
HiveServer2连接ZooKeeper出现Too many connections问题的解决
2014-07-24 08:49 1691HiveServer2连接ZooKeeper出现Too man ... -
hive 常用命令
2014-07-17 22:22 6391.hive通过外部设置参数传入脚本中: hiv ... -
CouderaHadoop中hive的Hook扩展
2014-07-16 21:18 3261最近在做关于CDH4.3.0的hive封装,其中遇到了很多问题 ... -
利用SemanticAnalyzerHook回过滤不加分区条件的Hive查询
2014-07-16 16:43 1422我们Hadoop集群中将近百分之80的作业是通过Hive来提交 ... -
hive 的常用命令
2014-07-16 10:07 0设置、查看hive当前的角色: set sys ... -
hive 授权
2014-07-15 10:51 898Hive授权(Security配置) 博客分类: Hive分 ...
相关推荐
hive窗口系列函数
数据蛙hive窗口函数 - 精心总结
03.hive窗口分析函数--应用场景--累积报表--用传统方法实现.mp4
通过学习将Oracle和Hive的常用函数整理出来做了个对比,提供他们各自支持的常用函数。
hive常用运算和函数.docx
大数据分析工具 hive 高级分析函数的使用与优化,笔记整理!
HiveSQL基础窗口函数学习笔记
HiveSQL窗口函数.pdf
hive、oracle常用函数对照表
HIVE函数大全,包括目前HIVE可用的所有函数,里面有详细说明。HIVE是基于Hadoop的一个数据仓库工具,用来进行数据提取、转化、加载,这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。
02.hive内置函数--窗口分析函数--row_number_over.mp4
Hive窗口函数的定义,demo等
hive函数大全,hive函数大全,hive函数大全,hive函数大全hive函数大全
大佬总结的hive的各种常用函数语法格式及其用法,Hive内部提供了很多函数给开发者使用,包括数学函数,类型转换函数,条件函数,字符函数,聚合函数,表生成函数等等
hive的udf函数实现
大数据hive中窗口函数的一些常用函数
hive和oracle常用函数对照,包含常用的函数分类 字符函数 数值函数 日期函数 聚合函数 转换函数 其他 增加的hive函数对比,只需要2个积分喔
Hive函数及语法说明;详细中文语法说明;兼容性说明
hive十一类别129个函数介绍
工作中用到了几个hive开窗函数,便想把hive开窗函数系统梳理一遍。 开窗函数 普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值,而开窗函数则可为窗口中的每...