AI 助理
备案控制台
开发者社区 数据库 文章 正文

[Hive]分析函数 RANK ROW_NUMBER CUME_DIST CUME_DIST

2017-02-22 1670
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/SunnyYoona/article/details/56488568 1. 常用分析函数下表列出了一些分析函数以及描述信息：分析函数描述RANK返回数据项在分区中的排名。
版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/SunnyYoona/article/details/56488568

1. 常用分析函数

下表列出了一些分析函数以及描述信息：

分析函数 描述
RANK 返回数据项在分区中的排名。排名值序列可能会有间隔
DENSE_RANK 返回数据项在分区中的排名。排名值序列是连续的，不会有间隔
PERCENT_RANK 计算当前行的百分比排名
ROW_NUMBER 确定分区中当前行的序号
CUME_DIST 计算分区中当前行的相对排名
NTILE() 将每个分区的行尽可能均匀地划分为指定数量的分组

2. 语法

window_function () OVER clause

3. 分析函数

3.1 RANK()

RANK函数会返回数据项在分区中的排名。OVER子句中的ORDER BY语句来确定根据哪个值进行排名。如果多行中的排序值相同，则会有相同的排名。如果有排名相同的情况下，则会在名次中留下空位（DENSE_RANK()不会出现这种情况，具体可以对比一下）。例如，如果两行排名为3，则下一个排名为5。

下面对用户每天浏览量进行一个排名。

select gid, dt, pv, rank() over (partition by gid order by pv desc) as pre_total_pv from tmp_pv order by gid, dt;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	7
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	2
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	5
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	6
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	3
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	3
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	5
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	4
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	1
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	7
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	3
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	5
Time taken: 24.065 seconds, Fetched: 14 row(s)

3.2 DENSE_RANK()

DENSE_RANK函数，同上面的RANK()函数一样，返回数据项在分区中的排名。OVER子句中的ORDER BY语句来确定根据哪个值进行排名。具有相等值的行，有相同的排名。不同于RANK()函数的地方在于如果两个或更多行具有相同的排名，在排名值的序列中没有间隙。例如，如果两行排名为3，则下一个排名为4，不同于RANK()函数返回5。

下面对用户每天浏览量进行一个排名：

hive> select gid, dt, pv, dense_rank() over (partition by gid order by pv desc) from tmp_pv order by gid, dt;
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	6
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	2
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	5
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	3
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	3
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	5
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	4
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	1
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	6
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	3
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	5
Time taken: 24.18 seconds, Fetched: 14 row(s)

3.3 PERCENT_RANK()

PERCENT_RANK函数使用以下公式计算当前行的百分比排名：

（x - 1）/（窗口分区中的行数 - 1）

其中x是当前行的排名

hive> select gid, dt, pv, percent_rank() over (partition by gid order by pv) from tmp_pv order by gid, dt;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	0.0
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	0.8333333333333334
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	1.0
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	0.3333333333333333
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	0.16666666666666666
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	0.5
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	0.5
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	0.16666666666666666
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	0.8333333333333334
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	0.5
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	1.0
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	0.0
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	0.6666666666666666
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	0.16666666666666666
Time taken: 25.236 seconds, Fetched: 14 row(s)

3.4 ROW_NUMBER()

ROW_NUMBER函数确定分区中当前行的序号。当前行的序号是由OVER子句中的ORDER BY表达式来确定的。

hive> select gid, dt, pv, row_number() over (partition by gid order by pv desc) from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	2
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	3
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	5
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	6
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	7
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	1
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	3
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	4
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	5
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	6
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	7
Time taken: 8.235 seconds, Fetched: 14 row(s)

3.5 CUME_DIST()

CUME_DIST函数计算分区中当前行的相对排名：

（前面的行数）/（分区中的总行数）

如果有相等值的行（取决于OVER子句中的order by）：

