Leetcode 180. 连续出现的数字
题目描述:
表: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 是唯一连续出现至少三次的数字。
解题思路:
1.假设ID是连续的
2.在Num列使用partition by 进行分区 并结合row_nuber() 在同一个区内编号
3.ID列-row_number()同一区内编号的结果相同
4.根据Num列和3中得到的列进行分组,返回组中次数>3的结果
实际操作:
0.建表
1.为满足第一个假设重新构造一个字段以防止出现ID不连续的现象
select row_number() over(order by l.Id) as newId,l.Id,l.Num
from logs as l
2.根据Num字段重新分区,为区内字段重新编号
select row_number() over(order by l.Id) as newId,l.Id,l.Num,row_number() over(partition by l.Num order by l.Id) as flag
from logs as l;
3.newId列-flag列查看结果,若连续则返回的结果应该相同
select row_number() over(order by l.Id) as newId,l.Id,l.Num, row_number() over(partition by l.Num order by l.Id) as flag,
row_number() over(order by l.Id)-row_number() over(partition by l.Num order by l.Id) as flag1
from logs as l;
4.根据Num和flag1进行分组,返回组内个数>n的Num
select nl.Num
from (
select * , row_number() over(order by l.Id)-row_number() over(partition by l.Num order by l.Id) as flag
from logs as l
) as nl
group by nl.Num,nl.flag
having count(*)>=3
关于row_number() 参见:
https://blog.csdn.net/beginerToBetter/article/details/116068001
关于 partition by ; group by 参见:
https://blog.csdn.net/beginerToBetter/article/details/116074988