【牛客】SQL137 第二快/慢用时之差大于试卷时长一半的试卷-窗口函数

描述

现有试卷信息表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	C++	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:51:01	78
2	1001	9002	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:59:01	86
5	1003	9002	2021-09-01 12:01:01	2021-09-01 12:31:51	89
6	1004	9002	2021-09-01 19:01:01	2021-09-01 19:30:01	85
7	1005	9001	2021-09-01 12:01:01	2021-09-01 12:31:02	85
8	1006	9001	2021-09-07 10:01:01	2021-09-07 10:21:01	84
9	1003	9001	2021-09-08 12:01:01	2021-09-08 12:11:01	40
10	1003	9002	2021-09-01 14:01:01	(NULL)	(NULL)
11	1005	9001	2021-09-01 14:01:01	(NULL)	(NULL)
12	1003	9003	2021-09-08 15:01:01	(NULL)	(NULL)

找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。由示例数据结果输出如下：

exam_id	duration	release_time
9001	60	2021-09-01 06:00:00

解释：试卷9001被作答用时有50分钟、50分钟、30分1秒、11分钟、10分钟，第二快和第二慢用时之差为50分钟-11分钟=39分钟，试卷时长为60分钟，因此满足大于试卷时长一半的条件，输出试卷ID、时长、发布时间。

方法一：使用自连接

with cte1 as
(select
exam_id,duration,release_time,
timestampdiff(minute,start_time,submit_time) as time,
row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) as up_rnk,
row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)) as down_rnk
from
exam_record left join examination_info using(exam_id)
where submit_time is not null)

select
t1.exam_id as exam_id,
t1.duration as duration,
t1.release_time as release_time
from 
cte1 t1 left join cte1 t2
on t1.up_rnk=t2.down_rnk and t1.exam_id=t2.exam_id
where t2.down_rnk=2 and 2*(t1.time-t2.time)>=t1.duration
order by exam_id desc

方法二：使用case when 函数 + sum()聚合函数

with cte1 as
(select
exam_id,duration,release_time,
timestampdiff(minute,start_time,submit_time) as time,
row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) as up_rnk,
row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)) as down_rnk
from
exam_record left join examination_info using(exam_id)
where submit_time is not null)

select
exam_id,duration,release_time
from
    (select
    exam_id,duration,release_time,
    sum(case 
        when up_rnk=2 then time
        when down_rnk=2 then -time
        else 0
        end) as sum_time
    from cte1
    group by exam_id)t
where 2*sum_time>=duration
order by exam_id desc