本题来自牛客：连续两次作答试卷的最大时间窗_牛客题霸_牛客网 (nowcoder.com)

题干信息：

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

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做了两张试卷，但是只有一天的作答记录，过滤掉。

测试数据


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

知识点讲解，开窗函数

避免字数过多，这里就不贴了，找到一篇比较不错的博客，大家需要的可以移步学习一下 zhuanlan.zhihu.com/p/514345120

聚合函数的开窗用法：

max(EXPR) over(partition by col1 order by col2)
min(EXPR) over(partition by col1 order by col2)
avg(EXPR) over(partition by col1 order by col2)
sum(EXPR) over(partition by col1 order by col2)
count(EXPR) over(partition by col1 order by col2)

题干信息补充

贴一下题干的内容

在计算avg_exam_cnt用到了两个参数，一个是最大时间窗，一个是最大间隔时间，一开始我没有意识到这里是两个参数，导致最终提交的代码不符合要求。两个参数的统计口径如下：用户1006分别在20210901、20210906、20210907作答过3次试卷，最大时间窗为6天（1号到6号），最大间隔时间是1号到7号这7天。

符合条件的用户为：2021年作答过试卷的，至少两天，这里不要求连续两天，即2021年有两天作答过即可。作答试卷指的是有start_time即为作答过试卷（这里有点坑，刚开始我以为要提交才算作答过）。

解题

求解最大时间窗

最大时间窗只能通过当前用户的两次作答的start_time相减得到，这里采用lag或lead函数都可以。


select
    uid, start_time,
    lag(start_time, 1, NULL) over(partition by uid order by start_time desc) as lag_time,
    date(start_time) as dt  -- 用于过滤掉不符合要求的用户，以及统计作答次数用
from exam_record where YEAR(start_time)=2021
;

结果如下：

求解题干各参数


-- 求最大的窗口时间的天数，以及相隔最大的天数，作答次数
select
    uid,
    count(dt) as cnt,  -- 作答次数
    max(datediff(lag_time, start_time))+1 as days_window,  -- 最大时间窗
    datediff(max(start_time), min(start_time))+1 as maxdt  -- 用户作答最大时间间隔（天）
from
    (select
        uid, start_time,
        lag(start_time, 1, NULL) over(partition by uid order by start_time desc) as lag_time,
        date(start_time) as dt
    from exam_record where YEAR(start_time)=2021) as t
group by uid
HAVING count(DISTINCT dt)>=2  -- 过滤掉作答天数不足2天的用户，因为存在一天作答多次的情况，需去重后统计
;

结果如下：

最终答案

对上述的临时表再求解一次均值即可

select
    uid,
    days_window,
    round(cnt/maxdt*days_window, 2) as avg_exam_cnt
from 
    (select
        uid,
        count(dt) as cnt,
        max(datediff(lag_time, start_time))+1 as days_window,
        datediff(max(start_time), min(start_time))+1 as maxdt
    from
        (select
            uid, start_time,
            lag(start_time, 1, NULL) over(partition by uid order by start_time desc) as lag_time,
            date(start_time) as dt
        from exam_record where YEAR(start_time)=2021) as t
    group by uid
    HAVING count(DISTINCT dt)>=2) as tb
order by days_window desc, avg_exam_cnt desc
;

每日SQL一练#2023102001