| mysql之窗口函数练习 | 您所在的位置：网站首页 › 排名函数并列 › mysql之窗口函数练习 |
mysql之窗口函数练习
|
🍊今天复习一下mysql中的窗口函数，主要是通过几道练习题复习和加深一下对窗口函数的理解，对往期内容感兴趣的同学可以参考如下内容👇: 链接: 牛客SQL大厂真题——某音短视频.链接: 京东数据分析SQL面试题.链接: 百度用户增长SQL面试题.🌰话不多说，让我们开始今日份的学习吧。 目录 1. 窗口函数1.1 排序函数1.2 聚合函数1.3分析函数 2. 窗口函数练习2.1 每类试卷得分前3名2.2 第二快/慢用时之差大于试卷时长一半的试卷2.3 连续两次作答试卷的最大时间窗2.4 近三个月未完成试卷数为0的用户完成情况2.5 未完成率较高的50%用户近三个月答卷情况2.6 试卷完成数同比2020年的增长率及排名变化 3. 总结 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%以下，于是自己亲自做了尝试，觉得很有参考价值。 链接: 进阶篇的窗口函数练习
描述：有两张表，分别为试卷信息表examination_info，试卷作答记录表exam_record，如下：
解题思路：我们还是对最终的答案进行一步一步拆解，首先找到用户在某一门考试下的最小分数和当前排名，第二个子查询查找出某一个用户在某一门考试下的最高分，也就每一门考试只保留一个用户的最高分，最后再根据要求取前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 tmp1.duration / 2 order by tmp1.exam_id desc 2.3 连续两次作答试卷的最大时间窗描述：现有试卷作答记录表exam_record 描述：现有表试卷作答记录表exam_record |
需要找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。由示例数据结果输出如下：
题目的详细链接: 题目详情
需要计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下： 
题目链接: 详细连接 解题思路：这块很多同学会想到自连接，其实不到迫不得，不要使用自连接，会影响查询销量，这里我们使用lead函数，可以取下一条数据，这样就可以实现自连接的效果。
需要找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名。由示例数据结果输出如下： 
题目链接: 详细连接 解题思路：这道题需要判断进3个月和月份中是未完成状态，因此我们需要首先对未完成的试卷进行打标，然后根据月份进行排序，这样我们就得到了一个带有是否完成试卷标记以及排序的表，最后我们根据uid进行分组，选出完成标记与表中所有数据行相等的uid说明该用户都完成了试卷，再选择前三即可。【本文地址】