每日SQL一练#2023101901

saberbin
74 阅读8分钟

这题来自牛客的sql在线练习，原链接：第二快慢用时之差大于试卷时长一半的试卷_牛客题霸_牛客网 (nowcoder.com)

这里描述一下题干： 现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++hard602021-09-01 06:00:00
39003算法medium802021-09-01 10:00:00

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

iduidexam_idstart_timesubmit_timescore
1100190012021-09-01 09:01:012021-09-01 09:51:0178
2100190022021-09-01 09:01:012021-09-01 09:31:0081
3100290022021-09-01 12:01:012021-09-01 12:31:0181
4100390012021-09-01 19:01:012021-09-01 19:59:0186
5100390022021-09-01 12:01:012021-09-01 12:31:5189
6100490022021-09-01 19:01:012021-09-01 19:30:0185
7100590012021-09-01 12:01:012021-09-01 12:31:0285
8100690012021-09-07 10:01:012021-09-07 10:21:0184
9100390012021-09-08 12:01:012021-09-08 12:11:0140
10100390022021-09-01 14:01:01(NULL)(NULL)
11100590012021-09-01 14:01:01(NULL)(NULL)
12100390032021-09-08 15:01:01(NULL)(NULL)

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

exam_iddurationrelease_time
9001602021-09-01 06:00:00

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

测试用数据：


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);

解答

这里用两种sql代码解答，但是思路都是一样的：

  • 查询出每种试卷第二快和第二慢所用的时间（分钟粒度），然后计算出两者的差
  • 上述结果与examination_info做join，求解并过滤出符合要求的数据行

方法一

-- 这个方法只用到了row_number窗口函数，其他的都是join操作了，比较简单，但是就是会非常绕，分步没那么容易出错。
-- 找到每份试卷作答第二慢的用户信息
select
    t.exam_id,
    t.timediff,
    row_number() over(partition by t.exam_id order by t.timediff desc) as slow_rn
from
    (select
        exam_id, 
        TIMESTAMPDIFF(MINUTE, start_time, submit_time) as timediff
    from exam_record where submit_time is not null) as t
;
-- 找到每份试卷作答第二快的用户信息
select
    t.exam_id,
    t.timediff,
    row_number() over(partition by t.exam_id order by t.timediff) as fast_rn
from
    (select
        exam_id, 
        TIMESTAMPDIFF(MINUTE, start_time, submit_time) as timediff
    from exam_record where submit_time is not null) as t
;

-- 求出第二快与第二慢的时间差
select
    t2.exam_id,
    t2.timediff - t4.timediff as diff
from 
    (select
        t1.exam_id,
        t1.timediff,
        row_number() over(partition by t1.exam_id order by t1.timediff desc) as slow_rn
    from
        (select
            exam_id, 
            TIMESTAMPDIFF(MINUTE, start_time, submit_time) as timediff
        from exam_record where submit_time is not null) as t1) as t2
inner join
    (select
        t3.exam_id,
        t3.timediff,
        row_number() over(partition by t3.exam_id order by t3.timediff) as fast_rn
    from
        (select
            exam_id, 
            TIMESTAMPDIFF(MINUTE, start_time, submit_time) as timediff
        from exam_record where submit_time is not null) as t3) as t4
on t2.exam_id=t4.exam_id where t2.slow_rn=2 and t4.fast_rn=2
;


-- 查询出符合要求的数据
select
    t5.exam_id, t6.duration, t6.release_time
from
    (select
        t2.exam_id,
        t2.timediff - t4.timediff as diff
    from 
        (select
            t1.exam_id,
            t1.timediff,
            row_number() over(partition by t1.exam_id order by t1.timediff desc) as slow_rn
        from
            (select
                exam_id, 
                TIMESTAMPDIFF(MINUTE, start_time, submit_time) as timediff
            from exam_record where submit_time is not null) as t1) as t2
    inner join
        (select
            t3.exam_id,
            t3.timediff,
            row_number() over(partition by t3.exam_id order by t3.timediff) as fast_rn
        from
            (select
                exam_id, 
                TIMESTAMPDIFF(MINUTE, start_time, submit_time) as timediff
            from exam_record where submit_time is not null) as t3) as t4
    on t2.exam_id=t4.exam_id where t2.slow_rn=2 and t4.fast_rn=2) as t5
inner join
    (select exam_id, duration, release_time, duration/2 as duration_time from examination_info) as t6
on t5.exam_id=t6.exam_id
where t5.diff>=t6.duration_time
-- 这里需要注意的是题干描述是大于，但是如果是大于是无法通过测试的，必须是大于等于才能，牛客经典操作题干描述与实际需求不匹配
order by t5.exam_id desc 
;

方法二

方法二的思路也是一样的，只是在求解第二快和第二慢的时间差这里的思路不同。 将第二快和第二慢两个时间在同一个表中求解出

select
        t.exam_id,
        t.timediff,
        row_number() over(partition by t.exam_id order by t.timediff desc) as slow_rn,
        row_number() over(partition by t.exam_id order by t.timediff) as fast_rn
    from
        (select
            exam_id, 
            TIMESTAMPDIFF(MINUTE, start_time, submit_time) as timediff
        from exam_record where submit_time is not null) as t;

image.png

可以发现，slow_rn、fast_rn都为2的时候可取得两值，再用max及min可以将两个值放在同一行，即可求得两者之差。

select exam_id, max(timediff) as slow_time, min(timediff) as fast_time, max(timediff)-min(timediff) as diff
from
    (select
        t.exam_id,
        t.timediff,
        row_number() over(partition by t.exam_id order by t.timediff desc) as slow_rn,
        row_number() over(partition by t.exam_id order by t.timediff) as fast_rn
    from
        (select
            exam_id, 
            TIMESTAMPDIFF(MINUTE, start_time, submit_time) as timediff
        from exam_record where submit_time is not null) as t) as t2
where slow_rn=2 or fast_rn=2
group by exam_id
;

image.png

那么后续只要将这个中间结果表与examination_info表进行join即可

select t4.exam_id, t4.duration, t4.release_time
from
    (select exam_id, max(timediff)-min(timediff) as diff
    from
        (select
            t.exam_id,
            t.timediff,
            row_number() over(partition by t.exam_id order by t.timediff desc) as slow_rn,
            row_number() over(partition by t.exam_id order by t.timediff) as fast_rn
        from
            (select
                exam_id, 
                TIMESTAMPDIFF(MINUTE, start_time, submit_time) as timediff
            from exam_record where submit_time is not null) as t) as t2
    where slow_rn=2 or fast_rn=2 group by exam_id) as t3
inner join examination_info as t4
on t3.exam_id=t4.exam_id
where t3.diff>=(t4.duration*0.5)
-- 这里需要注意的是题干描述是大于，但是如果是大于是无法通过测试的，必须是大于等于才能，牛客经典操作题干描述与实际需求不匹配
order by t4.exam_id desc
;

这里用的思路都比较简单，因为个人技术积累不太够。牛客题目的讨论区及解答区会更加优雅的解法，可以去学习一下。

avatar
文章
阅读
粉丝