mysql之窗口函数练习

🍊今天复习一下mysql中的窗口函数，主要是通过几道练习题复习和加深一下对窗口函数的理解，对往期内容感兴趣的同学可以参考如下内容👇:

• 链接: 牛客SQL大厂真题——某音短视频.
• 链接: 京东数据分析SQL面试题.
• 链接: 百度用户增长SQL面试题.

🌰话不多说，让我们开始今日份的学习吧。

1. 窗口函数

MySQL中的窗口函数（Window Functions）是一种用于计算和分析数据集中的子集的函数，这些函数在计算聚合值时可以对数据进行分组、排序、过滤等操作。它们与GROUP BY语句不同，GROUP BY语句只能进行一次分组，而窗口函数可以根据不同的条件进行多次分组。

窗口函数可以使用OVER子句指定分组、排序和窗口的范围。通常情况下，窗口函数可以分为三类：排名函数、聚合函数和分析函数。

1.1 排序函数

排序函数用于计算数据集中某个值在排序后的位置或排名。在MySQL中，常见的排序函数包括：

• RANK()：计算排名，并且当值相同时会出现“并列排名”。
• DENSE_RANK()：计算排名，如果有并列排名则会跳过排名，下一个排名不会重复。
• ROW_NUMBER()：计算每行的行号。

1.2 聚合函数

聚合函数用于对分组数据进行计算，例如计算分组中的平均值、总和、最大值和最小值等。常见的聚合函数包括：

• SUM()：计算分组中所有数值的总和。
• AVG()：计算分组中所有数值的平均值。
• COUNT()：计算分组中的记录数。
• MAX()：计算分组中所有数值的最大值。
• MIN()：计算分组中所有数值的最小值。

1.3分析函数

分析函数用于在保持数据集原有排序的情况下计算某个值。常见的分析函数包括：

• LAG()：返回指定行之前的某一行。
• LEAD()：返回指定行之后的某一行。
• FIRST_VALUE()：返回第一个值。
• LAST_VALUE()：返回最后一个值。
• NTILE()：将分组划分为相同大小的桶，返回桶的编号。

窗口函数提供了更灵活、更高效的数据分析功能，可以帮助我们更好地理解数据，找到数据中的趋势和规律。

2. 窗口函数练习

本次来做几道比较有意思的sql题，题目来源于牛客网，这几道题的通过率大多都在30%以下，于是自己亲自做了尝试，觉得很有参考价值。
链接: 进阶篇的窗口函数练习

2.1 每类试卷得分前3名

描述：有两张表，分别为试卷信息表examination_info，试卷作答记录表exam_record，如下：


需要找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。由示例数据结果输出如下：

题目的详细链接: 题目详情

解题思路：我们还是对最终的答案进行一步一步拆解，首先找到用户在某一门考试下的最小分数和当前排名，第二个子查询查找出某一个用户在某一门考试下的最高分，也就每一门考试只保留一个用户的最高分，最后再根据要求取前3名排序输出即可。

select
    tmp2.tag,
    tmp2.uid,
    tmp2.rk
from
    (
        select
            tmp1.exam_id,
            tmp1.tag,
            tmp1.uid,
            row_number() over (partition by tmp1.tag order by
tmp1.score desc,tmp1.min_score desc,tmp1.uid desc) rk
        from
            (
                select
                    t1.exam_id,
                    t2.tag,
                    t1.uid,
                    t1.score,
                    min(t1.score) over (partition by t1.uid,t2.tag) min_score,
                    row_number() over (partition by t1.uid,t2.tag order by t1.score desc
                    ) max_score
                from
                    exam_record t1
                    left join examination_info t2 on t1.exam_id = t2.exam_id
            ) tmp1
        where
            tmp1.max_score = 1
    ) tmp2
where
    tmp2.rk <= 3

2.2 第二快/慢用时之差大于试卷时长一半的试卷

题目描述：现在有两张表：试卷信息表examination_info，试卷作答记录表exam_record


找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。由示例数据结果输出如下：

题目链接: 详细连接
解题思路：我们主要是需要取出第二快和第二慢，于是我们在第一个字查询里面给做题快（时间升序）和慢（时间倒序）打上标记，在第二个子查询里面选出排名为2的数据，则可以选出第二快与第二慢，最后分组筛选出符合条件的数据即可。

select
    tmp1.exam_id,
    tmp1.duration,
    tmp1.release_time
from
    (
        select
            t1.exam_id,
            t1.start_time,
            t1.submit_time,
            t2.duration,
            t2.release_time,
            timestampdiff (second, start_time, submit_time) diff_time,
            row_number() over (
                partition by
                    exam_id
                order by
                    timestampdiff (minute, start_time, submit_time) desc
            ) quick_rk,
            row_number() over (
                partition by
                    exam_id
                order by
                    timestampdiff (minute, start_time, submit_time)
            ) slow_rk
        from
            exam_record t1
            left join examination_info t2 on t1.exam_id = t2.exam_id
        where
            t1.submit_time is not null
    ) tmp1
where
    quick_rk = 2
    or slow_rk = 2
group by
    tmp1.exam_id,
    tmp1.duration,
    tmp1.release_time
having
    (max(tmp1.diff_time) - min(diff_time)) / 60 > tmp1.duration / 2
order by
    tmp1.exam_id desc

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

描述：现有试卷作答记录表exam_record

需要计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下：

题目链接: 详细连接
解题思路：这块很多同学会想到自连接，其实不到迫不得，不要使用自连接，会影响查询销量，这里我们使用lead函数，可以取下一条数据，这样就可以实现自连接的效果。

