[MySQL] 연속으로 N번이상 반복되는 값 구하기

테이블과 결과

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.

문제

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

답

-- 내가 푼 방법
SELECT t1.first AS 'ConsecutiveNums'
FROM 
(
    SELECT 
        l.num AS 'first'
        ,LEAD(l.num, 1, 0) OVER(ORDER BY l.id) AS 'second'
        ,LEAD(l.num, 2, 0) OVER(ORDER BY l.id) AS 'third'
    FROM Logs l
) AS t1
WHERE t1.first = t1.second
AND t1.second = t1.third
;

--정답
SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l1.Id = l2.Id - 1 AND l1.Num = l2.Num
JOIN Logs l3 ON l1.Id = l3.Id - 2 AND l1.Num = l3.Num
;


-- 추천
SELECT DISTINCT l.num as ConsecutiveNums
FROM Logs l
WHERE (l.id + 1, l.num) IN (SELECT id, num FROM Logs) 
AND   (l.id + 2, l.num) IN (SELECT id, num FROM Logs)
;

links

social