HIVE分析函数
(window.slotbydup=window.slotbydup || []).push({
id: '2611110',
container: s,
size: '240,200',
display: 'inlay-fix'
您当前位置： &
[ 所属分类
hive支持的分析函数：总的概括：http://www.codesec.net/os/681.html********************************************************************************************************Rank over的用法：/mycoding/archive//1747065.html原始数据：a b c----------- ----------- ----1 3 E2 4 A3 2 D3 5 B4 2 C2 4 B需求：以a,b进行分组，在每个组内以b进行排名。select *,rank() over( partition by a,b order by b) from xxxx_数据为：a b c rank----------- ----------- ---- --------------------1 3 E 12 4 A 12 4 B 13 2 D 13 5 B 24 2 C 1分了5个组，第2行跟第3行是一个组，其他的每行是一个组。在第2行与第3行的组内以b排名，并列为1***************************************************************************************************************************************Row_Number() over的用法：/fxgachiever/archive//1826792.html原始数据：empid deptid salary1 10 5500.002 10 4500.003 20 1900.004 20 4800.005 40 6500.006 40 14500.007 40 44500.008 50 6500.009 50 7500.00需求：根据部门分组，显示每个部门的工资等级SQL脚本：SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee预期结果：empid deptid salary rank----------- ----------- --------------------------------------- --------------------1 10 2 10 4 20 3 20 7 40 6 40 5 40 9 50 8 50 ********************************************************************************************窗口函数的用法：http://blog.csdn.net/cnham/article/details/6101199select month,sum(tot_sales) month_sales, sum(sum(tot_sales)) over(order by month rows between unbounded preceding and unbounded following) total_sales from orders group by month.
本文数据库（综合）相关术语:系统安全软件
转载请注明本文标题：本站链接：
分享请点击：
1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责；
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性，不作出任何保证或承若；
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
阅读(1517)
一切随心意！且行且珍惜！
手机客户端
，专注代码审计及安全周边编程，转载请注明出处：http://www.codesec.net
转载文章如有侵权，请邮件 admin[at]codesec.net------ &table(1) &数据全集
drop table if exists databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020;
create &table &databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020 as
select a.*,b.city
from databasename.CiiFcst_hotel_Cii_basetable
where d='') a
inner join&
(select distinct masterhotel, city &from databasename.CiiFrcst_Hotel_Info &---有重复的数据
where d='') b on a.hotelid=b.masterhotel
where b.city in (375
--- select hotelid, count(*)
--- from databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020
------ & 各组均为31
------ table (2)
---- distribute by, sort by
drop table if exists databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020_1;
create &table &databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020_1 as
select a.*,&
rank()over(partition by hotelid order by starttime asc) rank & ----- 用row_num()会导致重复，为何？&
---,sum(notcancelcii)over(partition by hotelid) /sum(notcancelcii)over() r1, round(100000*notcancelcii/sum(notcancelcii)over()) cnt
from databasename.Tmp_CiiFcst_hotel_Cii_up_sample_1020
Hive bug: rank()over() 与row_num()over() 与 sum()over()分析函数不能混用，会导致数据重复
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：8857次
排名：千里之外
原创：44篇
(7)(5)(15)(1)(5)(1)(1)(3)(11)同步到微博问题导读：
1.NTILE作用是什么？
2.按照pv降序排列，生成分组内每天的pv名次可使用哪个窗口函数？
3.RANK 和 DENSE_RANK作用是什么？
本文中介绍前几个序列函数，NTILE,ROW_NUMBER,RANK,DENSE_RANK，下面会一一解释各自的用途。
Hive版本为 apache-hive-0.13.1
注意： 序列函数不支持WINDOW子句。（什么是WINDOW子句，）
数据准备：
& & cookie1,,1
& & cookie1,,5
& & cookie1,,7
& & cookie1,,3
& & cookie1,,2
& & cookie1,,4
& & cookie1,,4
& & cookie2,,2
& & cookie2,,3
& & cookie2,,5
& & cookie2,,6
& & cookie2,,3
& & cookie2,,9
& & cookie2,,7
& &&&
& & CREATE EXTERNAL TABLE lxw1234 (
& & cookieid string,
& & createtime string, --day
& & pv INT
& & ) ROW FORMAT DELIMITED
& & FIELDS TERMINATED BY ','
& & stored as textfile location '/tmp/lxw11/';
& &&&
& & DESC lxw1234;
& & cookieid STRING
& & createtime STRING
& & pv INT
& &&&
& & hive& select * from lxw1234;
& & OK
& & cookie1
1
& & cookie1
5
& & cookie1
7
& & cookie1
3
& & cookie1
2
& & cookie1
4
& & cookie1
4
& & cookie2
2
& & cookie2
3
& & cookie2
5
& & cookie2
6
& & cookie2
3
& & cookie2
9
& & cookie2
NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均匀，默认增加第一个切片的分布
& & SELECT
& & cookieid,
& & createtime,
& & pv,
& & NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,& && &&&--分组内将数据分成2片
& & NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2, --分组内将数据分成3片
& & NTILE(4) OVER(ORDER BY createtime) AS rn3 --将所有数据分成4片
& & FROM lxw1234
& & ORDER BY cookieid,
& &&&
& & cookieid day pv rn1 rn2 rn3
& & -------------------------------------------------
& & cookie1
1 1 1 1
& & cookie1
5 1 1 1
& & cookie1
7 1 1 2
& & cookie1
3 1 2 2
& & cookie1
2 2 2 3
& & cookie1
4 2 3 3
& & cookie1
4 2 3 4
& & cookie2
2 1 1 1
& & cookie2
3 1 1 1
& & cookie2
5 1 1 2
& & cookie2
6 1 2 2
& & cookie2
3 2 2 3
& & cookie2
9 2 3 4
& & cookie2
7 2 3 4复制代码–比如，统计一个cookie，pv数最多的前1/3的天
& & SELECT
& & cookieid,
& & createtime,
& & pv,
& & NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn
& & FROM lxw1234;
& &&&
& & --rn = 1 的记录，就是我们想要的结果
& &&&
& & cookieid day pv rn
& & ----------------------------------
& & cookie1
7 1
& & cookie1
5 1
& & cookie1
4 1
& & cookie1
4 2
& & cookie1
3 2
& & cookie1
2 3
& & cookie1
1 3
& & cookie2
9 1
& & cookie2
7 1
& & cookie2
6 1
& & cookie2
5 2
& & cookie2
3 2
& & cookie2
3 3
& & cookie2
2 3复制代码ROW_NUMBER
ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列
–比如，按照pv降序排列，生成分组内每天的pv名次
ROW_NUMBER() 的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。
& & SELECT
& & cookieid,
& & createtime,
& & pv,
& & ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn
& & FROM lxw1234;
& &&&
& & cookieid day pv rn
& & -------------------------------------------
& & cookie1
7 1
& & cookie1
5 2
& & cookie1
4 3
& & cookie1
4 4
& & cookie1
3 5
& & cookie1
2 6
& & cookie1
1 7
& & cookie2
9 1
& & cookie2
7 2
& & cookie2
6 3
& & cookie2
5 4
& & cookie2
3 5
& & cookie2
3 6
& & cookie2
2 7复制代码
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 lxw1234
& & WHERE cookieid = 'cookie1';
& &&&
& & cookieid day pv rn1 rn2 rn3
& & --------------------------------------------------
& & cookie1
7 1 1 1
& & cookie1
5 2 2 2
& & cookie1
4 3 3 3
& & cookie1
4 3 3 4
& & cookie1
3 5 4 5
& & cookie1
2 6 5 6
& & cookie1
1 7 6 7
& &&&
& & rn1: 15号和16号并列第3, 13号排第5
& & rn2: 15号和16号并列第3, 13号排第4
& & rn3: 如果相等，则按记录值排序，生成唯一的次序，如果所有记录值都相等，或许会随机排吧。复制代码
相关内容：
资料来源：
欢迎加入about云群、，云计算爱好者群，亦可关注||
主题帖子积分
Hive分析窗口函数(三) CUME_DIST,PERCENT_RANK
1、CUME_DIST如何使用?
2、PERCENT_RANK作用是什么？
这两个序列分析函数不是很常用，这里也介绍一下。
注意： 序列函数不支持WINDOW子句。（什么是WINDOW子句，）
Hive版本为 apache-hive-0.13.1
数据准备：
& & d1,user1,1000
& & d1,user2,2000
& & d1,user3,3000
& & d2,user4,4000
& & d2,user5,5000
& &&&
& & CREATE EXTERNAL TABLE lxw1234 (
& & dept STRING,
& & userid string,
& & sal INT
& & ) ROW FORMAT DELIMITED
& & FIELDS TERMINATED BY ','
& & stored as textfile location '/tmp/lxw11/';
& &&&
& &&&
& & hive& select * from lxw1234;
& & OK
& & d1 user1 1000
& & d1 user2 2000
& & d1 user3 3000
& & d2 user4 4000
& & d2 user5 5000复制代码CUME_DIST–CUME_DIST 小于等于当前值的行数/分组内总行数
–比如，统计小于等于当前薪水的人数，所占总人数的比例
& & SELECT
& & dept,
& & userid,
& & sal,
& & CUME_DIST() OVER(ORDER BY sal) AS rn1,
& & CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2
& & FROM lxw1234;
& &&&
& & dept userid sal rn1 rn2
& & -------------------------------------------
& & d1 user1
0.3333
& & d1 user2
0.6666
& & d1 user3
1.0
& & d2 user4
0.5
& & d2 user5
1.0
& &&&
& & 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.6666复制代码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 lxw1234;
& &&&
& & dept userid sal rn1 rn11 rn12 rn2
& & ---------------------------------------------------
& & d1 user1
1 5 0.0
& & d1 user2
2 5 0.5
& & d1 user3
3 5 1.0
& & d2 user4
4 5 0.0
& & d2 user5
5 5 1.0
& &&&
& & 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复制代码
资料来源：
欢迎加入about云群、，云计算爱好者群，亦可关注||
站长推荐 /6
about云|新出视频，openstack零基础入门，解决你ping不通外网难题
云计算hadoop视频大全(新增 yarn、flume|storm、hadoop一套视频
视频资料大优惠
大数据零基础由入门到实战
阶段1：hadoop零基础入门基础篇
阶段2：hadoop2入门
阶段3：大数据非hadoop系列课程
阶段4：项目实战篇
阶段5：大数据高级系列应用课程
阶段6：工作实用系列教程
等待验证会员请验证邮箱
新手获取积分方法
Powered by