Hive窗口和分析函数[RANK()、DENSE_RANK()、ROW_NUMBER()]

最新推荐文章于 2024-01-30 12:23:37 发布

图特摩斯科技

最新推荐文章于 2024-01-30 12:23:37 发布

阅读量1.6k

点赞数

分类专栏： hive

hive 专栏收录该内容

21 篇文章 1 订阅

订阅专栏

row_number()的使用方法及原博主相关文章。

Analytics functions

RANK()、DENSE_RANK()、ROW_NUMBER()

使用示例：

select calling_nbr,called_nbr,count,
  RANK() OVER (PARTITION by calling_nbr order by count desc) rank,
  DENSE_RANK() OVER (PARTITION by calling_nbr order by count desc) drank,
  ROW_NUMBER() OVER (PARTITION by calling_nbr order by count desc) rn
from test limit 100;

其中 PARTITION by calling_nbr 可选，若加上则是窗口内统计，否则则是全局统计。

部分结果如下：

calling_nbr	called_nbr	count	rank	drank	rn
----------------------------------------------------------
00019082203	18997727**2	3	1	1	1
00019082203	18999201**1	2	2	2	2
00019082203	13399086**1	2	2	2	3
00019082203	18909916**2	1	4	3	4
00019082203	18999374**7	1	4	3	5
00019082203	18999887**7	1	4	3	6
00019082203	18999826**8	1	4	3	7
00019082203	18016829**3	1	4	3	8
00019082203	13319869**8	1	4	3	9
00019082203	18999184**5	1	4	3	10
00019082203	18935809**9	1	4	3	11
00019082203	18199117**0	1	4	3	12
00019082203	18099490**6	1	4	3	13
00019082203	18999177**8	1	4	3	14
00019082203	18999584**1	1	4	3	15
00019082203	18999161**6	1	4	3	16
00019082203	18099204**9	1	4	3	17
----------------------------------------------------------
00019661102	18935805**8	2	1	1	1
00019661102	18999194**4	2	1	1	2
00019661102	18909974**9	1	3	2	3
00019661102	18997988**9	1	3	2	4
00019661102	18999973**3	1	3	2	5
00019661102	18997522**7	1	3	2	6
00019661102	18999966**8	1	3	2	7
00019661102	18034886**8	1	3	2	8

可以看到以calling_nbr为组进行组内的统计， ROW_NUMBER() 统计当前行号，从1开始； RANK() 为排名统计，若进行排名的值相同则后续留空，如上面的结果中出现了两个2,则接着从4开始； ROW_NUMBER() 与 RANK() 类似，不过出现相同值时不会后续留空，允许“并列第几”的存在。

NTILE()

分片函数，将数据等分为N份，示例如下，将整个表按照count从大到小进行排序并切分成5份。

select calling_nbr,called_nbr,count,
  ntile(5) OVER (order by count desc) til
from test;

这样可以通过til=1来选择前1/5的数据，til=2选择1/5到2/5间的数据，以此类推。

验证是否是等分：

select til,count(*) from til5_t group by til;

结果：

CUME_DIST()、PERCENT_RANK()

类似oracle中的CUME_DIST和PERCENT_RANK函数，用来计算分组中的位置，数值介于0和1之间。

示例：

select calling_nbr,called_nbr,count,
  RANK() OVER (PARTITION by calling_nbr order by count desc) rank,
  DENSE_RANK() OVER (PARTITION by calling_nbr order by count desc) drank,
  ROW_NUMBER() OVER (PARTITION by calling_nbr order by count desc) rn,
  CUME_DIST() OVER (PARTITION by calling_nbr order by count desc) dist,
  PERCENT_RANK() OVER (PARTITION by calling_nbr order by count desc) prank
from test limit 100;

结果（只选取了一组）：

00019639213	13364908300	2	1	1	1	0.043478260869565216	0.0
00019639213	15352666868	1	2	2	2	1.0	0.045454545454545456
00019639213	18099189171	1	2	2	3	1.0	0.045454545454545456
00019639213	18999908156	1	2	2	4	1.0	0.045454545454545456
00019639213	18999682115	1	2	2	5	1.0	0.045454545454545456
00019639213	18999033733	1	2	2	6	1.0	0.045454545454545456
00019639213	18999412012	1	2	2	7	1.0	0.045454545454545456
00019639213	18999499678	1	2	2	8	1.0	0.045454545454545456
00019639213	13319708861	1	2	2	9	1.0	0.045454545454545456
00019639213	18097711665	1	2	2	10	1.0	0.045454545454545456
00019639213	18099721994	1	2	2	11	1.0	0.045454545454545456
00019639213	15389923573	1	2	2	12	1.0	0.045454545454545456
00019639213	18935754120	1	2	2	13	1.0	0.045454545454545456
00019639213	18196110916	1	2	2	14	1.0	0.045454545454545456
00019639213	18099189175	1	2	2	15	1.0	0.045454545454545456
00019639213	18099023200	1	2	2	16	1.0	0.045454545454545456
00019639213	18999446189	1	2	2	17	1.0	0.045454545454545456
00019639213	18999685463	1	2	2	18	1.0	0.045454545454545456
00019639213	18999793295	1	2	2	19	1.0	0.045454545454545456
00019639213	13319873344	1	2	2	20	1.0	0.045454545454545456
00019639213	13319706226	1	2	2	21	1.0	0.045454545454545456
00019639213	18016893222	1	2	2	22	1.0	0.045454545454545456
00019639213	18999528429	1	2	2	23	1.0	0.045454545454545456

其中CUME_DIST的计算方式为分组所在位置/分组大小，如1/23=0.043478260869565216，23/23=1.0。PERCENT_RANK的计算方式为（分组中顺序-1)/(分组大小-1)，如(1-1)/(23-1)=0.0，(2-1)/(23-1)=0.045454545454545456。

The OVER clause

上面已经用到了 OVER 关键字，除了上面的用法以外，还有更强大的窗口选择关键字 ROWS 。用法： ROWS ((CURRENT ROW) | (UNBOUNDED | [num]) PRECEDING) AND (UNBOUNDED | [num]) FOLLOWING 。

举例，数据：

建表语句：

create table test(
calling string,
called string,
duration int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

使用ROWS关键字：

select calling,called,
  sum(duration) OVER (PARTITION by calling order by duration desc ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
from test;

结果：

此时对照原始数据完全看不出来是什么意思，在使用ROWS时根据duration进行了组内的排序，这里将排序的结果展示出来：

此时在看ROWS后面参数的含义，意思是从前1行开始到后1行结束，也就是窗口的大小限制成为3。结合结果数据和排序后的数据来开，当没有前一行的时候，窗口大小变成2，即当前行和后一行，如结果数据中第一行的78=67（当前行）+11（后一行），第二行88=67（前一行）+11（当前行）+10（后一行），当没有后一行时情况类似。

此外可以用 CURRENT ROW 限制成当前行，用 UNBOUNDED PRECEDING或FOLLOWING 来表示不限制前/后面的行数。一个典型的不限制前面行数的应用就是按月累积统计值，如1月的收入、2月的累积收入（包括1月）、3月的累积收入..