https://leetcode.com/problems/consecutive-numbers/description/
Table: Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
In SQL, id is the primary key for this table.
id is an autoincrement column starting from 1.
Find all numbers that appear at least three times consecutively.
Return the result table in any order.
The 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.
문제 자체는 간단해 보이는데 생각보다 구현하기 어려워서 당황했다.
처음에 이렇게 DINSTINCT를 빼 먹어서 또 틀렸었는데
꼭 DISTINCT를 사용하는 것을 습관화 하자...
SELECT 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
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
문제는 이렇게 풀었지만 이 함수는 ID에 하나라도 결측값이 있으면 오류가 나기 때문에 Lead 와 Lag function을 사용 하는 게 더 안전하다.
LEAD와 lag 를 활용한 Query.
SELECT DISTINCT num as ConsecutiveNums FROM ( SELECT num, LEAD(num, 1) OVER(ORDER BY id) AS ld, LAG(num, 1) OVER (ORDER BY id) AS lg FROM logs )t WHERE num=ld and num=lg;