[SQL] 윈도우 함수 — 분석가의 필수요소

1. 왜 윈도우 함수가 중요한가

SQL을 배우면서 대부분 이런 흐름을 거친다.

• WHERE, GROUP BY, JOIN은 어느 정도 익숙해짐

• 집계 함수(SUM, AVG, COUNT)도 쓸 수 있음

 

그런데 “행을 유지하면서 분석 지표를 같이 보고 싶다”는 순간 막히기도 한다.

예를 들면 이런 요구사항이다.

• 각 직원의 급여와 함께, 같은 부서 평균 급여를 같이 보고 싶다
• 매일 매출 데이터에서, 누적 매출과 일별 증감을 보고 싶다
• 동일 그룹 내에서 순위를 매기고 싶다

이때는 기존의 GROUP BY는 한계가 있다.

 

GROUP BY → 행을 줄이고, 집계 결과만 남긴다

분석에서는 오히려 → 행은 그대로 두고 분석용 컬럼을 추가하고 싶다.

이때 이 역할을 수행 하는 것이 바로 윈도우 함수(Window Function)다.

---

2. 윈도우 함수의 핵심 개념

집계를 하되, 행을 줄이지 않는다.

 

기존의 집계함수

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

 

• 부서별 평균은 나오지만

• 개별 직원 정보는 사라진다

 

윈도우 함수

SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

 

• 직원 한 명당 한 행 유지

• 같은 부서의 평균 급여를 옆에 붙여서 보여준다

---

3. OVER(PARTITION BY ...) 구조 이해하기

집계함수() OVER (PARTITION BY 기준컬럼)

윈도우 함수는 항상 OVER()와 함께 사용된다.

 

수도코드로 보면 다음과 같다.

전체 테이블을 가져온다 PARTITION BY 기준으로 그룹을 나눈다 각 그룹 안에서 집계를 계산한다 결과를 각 행 옆에 붙인다

중요한 점은 GROUP BY처럼 행을 합치지 않는다는 것이다.

---

4. 그룹 합계 / 평균 구하기

그룹 합 / 개수 / 평균을 행을 유지한 채로 붙이기

• COUNT/SUM/AVG OVER

• 집계 결과를 만들되, 행을 줄이지 않는다

집계함수(...) OVER (
PARTITION BY ...
ORDER BY ...
)

PARTITION BY → 어떤 기준으로 “그룹을 나눌지”
ORDER BY → 파티션 안에서의 순서 (선택)

PARTITION BY로 나눈 “그룹 안에서” 집계함수(SUM / COUNT / AVG)가 계산된다

 

예제: 부서별 평균 급여를 직원별로 확인

직원 데이터를 하나씩 본다 같은 부서 직원들만 묶는다 그 부서의 평균 급여를 계산한다 각 직원 행에 평균 급여를 붙인다

 

SQL 예제

SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

실행 순서 (Line by Line 개념)

1. employees 테이블 전체 조회 2. department 기준으로 파티션 생성 3. 각 파티션 내에서 AVG(salary) 계산 4. 계산된 평균을 각 행에 출력

---

5. 순번과 순위 함수

랭킹 계열 윈도우 함수 = 그룹 안에서 “줄 세우기”

• ROW_NUMBER / RANK / DENSE_RANK

🚨이 번호는 PARTITION BY로 묶인 그룹 안에서만 의미가 있다

랭킹함수() OVER (
    PARTITION BY 그룹기준
    ORDER BY 정렬기준
)

5-1. ROW_NUMBER()

무조건 1,2,3… 번호 (동점이어도 나눠짐)

ROW_NUMBER() OVER (
PARTITION BY ...
ORDER BY ...
)

개념

• 파티션 내에서 각 행에 고유한 번호를 부여

• 동점이 있어도 번호는 무조건 나뉜다

• 결과는 항상: 1, 2, 3, 4, …

→순위 보단 순번에 가까움

 

⭐ 포인트

• ORDER BY가 없으면 결과가 비결정적 (실행할 때마다 달라질 수 있음)

• “가장 최신 1건”, “각 그룹의 대표 행 1개” 뽑을 때 자주 사용

 

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

수도코드

