180. Consecutive Numbers

題目敘述

SQL Schema >

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')

Table: Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id is the primary key for this table.

Write an SQL query to find all numbers that appear at least three times consecutively.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Output:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
Explanation: 1 is the only number that appears consecutively for at least three times.

題目翻譯

從資料表 Logs 中找出連續出現至少三次的數值。以範例來說可以看到數值 2 雖然有三筆，但是並不是連續的(id 不是連續，中間差了一個 1)，因此不算在答案。

解法解析

這題有兩個解法，我比較喜歡第二種的解法。第一種解法是官方給的，利用 Multiple Table 合併大資料表後再利用 id 和 num 做篩選，因為是結合一個大表之後才做篩選，覺得效能上不是很好。第二種解法則是使用了 LEAD 函數，其敘述可以參考這篇文章，簡單說的話就是可以查到當前資料的附近資料。 解法上就是另外 LEAD 拿到 num0 的下一行資料 num 和下下一行的資料 num。

解法範例

MySQL

# Write your MySQL query statement below
SELECT
    DISTINCT l1.num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.id = l2.id - 1
    AND l2.id = l3.id - 1
    AND l1.num = l2.num
    AND l2.num = l3.num;

Runtime

# Write your MySQL query statement below
SELECT
    DISTINCT r.num0 AS ConsecutiveNums
FROM
    (
        SELECT
            num AS num0,
            LEAD(num, 1) OVER() AS num1,
            LEAD(num, 2) OVER() AS num2
        FROM
            logs
    ) AS r
WHERE
    r.num1 = r.num2
    AND r.num0 = r.num1