第二快/慢用时之差大于试卷时长一半的试卷

现有试卷信息表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、时长、发布时间。

示例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, '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);

输出:

9001|60|2021-09-01 06:00:00

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6388KB, 提交时间: 2022-02-08

SELECT distinct exam_id, duration, release_time
FROM 
(SELECT exam_id, duration, release_time,
 sum(case when rank1= 2 then costtime when rank2= 2 then -costtime else 0 end ) as sub
 from(
SELECT exam_id,duration, release_time,
TIMESTAMPDIFF(minute,start_time, submit_time) as costtime,
row_number() over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time, submit_time) desc) as rank1,
row_number() over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time, submit_time) asc)  as rank2
FROM exam_record
JOIN examination_info USING (exam_id)
 where submit_time is not null ) a 
 group by exam_id ) b 
 where sub * 2 >= duration
 order by exam_id desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6404KB, 提交时间: 2022-01-25

select DISTINCT a.exam_id,a.duration,a.release_time
from
(select e.exam_id,e.duration,TIMESTAMPDIFF(second,start_time,submit_time) as times,e.release_time,
nth_value(TIMESTAMPDIFF(second,start_time,submit_time),2) over (partition by er.exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) desc ) as low_2,
nth_value(TIMESTAMPDIFF(second,start_time,submit_time),2) over (partition by er.exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) asc) as fast_2
FROM exam_record er join  examination_info e on er.exam_id = e.exam_id and er.submit_time IS NOT NULL
) a

where low_2-fast_2>duration*30
order by a.exam_id desc;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6372KB, 提交时间: 2021-12-16

select 
ei.exam_id, 
ei.duration, 
ei.release_time
from
(
	select t2.exam_id, t3.used_time-t2.used_time as time_difference
	from (
		select *
		from (
			select *, 
			row_number() over (partition by exam_id order by used_time asc) as row_num
			from (
				select *, 
				TIMESTAMPDIFF(second, start_time, submit_time) as used_time 
				from exam_record where submit_time is not null
				) as t0  -- 找出成功提交的试卷信息，以及用时（秒）
			) as t1 -- 按照用时长短，升序排列
		where row_num = 2
		) as t2 -- 第二快的试卷id、开始/提交时间、用时
	join (
		select *
		from (
			select *, 
			row_number() over (partition by exam_id order by used_time desc) as row_num
			from (
				select *, 
				TIMESTAMPDIFF(second, start_time, submit_time) as used_time 
				from exam_record where submit_time is not null
				) as t0
			) as t1
		where row_num = 2
		) as t3 -- 第二慢的试卷id、开始/提交时间、用时
	on t2.exam_id = t3.exam_id
) as t4
join examination_info as ei
on t4.exam_id = ei.exam_id
where t4.time_difference > duration * 30
order by ei.exam_id desc;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6376KB, 提交时间: 2022-01-28

with second as (select exam_id,second_time
                from (
                        select exam_id,
                               timestampdiff(minute,start_time,submit_time) second_time,
                               row_number() over (partition by exam_id order by timestampdiff(minute,start_time,submit_time)) ranking
                        from exam_record
                        where score is not null
                       ) ranktable_time
where ranking = 2),
     second_last as (select exam_id,second_last_time
                from (
                        select exam_id,
                               timestampdiff(minute,start_time,submit_time) second_last_time,
                               row_number() over (partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) ranking
                        from exam_record
                        where score is not null
                       ) ranktable_time_last
where ranking = 2)

select exam_id,duration,release_time
from second sd 
    join second_last sd_l using(exam_id)
    join examination_info ei using(exam_id)
where (second_last_time - second_time)>= duration/2 
order by exam_id DESC

Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2021-12-19

SELECT 
    distinct t1.exam_id,
    duration,
    release_time
    FROM
    (
    SELECT er.exam_id,duration,release_time,
    NTH_VALUE(TIMESTAMPDIFF(minute,start_time,submit_time),2) over(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(minute,start_time,submit_time) DESC) AS head_2,
    NTH_VALUE(TIMESTAMPDIFF(minute,start_time,submit_time),2) over(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(minute,start_time,submit_time) ASC) AS end_2
    FROM examination_info ei
    LEFT JOIN exam_record er
    USING(exam_id)
    ) t1
    WHERE head_2-end_2 > duration*0.5
    ORDER BY exam_id desc

上一题

下一题

详情