今天题发现一个有趣的题目:找到一个班级里成绩排名,并算出每个人的前1名和前2名作为超越的对手,如果没有前1名,则找前2名和前3名。例如:有两个第八名,则没有第九名,第十名的前一名是第八名,第十名的前2名是第七名

举个例子:
一个班级的某科目成绩单如下:

学号姓名分数
1001赵君48
1002钱不95
1003孙见64
1004李黄77
1005周河82
1006吴之77
1007郑水64
1008王天92
1009冯上77
1010褚来82
1011卫奔85
1012蒋流67

需要获取到每个人的排名以及他所在前1名同学和前2名同学的姓名,目标结果如下所示。

image-20200508220830448

拿到题目首先造数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DROP TABLE IF EXISTS SCORE;
CREATE TABLE IF NOT EXISTS score(ID STRING,NAME STRING,SC STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC;

INSERT INTO TABLE SCORE VALUES
('1001','赵君','48'),
('1002','钱不','95'),
('1003','孙见','64'),
('1004','李黄','77'),
('1005','周河','82'),
('1006','吴之','77'),
('1007','郑水','64'),
('1008','王天','92'),
('1009','冯上','77'),
('1010','褚来','82'),
('1011','卫奔','85'),
('1012','蒋流','67');

首先给每位同学成绩进行排名,分为跳跃排名和不跳跃排名。实际业务中,前1名和前2名是自己下次考试的超越对手。其中跳跃排名是正常使用的排名,而不跳跃排名是为了进行关联而使用的关联条件。

1
2
3
4
5
6
7
SELECT
id, -- 学号
name, -- 姓名
sc, -- 成绩
RANK() OVER(ORDER BY sc DESC) AS rk, -- 排名1,带跳跃
DENSE_RANK() OVER(ORDER BY sc DESC) AS rk2 -- 排名2,不跳跃
FROM score

执行结果如下:

idnamescrkrk2
1002钱不9511
1008王天9222
1011卫奔8533
1010褚来8244
1005周河8244
1009冯上7765
1006吴之7765
1004李黄7765
1012蒋流6796
1007郑水64107
1003孙见64107
1001赵君48128

然后进行两次自关联,分别找到每一位同学的前1名和前2名,由于存在排名并列的情况,join后势必存在数据膨胀,也就是说前1名可能是多位同学,这里使用collect_list函数对结果进行收敛。

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
WITH tt AS(
SELECT
id, -- 学号
name, -- 姓名
sc, -- 成绩
RANK() OVER(ORDER BY sc DESC) AS rk, -- 排名1,带跳跃
DENSE_RANK() OVER(ORDER BY sc DESC) AS rk2 -- 排名2,不跳跃
FROM score
)
SELECT
t1.id,
t1.name,
t1.sc,
t1.rk,
--t1.rk2,
--COLLECT_LIST(t2.id), -- 前1名学号
COLLECT_LIST(t2.name), -- 前1名姓名
--COLLECT_LIST(t3.id), -- 前2名学号
COLLECT_LIST(t3.name) -- 前2名姓名
FROM tt AS t1
LEFT JOIN tt AS t2
ON t1.rk2-1=t2.rk2
LEFT JOIN tt AS t3
ON t1.rk2-2=t3.rk2
GROUP BY
t1.id,
t1.name,
t1.sc,
t1.rk,
t1.rk2
ORDER BY t1.rk;

执行结果如下:

image-20200508220901554

不难看出最后三位同学{郑水,孙见,赵君}的数据是有问题的,前1、2名数据有重复,为什么呢?

经过测试,实际上单独找前1名或者前2名使用collect_list都没有重复,如果找前1名和前2名放在一起会产生重复,因为collect相当于省略了group by操作,对膨胀的数据进行聚合(列转行),在第一次join中已经出现了数据膨胀,第二次join会加剧数据膨胀,而collect_list是在所有join完成之后,对重复的数据仅进行一次收敛。所以使用collect_set才会符合预期要求。

代码如下:

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
WITH tt AS(
SELECT
id, -- 学号
name, -- 姓名
sc, -- 成绩
RANK() OVER(ORDER BY sc DESC) AS rk, -- 排名1,带跳跃
DENSE_RANK() OVER(ORDER BY sc DESC) AS rk2 -- 排名2,不跳跃
FROM score
)
SELECT
t1.id,
t1.name,
t1.sc,
t1.rk,
--t1.rk2,
--COLLECT_SET(t2.id), -- 前1名学号
COLLECT_SET(t2.name), -- 前1名姓名
--COLLECT_SET(t3.id), -- 前2名学号
COLLECT_SET(t3.name) -- 前2名姓名
FROM tt AS t1
LEFT JOIN tt AS t2
ON t1.rk2-1=t2.rk2
LEFT JOIN tt AS t3
ON t1.rk2-2=t3.rk2
GROUP BY
t1.id,
t1.name,
t1.sc,
t1.rk,
t1.rk2
ORDER BY t1.rk;

执行结果:

image-20200508221007625