LeetCode-数据库180（连续出现的数字）解题思路

题干：

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示：

SQL架构：

Create table If Not Exists Logs (id int, num int)
Truncate table Logs
insert into Logs (id, num) values ('1', '1')
insert into Logs (id, num) values ('2', '1')
insert into Logs (id, num) values ('3', '1')
insert into Logs (id, num) values ('4', '2')
insert into Logs (id, num) values ('5', '1')
insert into Logs (id, num) values ('6', '2')
insert into Logs (id, num) values ('7', '2')

示例：

 解题思路（看答案前）

阶段一

首先容易错误的理解为：只是统计出现了三次以上的Num

SELECT CASE WHEN COUNT
 (SELECT P1.`Num` FROM logs P1
                        WHERE P1.Num = P2.Num) =3
       THEN P2.Num ELSE END AS 'ConsecutiveNums'
 FROM logs P2 

这样提交显然是不通过的，题目要求的是不仅出现三次及以上，并且是要连续出现。

阶段二

接下来想到使用窗口函数，计算移动平均。（注意使用MySQL5.7以上版本）

如果每个Num和它后两个Num一起计算平均值，如果和它相等，那就是。

SELECT DISTINCT ConsecutiveNums -- 去除重复
FROM (SELECT
CASE
        WHEN AVG( Num ) OVER ( ORDER BY Id ROWS 2 PRECEDING ) = Num -- 计算每一行和它前两行的平均值
      AND Id >= 3 -- 前两行的移动平均不是三行的平均值不符合题意
         THEN
            Num ELSE NULL
            END AS 'ConsecutiveNums' 
FROM
    `logs`
 ) P
WHERE ConsecutiveNums IS NOT NULL -- 去除掉NULL值

这样貌似没什么问题，提交。

 

 果然是有问题，但是居然通过了18个测试用例，那就继续沿着这个思路进行下去。

初步分析存在以下三个明显的问题（对应图中序号）：

①题目没有说主键自增1的Id字段一定是从1开始的；

②求连续三个数的平均值明显不靠谱，把图中测试用例Id=3后两条数据的Num改为0和16，计算出来的平均值是8，和Id=3的Num相等，8最终会出现在结果中，但它并不是连续的三个8；

③前两行和后两行的窗口函数会出问题，可能会得到错误的结果。

阶段三

接下来准备继续沿着窗口函数的思路做下去，有以下几个思路，如果最后实在做不出来再看题解

①每三行distinct后求COUNT，等于1的就是？

②原方法筛第一次，剩余部分用其它方法解决（用三个绝对值的和不能等于Num*3？头尾的情况用LIMIT筛出来单独处理？），考虑全面后最终能得出所有正确结果

 

---

2022.04.08

如果有更好的方法，欢迎评论区一起交流，后续思路和代码会不断更新，最终会更新正确答案，并且会对不同的正确答案进行分析。

---

---

阶段四

沿着窗口函数的思路，经过查找MySQL函数大全，发现窗口函数lead（）非常适合解决这道题

窗口函数lead( )

# lead()语法
LEAD(<expression>[,offset[, default_value]]) OVER (
    PARTITION BY (expr)
    ORDER BY (expr)
)

offset 是从当前行向前行的行数，以获取值。offset 必须是一个非负整数。如果offset 为零，则 LEAD() 函数计算 expression当前行的值。如果省略 offset，则 LEAD() 函数默认使用一个。如果没有后续行，则 LEAD() 函数返回 default_value 。

例如，如果 offset 是1，则最后一行的返回值为 default_value 。如果您未指定 default_value，则函数返回 NULL。

partition by <分组的名字>

order by <用于排序的列名>

窗户函数正确解法：

使用lead将num列向上偏移一行记为num1，（已经在lead内指定偏移的字段num，后面over中的partition by和order by可填可不填）

将num列向上偏移两行记为num2，

因此只需要找到num=num1=num2的列即为连续重复三次及以上的列了

最后再distinct去重，即为最终ConsecutiveNums

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

阶段五

其它解法：可以用自连接的方法

但是这样效率非常低，不推荐。

 在Id是连续的情况下，经过两次自连接，找出Id自增1两次Num仍相等的数据，去重后即为ConsecutiveNums

SELECT
   DISTINCT l1.Num AS ConsecutiveNums
FROM
    Logs l1,Logs l2,Logs l3
WHERE
    l2.Id = l1.Id+1
    AND l3.Id = l2.Id+1
    AND l1.Num =L2.Num
    AND l2.Num =l3.NUm;