빠른 비교

sum(amount) OVER (
  PARTITION BY account_id
  ORDER BY occurred_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_amount

문법

frame은 현재 행 기준으로 윈도 함수가 볼 행 범위를 정한다

PARTITION BY는 행 묶음을 나누고, ORDER BY는 묶음 안의 순서를 정합니다. frame은 그 순서 안에서 현재 행을 기준으로 계산에 포함할 행 범위를 더 좁힙니다. 누적 합계, 이동 평균, 앞뒤 기간 비교처럼 "현재 행 주변"만 계산하려면 frame을 명시해야 합니다.

SELECT
  account_id,
  occurred_at,
  amount,
  sum(amount) OVER (
    PARTITION BY account_id
    ORDER BY occurred_at, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_amount
FROM ledger_entries;

aggregate를 window 함수로 쓰면 frame 안의 행만 집계합니다. 같은 sum(amount)라도 일반 GROUP BY에서는 행을 줄이고, window 함수에서는 원본 행을 유지한 채 각 행에 계산 결과를 붙입니다.

기본 frame은 ORDER BY가 있을 때 누적 계산처럼 동작한다

윈도 정의에 ORDER BY가 있으면 기본 frame은 파티션 시작부터 현재 행의 마지막 peer까지입니다. 그래서 sum(...) OVER (ORDER BY created_at)은 보통 누적 합계처럼 보입니다. 하지만 같은 created_at 값을 가진 행이 여러 개 있으면 peer 행이 함께 frame에 들어가 결과가 예상과 달라질 수 있습니다.

-- 같은 created_at이 많으면 peer 단위로 같은 누적값이 나올 수 있음
sum(amount) OVER (ORDER BY created_at)

-- 행 단위 누적을 원하면 보조 정렬 키와 ROWS frame을 명시
sum(amount) OVER (
  ORDER BY created_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

last_value도 기본 frame 때문에 자주 오해됩니다. 기본 frame의 마지막 행은 파티션 전체의 마지막 행이 아니라 현재 frame의 마지막 행입니다. 파티션 전체의 마지막 값을 원하면 frame 끝을 UNBOUNDED FOLLOWING으로 넓혀야 합니다.

last_value(status) OVER (
  PARTITION BY user_id
  ORDER BY changed_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS final_status

ROWS는 행 개수를 기준으로 이동 창을 만든다

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW는 현재 행을 포함해 최대 7행을 계산 대상으로 삼습니다. 날짜가 빠진 날이 있더라도 행 개수 기준으로 움직이므로, "최근 7개 기록"에는 맞지만 "최근 7일"과는 다릅니다.

avg(amount) OVER (
  PARTITION BY account_id
  ORDER BY occurred_at, id
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7_rows

행 단위 계산에서는 정렬이 안정적이어야 합니다. 같은 정렬 값이 있을 수 있으면 id 같은 보조 키를 추가해 결과 재현성을 확보해야 합니다.

RANGE와 GROUPS는 peer를 함께 다룬다

RANGE는 정렬 값의 범위를 기준으로 frame을 만듭니다. 시간 기준 이동 창은 RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW처럼 표현할 수 있습니다. 이때 ORDER BY는 보통 하나의 시간 컬럼이어야 합니다.

sum(amount) OVER (
  PARTITION BY account_id
  ORDER BY occurred_at
  RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
) AS amount_last_7_days

GROUPS는 같은 ORDER BY 값을 가진 peer group을 단위로 앞뒤 그룹을 포함합니다. 같은 순위나 같은 날짜 묶음을 하나의 단위로 다뤄야 할 때 사용합니다.

선택 기준

윈도 함수 결과를 최종 표시 순서로 착각하면 안 됩니다. 윈도 함수의 ORDER BY는 함수 계산 순서이고, 쿼리 결과의 표시 순서를 보장하려면 마지막에 별도 ORDER BY를 둬야 합니다.

주의할 점

-- 파티션 전체 마지막 값이 아니라 현재 frame의 마지막 값일 수 있음
last_value(amount) OVER (
  PARTITION BY account_id
  ORDER BY occurred_at
)

-- 파티션 전체 마지막 값을 명시
last_value(amount) OVER (
  PARTITION BY account_id
  ORDER BY occurred_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

대량 데이터에서 여러 window 정의가 서로 다른 정렬을 요구하면 정렬 단계가 늘어날 수 있습니다. 같은 PARTITION BY, ORDER BY, frame을 공유할 수 있는 계산은 같은 윈도 정의로 묶는 편이 실행 계획을 단순하게 만듭니다.