题目
思路
-
首先对id开一次窗,目的是防止id可能出现不连续的情况,更新ID。
-
再根据Nu分组,按照ID开一次窗,目的是把Num相同的放到一起,再看看是否出现连续情况
- 把上面两个放到一起,IdNum和GroupNum的区别在于是否对Num进行了分组(题目本身是把id看做连续标准,但是这个题解把id不连续但是主键连续当做了标准,更加严谨一点)也就是说就针对这道题,其实可以忽略IdNum。
4. 做出它们两个的差值,如果是连续的话,那么差值应该是恒定不变的,于是可以对差值和Num做group by,就可以了
代码
# Write your MySQL query statement below
SELECT
distinct Num as ConsecutiveNums
FROM
(
SELECT
Num,
count(1) as c
FROM
(
SELECT
Id,
Num,
# row_number() over(
# order by Id
# )as IdNum,
# row_number() over(
# partition by Num
# order by Id
# )as GroupNum
row_number() over(
order by Id
)-row_number() over(
partition by Num
order by Id
)as chazhi
FROM
Logs
) as t1
GROUP BY
Num,chazhi
Having
c>=3
) as t2