表:Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示:
示例 1:
输入:
Logs 表:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
输出:
Result 表:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
解释:1 是唯一连续出现至少三次的数字。
答案:
思路
1.由统计重复出现次数,我们会直接联想到 count(),距离题目要求只差一个连续的场景。故我们需要在此基础上进一步判断是否为连续出现。
2.判断是否连续,若考虑id不连续的情况,光凭题目中的id就不够用了,我们可利用row_num函数按照id 升序对其进行连续排序,row_number() over(order by id) 记为rank1
3.统计num的重复数据,自然需要对num进行分组统计,同样对其进行排名 row_number() over(partition by Num order by Id) 记为rank2
SELECT Id,Num,
row_number() over(order by id) rank1,
row_number() over(partition by Num order by Id) rank2
FROM Logs
结果为
| Id | Num | rank1 | rank2 |
| -- | --- | ----- | ----- |
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 2 |
| 3 | 1 | 3 | 3 |
| 5 | 1 | 5 | 4 |
| 4 | 2 | 4 | 1 |
| 6 | 2 | 6 | 2 |
| 7 | 2 | 7 | 3 |
- 此时可以发现对于num连续重复出现的场景,num本身,以及rank1和rank2的差值为恒定不变的,利用goup by和 having可以筛选出这部分数据.
SELECT Num,COUNT(1) FROM (SELECT Id,Num,row_number() over(order by id) - row_number() over(partition by Num order by Id) as rankSub FROM Logs) Sub GROUP BY Sub.Num,Sub.rankSub HAVING COUNT(1) >= 3 #连续N次则替换为N
不过这部分数据了会包含一些在多个地方连续重复出现的数字,所以需要去重。
答案:
# Write your MySQL query statement below
SELECT DISTINCT t.num as ConsecutiveNums FROM (
SELECT Num,COUNT(1) FROM
(SELECT Id,Num,
row_number() over(order by id) -
row_number() over(partition by Num order by Id) as rankSub
FROM Logs) Sub
GROUP BY Sub.Num,Sub.rankSub HAVING COUNT(1) >= 3 #连续N次则替换为N
) t;
结语: 小编能力有限 欢迎大家多多指正
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/consecutive-numbers
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。