왜 집계 함수를 윈도우로 다시 쓰는가

15편에서 SUM, AVG 같은 그룹 함수를 GROUP BY와 함께 배웠습니다. 이 함수들은 여러 행의 값을 하나로 뭉쳐 그룹당 한 숫자를 냅니다. 그런데 19편에서 확인했듯, 윈도우 함수의 핵심은 행을 줄이지 않는 것입니다.

같은 SUM, AVG, COUNT, MAX, MIN을 OVER 절과 함께 쓰면, 각 행 옆에 “이 행이 속한 범위에서의 합계·평균”을 원본 행 개수 그대로 붙일 수 있습니다. 예를 들어 “각 직원의 급여와 함께, 그 부서 전체 급여 합계를 나란히 보여달라”는 요청은 GROUP BY로는 불가능하지만 윈도우 함수로는 자연스럽게 됩니다.

쉽게 말하면: 그룹 함수를 OVER 절과 함께 쓰면, “그룹으로 묶어서 요약하되 원본 행은 하나도 지우지 마라”는 뜻이 됩니다.

1. 집계 윈도우 함수 — SUM, AVG를 행마다 보여주기

작은 예시 — 입력 테이블

부서별 급여 데이터입니다(단위: 백만 원).

SQL

SELECT 사번, 이름, 부서, 급여,
       SUM(급여) OVER (PARTITION BY 부서) AS 부서합계,
       AVG(급여) OVER (PARTITION BY 부서) AS 부서평균
FROM 직원;

결과 테이블

결과 해석: ORDER BY 없이 PARTITION BY만 쓰면 파티션(부서) 전체가 계산 범위가 되어, 같은 부서 행은 모두 같은 합계·평균 값을 공유합니다. “내 급여가 부서 평균보다 높은가”를 한 줄로 비교하고 싶을 때 이 형태가 그대로 쓰입니다.

2. 프레임 절 — 계산 범위를 더 좁게 지정하기

ORDER BY를 추가하면 이야기가 달라집니다. ORDER BY가 있으면 “각 행 기준으로 어디부터 어디까지를 더할 것인가”라는 질문이 생기고, 이 범위를 정하는 것이 프레임(frame, 틀·범위라는 뜻) 절입니다.

쉽게 말하면: 프레임은 “지금 이 행을 계산할 때, 파티션 안의 몇 번째 행부터 몇 번째 행까지를 재료로 쓸 것인가”를 정하는 범위 지정자입니다.

프레임 문법

함수(컬럼) OVER (
  PARTITION BY 컬럼
  ORDER BY 컬럼
  ROWS BETWEEN 시작 AND 끝
)

경계로 쓸 수 있는 값은 다음과 같습니다.

PRECEDING은 “앞선, 선행하는”이라는 뜻이고 FOLLOWING은 “뒤따르는”이라는 뜻입니다. UNBOUNDED는 “경계가 없는”이라는 뜻으로, 파티션의 끝까지 무한히 확장한다는 의미입니다.

ROWS와 RANGE의 차이

프레임을 지정하는 방식에는 ROWS와 RANGE 두 가지가 있습니다.

• ROWS: 물리적인 행 개수로 범위를 정합니다. “앞 1행부터 뒤 1행까지”처럼 정렬 순서상 몇 번째 행인지만 봅니다.
• RANGE: 정렬 기준 컬럼의 값 범위로 범위를 정합니다. 예를 들어 RANGE BETWEEN 500 PRECEDING AND 500 FOLLOWING은 행 개수가 아니라 “현재 값보다 500 작은 값부터 500 큰 값까지”를 범위로 봅니다. 정렬 기준 값이 동점인 행이 여러 개 있으면 ROWS와 RANGE의 결과가 달라질 수 있습니다.

작은 예시 — ROWS로 이동 합계 계산하기

주문 4건이 있습니다.

SQL

