一、求每个分组内前五条记录
这是今天美团优选数据研发岗的其中一道面试题记录。
有一张student_subject表。记录了每个学生的课程的分数
| name | subject | score |
|---|---|---|
| 张三 | 语文 | 84 |
| 李四 | 数学 | 28 |
| 王五 | 英语 | 67 |
| 张三 | 数学 | 96 |
| 李四 | 语文 | 14 |
| 王五 | 语文 | 90 |
| ... | ... | ... |
(1) 求出每门课程的最高的分数
这个解决办法很简单, 直接分组取分组内最大值即可。
SELECT subject, max(score) AS max_score
FROM student_subject
GROUP BY subject
ORDER BY subject;
(2) 求出每门课程分数的前五名
子查询
额外增加一列表示分组内的排名。求分组内排名可以采用子查询的方式。查询条件为类型相同的, 找score大于当前值的记录总数, 在其基础上加一就是排名
SELECT t3.*
FROM (
SELECT t2.*
, (
-- 查询比当前score(t2.score)(子查询, 将此t2.score看做常量)更大的记录数,
-- 在此基础上加一就是其排名
SELECT count(*) + 1
FROM student_subject t1
WHERE t1.subject = t2.subject
AND t1.score > t2.score
) AS top
FROM student_subject t2
) t3
WHERE top <= 5
-- 按科目、分数顺序排序,
ORDER BY t3.subject, top;
还可以换一种写法如下, 但是依旧是子查询
SELECT t2.*
FROM student_subject t2
WHERE (
SELECT count(*) + 1
FROM student_subject t1
WHERE t1.subject = t2.subject
AND t1.score > t2.score
) <= 5
ORDER BY subject;
还可以修改为下一种写法
SELECT t2.*
FROM student_subject t2
WHERE EXISTS (
SELECT count(*) + 1
FROM student_subject t1
WHERE t1.subject = t2.subject
AND t1.score > t2.score
HAVING count(*) + 1 <= 5
)
ORDER BY subject;
上面这三种写法基本相同。
左连接
SELECT t3.subject, t3.name, t3.score
FROM (
SELECT t1.subject, t1.name, t1.score
FROM student_subject t1
LEFT JOIN student_subject t2
-- 左连接的条件是科目相同, 并且别人的分数不低于自己的分数(=可以让自己与自己左连接)
ON t1.subject = t2.subject
AND t1.score <= t2.score
) t3
-- 对每个科目的每个人做group by, count(*) 就是这个人的排名
GROUP BY t3.subject, t3.name, t3.score
-- 把排名前三的筛选出来
HAVING count(t3.name) <= 3
ORDER BY t3.subject, count(t3.name);
HIVE_SQL
Hive 里可以使用分析函数(窗口函数)
SELECT subject, name, score
FROM (
SELECT subject, name, score, ROW_NUMBER() OVER (PARTITION BY settop ORDER BY score DESC) AS rn
FROM student_subject
)
WHERE rn <= 5
ORDER BY subject;
二、多分组组内排序第一
这是面试字节抖音数仓团队的一道面试题。
有一张成绩表如下:
| id | u_id | exam_id | course_id | score |
|---|---|---|---|---|
| 1 | 20211013001 | 1 | 1 | 56 |
| 2 | 20211013001 | 2 | 1 | 64 |
| 3 | 20211013002 | 1 | 1 | 54 |
| 4 | 20211013001 | 1 | 2 | 87 |
| 5 | 20211013002 | 2 | 1 | 88 |
| 6 | 20211013001 | 2 | 2 | 55 |
| 7 | 20211013002 | 1 | 2 | 45 |
| 8 | 20211013002 | 2 | 2 | 99 |
求每个学生每一门课程的最高成绩。要求使用窗口函数实现。实现起来非常简单。如下：
SELECT t1.u_id, t1.course_id, t1.score
FROM (
SELECT u_id, course_id, score,
ROW_NUMBER() OVER (PARTITION BY u_id, course_id ORDER BY score DESC) AS rt
FROM `tb_score`
) t1
WHERE t1.rt = 1;
但是注意需要在外层再套一层SELECT, 不能直接一层SELECT ... WHERE rt = 1;