[LeetCode - Medium] 180. Consecutive Numbers 테이블 내 여러 행을 비교해야하는 문제 ( 여러 개의 값을 반환하는 서브 쿼리, 셀프조인, LAG/LEAD )

LeetCode - medium : Consecutive Numbers

Problem

Difficulty : Medium

Write an SQL query to find all numbers that appear at least three times consecutively. (id is the primary key for this table. id is an autoincrement column.)

세번 이상 연속적으로 등장한 num 모든 숫자를 출력해야한다. 다음 두 가지를 고려해야한다.

1. 연속적으로 등장했는 지 판별
2. 3번인지 판별

My Solution 1 - Self Join

SELECT
    DISTINCT a.num AS `ConsecutiveNums`
FROM logs a 

-- Multiple Self-Joins
INNER JOIN logs b 
    ON a.id = b.id-1
INNER JOIN logs c 
    ON b.id = c.id-1

WHERE 
    a.num = b.num 
    AND 
    b.num = c.num

⇒ Runtime: 634 ms, faster than 18.92% of MySQL online submissions for Consecutive Numbers.

My Solution 2 - Multi-Column Subquery

SELECT
    DISTINCT
    a.num AS `ConsecutiveNums`
FROM logs a

WHERE 
    -- where that id+1,num and id+2, num exists in the log
    -- id id+1 id+2 가 동일한 num을 가지는 레코드 필터링
    (id + 1, num) IN (SELECT id, num FROM Logs) 
    AND 
    (id + 2, num) IN (SELECT id, num FROM Logs)

⇒ Runtime: 438 ms, faster than 79.06% of MySQL online submissions for Consecutive Numbers.

My Solution 3

앞선 코드들은 id 값이 차이가 1이 아닌 경우에는 그대로 활용할 수 없기 때문에 윈도우 함수를 활용했다.

SELECT 
    DISTINCT num AS `ConsecutiveNums`

FROM
    (SELECT 
        LAG(num) OVER (ORDER BY id) AS `first`,
        num,
        LEAD(num) OVER (ORDER BY id) AS `third`
    FROM 
        logs
    ) a
WHERE num = first AND first = third

⇒ Runtime: 425 ms, faster than 83.79% of MySQL online submissions for Consecutive Numbers.

Lessons Learned

Multiple-Column Subquery 여러 개의 값을 반환하는 서브 쿼리 활용하기

Multiple-Column Subquery : Subquery가 한 번 실행되면서 두 개 이상의 컬럼을 검색해서 주 쿼리로 넘겨 준다.

WHERE 
    (id + 1, num) IN (SELECT id, num FROM Logs) 
    AND (id + 2, num) IN (SELECT id, num FROM Logs)

윈도우 함수 - 이전 행, 다음 행 값 불러오기

SELECT 
    -- 이전 id 행 값 불러오기
    LAG(num) OVER (ORDER BY id) AS `first`,
    num,
    -- 다음 id 행 불러오기 
  LEAD(num) OVER (ORDER BY id) AS `third`
FROM logs

Self Join 셀프조인

Self-join 단어 그대로, 테이블에 자기자신을 조인하는 것이다. 계층적 데이터를 다룰 때, 동일한 테이블내에 여러 행을 비교해야할 때 주로 사용한다.

INNER JOIN logs b  ON a.id = b.id-1
INNER JOIN logs c  ON b.id = c.id-1

WHERE a.num = b.num  AND  b.num = c.num

• 셀프조인을 사용할 때 주의해야하는 것 : Subquery aliases, Ambiquous column error

---

Source : Multiple-Column Subquery, Self Join 셀프조인