HIVE-利用ow_number() OVER(PARTITION BY)函数介绍求TOP-K

http://blog.csdn.net/631799/article/details/7419797

第一句话：

select row_number() over (partition by month order by ref_host_cnts desc;

partition：按照month分成区块

order by ：排序实在partition分成的区块中分别进行。

row_number()：对各个分区分别添加编号，类似于rownum的递增序列

 

 

嵌套后获取TOPK,获取每个月前两名的数据

select t.hour, t.od, t.ref_host, t.ref_host_cnts from
 (select ref_host, ref_host_cnts,month as hour,
row_number() over (partition by month order by ref_host_cnts desc) as od
from dw_ref_host_visit_cnts_h) t where od<=3;

转载于:https://www.cnblogs.com/kouryoushine/p/7845528.html