[Hive]分析函数 RANK ROW_NUMBER CUME_DIST CUME_DIST

标签: Hive 分析函数 RANK ROW_NUMBER NTILE
516人阅读 评论(0) 收藏 举报
分类:

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)
查看评论

nt下路由表的手工配置

 只要需要访问本网段之外的主机,就应该存在着一条路由信息,指引ip包的传送。所有的路由信息可以使用route print命令查看。如果网卡不是上Internet,应该不必为之配置默认网关,只需要手工加...
  • heyongzhou
  • heyongzhou
  • 2001-07-29 16:12:00
  • 1104

hive使用rank实现topN的查询

前置条件: 安装好hive背景: hive表中存有cookieid和time两个字段 目标: 通过rank方法查出每个cookieid在哪一天的次数最多具体步骤如下: 一、创建表CREATE...
  • hua245942641
  • hua245942641
  • 2015-12-14 18:36:08
  • 2948

Hive分析窗口函数之CUME_DIST和PERCENT_RANK

两个序列分析函数不是很常用,这里也介绍一下。 环境信息: Hive版本为apache-hive-0.14.0-bin Hadoop版本为hadoop-2.6.0 Tez版本为tez-0.7.0 ...
  • jiangshouzhuang
  • jiangshouzhuang
  • 2016-04-04 18:00:19
  • 3060

分析函数_CUME_DIST -- SQL Server 2012 & Hive

最近在整理 Hie 分析函数资料的时候,看到了 CUME_DIST,百度后还是不知其意。于是乎用起了之前介绍的 Webcrawler 搜索引擎 :http://www.webcrawler.com/ ...
  • Mike_H
  • Mike_H
  • 2015-12-12 15:50:39
  • 623

SQL SERVER中CUME_DIST和PERCENT_RANK函数

用实例数据解释SQL SERVER中CUME_DIST和PERCENT_RANK函数
  • lihui_830501
  • lihui_830501
  • 2014-03-12 01:22:14
  • 2368

Oracle分析函数ROW_NUMBER()|RANK()|LAG()使用详解

Oracle分析函数RANK()|ROW_NUMBER()|LAG()使用详解   ROW_NUMBER()函数: row_number()和rownum差不多,功能更强一点(可以在各个分组内从1...
  • haiross
  • haiross
  • 2013-11-01 10:12:17
  • 3025

高级查询 cume_dist()和percent_rank()

cume_dist()和percent_rank()这两个函数了,现在把他们的区别在这用个例子说明一下 SQL> create table cume (   2  id integer,  ...
  • raike
  • raike
  • 2011-10-28 15:33:28
  • 8022

hive的row_number()、rank()和dense_rank()的区别以及具体使用

row_number()、rank()和dense_rank()这三个是hive内置的分析函数,下面我们来看看他们的区别和具体的使用案例。 首先创建一个文件test: A,1 B,3 C,2 D,3 ...
  • qq_20641565
  • qq_20641565
  • 2016-10-17 20:05:21
  • 3490

[Oracle]高效的SQL语句之分析函数(三)--row_number() /rank()/dense_rank()

 有些时候我们希望得到指定数据中的前n列,示例如下:得到每个部门薪水最高的三个雇员:先创建示例表create table empasselect * from scott.emp;alter tabl...
  • huanghui22
  • huanghui22
  • 2007-05-03 00:31:00
  • 8964

hive函数--排序row_number,rank over

从学生的几门成绩中选出成绩最好的一门 1.row_number() over select student_id,class,score from ( select student_id ...
  • qq_17246605
  • qq_17246605
  • 2016-04-05 17:18:26
  • 5862
    个人资料
    专栏达人 持之以恒
    等级:
    访问量: 159万+
    积分: 2万+
    排名: 371
    博客专栏
    最新评论