【MYSQL】连续出现的数字

最新推荐文章于 2024-06-13 23:07:49 发布

Aubye

最新推荐文章于 2024-06-13 23:07:49 发布

阅读量355

点赞数

分类专栏： SQL 文章标签： mysql 数据库

原文链接：https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649250661&idx=1&sn=b017344c701fbfa02a87a88a1a2207cd&chksm=835fd355b4285a43d6c55c593f83bbc7aea1bb370df8f52210bb3b3f7f5c5f304c272d863a04&token=546838497&lang=zh_CN#rd

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1 题目介绍

题目来自力扣180，在拼多多笔试中出现过
原文章https://leetcode.cn/problems/consecutive-numbers/
在这里插入图片描述
在这里，主要强调的是连续出现的，例题中1重复出现了4次，但是只有3次是连续的，2出现了3次，但是因为最高的连续次数为2，所以不加入计数中

2 题目解析

2.1 自连接

什么是连续出现3次？
假设“学号”是按顺序排列的（如果不是，可以使用增加一列，让学号是按序号顺序排列的），所以每一学号与上一学号相差1。例如下图的3个学号是连续学号，他们之间的关系是：
某一学号（0002）=下一位的学号（0003）-1
下一位学号（0003）=下下位学号（0004）-1
在这里插入图片描述
如果这3个连续学号的成绩相等，就是题目要求的“至少连续出现3次的成绩”。
利用“自连接（自身连接）“的思路

也就是说，原理是这3个子表中有至少三个连续的学号，且这三个学号对应的成绩是相同的，则会取出来。在题目中，表述的是至少连续出现3次及以上的数字，所以建立三个子表，如果是连续出现2次及以上，则建立两个子表。如果出现至少连续10个，则这种计算方式比较慢，且比较繁琐。

select distinct a.成绩 as 最终答案#注意使用distinct取出唯一值
from score as a,
   score as b,
   score as c;
 where a.学号 = b.学号 - 1
   and b.学号 = c.学号 - 1
   and a.成绩 = b.成绩
   and b.成绩 = c.成绩;

在本题目中，代码为

# Write your MySQL query statement below
select distinct a.Num as ConsecutiveNums
from Logs as a,
   Logs as b,
   Logs as c
where a.Id = b.Id - 1
and b.Id = c.Id - 1
and a.Num = b.Num
and b.Num = c.Num

2.2 窗口函数

两只篮球队进行了激烈的比赛，比分交替上升。比赛结束后，你有一张两队分数的明细表，该表记录了球队、球员号码、球员姓名、得分分数以及得分时间。现在球队要对比赛中表现突出的球员做出奖励。请你写一个sql语句统计出，连续三次（及以上）为球队得分的球员名单
在这里插入图片描述
连续三次（及以上）为球队得分的球员名单，用大白话翻译这句话就是：找出【每个球队】里为该球队连续三次（及以上）得分的球员【姓名】。
因为该问题是“连续问题”，也就是得分连续三次以上是指比赛按得分时间从前到后排序。所以要用窗口函数，先根据球队分组，再按得分时间排序。
例如，下图按球队分组后，再按照得分时间降序排序后，我们可以看出，A队中的A1球员，B队中的B3球员，其姓名均连续出现3次。
在这里插入图片描述

select *,
       rank() over(partition by 球队 
                   order by 得分时间) as 排名
from 分数表;

在这里这么做窗口函数，是需要按球队分组，然后根据得分时间排序，从而找到连续得分的球员，排名是为了标注其得分是连续行为。
在这里插入图片描述
接下来，需要找到连续出现3次及以上的值，如果我们将第1列“球员姓名”向上错位1行到第2列，向上错位2行到第3列，那么原本第1列连续的3个值会到同一行中去。例如下图，第1列三个连续A1值，现在到了同一行。
在这里插入图片描述
经过这种变化以后，此时我们只需要一个where子句限制三列的值相等，就可以筛选出连续出现三次的球员姓名。
那么，如何用SQL实现上述错位两列的效果呢？
可以用窗口函数lag或者lead：

向上窗口函数lead：取出字段名所在的列，向上N行的数据，作为独立的列
向下窗口函数lag：取出字段名所在的列，向下N行的数据，作为独立的列
lag(字段名,N,默认值) over(partion by …order by …)
lead(字段名,N,默认值) over(partion by …order by …)
默认值是指，当向上N行或者向下N行值时，如果已经超出了表行和列的范围时，会将这个默认值作为函数的返回值，若没有指定默认值，则返回Null。

下图是用向上窗口函数lead，得到球员姓名向上1行的列（第2列），因为A1向上1行超出了表行列的范围，所以这里对应的值就是默认值（不设置默认值就是null）。
在这里插入图片描述

select 球员姓名,
       lead(球员姓名,1) over(partition by 球队 
                            order by 得分时间) as 下一项
from 分数表;

下图是用向下窗口函数lag，得到球员姓名向下1行的列（第2列）
在这里插入图片描述
对应的SQL语句如下：

select 球员姓名,
       lag(球员姓名,1) over(partition by 球队 
                           order by 得分时间) as 上一行
from 分数表;

根据前面的分析，我们要得到球员姓名向上1行，和向上2行的值，也就是：
在这里插入图片描述

select 球员姓名,
       lead(球员姓名,1) over(partition by 球队 order by 得分时间) as 姓名1, #n=1，向上一行
       lead(球员姓名,2) over(partition by 球队 order by 得分时间) as 姓名2 #n=2，向上两行
from 分数表;

在这里插入图片描述
完成上面工作，现在就可以使用where子句筛选出三个值都相同的行，也就是球员姓名 = 姓名1 and 球员姓名 = 姓名2。
但是需要注意，不能直接在上述步骤后加入where子句。因为根据SQL的运行顺序，会先运行from和where子句，再运行select子句。
因此姓名1和姓名2两列要最后运行select时才会出现，我们需要用子查询来解决，同时最后的球员姓名需要去重（disitinct）。

select distinct 球员姓名
from(
select 球员姓名,
lead(球员姓名,1) over(partition by 球队 order by 得分时间) as 姓名1,
lead(球员姓名,2) over(partition by 球队 order by 得分时间) as 姓名2
from 分数表
) as a
where (a.球员姓名 = a.姓名1 and a.球员姓名 = a.姓名2);

这个也是3次及以上就平移两次，如果是2次及以上就平移 1次，如果次数比较多的话比较繁琐。在开头的题目中实现方法如下

select distinct Num as ConsecutiveNums
from(
    select Num, lead(Num,1) over(order by Id) as Num1,
    lead(Num,2) over(order by Id) as Num2
    from Logs
)as a
where a.Num = a.Num1 and a.Num1 = a.Num2