[LeetCode] Consecutive Numbers / N가지 풀이

https://leetcode.com/problems/consecutive-numbers/description/

 

1. INNER JOIN을 이용한 풀이

SELECT a.num AS ConsecutiveNums
FROM logs a
	LEFT JOIN logs b ON a.id + 1 = b.id
    LEFT JOIN logs c ON a.id + 2 = c.id
WHERE a.num = b.num AND b.num = c.num

 

2. LEAD 함수를 활용한 풀이

1) 2행에 나오는 숫자, 3행에 나오는 숫자를 1행에 당겨오는 작업

SELECT num,
	LEAD(num, 1) OVER(ORDER BY id) AS next1, -- id column을 기준으로 순서를 파악한 후 num column을 1칸 당겨줌
    LEAD(num, 2) OVER(ORDER BY id) AS next2
FROM logs

 

2) 1) 쿼리의 실행 결과를 FROM절 서브쿼리로 넣고 WHERE절을 이용해서 조건을 걸어줌

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 next2
    FROM logs
) l
WHERE l.num =l.next AND l.next = l.next2

 

LEAD는 원하는만큼 데이터를 당겨오는 Window 함수라면,

LAG는 원하는만큼 데이터를 미는 Window 함수다.

 

3. LAG 함수를 활용한 풀이

SELECT num,
        LAG(num, 1) OVER (ORDER BY id) AS before1, -- 1칸 앞, id column을 기준으로 앞뒤를 판단
        LAG(num, 2) OVER (ORDER BY id) AS before2
FROM logs

SELECT DISTINCT l.num AS ConsecutiveNums
FROM (
    SELECT num,
            LAG(num, 1) OVER (ORDER BY id) AS before1, -- 1칸 앞, id column을 기준으로 앞뒤를 판단
            LAG(num, 2) OVER (ORDER BY id) AS before2
    FROM logs
) l
WHERE l.num = l.before1 AND l.before1 = l.before2