LeetCode：(DataBase)Consecutive Numbers

題目連結：https://leetcode.com/problems/consecutive-numbers/

題意簡單粗暴，要找到連續出現三次的數字。

以下圖為例，

1符合條件，2雖然出現三次但不連續。

另外結果輸出不重複，就算1連續出現6次，還是只有一個輸出結果。

初始想法

1. 使用 window function，找到前一格和前兩格的數值。
2. 如果數值和前一格還有前兩格都相同，則輸出
3. 使用 DISTINCT過濾重複數值。

WITH get_1_and_2_lags AS (
    SELECT
        *,
        LAG(num,1)OVER(ORDER BY id) AS lag_1,
        LAG(num,2)OVER(ORDER BY id) AS lag_2
    FROM
        Logs
)
SELECT DISTINCT
    num AS ConsecutiveNums
FROM
    get_1_and_2_lags
WHERE
    num = lag_1
    AND num = lag_2

檢討初始解法

其實沒什麼好檢討的（誤

1. 萬一要檢視連續出現 50次的數字，就會很麻煩，換句話說解法比較不通用。

參考他人解法

WITH get_row_number AS (
    SELECT 
        id,
        num, 
        CAST(row_number() over (order by num, id) AS SIGNED) AS row_num
    FROM 
        Logs
    ORDER BY
        id,
        num
),
id_minus_row_number AS (
    SELECT
        num,
        id - row_num AS user_defined_rank
    FROM
        get_row_number
),
get_consecutive_count AS (
    SELECT
        num,
        user_defined_rank,
        COUNT(CONCAT(num,"-",user_defined_rank)) AS consecutive_count
    FROM
        id_minus_row_number
    GROUP BY
        num,
        user_defined_rank
)
SELECT DISTINCT
    num AS ConsecutiveNums
FROM
    get_consecutive_count
WHERE
    consecutive_count >= 3

這個解法真的堪稱完美，不論要找連續出現幾次的都不怕。

範例題目與解答說名

解法說明

1. 針對 num再對 id做排序，由小到大，所以1會先被排完，然後是2。
2. 計算 id-num與其對應的 count(user_defined_rank)。