부서별로 나눈다 급여 높은 순으로 정렬한다 위에서부터 번호를 매긴다

---

5-2. RANK

동점이면 같은 순위, 다음 순위는 건너뜀(갭)

RANK() OVER (
PARTITION BY ...
ORDER BY ...
)

개념적으로 “등수 개념에 가까움” (1등이 2명이면 다음은 3등)

---

 

5-3. DENSE_RANK

동점이면 같은 순위, 다음 순위는 안 건너뜀(촘촘)

DENSE_RANK() OVER (
PARTITION BY ...
ORDER BY ...
)

개념적으로 “그룹 내 순서 레벨” (1 → 2 → 3 처럼 촘촘하게 증가)

---

표로 살펴보기

상황	ROW_NUMBER	RANK	DENSE_RANK
동점 처리	무시	같은 순위	같은 순위
다음 순위	연속	건너뜀	연속
대표 1행	최적	x	x

“각 그룹에서 1행만 뽑고 싶다”
→ ROW_NUMBER()

“순위 자체가 의미 있는 경우”

→ RANK()

“등급 / 레벨처럼 연속된 순위가 필요”
→ DENSE_RANK()

 

🚨 햇갈리는 포인트! 🚨

전체 테이블에서 등수를 정하는게 아님!

PARTITION BY 로 나눠진 컬럼 내부에서 등수를 매기는 것!

 

머리속에서 이렇게 이해하기

user A: 1, 2, 3 ... user B: 1, 2 ... user C: 1 ...

 

만약 PARTITION BY를 쓰지 않고 ROW_NUMBER() OVER (ORDER BY created_at DESC)를 쓰면?

전체 순위가 나옴.

---

6. 누적합 (Cumulative Sum)

누적합/비율/최근 N개 평균 (SUM/AVG OVER + ORDER BY + Frame)

• 행의 위치에 따라 계산에 포함되는 범위가 달라진다

 

어떨때 사용하나?

• 하루하루 매출이 쌓여서 지금까지 총 매출

• 이 주문이 전체 매출 중 몇 %

• 최근 3일 평균 매출

 

 

누적합: 프레임(Frame)

ORDER BY가 있는 윈도우 집계에는 기본 Frame이 자동 적용된다

ROWS BETWEEN [시작 지점] AND [끝 지점]

 

⭐ Frame 문법정리

Frame	의미
UNBOUNDED PRECEDING	맨 처음부터
CURRENT ROW	현재 행
N PRECEDING	현재 기준 앞의 N행
N FOLLOWING	현재 기준 뒤의 N행

 

-- 누적합 형태
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

개념

• “그룹의 첫 행 ~ 현재 행까지 누적” 이라는 뜻

• 물리적인 행 기준
• "앞에서부터 지금 이 행까지"를 명확히 보장

→ 누적합 = ROWS 프레임을 떠올리자!

 

비유
시험 성적을 한 번 볼 때마다
지금까지 받은 점수를 전부 더한 총점

 

생각의 흐름

1. PARTITION BY → 누구 기준? 2. ORDER BY → 어떤 순서? 3. Frame → 몇 개를 볼까?

 

문법 형태 예시

함수(...) OVER (
PARTITION BY ...
ORDER BY ...
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

---

6-1. 각 행의 비율 계산

개념

• 한 행의 값은 그대로 둔다
• 같은 그룹의 전체 합계를 윈도우 함수로 붙인다
• 둘을 나눈다

🚨 GROUP BY는 행이 줄어들기 떄문에 불가능

value / SUM(value) OVER (PARTITION BY group_key)

이 행의 값 = value
이 행이 속한 그룹의 전체합= SUM(value) OVER (PARTITION BY group_key)

 

---

7. LAG / LEAD — 이전 값과 다음 값

“이전/다음 행 비교” (LAG / LEAD) + NULL 처리(IFNULL/COALESCE)

 

어떨때 사용하나?

• 이번 달 매출이 지난달보다 얼마나 늘었나?

• 이 로그의 이전 상태는 뭐였지?

• 오늘 점수가 어제보다 올랐나 떨어졌나?

공통점?

“내 옆 행(이전/다음)과 비교”

 

GROUP BY 나 집계(SUM/COUNT)나 순위(ROW_NUMBER, RANK, DENSE_RANK)와 달리,
LAG / LEAD는 행 자체를 이동해서 참조하는 함수

---

 

7-1) LAG / LEAD의 개념 졉근

