[SQL : INNER JOIN , WINDOW FUNCTION ] LeetCode: 180. Consecutive Numbers

180. Consecutive Numbers

---

>> 문제

+-------------+---------+
| 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:

Logs table:
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

Result table:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
1 is the only number that appears consecutively for at least three times.

 

>> 오답 풀이

SELECT L1.NUM AS ConsecutiveNums 
FROM Logs L1
    INNER JOIN Logs L2 on L1.ID = L2.ID - 1 
    INNER JOIN Logs L3 on L1.ID = L3.ID - 2 
WHERE L1.NUM = L2.NUM AND L2.NUM = L3.NUM AND L1.NUM = L3.NUM

- 연속된 3개의 숫자만 찾을 때는 가능하지만, 3개이상을 찾고 싶을 때 이렇게 문제를 풀면 안된다.. 

- 4개 이상일때, 값들이 중복해서 나오기 때문이다.

 

>> 정답 풀이

SELECT DISTINCT L1.NUM AS ConsecutiveNums 
FROM Logs L1
    INNER JOIN Logs L2 on L1.ID = L2.ID - 1 
    INNER JOIN Logs L3 on L1.ID = L3.ID - 2 
WHERE L1.NUM = L2.NUM AND L2.NUM = L3.NUM AND L1.NUM = L3.NUM

- 이것을 해결하는 간단한 방법은 그냥 DISTINCT 를 사용하는 것이다. 

: 그렇다면 중복값들이 제거되서 원하는 숫자 하나를 뽑아줄 수 있게 된다.

 

 

>> window 함수를 사용한 다른 풀이

SELECT DISTINCT l.num as ConsecutiveNums
FROM (
    SELECT Num
    , LEAD( Num , 1 ) OVER ( ORDER BY Id ) as next
    , LEAD( Num , 2 ) OVER ( ORDER BY Id ) as afternext
FROM logs
) l
WHERE l.num = l.next and l.next = l.afternext 

 

leetcode.com/problems/consecutive-numbers/submissions/

Consecutive Numbers - LeetCode