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

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

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：


找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷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、时长、发布时间。

• 题目分析

1.找到每一份试卷的ID，限制时间，发布时间，以及每份时间完成的耗时，耗时降序和增序排序，需要计算每一份耗时，不去重，不分组
    1.两表关联join...on..
    2.筛掉没做完的试卷
    3.试卷ID，限制时间直接获取计算时间差timestampdiff()
    4.利用分组聚合排序 row_number()over(partition by ... order by ...)
    5.查询出来的表格定义为t1
2.根据上面表的信息查询每份试卷的限制时间，发布时间和第二慢与第二快的差值
    1.进行试卷ID分组
    2.在t1表中查询试卷ID,限制时间,发布时间
    3.将每组试卷完成时间累加最快排名第二加负值,最慢第二加正值，其余加0
        sum(case when rank1=2 then costtime when rank2 = 2 then -costtime
        else 0 end) as sub 查询表为t2
3.最后从t2筛选出结果
4.按照时间ID降序 order

• 代码实现

SELECT
    DISTINCT exam_id,
    duration,
    release_time
FROM
    (SELECT
        exam_id as exam_id,
        duration,
        release_time,
        sum(
            CASE
                WHEN rank1= 2 then costtime
                WHEN rank2= 2 then -costtime
                ELSE 0
            END
        )as sub
    FROM(
        SELECT
            a.exam_id,
            duration,
            release_time,
            TIMESTAMPDIFF(minute,start_time,submit_time) as costtime,
            ROW_NUMBER()OVER(PARTITION BY b.exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time)DESC)rank1 ,
            ROW_NUMBER()OVER(PARTITION BY b.exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time)ASC)rank2
        FROM examination_info a
        LEFT JOIN exam_record b
        ON a.exam_id = b.exam_id
        WHERE b.submit_time is not null
        )t1
    GROUP BY exam_id
    )t2
WHERE  sub*2 >=duration
ORDER BY exam_id DESC

• 创建sql表

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, 'C++', '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:51:01', 78),
(1001, 9002, '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:59:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9002, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:12:01', 84),
(1003, 9001, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null),
(1005, 9001, '2021-09-01 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null);

• 表的字段