Hive查询各自区组的money排名前十的账号_hive分组取前10

有一个账号表如下，请写出SQL语句，查询各自区组的money排名前十的账号（分组取前10）
1）建表（MySQL）

CREATE TABLE `test_ten_account`
(
    `dist_id` int COMMENT '区组id',
    `account` string COMMENT '账号',
    `gold`    int COMMENT '金币'
)
    row format delimited fields terminated by ',';

insert into table test_ten_account values ('1','11',100006);
insert into table test_ten_account values ('1','12',110000);
insert into table test_ten_account values ('1','13',102000);
insert into table test_ten_account values ('1','14',100300);
insert into table test_ten_account values ('1','15',100040);
insert into table test_ten_account values ('1','18',110000);
insert into table test_ten_account values ('1','16',100005);
insert into table test_ten_account values ('1','17',180000);

insert into table test_ten_account values ('2','21',100800);
insert into table test_ten_account values ('2','22',100030);
insert into table test_ten_account values ('2','23',100000);
insert into table test_ten_account values ('2','24',100010);
insert into table test_ten_account values ('2','25',100070);
insert into table test_ten_account values ('2','26',100800);

insert into table test_ten_account values ('3','31',106000);
insert into table test_ten_account values ('3','32',100400);
insert into table test_ten_account values ('3','33',100030);
insert into table test_ten_account values ('3','34',100003);
insert into table test_ten_account values ('3','35',100020);
insert into table test_ten_account values ('3','36',100500);
insert into table test_ten_account values ('3','37',106000);
insert into table test_ten_account values ('3','38',100800);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

2）最终SQL

with a as (select dist_id,
                  account,
                  gold,
                  rank() over (distribute by dist_id sort by gold) `rn`
           from test_ten_account
)
select dist_id, account, gold, rn
from a
where a.rn <= 10
;
1
2
3
4
5
6
7
8
9
10

总结

就是简单的分组求Top N类型