【sql】leetcode.180连续出现的数字

编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+
| Id | num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

一、比较法
找出每个数字它的后三个ID的num和它都相同的。

select distinct a.num as ConsecutiveNums
from Logs as a, Logs as b, Logs as c
where a.id = b.id-1 and b.id = c.id-1 and a.num = b.num and a.num = c.num

二、窗口函数
加入第二列复制第一列num并向上移一行，加入第二列复制第一列num并向上移两行，最后比较每行的三个数是否相同即可。

select distinct num as ConsecutiveNums
from (
select num, lead(num,1)over()as num1,lead(num,2)over()as num2 from Logs
)as a
where a.num = a.num1 and a.num = a.num2

lead(field, num, defaultvalue) : field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。
lead函数与over()函数一起使用。

三、自定义变量
记录当前数字连续出现的次数，选出多于三次的数字。

select distinct num as ConsecutiveNums
from ( select num, if(@pre = num, @cou := @cou+1, @cou:=1) as numcount, @pre := num
from Logs,(select @pre:= null,@count:=0)
)as a
where numcount >=3

四、ID不连续及出现多余三次的情况
首先，将num值一样的按照ID大小进行内部排序，即row_number() over(partition by num order by id)。
其次用原来的id减去新的ID如果是连续且相等，则得出的数字是相同的。
再根据num值和得出的数字进行分组，选出组内个数超过3个的。

select distinct num as ConsecutiveNums
from(
	select num,count(*) as cou
	from(
		select id,num,row_number()over(order by id)-row_number() over(partition by num order by id) as newid
		from Logs
	)as a
	group by num, newid
)as b
where cou>=3