정현석

개인 저장공간입니다.
Since 2021.10

3 분 소요

MySQL 윈도우 함수의 PRECEDING, FOLLOWING, CURRENT ROW 옵션은 각 행에 대해 계산할 프레임(창) 범위를 지정한다. 프레임 정의를 정확히 이해하면 이동 평균, 누적 합계, 전후 행 비교를 한 줄의 SQL로 표현할 수 있다.

윈도우 프레임 동작 시각화 (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING):
────────────────────────────────────────────────────────────────
 행 번호 │  값  │ 1 PRECEDING │ CURRENT ROW │ 1 FOLLOWING │ SUM(프레임)
────────────────────────────────────────────────────────────────
    1    │  10  │      -      │      O      │      O      │ 10+20 = 30
    2    │  20  │      O      │      O      │      O      │ 10+20+30 = 60
    3    │  30  │      O      │      O      │      O      │ 20+30+40 = 90
    4    │  40  │      O      │      O      │      -      │ 30+40 = 70
────────────────────────────────────────────────────────────────
※ UNBOUNDED PRECEDING/FOLLOWING은 파티션의 처음/끝까지 확장됨.

윈도우 프레임의 주요 옵션

  1. PRECEDING: 현재 행을 기준으로 앞쪽의 행들을 지정한다.
    • N PRECEDING: 현재 행에서 앞으로 N개의 행
  2. FOLLOWING: 현재 행을 기준으로 뒤쪽의 행들을 지정한다.
    • N FOLLOWING: 현재 행에서 뒤로 N개의 행
  3. CURRENT ROW: 현재 행을 프레임의 경계로 삼는다. ROWS에서는 현재 행 자체가, RANGE에서는 현재 행과 ORDER BY 값이 같은 행들(peer)이 경계가 된다1.
  4. UNBOUNDED PRECEDING: 윈도우 프레임의 시작을 파티션의 첫 번째 행으로 지정한다1.
  5. UNBOUNDED FOLLOWING: 윈도우 프레임의 끝을 파티션의 마지막 행으로 지정한다1.

프레임 시작 지정자는 프레임 끝 지정자보다 뒤에 올 수 없다. BETWEEN 2 FOLLOWING AND CURRENT ROW처럼 시작이 끝보다 뒤인 조합은 오류이며, BETWEEN CURRENT ROW AND 2 FOLLOWING 순서로 써야 한다1.

윈도우 함수 기본 구문

<윈도우 함수> OVER (PARTITION BY column ORDER BY column ROWS | RANGE BETWEEN <프레임 정의>)
  • ROWS: 실제 행의 개수를 기준으로 프레임을 설정한다.
  • RANGE: ORDER BY 값의 범위를 기준으로 프레임을 설정한다 (예: 날짜 또는 숫자 범위).

ROWS vs RANGE

  • ROWS: 실제로 행 개수를 기준으로 윈도우 프레임을 설정한다.
    • 예: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW는 현재 행과 그 앞 2개의 행을 포함한다.
  • RANGE: 값의 범위를 기준으로 윈도우 프레임을 설정한다. expr PRECEDING/expr FOLLOWING의 경계는 현재 행의 값에서 expr을 뺀 값/더한 값과 같은 행들(peer)이다1.
    • 예: RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW는 날짜 값을 기준으로 프레임을 설정한다.
    • RANGE에 숫자형 expr을 쓰면 ORDER BY도 숫자형 식이어야 하고, INTERVAL(날짜형)을 쓰면 ORDER BY도 날짜형 식이어야 한다1.

프레임 지정 옵션 설명

1. UNBOUNDED PRECEDING

  • 처음부터 현재 행까지의 모든 행을 포함한다.
  • “시작이 없을 만큼” 이전의 모든 행, 즉 파티션의 첫 행부터를 포함한다는 의미다1.
# 설명: 첫 번째 행부터 현재 행까지 누적 합계를 계산합니다.
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

2. CURRENT ROW

  • 현재 행을 프레임의 경계로 삼는다.
  • 윈도우 프레임이 현재 행에서 시작하거나 끝날 때 사용한다. RANGE에서는 현재 행과 ORDER BY 값이 같은 행들(peer)이 함께 포함된다1.
# 설명: 현재 행과 그 뒤에 있는 2개의 행에 대해 평균 급여를 계산합니다.
AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)

3. N PRECEDING

  • 현재 행을 기준으로 앞으로 N개의 행을 포함한다.
