SQL 윈도우 함수에서 PRECEDING과 CURRENT ROW 이해하기

PRECEDING과 CURRENT ROW는 SQL 윈도우 함수에서 윈도우 프레임을 정의할 때 중요한 역할을 하는 요소들이다. 이 개념들은 주로 계산에 포함될 행의 범위를 지정하는 데 사용된다. 예를 들어, 롤링 평균(rolling average), 누적 합계(cumulative sum) 등과 같은 집계 함수를 특정 범위의 행에 대해 계산할 때 유용하다.

1. ROWS BETWEEN - 고정된 행 수

ROWS BETWEEN은 고정된 행 수를 기준으로 윈도우를 정의한다. 즉, 현재 행을 기준으로 앞뒤로 일정한 개수의 행을 포함시키는 방식이다. 값에 관계없이 고정된 개수의 행을 포함시키고 싶을 때 유용하다.

예시:

SELECT 
    artist_id,
    listen_date,
    AVG(daily_listens) OVER (
        PARTITION BY artist_id
        ORDER BY listen_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_avg_listens
FROM artist_listens

설명:

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: 이는 현재 행과 그 이전 6개의 행을 포함하는 윈도우를 의미한다. 따라서 총 7개의 행(현재 행 포함)에 대해 평균을 계산한다.
• PRECEDING: "6 PRECEDING"은 현재 행을 포함하고, 그 이전 6개의 행을 포함한다.
• CURRENT ROW: 현재 행도 계산에 포함된다.

2. RANGE BETWEEN - 값 기반 범위

RANGE BETWEEN은 고정된 행 수가 아닌, 특정 값 범위를 기준으로 윈도우를 정의한다. 이는 날짜 범위나 값 범위와 같이 일정 범위의 값을 고려하고 싶을 때 유용하다.

예시:

SELECT 
    artist_id,
    listen_date,
    AVG(daily_listens) OVER (
        PARTITION BY artist_id
        ORDER BY listen_date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS rolling_avg_listens
FROM artist_listens

설명:

• RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW: 이는 윈도우가 현재 행과 그 이전 6일 내의 모든 행을 포함하도록 설정한다.
• PRECEDING: "6 days PRECEDING"은 현재 행에서 6일 전까지의 모든 행을 포함한다.
• CURRENT ROW: 현재 행도 계산에 포함된다.

3. PRECEDING과 CURRENT ROW의 다른 조합

예시 1: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

• 이 경우, 윈도우는 첫 번째 행부터 시작하여 현재 행까지 모든 행을 포함한다. 이는 주로 누적 합계(cumulative sum)나 실행 중인 합계 계산에 사용된다.

SELECT 
    artist_id,
    listen_date,
    SUM(daily_listens) OVER (
        PARTITION BY artist_id
        ORDER BY listen_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_listens
FROM artist_listens

설명:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 윈도우는 첫 번째 행부터 시작하여 현재 행까지 포함된다. 이 방식은 누적 합계를 계산할 때 유용하다.

예시 2: ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

• 이 경우, 윈도우는 현재 행부터 끝까지 모든 행을 포함한다. 주로 미래의 데이터를 예측하거나 집계할 때 사용될 수 있다.

SELECT 
    artist_id,
    listen_date,
    SUM(daily_listens) OVER (
        PARTITION BY artist_id
        ORDER BY listen_date
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS future_listens
FROM artist_listens

설명:

• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 윈도우는 현재 행부터 시작하여 데이터셋의 끝까지 모든 행을 포함한다. 이는 미래 데이터를 예측하거나 후속 데이터를 집계하는 데 유용하다.

4. ROWS와 RANGE의 주요 차이점

• ROWS: 고정된 개수의 행을 기준으로 윈도우를 정의한다. 따라서 데이터의 값이나 간격에 관계없이 항상 정확히 지정된 개수의 행을 포함시킨다. 예를 들어, "6 PRECEDING"은 항상 이전 6개의 행과 현재 행을 포함한다.
  • 적합한 경우: 데이터 간의 간격이 불규칙하거나, 고정된 수의 행을 분석해야 할 때 유용하다.
• RANGE: 값 범위에 기반한 윈도우를 정의한다. 따라서 특정 범위 내에 있는 모든 값을 포함시킨다. 예를 들어, "6 days PRECEDING"은 6일 전부터 현재 행까지 모든 데이터를 포함한다.
  • 적합한 경우: 값의 범위나 시간 범위를 분석할 때 유용하다. 데이터 간의 간격이 다를 때도 유용하게 사용할 수 있다.

---

이처럼 PRECEDING과 CURRENT ROW는 SQL 윈도우 함수에서 데이터를 분석하는 강력한 도구이며, ROWS와 RANGE는 각기 다른 상황에 맞게 선택하여 사용할 수 있다. 데이터 분석을 할 때, 어떤 방식이 더 적합한지 고려하여 적절한 윈도우 프레임을 설정하는 것이 중요하다.