180. 连续出现的数字
难度中等
SQL架构
表:Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示:
Logs 表:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
Result 表:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 是唯一连续出现至少三次的数字。
解法一:
通过自表连接找到当前id的下一个和下下一个进行连接,然后通过条件判断获取当前的num等于下面连这两个的num的num值,然后进行去重操作,这样就获得了连续出现三次的num,这种写法没有考虑id不连续,以及若出现n次连续的情况,可用性不高
代码示例:
select distinct a.Num as ConsecutiveNums from logs as a
left join logs as b on a.id = b.id+1
left join logs as c on a.id = c.id+2
where a.Num = b.Num and a.Num = c.Num;
解法二:
1、首先考虑若id不连续的情况出现的话我们就需要多查询一列来构造连续的一列,这里可以用到窗口函数 row_number()
select *,row_number() over (order by id) as new_id from Logs;
这样可以实现若id不连续,通过查询构建一个连续的列new_id,具体实现如下表
id | num | new_id |
---|---|---|
1 | 2 | 1 |
3 | 2 | 2 |
4 | 3 | 3 |
7 | 3 | 4 |
8 | 3 | 5 |
9 | 2 | 6 |
2、然后在通过分组查询将num一致的列分组排序查询
select *,row_number over (partition by Num order by id) as new_order from Logs;
运行结果如下表
id | num | new_order |
---|---|---|
1 | 2 | 1 |
3 | 2 | 2 |
9 | 2 | 3 |
4 | 3 | 1 |
7 | 3 | 2 |
8 | 3 | 3 |
sql示例:
select *,row_number() over(order by id) as new_id,
row_number() over (partition by Num order by id) as new_order
from Logs;
运行结果如下表:
id | num | new_id | new_order |
---|---|---|---|
1 | 2 | 1 | 1 |
3 | 2 | 2 | 2 |
9 | 2 | 6 | 3 |
4 | 3 | 3 | 1 |
7 | 3 | 4 | 2 |
8 | 3 | 5 | 3 |
将这两次查询联合起来我们可以发现规律:
用new_id减去new_order的值只要相同,那么他们就是连续的。也就是:如果一个num连续出现时,那么它出现的[真实序列]-它出现的次数一定是个定值。解题思路:第一步就是首先获取他出现的真实序列,第二步就是获取它出现的次数,然后下一步就是它们之间相减,减完之后若值相同,那么他们一定是连续的。
3、[真实序列]-它出现的次数
select *,
row_number() over(order by id) - row_number() over (partition by Num order by id) as sta,
from Logs;
运行结果表:
id | num | sta |
---|---|---|
1 | 2 | 0 |
3 | 2 | 0 |
9 | 2 | 3 |
4 | 3 | 2 |
7 | 3 | 2 |
8 | 3 | 2 |
可以看出有两个连续的数,一个是id为1、3的两个连续,另一个是4、7、8三个连续,根据数据可以看出没有问题,那么我们就可以通过这个来找出连续出现n次的数据。
select distinct Num as ConsecutiveNums
from
(
select id,Num,
row_number() over (order by id) - row_number() over (partition by Num order by id) as sta from Logs
) as Sub group by Num , sta having count(1)>=3
在这里 查询 子查询查询出来的结果集,通过Num分组,连续出现的次数如果超过三次则将改Num查询出来,并进行去重操作,若要查询出现n次的数据只需要将having条件中的count(1)条件改为大于n即可。
运行结果:
ConsecutiveNums : 1
总结:
第一种解法比较容易理解和实现,但是具有一定的局限性。
第二种解法通过规律解题,实现相比来说比较复杂,但是实用性更强。