윈도 함수 응용 - 순위·누적·이동 평균
이 장에서 배우는 것
집계 함수는 여러 행을 하나로 뭉치지만, 윈도 함수(window function)는 행을 그대로 둔 채로 옆에 순위·누적값·이전 행 값을 붙여준다. 앞 장에서 ROLLUP·CUBE·GROUPING SETS 로 소계·총계 표를 읽는 법을 다뤘다면, 이 장은 행 단위로 붙는 계산값을 다룬다. RANK·DENSE_RANK·ROW_NUMBER 의 차이, PARTITION BY 와 ORDER BY 조합, ROWS 와 RANGE 프레임의 차이, LAG·LEAD 를 이용한 이전 행 비교, 누적 합계와 이동 평균을 실습한다.
- RANK, DENSE_RANK, ROW_NUMBER 가 동점을 처리하는 방식의 차이를 구분한다
- PARTITION BY 와 OVER() 절 안의 ORDER BY 가 각각 무엇을 바꾸는지 이해한다
- ROWS 프레임과 RANGE 프레임이 다른 결과를 내는 상황을 직접 계산으로 확인한다
- LAG·LEAD 로 이전·다음 행 값을 끌어와 증감을 계산한다
- 누적 합계와 이동 평균 쿼리를 스스로 작성하고 검증한다
문제 상황
온라인 서점 마케팅팀이 다음 세 가지를 요청했다. 첫째, 회원 등급(GRADE)별로 주문 금액 순위를 매겨 상위 구매자를 뽑고 싶다. 둘째, 회원별로 날짜순 누적 구매액과 최근 2건 이동 평균을 보고 싶다. 셋째, 직전 주문 대비 이번 주문 금액이 얼마나 늘거나 줄었는지 보고 싶다. 이 세 요청은 모두 GROUP BY 로는 풀리지 않는다. GROUP BY 는 여러 행을 한 행으로 줄이지만, 마케팅팀은 원래 행 개수를 유지한 채 옆에 순위·누적값·증감을 붙이고 싶어 하기 때문이다. 이럴 때 쓰는 것이 윈도 함수다.
순위를 매기는 세 함수
이 장에서는 아래 네 테이블을 사용한다. 주문상세의 AMOUNT 는 QTY × PRICE 로, 표본 데이터에서는 한 주문에 도서 한 종만 담아 계산을 단순하게 유지했다.
| MEMBER_ID | MEMBER_NAME | GRADE |
|---|---|---|
| M01 | 김도윤 | GOLD |
| M02 | 이서연 | SILVER |
| M03 | 박준호 | GOLD |
| M04 | 최유리 | SILVER |
| ORDER_ID | MEMBER_ID | ORDER_DATE | AMOUNT |
|---|---|---|---|
| O101 | M01 | 2026-01-05 | 22000 |
| O102 | M02 | 2026-01-08 | 30000 |
| O103 | M01 | 2026-02-02 | 28000 |
| O104 | M03 | 2026-02-10 | 22000 |
| O105 | M02 | 2026-02-15 | 16000 |
| O106 | M04 | 2026-03-01 | 39000 |
| O107 | M01 | 2026-03-04 | 15000 |
| O108 | M03 | 2026-03-20 | 28000 |
GOLD 등급(M01, M03)의 주문을 금액 내림차순으로 보면 28,000원이 두 건(O103, O108), 22,000원이 두 건(O101, O104) 있다. 이 동점이 RANK, DENSE_RANK, ROW_NUMBER 를 구분하는 좋은 재료가 된다.
| 함수 | 동점 처리 | 다음 순위 | 대표 용도 |
|---|---|---|---|
| RANK | 동점에 같은 순위 부여 | 동점 개수만큼 건너뜀 | 공동 순위 표시 |
| DENSE_RANK | 동점에 같은 순위 부여 | 건너뛰지 않고 이어짐 | 등급 구간 나누기 |
| ROW_NUMBER | 동점이어도 고유 번호 | 항상 1씩 증가 | Top-N 한 건 추출, 페이지네이션 |
ROW_NUMBER 는 동점을 아예 구분하지 않고 고유 번호를 매기기 때문에, 어떤 행이 1번이 되는지는 ORDER BY 에 동점 처리용 열을 추가하지 않으면 보장되지 않는다. 이 문제는 뒤의 자주 틀리는 것 절에서 다시 다룬다.
프레임 지정 - ROWS 와 RANGE
기본 프레임의 함정
OVER() 안에 ORDER BY 만 쓰고 프레임을 생략하면, 표준 SQL 은 기본값으로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 를 적용한다. 즉 ORDER BY 가 있는 순간 SUM 이나 AVG 는 파티션 전체 합계가 아니라 누적값을 돌려준다. 파티션 전체 총합을 원한다면 ORDER BY 를 빼거나, 프레임을 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 으로 명시해야 한다. 이 차이는 MySQL 8 매뉴얼의 프레임 설명에도 나와 있다.
ROWS BETWEEN 과 RANGE BETWEEN
ROWS 는 물리적인 행 위치를 기준으로 프레임을 정한다. RANGE 는 ORDER BY 에 쓴 값 자체를 기준으로, 같은 값을 가진 행을 하나의 피어 그룹(peer group)으로 묶어 같은 결과를 준다. GOLD 등급 주문을 금액 내림차순으로 누적 합계를 구해보면 이 차이가 드러난다.
| 구분 | 기준 | 동점 처리 | 사용 시점 |
|---|---|---|---|
| ROWS | 물리적 행 위치 | 동점이라도 행마다 다른 값 | 정확히 몇 번째 행까지로 범위를 고정할 때(이동 평균 등) |
| RANGE | ORDER BY 값(논리적 범위) | 같은 값을 가진 행은 모두 같은 결과 | 정렬 기준 값 자체로 묶어야 할 때 |
이전 행과 비교하기 - LAG 와 LEAD
LAG 는 현재 행보다 앞선 행의 값을, LEAD 는 뒤따르는 행의 값을 가져온다. 두 함수 모두 PARTITION BY 를 빠뜨리면 다른 회원의 데이터끼리 비교하게 되므로, "회원별 직전 주문"처럼 파티션 단위 비교가 필요할 때는 반드시 PARTITION BY 를 같이 써야 한다. 기본 두 번째 인자는 오프셋(몇 행 앞/뒤)이고, 세 번째 인자로 이전 행이 없을 때 쓸 기본값을 지정할 수 있다.
MySQL 8 과 Oracle 차이
| 항목 | MySQL 8 | Oracle | 영향 |
|---|---|---|---|
| NULLS FIRST/LAST | 지원 안 함(CASE 식으로 우회) | ORDER BY 뒤에 직접 지정 가능 | NULL 이 섞인 정렬 기준일 때 결과 순서가 달라질 수 있음 |
| LAG/LEAD 의 IGNORE NULLS | 지원 안 함 | LAG(col) IGNORE NULLS 지원 | MySQL 에서는 NULL 건너뛰기를 서브쿼리로 직접 구현해야 함 |
| WINDOW 절 재사용 | SELECT ... WINDOW w AS (...) 지원 | 해당 구문 없음 | Oracle 은 OVER() 를 매번 반복 작성해야 함 |
완성 코드
-- 스키마와 표본 데이터
CREATE TABLE MEMBER (
MEMBER_ID VARCHAR(10) PRIMARY KEY,
MEMBER_NAME VARCHAR(20) NOT NULL,
GRADE VARCHAR(10) NOT NULL
);
CREATE TABLE BOOK (
BOOK_ID VARCHAR(10) PRIMARY KEY,
BOOK_NAME VARCHAR(40) NOT NULL,
CATEGORY VARCHAR(20) NOT NULL,
PRICE INT NOT NULL
);
CREATE TABLE ORDERS (
ORDER_ID VARCHAR(10) PRIMARY KEY,
MEMBER_ID VARCHAR(10) NOT NULL,
ORDER_DATE DATE NOT NULL,
FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER(MEMBER_ID)
);
CREATE TABLE ORDER_DETAIL (
ORDER_ID VARCHAR(10) NOT NULL,
BOOK_ID VARCHAR(10) NOT NULL,
QTY INT NOT NULL,
AMOUNT INT NOT NULL,
PRIMARY KEY (ORDER_ID, BOOK_ID),
FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ORDER_ID),
FOREIGN KEY (BOOK_ID) REFERENCES BOOK(BOOK_ID)
);
INSERT INTO MEMBER VALUES
('M01', '김도윤', 'GOLD'),
('M02', '이서연', 'SILVER'),
('M03', '박준호', 'GOLD'),
('M04', '최유리', 'SILVER');
INSERT INTO BOOK VALUES
('B01', 'SQL 기초', 'IT', 22000),
('B02', '추리소설 A', '소설', 15000),
('B03', '데이터베이스 실무', 'IT', 28000),
('B04', '에세이 모음', '에세이', 13000),
('B05', '추리소설 B', '소설', 16000);
INSERT INTO ORDERS VALUES
('O101', 'M01', '2026-01-05'),
('O102', 'M02', '2026-01-08'),
('O103', 'M01', '2026-02-02'),
('O104', 'M03', '2026-02-10'),
('O105', 'M02', '2026-02-15'),
('O106', 'M04', '2026-03-01'),
('O107', 'M01', '2026-03-04'),
('O108', 'M03', '2026-03-20');
INSERT INTO ORDER_DETAIL VALUES
('O101', 'B01', 1, 22000),
('O102', 'B02', 2, 30000),
('O103', 'B03', 1, 28000),
('O104', 'B01', 1, 22000),
('O105', 'B05', 1, 16000),
('O106', 'B04', 3, 39000),
('O107', 'B02', 1, 15000),
('O108', 'B03', 1, 28000);
-- 1) 등급별 주문 금액 순위 - 세 함수 비교
SELECT
m.GRADE,
o.ORDER_ID,
m.MEMBER_ID,
od.AMOUNT,
RANK() OVER (PARTITION BY m.GRADE ORDER BY od.AMOUNT DESC) AS RANK_NO,
DENSE_RANK() OVER (PARTITION BY m.GRADE ORDER BY od.AMOUNT DESC) AS DENSE_NO,
ROW_NUMBER() OVER (PARTITION BY m.GRADE ORDER BY od.AMOUNT DESC, o.ORDER_ID) AS ROW_NO
FROM ORDERS o
JOIN MEMBER m ON m.MEMBER_ID = o.MEMBER_ID
JOIN ORDER_DETAIL od ON od.ORDER_ID = o.ORDER_ID
ORDER BY m.GRADE, RANK_NO, o.ORDER_ID;
-- 2) 회원별 누적 합계, 최근 2건 이동 평균, 직전 주문 대비 증감
SELECT
o.MEMBER_ID,
o.ORDER_ID,
o.ORDER_DATE,
od.AMOUNT,
SUM(od.AMOUNT) OVER (
PARTITION BY o.MEMBER_ID ORDER BY o.ORDER_DATE
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS CUM_AMOUNT,
ROUND(AVG(od.AMOUNT) OVER (
PARTITION BY o.MEMBER_ID ORDER BY o.ORDER_DATE
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
), 0) AS MOVING_AVG2,
LAG(od.AMOUNT) OVER (PARTITION BY o.MEMBER_ID ORDER BY o.ORDER_DATE) AS PREV_AMOUNT,
od.AMOUNT - LAG(od.AMOUNT) OVER (PARTITION BY o.MEMBER_ID ORDER BY o.ORDER_DATE) AS DIFF_AMOUNT
FROM ORDERS o
JOIN ORDER_DETAIL od ON od.ORDER_ID = o.ORDER_ID
WHERE o.MEMBER_ID = 'M01'
ORDER BY o.ORDER_DATE;
-- 3) ROWS 프레임과 RANGE 프레임의 차이 - GOLD 등급, 금액 내림차순
SELECT
m.MEMBER_ID,
o.ORDER_ID,
od.AMOUNT,
SUM(od.AMOUNT) OVER (
PARTITION BY m.GRADE ORDER BY od.AMOUNT DESC, o.ORDER_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS CUM_ROWS,
SUM(od.AMOUNT) OVER (
PARTITION BY m.GRADE ORDER BY od.AMOUNT DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS CUM_RANGE
FROM ORDERS o
JOIN MEMBER m ON m.MEMBER_ID = o.MEMBER_ID
JOIN ORDER_DETAIL od ON od.ORDER_ID = o.ORDER_ID
WHERE m.GRADE = 'GOLD'
ORDER BY od.AMOUNT DESC, o.ORDER_ID;
줄별 해설
첫 번째 쿼리는 PARTITION BY m.GRADE 로 등급마다 순위를 따로 매기고, ORDER BY od.AMOUNT DESC 로 금액이 큰 순서를 기준으로 삼는다. RANK_NO, DENSE_NO 는 od.AMOUNT DESC 하나만 기준으로 두어 동점을 그대로 드러내고, ROW_NO 는 뒤에 o.ORDER_ID 를 추가해 동점이어도 결과가 실행마다 흔들리지 않도록 했다.
두 번째 쿼리의 CUM_AMOUNT 는 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 로 "파티션 시작부터 현재 행까지"를 명시해 누적 합계를 만든다. MOVING_AVG2 는 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 로 프레임을 현재 행과 바로 앞 1건으로 좁혀 최근 2건 평균을 낸다. LAG(od.AMOUNT) 는 같은 회원의 직전 주문 금액을 가져오고, 첫 주문에서는 이전 행이 없으므로 NULL 이 된다.
세 번째 쿼리는 같은 데이터를 두 가지 프레임으로 계산해 차이를 대조한다. CUM_ROWS 는 ORDER BY 에 o.ORDER_ID 를 추가해 동점이어도 정해진 물리적 순서대로 하나씩 누적한다. CUM_RANGE 는 ORDER BY 에 od.AMOUNT DESC 하나만 남겨, 28,000원 두 건과 22,000원 두 건을 각각 하나의 피어 그룹으로 묶어 같은 누적값을 부여한다.
실행 결과
mysql> -- 쿼리 1 실행 결과
GRADE ORDER_ID MEMBER_ID AMOUNT RANK_NO DENSE_NO ROW_NO
GOLD O103 M01 28000 1 1 1
GOLD O108 M03 28000 1 1 2
GOLD O101 M01 22000 3 2 3
GOLD O104 M03 22000 3 2 4
GOLD O107 M01 15000 5 3 5
SILVER O106 M04 39000 1 1 1
SILVER O102 M02 30000 2 2 2
SILVER O105 M02 16000 3 3 3
mysql> -- 쿼리 2 실행 결과 (M01)
MEMBER_ID ORDER_ID ORDER_DATE AMOUNT CUM_AMOUNT MOVING_AVG2 PREV_AMOUNT DIFF_AMOUNT
M01 O101 2026-01-05 22000 22000 22000 NULL NULL
M01 O103 2026-02-02 28000 50000 25000 22000 6000
M01 O107 2026-03-04 15000 65000 21500 28000 -13000
mysql> -- 쿼리 3 실행 결과 (GOLD)
MEMBER_ID ORDER_ID AMOUNT CUM_ROWS CUM_RANGE
M01 O103 28000 28000 56000
M03 O108 28000 56000 56000
M01 O101 22000 78000 100000
M03 O104 22000 100000 100000
M01 O107 15000 115000 115000
실무에서 자주 틀리는 것
기본 프레임을 총합으로 착각한다
-- 틀린 코드: 회원별 총 구매액을 매 행에 표시하려 했는데
SELECT MEMBER_ID, ORDER_ID, AMOUNT,
SUM(AMOUNT) OVER (PARTITION BY MEMBER_ID ORDER BY ORDER_DATE) AS TOTAL_AMOUNT
FROM ORDER_JOINED;
-- ORDER BY 가 있어 기본 프레임이 RANGE UNBOUNDED PRECEDING TO CURRENT ROW 가 되어
-- TOTAL_AMOUNT 가 누적합으로 나온다-- 고친 코드: ORDER BY 를 빼거나 프레임을 명시한다
SELECT MEMBER_ID, ORDER_ID, AMOUNT,
SUM(AMOUNT) OVER (PARTITION BY MEMBER_ID) AS TOTAL_AMOUNT
FROM ORDER_JOINED;WHERE 절에 윈도 함수를 직접 쓴다
-- 틀린 코드: WHERE 절에서 윈도 함수의 별칭을 바로 쓸 수 없다
SELECT ORDER_ID, AMOUNT,
RANK() OVER (PARTITION BY GRADE ORDER BY AMOUNT DESC) AS RANK_NO
FROM ORDER_JOINED
WHERE RANK_NO = 1;-- 고친 코드: 서브쿼리나 CTE 로 한 번 감싼 뒤 바깥에서 필터링한다
WITH RANKED AS (
SELECT ORDER_ID, AMOUNT,
RANK() OVER (PARTITION BY GRADE ORDER BY AMOUNT DESC) AS RANK_NO
FROM ORDER_JOINED
)
SELECT * FROM RANKED WHERE RANK_NO = 1;LAG·LEAD 에 PARTITION BY 를 빠뜨린다
-- 틀린 코드: 전체 주문을 날짜순으로 한 줄로 보고 비교해버린다
SELECT MEMBER_ID, ORDER_DATE, AMOUNT,
LAG(AMOUNT) OVER (ORDER BY ORDER_DATE) AS PREV_AMOUNT
FROM ORDER_JOINED;
-- 결과: 바로 앞 날짜가 다른 회원 주문이면 엉뚱한 값과 비교하게 된다-- 고친 코드: PARTITION BY 로 같은 회원 안에서만 비교한다
SELECT MEMBER_ID, ORDER_DATE, AMOUNT,
LAG(AMOUNT) OVER (PARTITION BY MEMBER_ID ORDER BY ORDER_DATE) AS PREV_AMOUNT
FROM ORDER_JOINED;동점 처리용 정렬 키를 빠뜨린다
-- 틀린 코드: AMOUNT 가 같은 행이 여러 개면 순서가 보장되지 않는다
SELECT ORDER_ID, AMOUNT,
ROW_NUMBER() OVER (PARTITION BY GRADE ORDER BY AMOUNT DESC) AS ROW_NO
FROM ORDER_JOINED;-- 고친 코드: 동점을 깨는 열(ORDER_ID 등)을 추가해 결과를 고정한다
SELECT ORDER_ID, AMOUNT,
ROW_NUMBER() OVER (PARTITION BY GRADE ORDER BY AMOUNT DESC, ORDER_ID) AS ROW_NO
FROM ORDER_JOINED;한눈에 보기
| 키워드 | 역할 | 주의점 |
|---|---|---|
| PARTITION BY | 그룹을 나눠 그룹 안에서만 계산을 독립시킨다 | 빠뜨리면 전체 테이블을 한 그룹으로 계산한다 |
| OVER() 안 ORDER BY | 순위·누적 계산의 정렬 기준을 정한다 | 있는 순간 기본 프레임이 누적형으로 바뀐다 |
| ROWS / RANGE | 프레임을 물리적 행 또는 값 기준으로 정한다 | 동점이 있으면 두 결과가 달라진다 |
| LAG / LEAD | 이전·다음 행 값을 가져와 증감을 계산한다 | PARTITION BY 없이 쓰면 다른 그룹과 섞인다 |
연습 문제
- GOLD 등급에서 28,000원 주문이 두 건(O103, O108) 있을 때, RANK, DENSE_RANK, ROW_NUMBER 값이 각 함수마다 어떻게 다른지 설명하라.
- SILVER 등급 회원(M02, M04)의 주문 중 금액이 가장 큰 1건만 뽑고 싶다. WHERE 절에 윈도 함수를 바로 쓸 수 없는 이유를 설명하고, 올바른 쿼리를 작성하라.
- M03 의 주문은 O104(2026-02-10, 22,000원), O108(2026-03-20, 28,000원) 두 건이다. 날짜순으로 CUM_AMOUNT(누적 합계)와 LAG 를 이용한 DIFF_AMOUNT(직전 주문과의 차이)를 계산하라.
- SILVER 등급 주문 금액은 39,000원, 30,000원, 16,000원으로 동점이 없다. 이 경우 ROWS 프레임과 RANGE 프레임으로 각각 누적 합계를 구하면 두 결과가 같아지는 이유를 설명하라.
정답과 해설
1번 RANK 는 두 건 모두 1위를 주고 다음 행인 22,000원 두 건에는 3위를 준다(2위를 건너뜀). DENSE_RANK 는 두 건 모두 1위를 주고 다음 22,000원 두 건에는 2위를 준다(건너뛰지 않음). ROW_NUMBER 는 동점이어도 1, 2 처럼 서로 다른 고유 번호를 매기며, 어느 행이 1번이 되는지는 ORDER BY 에 동점 처리용 열(예: ORDER_ID)을 추가해야 정해진다.
2번 RANK, DENSE_RANK, ROW_NUMBER 같은 윈도 함수는 SELECT 목록에서만 계산되고 WHERE 절이 실행되는 단계에는 아직 값이 없어 직접 참조할 수 없다. CTE 나 서브쿼리로 한 번 감싼 뒤 바깥 쿼리의 WHERE 절에서 걸러야 한다.
WITH RANKED AS (
SELECT o.ORDER_ID, m.MEMBER_ID, od.AMOUNT,
ROW_NUMBER() OVER (ORDER BY od.AMOUNT DESC, o.ORDER_ID) AS ROW_NO
FROM ORDERS o
JOIN MEMBER m ON m.MEMBER_ID = o.MEMBER_ID
JOIN ORDER_DETAIL od ON od.ORDER_ID = o.ORDER_ID
WHERE m.GRADE = 'SILVER'
)
SELECT * FROM RANKED WHERE ROW_NO = 1;
-- 결과: O106, M04, 39000, 13번 CUM_AMOUNT 는 O104 에서 22,000, O108 에서 22,000+28,000=50,000 이다. DIFF_AMOUNT 는 O104 에서 이전 주문이 없어 NULL, O108 에서 28,000-22,000=6,000 이다.
4번 RANGE 는 동점(같은 ORDER BY 값)이 있을 때만 여러 행을 하나의 피어 그룹으로 묶어 ROWS 와 다른 결과를 만든다. SILVER 주문 금액은 39,000, 30,000, 16,000 으로 모두 값이 달라 각 행이 자기 혼자만의 피어 그룹이 되므로, RANGE 도 ROWS 와 마찬가지로 한 행씩 누적하게 되어 두 결과가 39,000 / 69,000 / 85,000 으로 같아진다.