窗口函数SQL练习

题目描述（题目来源：牛客网）

 

https://www.nowcoder.com/exam/oj

 

由此题复习了窗口函数的知识

<窗口函数> OVER ([PARTITION BY <列清单>]
                                   ORDER BY <排序用列清单>)
*[] 中的代码可以省略          

 

---

 

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：


找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。由示例数据结果输出如下：

解释：有作答得分记录的试卷tag有SQL和算法，SQL试卷用户1001、1002、1003、1004有作答得分，最高得分分别为81、81、89、85，最低得分分别为78、81、86、40，因此先按最高得分排名再按最低得分排名取前三为1003、1004、1002。

示例1

输入：

drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 78),
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null);

输出：

SQL|1003|1
SQL|1004|2
SQL|1002|3
算法|1005|1
算法|1006|2
算法|1003|3

我的解答：

SELECT SP2.tag, SP2.uid,
ROW_NUMBER() OVER (PARTITION BY SP2.tag ORDER BY SP2.tag_rank) AS ranking
FROM(SELECT SP.tag, SP.uid,SP.min_rank,SP.max_rank,SP.tag_rank,SP.uid_rank
FROM(SELECT P.tag,P.uid,P.score,
       DENSE_RANK() OVER (PARTITION BY P.uid
                    ORDER BY P.score DESC) AS min_rank,
       DENSE_RANK() OVER (PARTITION BY P.uid
                    ORDER BY P.score) AS max_rank,
       DENSE_RANK() OVER (PARTITION BY P.tag
                    ORDER BY P.score DESC) AS tag_rank,
       DENSE_RANK() OVER (PARTITION BY P.tag
                    ORDER BY P.uid DESC) AS uid_rank
FROM(SELECT tag,uid,score
  FROM exam_record AS P1 LEFT JOIN examination_info AS P2 ON 
       P1.exam_id = P2.exam_id) AS P) AS SP
GROUP BY SP.tag,SP.uid
HAVING  SP.uid_rank <= 3) AS SP2;

笔者写的代码只能得出通过以上数据查询的正确结果且较为复杂，原因是没有考虑“两人最大分数相同，选择最小分数大者”的情况，因为实在牛客网网页上的编辑器，此时需要用到多个窗口

以下是别人写的优秀代码：学习一下~

select *
from
    (
    select
    tag
    ,uid
    ,rank() over(partition by tag order by max_score desc,min_score desc,uid desc) rk
    from
    (
        select
        tag
        ,uid
        ,max(score) max_score
        ,min(score) min_score
        from exam_record er
        left join examination_info ei
        on er.exam_id = ei.exam_id
        group by tag,uid
    ) t
    group by tag,uid
    ) tt
where rk <= 3

#如果查询的其他部分（WHERE，GROUP BY，HAVING）需要窗口函数，
#请使用子查询，然后在子查询中在使用窗口函数
#1、专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
#2、聚合函数，如sum. avg, count, max, min等