SELECT ID, 주문일, 주문액,
       SUM(주문액) OVER (
         ORDER BY 주문일
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS 이동합계
FROM 주문;

결과 테이블

결과 해석: 각 행의 이동합계는 “바로 앞 1행 + 현재 행 + 바로 뒤 1행”입니다. ID 1은 앞 행이 없어 100+200=300, ID 2는 100+200+300=600, ID 3은 200+300+400=900, ID 4는 뒤 행이 없어 300+400=700입니다. 프레임 경계 밖의 행은 존재하지 않는 것처럼 자동으로 제외되기 때문에 첫 행과 마지막 행은 합산 대상이 자연히 줄어듭니다.

프레임을 생략하면 어떻게 되는가

ORDER BY만 쓰고 프레임을 아예 생략하면, DBMS는 기본 프레임을 자동으로 적용합니다. 그 기본값이 바로 다음 절의 핵심 함정입니다.

ORDER BY만 쓰고 프레임 생략 시 기본값:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

즉 “파티션의 맨 처음부터 현재 행까지”가 기본 범위입니다. 그래서 ORDER BY만 있고 프레임이 없는 SUM() OVER는 흔히 누적 합계(맨 처음부터 지금까지 계속 더한 값)로 동작합니다.

3. LAG와 LEAD — 이전 행, 다음 행 값 가져오기

LAG(래그, “뒤처지다·지연되다”라는 뜻)와 LEAD(리드, “앞서다·이끌다”라는 뜻)는 현재 행을 기준으로 정렬 순서상 이전 행 또는 다음 행의 값을 그대로 가져오는 함수입니다. 전월 대비, 전일 대비 같은 비교 계산에 필수입니다.

LAG(컬럼, 오프셋, 기본값) OVER (ORDER BY 정렬컬럼)
LEAD(컬럼, 오프셋, 기본값) OVER (ORDER BY 정렬컬럼)

• 오프셋: 몇 행 앞(또는 뒤)을 볼지. 생략하면 1행입니다.
• 기본값: 참조할 행이 없을 때(맨 첫 행에서 LAG를 쓰는 경우 등) 대신 넣을 값. 생략하면 NULL입니다.

작은 예시 — 입력 테이블

SQL — 직전 급여와 비교하기

SELECT 이름, 급여,
       LAG(급여) OVER (ORDER BY 급여) AS 직전급여
FROM 성적;

결과 테이블

결과 해석: ORDER BY 급여(오름차순)로 정렬했으므로 “이전 행”은 곧 급여가 한 단계 낮은 행입니다. 첫 행인 최수진은 이전 행이 없어 NULL이 나옵니다.

4. FIRST_VALUE와 LAST_VALUE — 프레임의 첫 값, 마지막 값

FIRST_VALUE와 LAST_VALUE는 이름 그대로 현재 프레임 안에서 정렬 기준으로 첫 번째, 마지막 값을 가져옵니다. 여기서 “현재 프레임 안에서”라는 조건이 결정적입니다.

FIRST_VALUE는 대체로 안전하다

SELECT 이름, 급여,
       FIRST_VALUE(급여) OVER (ORDER BY 급여) AS 최소급여
FROM 성적;

ORDER BY 급여(오름차순)로 정렬하면 프레임의 시작은 항상 파티션의 첫 행(가장 작은 값)입니다. 프레임을 생략해도 기본 프레임인 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 이미 “맨 처음부터”를 포함하므로, FIRST_VALUE는 어느 행에서 보든 항상 같은 최소값 70을 반환합니다. 즉 기본 프레임에서도 FIRST_VALUE는 기대한 대로 동작합니다.

LAST_VALUE가 함정인 이유

문제는 LAST_VALUE입니다. “마지막 값이니까 당연히 파티션의 최댓값(또는 정렬상 끝값)이 나오겠지”라고 기대하기 쉽지만, 기본 프레임에서는 그렇지 않습니다.

SELECT 이름, 급여,
       LAST_VALUE(급여) OVER (ORDER BY 급여) AS 최대급여_기대
FROM 성적;

결과 해석: 100(전체 최댓값)이 나올 것이라 기대했지만, 실제로는 각 행 자신의 급여값이 그대로 나옵니다. 이유는 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, 즉 “맨 처음부터 현재 행까지”이기 때문입니다. LAST_VALUE는 “이 프레임 안에서 마지막 값”을 찾는데, 프레임의 끝이 바로 CURRENT ROW(현재 행)로 고정되어 있으니, 결국 그 프레임의 마지막 값은 언제나 현재 행 자신이 되어버립니다.

비유: FIRST_VALUE는 “지금까지 읽은 페이지 중 첫 페이지가 몇 쪽인가”를 묻는 것과 같아서 항상 1쪽으로 답이 고정됩니다. LAST_VALUE는 “지금까지 읽은 페이지 중 마지막 페이지가 몇 쪽인가”를 묻는 것인데, “지금까지”의 끝은 항상 방금 읽은 그 페이지이므로 매번 다른 답(현재 페이지 번호)이 나오는 것뿐입니다. 책 전체의 마지막 페이지를 물은 게 아니었던 겁니다.

올바른 사용법 — 프레임을 파티션 전체로 넓히기

LAST_VALUE로 진짜 파티션의 마지막 값(또는 최댓값)을 얻으려면 프레임을 명시적으로 끝까지 넓혀야 합니다.

SELECT 이름, 급여,
       LAST_VALUE(급여) OVER (
         ORDER BY 급여
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS 최대급여
FROM 성적;

결과 해석: 프레임을 UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING(파티션 전체)으로 넓히자 비로소 모든 행에서 파티션 전체의 마지막 값인 100이 반환됩니다. 실무에서는 최댓값이 필요하면 아예 MAX() OVER (PARTITION BY ...)를 쓰는 편이 프레임을 신경 쓸 필요가 없어 더 안전합니다.

직접 해보기

프레임 절이 없을 때와 명시했을 때 누적 합계가 정말 같은 값인지 나란히 조회해 확인한다.

SELECT 이름, 급여,
       SUM(급여) OVER (ORDER BY 급여) AS 누적합계_기본프레임,
       SUM(급여) OVER (
         ORDER BY 급여
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS 누적합계_명시프레임
FROM 성적;

무엇을 보아야 하나: 두 컬럼 값이 실제로 행마다 똑같이 나오는지 확인한다. 기본 프레임 자체가 이 명시적 프레임과 같다는 뜻이다.

직접 해보기 2

이 편 최대 함정인 LAST_VALUE를 직접 실행해 눈으로 확인한다. 프레임 없이 한 번, 파티션 전체로 넓혀서 한 번 조회한다.

SELECT 이름, 급여,
       LAST_VALUE(급여) OVER (ORDER BY 급여) AS 최대급여_함정,
       LAST_VALUE(급여) OVER (
         ORDER BY 급여
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS 최대급여_정상
FROM 성적;

무엇을 보아야 하나: 첫 번째 컬럼(최대급여_함정)이 100이 아니라 매 행 자신의 급여값을 그대로 돌려주는지, 두 번째 컬럼(최대급여_정상)은 모든 행에서 100으로 똑같이 나오는지 확인한다.

왜 이걸 해보나: “LAST_VALUE는 항상 마지막(최댓값)을 돌려준다”는 착각이 이 편 기출의 대표 함정이다. 두 결과를 나란히 보면 프레임의 끝이 CURRENT ROW로 고정된다는 것이 무슨 뜻인지 확실해진다.

핵심 정리

• 집계 함수를 OVER와 함께 쓰면 행을 줄이지 않고 그룹 단위 요약값을 각 행에 붙일 수 있다.
• 프레임은 ROWS BETWEEN 시작 AND 끝(또는 RANGE)으로 지정하며, ROWS는 물리적 행 개수, RANGE는 정렬 기준 값의 범위로 계산 범위를 정한다.
• ORDER BY만 쓰고 프레임을 생략하면 기본값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이며, 이 때문에 집계 함수는 흔히 누적 합계로 동작한다.
• LAG는 이전 행, LEAD는 다음 행의 값을 가져온다. 정렬 방향을 바꾸면 “이전·다음”의 의미도 뒤집힌다.
• FIRST_VALUE는 기본 프레임에서도 안전하게 동작하지만, LAST_VALUE는 기본 프레임의 끝이 CURRENT ROW로 고정되어 있어 매 행마다 자기 자신의 값을 반환하는 함정에 빠지기 쉽다. 진짜 마지막 값이 필요하면 프레임을 UNBOUNDED FOLLOWING까지 넓히거나 MAX() OVER를 쓴다.

마무리 복습

참고 자료

• Oracle Database SQL Language Reference – Analytic Functions 
• MySQL 8.0 Reference Manual – Window Functions