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

问题分析

1. 找出2021年至少有两天作答的用户
2. 计算每个用户连续两次作答的最大时间窗
3. 基于历史数据预测在这个时间窗内平均会做多少套试卷

版本1 

with-- 功能​：筛选2021年至少有两天作答的用户及其作答记录-- 子查询找出2021年至少有两天作答的用户ID（count(distinct date(start_time)) >= 2）-- 主查询获取这些用户在2021年的所有作答记录-- 结果包含三列：uid（用户ID）、exam_id（试卷ID）、start_time（作答日期）user_with_multiple_days as (selectuid,exam_id,date(start_time) as start_timefromexam_recordwhereuid in (selectuidfromexam_recordwhereyear(start_time) = 2021group byuidhavingcount(distinct date(start_time)) >= 2)and year(start_time) = 2021),-- 功能​：计算每个用户的日均作答量-- 通过自连接找出每个用户的所有作答日期组合（u1.start_time < u2.start_time）-- 计算每个用户的总作答次数（子查询中的count(start_time)）-- 计算最大日期跨度（max(datediff(u2.start_time, u1.start_time) + 1)）-- 用总作答次数除以最大日期跨度得到日均作答量（max_avg）max_avg_temp as (selectu1.uid,(selectcount(start_time)fromuser_with_multiple_dayswhereuid = u1.uid) / max(datediff(u2.start_time, u1.start_time) + 1) as max_avgfromuser_with_multiple_days u1join user_with_multiple_days u2 on u1.uid = u2.uidand u1.start_time < u2.start_timegroup byu1.uid),-- 功能​：计算每个用户连续两次作答的最大时间窗-- 使用窗口函数lag()获取每个用户的上一次作答日期-- datediff(start_time, lag(start_time)) + 1计算相邻两次作答的时间窗-- +1是因为间隔2天实际上是3天的窗口（如1号和3号是3-1=2天，但实际窗口是3天）-- max(days_window)找出每个用户的最大时间窗max_windows_temp as (selectuid,max(days_window) as days_windowfrom(selectuid,start_time,datediff(start_time,lag(start_time) over (partition byuidorder bystart_time asc)) + 1 as days_windowfromuser_with_multiple_days) as t1group byuid)
selectuid,days_window,round(days_window * max_avg, 2) as avg_exam_cnt
frommax_avg_tempjoin max_windows_temp using (uid)
order bydays_window desc,avg_exam_cnt desc

1. 第一个CTE：user_with_multiple_days

user_with_multiple_days as (selectuid,exam_id,date(start_time) as start_timefromexam_recordwhereuid in (selectuidfromexam_recordwhereyear(start_time) = 2021group byuidhavingcount(distinct date(start_time)) >= 2)and year(start_time) = 2021
)

​功能​：筛选2021年至少有两天作答的用户及其作答记录

​详细说明​：

• 子查询找出2021年至少有两天作答的用户ID（count(distinct date(start_time)) >= 2）
• 主查询获取这些用户在2021年的所有作答记录
• 结果包含三列：uid（用户ID）、exam_id（试卷ID）、start_time（作答日期）

2. 第二个CTE：max_avg_temp

max_avg_temp as (selectu1.uid,(selectcount(start_time)fromuser_with_multiple_dayswhereuid = u1.uid) / max(datediff(u2.start_time, u1.start_time) + 1) as max_avgfromuser_with_multiple_days u1join user_with_multiple_days u2 on u1.uid = u2.uidand u1.start_time < u2.start_timegroup byu1.uid
)

​功能​：计算每个用户的日均作答量

​详细说明​：

• 通过自连接找出每个用户的所有作答日期组合（u1.start_time < u2.start_time）
• 计算每个用户的总作答次数（子查询中的count(start_time)）
• 计算最大日期跨度（max(datediff(u2.start_time, u1.start_time) + 1)）
• 用总作答次数除以最大日期跨度得到日均作答量（max_avg）

3. 第三个CTE：max_windows_temp

max_windows_temp as (selectuid,max(days_window) as days_windowfrom(selectuid,start_time,datediff(start_time,lag(start_time) over (partition byuidorder bystart_time asc)) + 1 as days_windowfromuser_with_multiple_days) as t1group byuid
)

​功能​：计算每个用户连续两次作答的最大时间窗

​详细说明​：

• 使用窗口函数lag()获取每个用户的上一次作答日期
• datediff(start_time, lag(start_time)) + 1计算相邻两次作答的时间窗
  • +1是因为间隔2天实际上是3天的窗口（如1号和3号是3-1=2天，但实际窗口是3天）
• max(days_window)找出每个用户的最大时间窗

最终查询

selectuid,days_window,round(days_window * max_avg, 2) as avg_exam_cnt
frommax_avg_tempjoin max_windows_temp using (uid)
order bydays_window desc,avg_exam_cnt desc

​逻辑​：

• 将最大时间窗(days_window)乘以日均作答量(max_avg)
• 预测在该时间窗内平均会做多少套试卷(avg_exam_cnt)
• 按最大时间窗和预测作答量降序排序

简化1

待补充。。。