hive内group by取第一条数据，Hive中row_number的使用

1、hive的分组和组内排序---语法

语法：
row_number() over (partition by 字段a order by 计算项b desc ) rank

• rank是排序的别名
• partition by：类似hive的建表，分区的意思；
• order by ：排序，默认是升序，加desc降序；
• 这里按字段a分区，对计算项b进行降序排序

2、hive的分组和组内排序 --- 实例

要取top10品牌，各品牌的top10渠道，各品牌的top10渠道中各渠道的top10档期

1、取top10品牌

select “品牌” , sum/count/其他() as num from "table_name" order by num desc limit 10;

2、取top10品牌下各品牌的top10渠道

select a.* from (select "品牌","渠道",sum/count() as num, row_number () over (partition by "品牌" order by num desc) rank from “table_name” where 品牌限制条件 group by “品牌”,“渠道” ) a having a.rank <= 10;

3、 取top10品牌下各品牌的top10渠道中各渠道的top10档期

select a.* from (select "品牌","渠道","档期",sum/count/其他() as num row_number() over (partition by "档期" order by num desc) rank from "table_name" where 品牌限制条件 group by “品牌”,“渠道) a Having a.rank <= 10;

我的应用：rank编号，rank小组内从1开始编号

SELECT v.visitor_phone,v.city，v.bigarea,
row_number()over (partition by visitor_phone order by visitor_phone desc) rank 
from visitor v 
WHERE 1=1 and visitor_name in('蒋凤','周金魁')
group BY v.visitor_phone,v.city
---------------------------------
18222666666 北京 华北区   1
18222666666 天津	华北区	2
13402777777 北京	华北区	1
13402777777 成都	中西部	2

取编号为1的

SELECT a.* from(
SELECT v.visitor_phone,v.city，v.bigarea,
row_number()over (partition by visitor_phone order by visitor_phone desc) rank 
from visitor v 
WHERE 1=1 and visitor_name in('蒋凤','周金魁')
group BY v.visitor_phone,v.city
)a
where a.rank=1;
-------------------------------------
18222666666 北京 华北区   1
13402777777 北京 华北区	1