SQL题目实战---力扣篇3---连续问题180.（连续出现的数字)

先来看题目

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
在 SQL 中，id 是该表的主键。
id 是一个自增列。

找出所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

结果格式如下面的例子所示：

示例 1:

输入：
Logs 表：
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字。

表格代码如下

drop table Logs;

Create table  Logs (id int, num int);
Truncate table Logs;
insert into Logs (id, num) values ('1', '1');
insert into Logs (id, num) values ('2', '1');
insert into Logs (id, num) values ('3', '1');
insert into Logs (id, num) values ('4', '2');
insert into Logs (id, num) values ('5', '1');
insert into Logs (id, num) values ('6', '2');
insert into Logs (id, num) values ('7', '2');

select * from logs;

-- 需求: 找出所有至少连续出现三次的数字。
-- 返回的结果表中的数据可以按 任意顺序 排列。

此题是一道经典的连续问题,问连续出现的数字,那么我们遇到这种类型题目该如何去解呢?

给大家介绍一个解题套路 : 首先构造两个数列 , 分别是数列 1 和 数列 2 (这两个数列一定要有规律, 一般是等差数列), 再把他们两个相减得到一个新数列 , 这个新数列中重复出现数字的个数就是连续数字出现的最大连续数

我们接下来按照上述解题思路来分析一下 :

我们观察到 : 题目给的 logs 表中 , id 首先就是一个等差数列 , 那么我们的数列 1 就有了 , 现在如何构造 数列 2 , 并且让数列 2 有规律 , 注意 , 这里的数列不能是瞎构造的 , 一定要有规律 

这是我们就想起了窗口函数 , 把相同数字进行分组并且使用 row_number 进行排序 , 这样会构造出来一个有规律性的数列 

那我们就试试

select
    id, num,
    row_number() over (partition by num order by id) rank ,
    id - row_number() over (partition by num order by id) diff
from LOGS
order by id
;

从图中我们可以看出 , 我按照上述操作构造出了数列 2 , 也就是 rank 这一列 , 并且我让 数列1 也就是 id 列与之相减 得到了一个新的数列 diff 

我们发现 diff数列 前三个都是0 , 那么就证明 这三个0对应的数字连续出现了3次 , 也就是 1 连续出现了三次 , 到第四行的时候 diff 不再是 0 了 , 变成了 1 , 那么就证明 1 没有继续连续出现 , 一定是断了 , 我们一看 , 果不其然 , 第四行 出现的是 2 , 打破了 1 连续出现的情况

所以我们就计算 diff 这一列有多少个一样的数字 , 并且过滤一下相同数字次数大于 3 , 最后找到对应我们需要的数字即可得出答案

实现如下:

with t1 as (
    select
    id, num,
    row_number() over (partition by num order by id) rank ,
    id - row_number() over (partition by num order by id) diff
from LOGS
)
select
    distinct num ConsecutiveNums
     --,diff
    --count(1) l_appearence_times
from t1
group by num,diff
having count(1) >= 3

最终得到答案 , 注意 别忘记对最终结果去重 , 不然会有重复的编号出现 , 比如 1 在开头连续出现了4次 , 在中间连续出现了7次 ,如果不去重最终得到的数据就会有两个 1 , 这样的结果不能通过测试 !

那么本题的最后 , 给大家解释一点 , 大家可能会疑惑 , 原来题目就是找连续出现的一样的数字 , 最后两个数列相减之后还是找有几个一样的数字 , 那都是找一样的数字 , 为什么还要构造两个数列并且让他们两个相减呢 ?

答案就是 : 题目给的连续出现的数字 , 他前面出现了 , 后面可能还出现 , 比如说  1 1 1 2 1 1 1 , 这个 1 在后面也出现了 , 这时你就无法直接用分组聚合求 1 的个数 , 所以无法判断 1 到底连续出现了几次 

但是 , 两个数列作差之后的数字是独一无二的 , 比如 两个数列作差之后 1 后面对应的数字是  0  , 那么 如果后面 1 再连续出现 后面对应的数字就一定不是 0 了 , 所以根据这个唯一性 , 我们可以使用分组聚合来统计 新数列中 相同数字出现的个数 ! ! !

因为具有----- 唯 一 性  ! ! !

结尾欢迎大家点赞收藏加评论，后续会持续更新题目，无需力扣plus会员也能做题 ~ ~ ~