连续两次作答试卷的最大时间窗

id	uid	exam_id	start_time	submit_time	score
1	1006	9003	2021-09-07 10:01:01	2021-09-07 10:21:02	84
2	1006	9001	2021-09-01 12:11:01	2021-09-01 12:31:01	89
3	1006	9002	2021-09-06 10:01:01	2021-09-06 10:21:01	81
4	1005	9002	2021-09-05 10:01:01	2021-09-05 10:21:01	81
5	1005	9001	2021-09-05 10:31:01	2021-09-05 10:51:01	81

请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下：

uid	days_window	avg_exam_cnt
1006	6	2.57

解释：用户1006分别在20210901、20210906、20210907作答过3次试卷，连续两次作答最大时间窗为6天（1号到6号），他1号到7号这7天里共做了3张试卷，平均每天3/7=0.428571张，那么6天里平均会做0.428571*6=2.57张试卷（保留两位小数）；

用户1005在20210905做了两张试卷，但是只有一天的作答记录，过滤掉。

示例1

输入:

drop table if exists exam_record;
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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:02', 84),
(1006, 9001, '2021-09-01 12:11:01', '2021-09-01 12:31:01', 89),
(1006, 9002, '2021-09-06 10:01:01', '2021-09-06 10:21:01', 81),
(1005, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 81),
(1005, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81);

输出:

1006|6|2.57

原站题解

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

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

SELECT uid,max(day1)+1 days_window,
round((count(*)/(sum(day1)+1))*(max(day1)+1),2) avg_exam_cnt
from(
select uid,date1,lag1,datediff(date1,lag1) day1
from(
select uid,date(start_time) date1,
lag(date(start_time),1,date(start_time)) 
over(partition by uid order by start_time) lag1
from exam_record where uid in(
select uid from exam_record where year(start_time)=2021
group by uid having count(distinct date(start_time))>1) and year(start_time)=2021) a) b
group by uid
order by days_window desc,avg_exam_cnt desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6476KB, 提交时间: 2021-12-14

SELECT uid, days_window, ROUND(days_window * exam_cnt / diff_days, 2) as avg_exam_cnt
FROM (
    SELECT uid,
        count(start_time) as exam_cnt,  -- 此人作答的总试卷数
        DATEDIFF(max(start_time), min(start_time))+1 as diff_days, -- 最早一次作答和最晚一次作答的相差天数
        max(DATEDIFF(next_start_time, start_time))+1 as days_window -- 两次作答的最大时间窗
    FROM (
        SELECT uid, exam_id, start_time,
            lead(start_time) over(
                PARTITION BY uid ORDER BY start_time) as next_start_time -- 将连续的下次作答时间拼上
        FROM exam_record
        WHERE YEAR(start_time)=2021
    ) as t_exam_record_lead
    GROUP BY uid
) as t_exam_record_stat
WHERE diff_days > 1
ORDER BY days_window DESC, avg_exam_cnt DESC

Mysql 解法, 执行用时: 36ms, 内存消耗: 6548KB, 提交时间: 2021-12-27

SELECT uid, MAX(DATEDIFF(a,start_time))+1 days_window,
       ROUND(COUNT(start_time)*(MAX(DATEDIFF(a,start_time))+1)/(DATEDIFF(MAX(start_time),MIN(start_time))+1),2) avg_exam_cnt
FROM
(SELECT uid, start_time,
       LEAD(start_time) OVER(PARTITION BY uid
                             ORDER BY start_time) a
FROM exam_record
WHERE YEAR(start_time)=2021) t1
GROUP BY uid
HAVING MAX(DATEDIFF(a,start_time)) > 0
ORDER BY days_window DESC, avg_exam_cnt DESC

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

with t as(
    select uid, exam_id, date_format(start_time, '%Y%m%d') as d,
    row_number() over(partition by uid order by date_format(start_time, '%Y%m%d')) as r
    from exam_record 
    where uid in (select uid
                  from exam_record group by uid 
                  having count(distinct date_format(start_time, '%Y%m%d')) >= 2
                  and year(start_time) =2021
                 )
    )

select t1.uid, max(timestampdiff(day, t1.d, t2.d)+1) as days_window,
round(t3.av*max(timestampdiff(day, t1.d, t2.d)+1),2) as avg_exam_cnt
from t as t1 join t as t2 on t1.uid = t2.uid and t2.r = t1.r + 1
join (
    select uid, 
    count(exam_id)/(timestampdiff(day,min(date_format(start_time, '%Y%m%d')) ,max(date_format(start_time, '%Y%m%d')))+1) as av
    from exam_record
    where uid in(select uid
                  from exam_record group by uid 
                  having count(distinct date_format(start_time, '%Y%m%d')) >= 2
                  and year(start_time) =2021
                 )
    group by uid      
    )t3 on t1.uid = t3.uid 
group by t1.uid
order by days_window desc, avg_exam_cnt desc

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

#筛选出2021年至少两天作答试卷的人
#计算最大时间窗口得到days_window
#计算平均作答试卷数，保留两位小数
#排序



select b.uid, b.time_window as days_window,
round(b.exam_num / b.days * b.time_window, 2) as avg_exam_cnt
from


(select a.uid, count(a.start_time) as exam_num, 
max(datediff(a.next_day, a.start_time)) + 1 as time_window,
datediff(max(a.start_time), min(a.start_time)) + 1 as days
from
(select uid, start_time,
lead(start_time) over(partition by uid order by start_time) as next_day
from exam_record
where year(start_time) = 2021) as a
group by uid) as b

where b.time_window >= 2
order by days_window desc, avg_exam_cnt desc

上一题

下一题

详情