窗口函数实战（三）

本篇文章是笔者在牛客网上摘选的几道比较有挑战性的SQL窗口函数编程题。

1.近三个月未完成试卷数为0的用户完成情况【中等】（原题链接）

首先来分析一下题目的查询要求。

• 用户近三个有试卷作答记录的月份（可以用窗口函数解决）。
• 并且这三个月中，不能有试卷是未完成状态
• 统计这三个月的试卷完成数

下面先给出一种解法。

with a as (
    select uid, exam_id, start_time, score,
           dense_rank() over (partition by uid order by date_format(start_time,'%Y-%m') desc) as start_time_rank
    from exam_record
)
select uid, count(score) as exam_complete_cnt
from a
where uid not in (
    select uid
    from (
        select *
        from a
        where start_time_rank <= 3
    ) b
    where score is null
) and start_time_rank <= 3
group by uid
order by exam_complete_cnt desc, uid desc

可以看到，上面这种解法在寻找满足条件的 uid 比较麻烦。其实，一个 having count(score) = count(uid) 就可以解决。

select uid, count(score) as exam_complete_cnt
from (
    select uid, start_time, score,
           dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) as start_time_rank
    from exam_record
) a
where start_time_rank <= 3
group by uid
having count(score) = count(uid)
order by exam_complete_cnt desc, uid desc

2.连续两次作答试卷的最大时间窗【较难】（原题链接）

我们来分析一下题目的要求。

• 2021年，至少有 2 天作答过试卷的用户。
• 该年，连续两次作答试卷的最大时间窗 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 张试卷（保留两位小数）；

下面给出一种解法。

with a as (
    select uid, start_time,
           DATEDIFF( LAG(start_time, 1) over ( partition by uid order by start_time DESC ), start_time ) + 1 as days_win
    from exam_record
    where year(start_time) = 2021
)
select uid, days_window, round(days_window * avg_exam, 2) as avg_exam_cnt
from (
    select uid, max(days_win) as days_window,
           count(start_time) / (DATEDIFF(max(start_time),min(start_time)) + 1) as avg_exam
    from a
    group by uid
    having days_window >= 2
) b
order by days_window desc, avg_exam_cnt desc

• 首先利用 LAG 函数得到上一次答题的时间；
• 再利用 DATEDIFF 得到两次答题之间的窗口大小（注意 +1）；
• 剩下的计算就比较简单了。

3.第二快/慢用时之差大于试卷时长一半的试卷【较难】（原题链接）

题目的要求很简单：找到 第二快和第二慢用时之差 大于 试卷时长的一半 的试卷信息，按试卷ID降序排序。

以下是我给出的一种解法。

SELECT distinct exam_id, duration, release_time
from (
    select exam_id, duration, release_time, 
           nth_value(duration_time, 2) over (partition by exam_id order by duration_time desc) as max2_duration_time,
           nth_value(duration_time, 2) over (partition by exam_id order by duration_time) as min2_duration_time
    from (
        select exam_record.exam_id, duration, release_time, 
               (unix_timestamp(submit_time) - unix_timestamp(start_time)) / 60 as duration_time
        from exam_record
        left join examination_info using(exam_id)
        where submit_time is not null
    ) a
) b
where max2_duration_time is not null and min2_duration_time is not NULL
      and (max2_duration_time - min2_duration_time) > 0.5 * duration
order by exam_id desc

另外给出一种解法。

SELECT exam_id, duration, release_time
from (
    select exam_id, duration, release_time,
           sum( case when duration_time_asc = 2 then duration_time 
                     when duration_time_desc = 2 then -duration_time 
                     else 0 end ) as sub
    from (
        select exam_record.exam_id, duration, release_time, 
               timestampdiff(second, start_time, submit_time) as duration_time,
               row_number() over (partition by exam_id order by timestampdiff(second, start_time, submit_time) desc) as duration_time_asc,
               row_number() over (partition by exam_id order by timestampdiff(second, start_time, submit_time)) as duration_time_desc
        from exam_record
        left join examination_info using(exam_id)
        where submit_time is not null
    ) a
    group by exam_id
) b
where sub > duration * 60 * 0.5
order by exam_id desc

• 对比第一种方法用的 nth_value，上面这种做法使用的是 row_number。
• 在计算 第二快和第二慢用时之差 的处理也不一样。

4.每类试卷得分前3名【中等】（原题链接）

此题难度不大，要求也不复杂：找到每类试卷得分的前 3 名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择 uid 大者。

有作答得分记录的试卷 tag 有 SQL 和算法，SQL 试卷用户 1001、1002、1003、1004 有作答得分，最高得分分别为 81、81、89、85，最低得分分别为 78、81、86、40，因此先按最高得分排名再按最低得分排名取前三为 1003、1004、1002。

select * 
from (
    select tag as tid, uid,
           row_number() over (partition by tag order by tag, max(score) desc, min(score) desc, uid desc) as ranking
    from exam_record
    left join examination_info on exam_record.exam_id = examination_info.exam_id
    group by examination_info.tag, exam_record.uid
) a
where ranking <=3