（前面的行数+相等值行数）/（分区中的总行数）
hive> select gid, dt, pv, cume_dist() over (partition by gid order by pv) from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	0.14285714285714285
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	0.2857142857142857
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	0.42857142857142855
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	0.7142857142857143
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	0.7142857142857143
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	0.8571428571428571
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	1.0
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	0.14285714285714285
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	0.42857142857142855
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	0.42857142857142855
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	0.5714285714285714
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	0.7142857142857143
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	0.8571428571428571
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	1.0
Time taken: 10.416 seconds, Fetched: 14 row(s)

3.6 NTILE()

NTILE函数将每个分区的行尽可能均匀地划分为指定数量的分组。 例如，NTILE(4)表示划分为4个分组。NTILE函数分组取决于OVER子句中的ORDER BY子句。

hive> select gid, dt, pv, ntile(4) over (partition by gid order by pv) from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	2
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	2
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	3
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	3
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	4
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	1
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	1
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	3
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	3
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	4
Time taken: 10.363 seconds, Fetched: 14 row(s)
文章标签：
SQL
HIVE
Serverless
关键词：
函数number
rank number
Hive number
Hive分析函数
Hive cume_dist
sjf0115
目录
相关文章
武子康
|
3月前
|
SQL 分布式计算 Hadoop
Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY（一）
Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY（一）
武子康
58 4
武子康
|
3月前
|
SQL 分布式计算 Hadoop
Hadoop-19 Flume Agent批量采集数据到HDFS集群 监听Hive的日志 操作则把记录写入到HDFS 方便后续分析
Hadoop-19 Flume Agent批量采集数据到HDFS集群 监听Hive的日志 操作则把记录写入到HDFS 方便后续分析
武子康
54 2
武子康
|
3月前
|
SQL
Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY（二）
Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY（二）
武子康
45 2
敏叔V587
|
8月前
|
SQL HIVE
hive高频函数(一)
hive高频函数(一)
敏叔V587
60 0
顾翔
|
4月前
|
SQL JavaScript 前端开发
Hive根据用户自定义函数、reflect函数和窗口分析函数
Hive根据用户自定义函数、reflect函数和窗口分析函数
顾翔
48 6
zcongfly
|
7月前
|
关系型数据库 MySQL
【随手记】MySQL中ROW_NUMBER()、RANK()和DENSE_RANK()函数的用法
【随手记】MySQL中ROW_NUMBER()、RANK()和DENSE_RANK()函数的用法
zcongfly
216 1
孜倦与shine
|
7月前
|
SQL 数据采集 数据可视化
基于Hive的招聘网站的大数据分析系统
基于Hive的招聘网站的大数据分析系统
孜倦与shine
153 2
孜倦与shine
|
7月前
|
SQL 关系型数据库 MySQL
基于Hive的天气情况大数据分析系统（通过hive进行大数据分析将分析的数据通过sqoop导入到mysql，通过Django基于mysql的数据做可视化）
基于Hive的天气情况大数据分析系统（通过hive进行大数据分析将分析的数据通过sqoop导入到mysql，通过Django基于mysql的数据做可视化）
孜倦与shine
310 0
Byyyi
|
8月前
|
SQL XML JSON
Hive函数全解——思维导图 + 七种函数类型
Hive函数全解——思维导图 + 七种函数类型
Byyyi
165 2
Hive函数全解——思维导图 + 七种函数类型
Byyyi
|
8月前
|
SQL 数据采集 存储
Hive实战 —— 电商数据分析(全流程详解 真实数据)
关于基于小型数据的Hive数仓构建实战，目的是通过分析某零售企业的门店数据来进行业务洞察。内容涵盖了数据清洗、数据分析和Hive表的创建。项目需求包括客户画像、消费统计、资源利用率、特征人群定位和数据可视化。数据源包括Customer、Transaction、Store和Review四张表，涉及多个维度的聚合和分析，如按性别、国家统计客户、按时间段计算总收入等。项目执行需先下载数据和配置Zeppelin环境，然后通过Hive进行数据清洗、建表和分析。在建表过程中，涉及ODS、DWD、DWT、DWS和DM五层，每层都有其特定的任务和粒度。最后，通过Hive SQL进行各种业务指标的计算和分析。
Byyyi
1196 1
Hive实战 —— 电商数据分析(全流程详解 真实数据)