hive 窗口函数_Datatist科技专栏 | Hive排序窗口函数

Datatist原上野的纯干货分享。

作者：原上野

设计：Cindy

编辑：AI君

在开发过程中经常会遇见排序的场景，比如取top N的问题，这时候row_number(),rank,dense_ran()这三个函数就派上用场了，其中，row_number()最为常用。

虽然都可以排序，但是他们之间还有点细微的区别，具体的差异请参阅下面的实例;本文除了这三个函数外，还简单介绍了下NTILE() over()这个函数。

开始之前先在hive准备一张表dw_table_test,数据如下：

1

row_number() over()

(a) 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 url order by pv desc) as rn from dw_table_test;

结果：

(b)row_number() over()无分组排序

如下例子，按照pv从高到低排序

Sql：select url, pv,row_number() over(order by pv desc) as rn from dw_table_test;

结果：

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 From dw_table_test;

结果：

(b) rank() over()无分组排序

如下例子,按照pv从高到低排序

Sql：select url,pv,rank() over(order by pv desc) as rn From dw_table_test;

结果：

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 by pv desc) as rn from dw_table_test;

结果：

(b)dense_rank () over()无分组排序

如下例子，按照pv从高到低排序

Sql:select url,pv, dense_rank() over(order by pv desc) as rn from dw_table_test;

结果：

4

NTILE() over()

NTILE(n)，属于分析函数，用于将分组数据按照顺序切分成n片，返回当前切片值,如果切片不均匀，默认增加第一个切片的分布。

(a)NTILE() over()有分组

如下例子，根据url进行分组，按照pv从高到低排序将数据拆成两份

Sql：select url,pv,NTILE(2) over(partition by url order by pv desc ) as rn from dw_table_test;

结果：

(b)NTILE() over()无分组

如下例子，按照pv从高到低排序将数据拆成两份

Sql：select url,pv,NTILE(2) over(order by pv desc ) rn from dw_table_test;

结果：

5

总结

通过上面的事例可以很轻易区分它们，以便应用于不同的场景;还有一点需要注意就是：在以上提到的四个窗口函数中，都不支持ROWS BETWEEN/ RANGE BETWEEN, ROWS BETWEEN表示在当前行之间，RANGE BETWEEN表示在当前值范围内;而类似于sum，count这样的聚合函数是可以这样使用，比如下面的实例。

原始数据

Sql:

Select * from dw_table_test order by date_time desc;

处理后

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;

结果：