180.连续出现的数字

题目描述

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
在 SQL 中，id 是该表的主键。
id 是一个自增列。

找出所有至少连续出现三次的数字。

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

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

示例 1:

输入：
Logs 表：
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字。

SQL Schema

Drop table If Exists Logs;
Create table If Not Exists Logs
(
    id  int,
    num int
);
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');

答案

Note

前提条件：由官方给出的测试用例可以推断出，id 如果不连续的话，即使 num 是连续的也不作数！如下所示：

| id | num |
| -- | --- |
| 1  | 1   |
| 2  | 1   |
| 4  | 1   |
| 5  | 1   |
| 6  | 2   |
| 7  | 1   |

解法一：三表联查

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1
 INNER JOIN Logs l2 ON l1.id = l2.id - 1 AND l1.num = l2.num
 INNER JOIN Logs l3 ON l2.id = l3.id - 1 AND l2.num = l3.num;

解法二：排名窗口函数

利用 ROW_NUMBER() 排名窗口函数：

• 如果 num 是连续的话，num 所对应的 id 和 rank 值都是同时加一，因此它们之间的差值肯定是一个定值；
• 如果 num 不是连续的话，num 所对应的 id 和 rank 值之间的差值会不相同。

因此，针对 num, (id - rank) 进行分组，然后使用聚合函数 COUNT(*) 取出大于等于 3 的 num 即可。

SELECT DISTINCT num AS ConsecutiveNums
FROM (SELECT num,
             id - CAST(ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS SIGNED) AS `diff`
      FROM Logs) t
GROUP BY num, diff
HAVING COUNT(*) >= 3;

解法三：取值窗口函数

利用 LAG() 和 LEAD() 取值窗口函数，如果 num 是连续的话，则当前行的 num 值与上下两行的 num 值应该相等。

对于该解法，即使 id 不连续，只要 num 是连续的则认为 num 是连续的。与咱们提出的前提条件不符（与官方其中一个测试用例相悖），因此该解法无法通过提交。

SELECT DISTINCT num AS ConsecutiveNums
FROM (SELECT num,
          LAG(num, 1, NULL) OVER (ORDER BY id)  AS num1,
          LEAD(num, 1, NULL) OVER (ORDER BY id) AS num2
   FROM Logs) t
WHERE num = num1
AND num = num2;