📘 윈도우 함수(LAG)로 연속 패턴 판별

여러 SQL 문제를 풀던 중 같은 개체가 여러 시점(year)에 등장하는 데이터를 다루다가 몇가지 실수가 있어서 정리한다.

---

1. 데이터 구조 (예시)

participation (
    athlete_id INT,
    year INT,
    team_code VARCHAR,
    sport VARCHAR
)

athlete_info (
    id INT,
    name VARCHAR
)

목표 : 같은 개체가 4년 간격으로 연속 등장한 기록을 찾는 것.

---

2. LAG 사용 시 첫 번째 실수 — 잘못된 정렬 기준

LAG(year) OVER (PARTITION BY athlete_id ORDER BY athlete_id)

PARTITION 내부에서는 athlete_id가 전부 동일하므로
ORDER BY athlete_id는 사실상 의미가 없다.
결국 LAG는 “들어온 순서” 기준으로 계산된다.

데이터가 우연히 연도순으로 들어온 경우에는 맞게 보이지만,
순서가 조금만 뒤섞이면 결과가 바로 틀어진다.

---

3. 정렬 기준 수정 — 시점(year) 기준이 되어야 함

LAG(p.year) OVER (
    PARTITION BY p.athlete_id
    ORDER BY p.year
)

• PARTITION BY 는 그룹을 나누는 기준
• ORDER BY 는 그룹 내부에서 비교 순서를 결정하는 기준

---

4. 두 번째 실수 — SELECT * 로 인한 컬럼 충돌

JOIN이 여러 개일 때 CTE 내부에서 SELECT *를 사용하면
id 컬럼이 여러 테이블에서 중복되어 오류가 발생한다.

특히 CTE는 “하나의 테이블”처럼 취급되므로
컬럼명 충돌에 매우 민감하다.

그래서 필요한 컬럼만 명시적으로 적는 방식으로 바꿨다.

---

5. 정리된 최종 쿼리 (예시 기준)

WITH sorted AS (
    SELECT
        a.id AS athlete_id,
        a.name,
        p.year,
        LAG(p.year) OVER (
            PARTITION BY a.id
            ORDER BY p.year
        ) AS prev_year
    FROM participation p
    JOIN athlete_info a ON p.athlete_id = a.id
    WHERE p.sport = '여자 배구'
      AND p.team_code = 'KOR'
)

SELECT DISTINCT
    athlete_id AS id,
    name
FROM sorted
WHERE year - prev_year = 4;

이 쿼리의 핵심 로직:

1. 같은 개체 기준으로 PARTITION
2. 시점(year) 기준으로 정렬
3. LAG로 이전 연도 계산
4. year - prev_year = 4 조건으로 연속성 판별
5. DISTINCT로 최종 중복 제거

---

6. 이번에 정리한 포인트

• LAG는 ORDER BY가 잘못되면 의미가 없다.
• PARTITION은 그룹, ORDER BY는 순서. 두 역할이 명확히 구분된다.
• CTE 안에서 SELECT *는 컬럼 충돌을 만들기 쉽다.
• 필요한 컬럼만 명시하는 게 유지보수에도 더 안전하다.
• JOIN + 윈도우 함수 조합에서는 정렬 기준을 항상 먼저 점검해야 한다.

---

7. 마무리

운이 좋아서 해결되는 일이 없도록 예외상황, 변수 등을 다양하게 고려하고 생각해보자!