在开发过程中，经常会遇见排序的场景，比如取top N的问题，这时候row_number(),rank,dense_ran()这三个函数就派上用场了，其中，row_number()最为常用。虽然都可以排序，但是他们之间还有点细微的区别，具体的差异请参阅下面的实例；本文除了这三个函数外，还简单介绍了下NTILE() over()这个函数。
开始之前先在hive准备一张表dw_table_test,数据如下：
url,pv,data_time
a,1000,2019-06-01
a,2000,2019-06-02
a,2000,2019-06-03
a,2000,2019-06-04
a,3000,2019-06-05
b,1000,2019-06-05
b,2000,2019-06-08
c,2000,2019-06-04
c,2000,2019-06-05
c,3000,2019-06-06
[if !supportLists]1 [endif]row_number() over()
[if !supportLists](a)[endif] row_number() over()分组排序功能
注意：在使用 row_number() over()函数时候，over()里的分组以及排序的执行晚于where group by order by的执行。
partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。
如下例子，根据每url进行分组，按照pv从高到低排序
Sql：select url, pv,row_number() over(partition by urlorder by pv desc) as rn from dw_table_test;
结果：
a 3000 1
a 2000 2
a 2000 3
a 2000 4
a 1000 5
b 2000 1
b 1000 2
c 3000 1
c 2000 2
c 2000 3
（a）row_number() over()无分组排序
如下例子，按照pv从高到低排序
Sql：select url,pv,row_number() over(order by pv desc) as rn from dw_table_test;
结果：
c 3000 1
a 3000 2
c 2000 3
c 2000 4
b 2000 5
a 2000 6
a 2000 7
a 2000 8
b 1000 9
a 1000 10
2、rank() over()
rank() over()是跳跃排序，有两个第二名时接下来就是第四名。
（a）rank() over()分组排序功能
如下例子，根据每url进行分组，按照pv从高到低排序
Sql：select url,pv,rank() over(partition by url order by pv desc) as rn Fromdw_table_test;
结果：
a 3000 1
a 2000 2
a 2000 2
a 2000 2
a 1000 5
b 2000 1
b 1000 2
c 3000 1
c 2000 2
c 2000 2
（b）rank() over()无分组排序
如下例子,按照pv从高到低排序
Sql：select url,pv,rank() over(order by pv desc) asrn From dw_table_test;
结果：
c 3000 1
a 3000 1
c 2000 3
c 2000 3
b 2000 3
a 2000 3
a 2000 3
a 2000 3
b 1000 9
a 1000 9
3、dense_rank() over()
dense_rank() over()是连续排序，有两个第二名时仍然跟着第三名，而row_number是没有重复值的。
（a）dense_rank () over()分组排序功能
如下例子，根据每url进行分组，按照pv从高到低排序
Sql:select url,pv, dense_rank() over(partition by url order bypv desc) as rn from dw_table_test;
结果：
a 3000 1
a 2000 2
a 2000 2
a 2000 2
a 1000 3
b 2000 1
b 1000 2
c 3000 1
c 2000 2
c 2000 2
（b）dense_rank () over()无分组排序
如下例子，按照pv从高到低排序
Sql:select url,pv, dense_rank() over(order by pv desc) as rnfrom dw_table_test;
结果：
c 3000 1
a 3000 1
c 2000 2
c 2000 2
b 2000 2
a 2000 2
a 2000 2
a 2000 2
b 1000 3
a 1000 3
4、NTILE() over()
NTILE(n)，属于分析函数，用于将分组数据按照顺序切分成n片，返回当前切片值,如果切片不均匀，默认增加第一个切片的分布。
（a）NTILE() over()有分组
如下例子，根据url进行分组，按照pv从高到低排序将数据拆成两份
Sql：selecturl,pv,NTILE(2) over(partition by url order by pv desc ) as rn fromdw_table_test;
结果：
a 3000 1
a 2000 1
a 2000 1
a 2000 2
a 1000 2
b 2000 1
b 1000 2
c 3000 1
c 2000 1
c 2000 2
（b）NTILE() over()无分组
如下例子，按照pv从高到低排序将数据拆成两份
Sql：selecturl,pv,NTILE(2) over(order by pv desc ) rn from dw_table_test;
结果：
c 3000 1
a 3000 1
c 2000 1
c 2000 1
b 2000 1
a 2000 2
a 2000 2
a 2000 2
b 1000 2
a 1000 2
5、总结
通过上面的事例可以很轻易区分它们，以便应用于不同的场景；还有一点需要注意就是：在以上提到的四个窗口函数中，都不支持ROWS BETWEEN/ RANGE BETWEEN, ROWS BETWEEN表示在当前行之间，RANGE
BETWEEN表示在当前值范围内；而类似于sum，count这样的聚合函数是可以这样使用，比如下面的实例。
原始数据
Sql:
Select * from dw_table_test orderby date_time desc;
b 2000 2019-06-08
c 3000 2019-06-06
c 2000 2019-06-05
b 1000 2019-06-05
a 3000 2019-06-05
c 2000 2019-06-04
a 2000 2019-06-04
a 2000 2019-06-03
a 2000 2019-06-02
a 1000 2019-06-01
处理后
rows between 1 preceding and 1 following表示在当前行上下一行之间的范围内
sql：select url,date_time,sum(pv) over(order by date_time desc rows
between 1 preceding and 1 following) as rn from dw_table_test;
结果：
b 2019-06-08 5000 =2000+3000
c 2019-06-06 7000 =2000+3000+2000
c 2019-06-05 6000 =3000+2000+1000
b 2019-06-05 6000 =2000+1000+3000
a 2019-06-05 6000 …
c 2019-06-04 7000
a 2019-06-04 6000
a 2019-06-03 6000
a 2019-06-02 5000
a 2019-06-01 3000 =2000+1000
网友评论