LAG(값)

• 현재 행 기준 → 이전 행 값 가져오기
• “어제 값”, “이전 상태”, “직전 이벤트”를 볼 때 사용

 

LEAD(값)

• 현재 행 기준 → 다음 행 값 가져오기
• “다음 값”, “다음 상태”, “이후 이벤트”를 볼 때 사용

LAG(컬럼, 몇 칸 전, 기본값) OVER (
    PARTITION BY ...
    ORDER BY ...
)

LEAD(컬럼, 몇 칸 후, 기본값) OVER (
    PARTITION BY ...
    ORDER BY ...
)

 

LAG(값) : 내 바로 “이전 행”의 값을 가져온다
LEAD(값) : 내 바로 “다음 행”의 값을 가져온다

 

ORDER BY

→ 는 사실상 필수(“전/후” 기준이 필요)

 

⭐ORDER BY는 사실상 필수

LAG / LEAD에서 ORDER BY는 선택이 아니라 의미를 결정하는 기준
ORDER BY가 없을 경우:
“이전/다음”의 기준이 없다
결과는 비결정적 (매 실행마다 달라질 수 있음)

 

⭐ NULL이 생기는 이유 (중요)

LAG / LEAD는 구조적으로 NULL을 만들 수밖에 없다.

• 파티션의 첫 행 → LAG = NULL

• 파티션의 마지막 행 → LEAD = NULL

이 NULL은 “데이터가 없는 것”이 아닌

“참조할 이전/다음 행이 구조적으로 존재하지 않음” 을 의미

→반환값(default)를 설정하거나 IFNULL, COALESCE을 사용하자

---

 

추가내용

그룹별 Top N 데이터 추출하기

분석 SQL을 어느 정도 다루다 보면 반드시 마주치는 요구사항이 있다.

• 카테고리별 상위 상품 3개
• 부서별 급여 Top 2 직원
• 사용자별 최근 주문 1건

• 날짜별 매출 상위 N건

이런 문제는 모두 같은 구조를 가진다.

 

그룹마다 상위 N개 행만 남기기 (Top N per Group)

그룹별 Top N 문제는 다음 3가지 요소로 구성된다.

1. 그룹을 나눈다 → PARTITION BY
2. 그룹 안에서 정렬한다 → ORDER BY
3. 순번을 붙이고 잘라낸다 → ROW_NUMBER / RANK + 필터링

즉, 정렬 + 순번 + 필터 이 조합이 항상 등장한다.

---

접근 방법 정리

처음 문제를 보면 바로 SQL을 쓰려고 하지 말고 다음 질문을 순서대로 던져보자

 

Step 1. 무엇 기준으로 “Top”인가?

가장 먼저 정렬 기준을 명확히 한다.

• 가격 기준 Top?

• 날짜 기준 최신?

• 점수 기준 상위?

이 기준이 ORDER BY가 된다.

 

Step 2. 그룹은 무엇인가?

그 다음 “그룹마다”의 기준을 정한다.

• 부서별?

• 사용자별?

• 카테고리별?

이 기준이 PARTITION BY가 된다.

 

Step 3. 순번을 붙인다.

그룹 내부에서 정렬된 상태로 각 행에 순번을 붙인다.

ROW_NUMBER() OVER (
    PARTITION BY 그룹컬럼
    ORDER BY 기준컬럼 DESC
)

이때 보통 별칭으로 rn을 사용한다.

 

Step 4. N까지만 남긴다

마지막으로 순번을 기준으로 잘라낸다.

WHERE rn <= N

 

 

이것만 기억하자!

 

• 그룹 안에서 순위를 매긴다
• 그 순위를 WHERE로 잘라낸다

---

ROW_NUMBER vs RANK 선택 기준

• ROW_NUMBER → 무조건 1, 2, 3… (동점 무시)
• RANK → 동점 허용, 순위 건너뜀