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

待補充。。。