【SQL 06 连续两次作答试卷的最大时间窗】
题目描述
问题：
请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。
题目举列解释：用户1006分别在20210901、20210906、20210907作答过3次试卷，连续两次作答最大时间窗为6天（1号到6号），他1号到7号这7天里共做了3张试卷，平均每天3/7=0.428571张，那么6天里平均会做0.428571*6=2.57张试卷（保留两位小数）；
用户1005在20210905做了两张试卷，但是只有一天的作答记录，过滤掉。
试卷作答记录表 exam_record:
| 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 |
- uid：用户ID
- exam_id：试卷ID
- start_time：开始作答时间
- submit_time：交卷时间
- score：得分
最终输出样式：
| uid | days_window | avg_exam_cnt |
|---|---|---|
| 1006 | 6 | 2.57 |
题目分析
题目理解：
- 统计纬度：在2021年内，至少有两天作答过试卷的人：
- 2021年内：筛选start_time 在2021年内；
- 至少有两天作答过试卷的人：同一个uid，最大答题日期和最小答题日期间隔至少大于1天 - 统计指标：连续最大时间窗（days_window），平均作答套数
- 用户ID（uid）：按uid 分组
- 最大时间窗（days_window）：连续两次作答试卷的最大时间窗days_window
- 按最大时间窗计算的平均作答套数：（这些用户总共作答的次数/头尾作答时间窗）* 最大间隔天数 - 排序格式：
- 按最大时间窗和平均做答试卷套数倒序排序
实现方式：
- 统计纬度：
- 用YEAR()函数获取日期中年份作为筛选条件：WHERE YEAR(start_time) = 2021
- 最大答题日期和最小答题日期间隔至少大于1天：DATEDIFF(MAX(start_time)-MIN(start_time)) +1 > 1 - 统计指标：
- 最大时间窗（days_window）：连续两次作答试卷的最大时间窗days_window
1. 用LEAD() OVER()函数获得每次作答的下一次作答日期数据：LEAD(start_time, 1,NULL) OVER(PARTITION BY uid ORDER BY start_time) AS next_start_time
2. 找出最大的时间窗间隔：MAX(DATEDIFF(next_start_time,start_time))+1 AS days_window
- 按最大时间窗计算的平均作答套数：（这些用户总共作答的次数/头尾作答时间窗）* 最大间隔天数
3. 这些用户2021年总共作答的次数：COUNT(start_time) AS total_cnt
4. 头尾作答时间窗：DATEDIFF(MAX(start_time,MIN(start_time))+1 AS total_window
5. 最大间隔天数 = 最大时间窗（days_window）
6. 平均作答套数 =ROUND(total_cnt / total_window * days_windows,2) AS avg_exam_cnt - 排序格式：
-ORDER BY days_window DESC, avg_exam_cnt DESC
SQL实现
WITH t2 AS (
SELECT uid,COUNT(start_time) total_cnt, -- 用户2021年作答的次数DATEDIFF(MAX(start_time),MIN(start_time))+1 total_window, -- 头尾作答时间窗 MAX(DATEDIFF(next_start_time,start_time))+1 days_window -- 最大间隔天数
FROM (SELECT uid,start_time,LEAD(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_start_time -- 第二次作答时间FROM exam_recordWHERE YEAR(start_time) = 2021 -- 2021年的数据) t1
GROUP BY uid
)
SELECT uid,days_window,ROUND(total_cnt* days_window/total_window,2) avg_exam_cnt
FROM t2
WHERE total_window >1
ORDER BY days_window DESC,avg_exam_cnt DESC
;
Knowledge Point 知识点
1.DATEDIFF() 函数
定义：DATEDIFF() 函数返回两个日期之间的时间间隔。
语法：DATEDIFF(datepart,startdate,enddate);
startdate:开始日期
enddata:结束日期
datepart 参数可以是下表的值
| datepart name | datepart abbreviation |
|---|---|
| year | y, yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy |
| day | dd, d |
| week | wk, ww |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
2.LEAD() 函数（LAG() 函数）的窗口函数
定义：LEAD() （LAG() 函数）函数用于访问相同结果集的后（前）续行中的数据，代替自联接。 LEAD（LAG）以当前行之后（前）的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与后续行中的值进行比较。
语法：
LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
LAG( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
scalar_expression
要根据指定偏移量返回的值。 这是一个返回单个（标量）值的任何类型的表达式。 scalar_expression 不能为分析函数。offset
从在其中获取值的当前行前移的行数。 如果未指定，则默认值为 1。 offset 可以是列、子查询或其他表达式，它们的计算值为正整数，或可隐式转换为 bigint。 offset 不能是负数值或分析函数。default
偏移量超出分区范围时返回的值。 如果未指定默认值，则返回 NULL。 default 可以是列、子查询或其他表达式，但它不能是分析函数。 default 的类型与 scalar_expression 的类型必须兼容。OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause 将 FROM 子句生成的结果集划分为要应用函数的分区 。 如果未指定，则此函数将查询结果集的所有行视为单个组。 order_by_clause 在应用函数之前确定数据的顺序 。 当指定 partition_by_clause 时，它确定每个分区中数据的顺序。 需要 order_by_clause 。
Reference
牛客网练习
Microsoft SQL LEAD()官方指南
Microsoft SQL LAG()官方指南
Microsoft SQL DATEDIFF()官方指南
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！