SQL学习笔记(29连续两次作答试卷的最大时间窗-lead/lag)

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

描述

现有试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):
在这里插入图片描述
请计算在2021年至少有两天作答过试卷的人中,计算该年连续两次作答试卷的最大时间窗days_window,那么根据该年的历史规律他在days_window天里平均会做多少套试卷,按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下:
在这里插入图片描述
解释:用户1006分别在20210901、20210906、20210907作答过3次试卷,连续两次作答最大时间窗为6天(1号到6号),他1号到7号这7天里共做了3张试卷,平均每天3/7=0.428571张,那么6天里平均会做2.57张试卷(保留两位小数);
用户1005在20210905做了两张试卷,但是只有一天的作答记录,过滤掉。

示例
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);
解答
  1. 用lead / lag函数在原表基础上加上下一次 / 上一次考试的时间,时间要求在2021年,表名记作start_next_time
  2. 在第一张表start_next_time基础上计算两次考试间最大时间差,所有考试时间的最大时间差,考试次数,以uid汇总,表名记作diff_time
  3. 在第二张表diff_time基础上计算days_window,avg_exam_cnt,按照条件选出考试两天以上的人,并按要求排序

注: 以日为单位的时间相减用DATEDIFF(max_time, min_time)

SELECT diff_time.uid, days_window, ROUND(n / days * days_window, 2)  as avg_exam_cnt
FROM(
    SELECT start_next_time.uid, max(DATEDIFF(next_time, start_time))+1 as days_window, 
            DATEDIFF(max(next_time) , min(start_time))+1 as days,
            count(*) as n
    FROM(
        SELECT uid, start_time, lead(start_time)over(partition by uid order by start_time) as next_time
        from exam_record
        where year(start_time) = 2021
        ) start_next_time
    GROUP BY uid
    ) diff_time
WHERE days_window > 1 
ORDER BY days_window DESC, avg_exam_cnt DESC
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值