# 설명: 현재 행에서 앞으로 3개의 행과 현재 행을 포함한 누적 합계를 계산합니다.
SUM(sales) OVER (ORDER BY sales_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

4. N FOLLOWING

  • 현재 행을 기준으로 뒤로 N개의 행을 포함한다.
# 설명: 현재 행과 그 뒤의 2개의 행에 대해 누적 합계를 계산합니다.
SUM(sales) OVER (ORDER BY sales_date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)

5. UNBOUNDED FOLLOWING

  • 현재 행부터 끝까지 모든 행, 즉 파티션의 마지막 행까지를 포함한다1.
# 설명: 현재 행부터 마지막 행까지의 합계를 계산합니다.
SUM(sales) OVER (ORDER BY sales_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

윈도우 프레임의 제약과 주의점

  • ORDER BY 유무가 기본 프레임을 바꾼다: ORDER BY가 있으면 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, 없으면 파티션 전체다. 같은 쿼리에 ORDER BY를 추가하는 것만으로 SUM() 값이 달라질 수 있으므로, 집계 범위를 고정하려면 프레임 절을 직접 명시한다1.
  • 지원되지 않는 프레임 문법: MySQL은 GROUPS 프레임 단위와 프레임의 EXCLUDE 절, IGNORE NULLS, FROM LAST를 파싱은 하지만 오류를 낸다. 실제로 쓸 수 있는 프레임 단위는 ROWS와 RANGE뿐이다2.
  • 행 값에 따라 변하는 동적 경계 미지원: 프레임 경계가 현재 행의 값에 의존하는 형태(expr 자리에 컬럼을 쓰는 형태)는 지원하지 않는다2.
  • 중첩 윈도우 함수와 집계 윈도우 함수의 DISTINCT 미지원: COUNT(DISTINCT x) OVER (...)처럼 집계 윈도우 함수에 DISTINCT를 쓰거나, 윈도우 함수의 결과를 다른 윈도우 함수에 바로 넣을 수 없다. 중첩이 필요하면 서브쿼리나 CTE로 한 단계 나눈다2.
  • UPDATE/DELETE에서의 사용 제한: SQL 표준 제약으로, 윈도우 함수를 UPDATE/DELETE의 갱신 대상 행을 정하는 데는 쓸 수 없고 서브쿼리에서 행을 선택하는 용도로만 쓸 수 있다. 윈도우 개수는 MySQL 8.0.28부터 SELECT당 최대 127개다2.

예시

1. 누적 합계 (처음부터 현재까지)

# 설명: 첫 번째 행부터 현재 행까지의 누적 합계를 계산합니다.
SELECT 
    sales_date, 
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM Sales;

2. 현재 행과 이전 2개의 행의 합계

# 설명: 현재 행과 그 앞의 2개 행의 합계를 계산합니다. 총 3개의 행에 대해 집계가 이루어집니다.
SELECT 
    sales_date, 
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS window_sales
FROM Sales;

3. 현재 행과 다음 2개의 행에 대한 평균

# 설명: 현재 행과 그 뒤의 2개 행에 대한 평균을 계산합니다.
SELECT 
    sales_date, 
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS avg_sales
FROM Sales;

4. 현재 행과 모든 이후 행에 대한 최대값

# 설명: 현재 행부터 마지막 행까지의 `sales_amount` 값 중 최대값을 반환합니다.
SELECT 
    sales_date, 
    sales_amount,
    MAX(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS max_future_sales
FROM Sales;

References

  1. MySQL 8.0 Reference Manual — 14.20.3 Window Function Frame Specification — 프레임 경계 지정자(CURRENT ROW, UNBOUNDED PRECEDING/FOLLOWING, expr PRECEDING/FOLLOWING)의 의미, frame_start/frame_end 순서 제약, ORDER BY 유무에 따른 기본 프레임, RANGE와 ORDER BY 식 유형 요건. ↩ ↩2 ↩3 ↩4 ↩5 ↩6 ↩7 ↩8 ↩9 ↩10

  2. MySQL 8.0 Reference Manual — 14.20.5 Window Function Restrictions — 미지원 기능(GROUPS/EXCLUDE/IGNORE NULLS/FROM LAST, 동적 프레임 경계, 중첩 윈도우 함수, 집계 윈도우 함수의 DISTINCT), UPDATE/DELETE 제약, SELECT당 윈도우 개수 상한. ↩ ↩2 ↩3 ↩4

댓글남기기

참고