题解 138 | #连续两次作答试卷的最大时间窗#
【场景】：时间窗
【分类】：专用窗口函数、嵌套子查询
分析思路
难点：
1.如何求窗口期：使用lag()或者lead()，再分组求最大值
2.作答试卷不去重
（1）统计2021年至少有两天作答过试卷的人
-
[条件]：where year(start_time) = 2021 group by uid having count(date(start_time)) >= 2
-
[使用]：count()、max()、min()
（2）得到平均每天作答试卷数、当前行日期和当前行的前一行日期
- [使用]：timestampdiff(day,min_date,max_date);
当前行的前一行日期
- [使用]：lag(date,1) over(partition by uid order by date) 对应 timestampdiff(day,pre_date,date)
当前行的后一行日期
- [使用]：lead(date,1) over(partition by uid order by date) 对应timestampdiff(day,date,latter_date)
（3）计算avg_exam_cnt，按最大时间窗和平均做答试卷套数倒序排序
得到平均每天作答试卷数、当前行日期和当前行的前一行日期的差求最大值，得到days_window和平均每天作答试卷数
-
[使用]： max(timestampdiff(day,pre_date,date))+1
-
[使用]：order by days_window desc,avg_exam_cnt desc
最终结果
select 查询结果 [用户ID;最大时间窗;平均做答试卷套数]
from 从哪张表中查询数据[嵌套from子查询]
where 查询条件 [空窗期不为空]
order by 按照指定条件排序 [按最大时间窗和平均做答试卷套数倒序排序]
扩展
前往查看： MySQL 窗口函数
求解代码
方法一
with子句+一步步拆解
with
temp as(
#2021年至少有两天作答过试卷的人
select
uid,
count(date(start_time)) as cnt_date,
max(date(start_time)) as max_date,
min(date(start_time)) as min_date
from exam_record
where year(start_time) = 2021
group by uid having count(date(start_time)) >= 2
)
,temp1 as(
#得到平均每天作答试卷数、当前行日期和当前行的前一行日期
select
uid,
cnt_date/(timestampdiff(day,min_date,max_date)+1) as avg_exam,
date,
lag(date,1) over(partition by uid order by date) as pre_date
from temp
join(
select distinct
uid,
date(start_time) as date
from exam_record
where year(start_time) = 2021
) attr using(uid)
)
,temp2 as(
#统计平均每天试卷数、最大时间窗
#当前行日期和当前行的前一行日期求差的最大值为days_window
select
uid,
max(avg_exam) as avg_exam,
max(timestampdiff(day,pre_date,date))+1 as days_window
from temp1
group by uid
)
#计算avg_exam_cnt，按最大时间窗和平均做答试卷套数倒序排序。
select
uid,
days_window,
round(avg_exam*days_window,2) as avg_exam_cnt
from temp2
where days_window is not null
order by days_window desc,avg_exam_cnt desc
优化代码
with
main as(
#2021年至少有两天作答过试卷的人
select
uid,
count(date(start_time)) as cnt_date,
max(date(start_time)) as max_date,
min(date(start_time)) as min_date
from exam_record
where year(start_time) = 2021
group by uid having count(date(start_time)) >= 2
)
,main1 as(
#得到平均每天作答试卷数、当前行日期和当前行的前一行日期
select
uid,
cnt_date/(timestampdiff(day,min_date,max_date)+1) as avg_exam,
date,
lag(date,1) over(partition by uid order by date) as pre_date
from main
join(
select distinct
uid,
date(start_time) as date
from exam_record
where year(start_time) = 2021
) attr using(uid)
)
#计算avg_exam_cnt，按最大时间窗和平均做答试卷套数倒序排序。
select
uid,
max(timestampdiff(day,pre_date,date))+1 as days_window,
round(max(avg_exam)*(max(timestampdiff(day,pre_date,date))+1),2) as avg_exam_cnt
from main1
where pre_date is not null
group by uid
order by days_window desc,avg_exam_cnt desc
方法二
from嵌套子查询
#计算avg_exam_cnt，按最大时间窗和平均做答试卷套数倒序排序。
select
uid,
max(timestampdiff(day,pre_date,date))+1 as days_window,
round(max(avg_exam)*(max(timestampdiff(day,pre_date,date))+1),2) as avg_exam_cnt
from(
#得到平均每天作答试卷数、当前行日期和当前行的前一行日期
select
uid,
cnt_date/(timestampdiff(day,min_date,max_date)+1) as avg_exam,
date,
lag(date,1) over(partition by uid order by date) as pre_date
from(
#2021年至少有两天作答过试卷的人
select
uid,
count(date(start_time)) as cnt_date,
max(date(start_time)) as max_date,
min(date(start_time)) as min_date
from exam_record
where year(start_time) = 2021
group by uid having count(date(start_time)) >= 2
) main
join(
select distinct
uid,
date(start_time) as date
from exam_record
where year(start_time) = 2021
) attr using(uid)
) main1
where pre_date is not null
group by uid
order by days_window desc,avg_exam_cnt desc