select 
tmp1.uid,
max(tmp1.diff_day)+1 days_window,
round(count(1)*(max(tmp1.diff_day)+1)/(datediff(max(start_day),min(start_day))+1),2) avg_exam_cnt
from (
select 
uid,
date(start_time) start_day,
ifnull(lead(date(start_time))over(partition by uid order by start_time),date(start_time))next_day,
datediff(ifnull(lead(date(start_time))over(partition by uid),date(start_time)),date(start_time)) diff_day
from exam_record t1
where year(start_time)='2021'
)tmp1
group by tmp1.uid
having days_window>1
order by days_window desc,avg_exam_cnt desc

2.4 近三个月未完成试卷数为0的用户完成情况

描述：现有表试卷作答记录表exam_record

需要找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名。由示例数据结果输出如下：

题目链接: 详细连接
解题思路：这道题需要判断进3个月和月份中是未完成状态，因此我们需要首先对未完成的试卷进行打标，然后根据月份进行排序，这样我们就得到了一个带有是否完成试卷标记以及排序的表，最后我们根据uid进行分组，选出完成标记与表中所有数据行相等的uid说明该用户都完成了试卷，再选择前三即可。

select
tmp1.uid,count(tmp1.start_time) exam_complete_cnt

from(
select *,
case when submit_time is null then 0 else 1 end sub_tag ,
dense_rank()over(partition by uid order by date_format(start_time,'%Y-%m') desc) mon_rk
from exam_record
)tmp1
where  tmp1.mon_rk<4 
group by tmp1.uid
having sum(sub_tag)=count(1)
order by exam_complete_cnt desc ,tmp1.uid desc

2.5 未完成率较高的50%用户近三个月答卷情况

这道题是我觉得最难的，我用了一些自连接的方式来解决该问题。
该题的描述：有户信息表user_info、试卷信息表examination_info、试卷作答记录表exam_record



需要计算SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。

题目链接: 详细连接
解题思路：这道题需要解决前50%如何计算？主要有几种思路：1.count出所有的数据，计算行数，然后计算中间值。2. 窗口函数计算行数求中间值。而思路是首先计算出每个用户的完成率，后续再对完成率进行排序，塞选出满足条件的用户，因此第一个临时表计算的就是每个用户的完成率，然后在主体中进行排序，根据max（排序的序号）函数计算50%的值mid，然后用排序的序号与mid进行比较，大于等于mid的就是前50%。

with user_tab as(
select
uid,tag,level,
row_number()over(order by ok_rate desc) rk
from(
select
uid,tag,level,
count(case when tmp1.tag='sql' and tmp1.sub_tag=1 then 1 else null end)/count(case when tmp1.tag='sql' then 1 else null end) ok_rate
from
(
select
t1.uid,
t1.exam_id,
start_time,
t2.tag,
t3.level,
case when submit_time is null then 0 else 1 end sub_tag,
case when t2.tag ='SQL'  then 1 else 0 end sql_tag
from exam_record t1
left join examination_info t2
on t1.exam_id=t2.exam_id
left join user_info t3
on t1.uid=t3.uid
)tmp1
group by uid,tag,level
having tag='sql'
)tmp2
)



select
uid,start_mon,count(*),count(sub_tag2)
from(
select uid,date_format(start_time,'%Y%m') start_mon,
case when submit_time is null then null else submit_time end sub_tag2,
dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc ) rk2
from exam_record
where uid in (
select uid
from 
(
select  uid,tag,level,rk,max(rk)over(partition by null)/2 bi_rk
from  user_tab

)tab1
where rk>bi_rk and level in (6,7)))tab2
where rk2<=3
group by uid,start_mon
order by uid,start_mon

2.6 试卷完成数同比2020年的增长率及排名变化

描述：有试卷信息表examination_info、作答记录表exam_record


题目链接: 详细连接
需要计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。

解题思路：这里我们需要先筛选出2020年和2021年上半年的答题记录，然后计算该时间段内答题的数据，接着利用leader函数选出同一tag下不同年份的数据，最后计算相关值即可。（这里需要注意两个rk相减出现负数会报错，需要转换类型。）

select
tmp3.tag,
tmp3.next_num exam_cnt_20,
tmp3.sub_tag exam_cnt_21,
concat(round((tmp3.sub_tag-tmp3.next_num)/tmp3.next_num*100,1),'%') growth_rate,
tmp3.next_rk exam_cnt_rank_20,
tmp3.rk1 exam_cnt_rank_21,
cast(tmp3.rk1 as signed)-cast(tmp3.next_rk as signed)
from(
select
*,
lead(sub_tag)over(partition by tmp2.tag order by start_year desc) next_num,
lead(rk1)over(partition by tmp2.tag order by start_year desc) next_rk
from
    (
        select
            tmp1.tag,
            start_year,
            sub_tag,
            rank() over (partition by start_year order by
sub_tag desc
            ) rk1
        from
            (
                select
                    t2.tag,
                    year (t1.start_time) start_year,
                    count(
                        case when submit_time is null then null else t1.uid end
                    ) sub_tag
                from
                    exam_record t1
                    left join examination_info t2 on t1.exam_id = t2.exam_id
                where
                    t1.submit_time is not null
                    and month (start_time) < 6 and year(start_time)in (2020,2021)
                group by
                    t2.tag,
                    year (t1.start_time)
            ) tmp1
    ) tmp2
)tmp3
where tmp3.next_num is not null and tmp3.next_rk is not null
order by growth_rate desc,exam_cnt_rank_21 desc

3. 总结

这些题目都是自己写的，都很经典，大家有其他的解题思路可以一起分享。