【牛客】SQL136 每类试卷得分前3名-窗口函数

描述

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

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2021-09-01 06:00:00
2	9002	SQL	hard	60	2021-09-01 06:00:00
3	9003	算法	medium	80	2021-09-01 10:00:00

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

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2021-09-01 09:01:01	2021-09-01 09:31:00	78
2	1002	9001	2021-09-01 09:01:01	2021-09-01 09:31:00	81
3	1002	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	81
4	1003	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	86
5	1003	9002	2021-09-01 12:01:01	2021-09-01 12:31:51	89
6	1004	9001	2021-09-01 19:01:01	2021-09-01 19:30:01	85
7	1005	9003	2021-09-01 12:01:01	2021-09-01 12:31:02	85
8	1006	9003	2021-09-07 10:01:01	2021-09-07 10:21:01	84
9	1003	9003	2021-09-08 12:01:01	2021-09-08 12:11:01	40
10	1003	9002	2021-09-01 14:01:01	(NULL)	(NULL)

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

tid	uid	ranking
SQL	1003	1
SQL	1004	2
SQL	1002	3
算法	1005	1
算法	1006	2
算法	1003	3

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

方法一：

select * from
    (select
    tid,uid,
    row_number() over(partition by tid order by down_rnk) as ranking
    from
        (select
        tid,uid,
        dense_rank() over(partition by tid order by max_score desc) as down_rnk,
        dense_rank() over(partition by tid order by min_score desc) as up_rnk
        from
            (select
            tag as tid,
            uid,
            max(score) as max_score,
            min(score) as min_score
            from
            exam_record left join examination_info using(exam_id)
            where submit_time is not null
            group by uid,tid)t1
        order by tid,down_rnk,up_rnk,uid desc)t2
    )t3
where ranking<=3

方法二：

select *
from
    (select tag as tid,uid,
    row_number() over(partition by tag order by max(score) desc,min(score) desc,uid desc) as ranking
    from
    exam_record left join examination_info using(exam_id)
    where submit_time is not null
    group by uid,tid)t1
where ranking<=3