[MySQL] Windows Functions 정리

Jinyoung Cheon·2025년 10월 29일

mysql study

1. 윈도우 함수 (Windowns Functions)

데이터를 집계하거나 순위를 매길 때 GROUP BY를 사용하지 않고
개별 행의 속성을 유지하면서 계산할 수 있게 해주는 기능

일반적인 집계 함수(SUM, AVG 등)는 GROUP BY 절과 함께 쓰여 여러 행을 하나의 행으로 압축(Collapse)한다.

하지만 윈도우 함수는 행을 압축하지 않는다.
대신, 쿼리 결과의 각 행에 대해 윈도우라고 불리는 특정 행 집합을 기준으로 계산한 값을 추가 열로 보여준다.

비유: 반 학생들의 '반 평균' 점수를 알고 싶을 때,

GROUP BY: 'A반'의 평균 점수 70점'이라는 한 줄의 결과만 보여준다.
윈도우 함수: '철수 (A반)'의 점수 80점, 'A반 평균' 70점 / '영희 (A반)'의 점수 60점, 'A반 평균' 70점... 처럼 모든 학생 목록을 보여주면서 각 학생 옆에 반 평균을 같이 보여준다.

2. 기본 문법

SELECT 
	함수명(인자) OVER (
		[PARTITION BY 컬럼1, 컬럼2 ...]
		[ORDER BY 컬럼3, 컬럼4 ...] 
	) AS 별칭
FROM 테이블명;

FUNCTION() :사용할 윈도우 함수 (예: RANK(), SUM())
OVER(): 윈도우 함수임을 명시.
PARTITION BY (선택): 윈도우를 나눌 기준.(예: PARTITION BY department -> 부서별로 윈도우를 나눔)
ORDER BY (선택): 윈도우 내에서 어떤 순서로 계산할지 정한다. (예: 급여 순, 입사일 순)

3. 예시 데이터

id	name	department	salary
1	Alice	Sales	5000
2	Bob	Sales	6000
3	Charlie	Sales	5000
4	David	Engineering	7000
5	Eve	Engineering	8000
6	Frank	Marketing	5500

4. 주요 윈도우 함수

4-1. 순위 함수 (Ranking Functions)

PARTITION BY로 그룹을 나누고, 그 안에서 ORDER BY로 순위를 매긴다.

ROW_NUMBER()

의미: 윈도우 내에서 순서를 1부터 차례대로 계산한다. (중복 값도 다른 순위 부여)
사용법: ROW_NUMBER() OVER (ORDER BY salary DESC)

예시: 전체 직원 급여 순위 (동점자도 다른 순위)

SELECT
	name, department, salary,
	ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

결과:
name department salary row_num
Eve Engineering 8000 1
David Engineering 7000 2
Bob Sales 6000 3
Frank Marketing 5500 4
Alice Sales 5000 5
Charlie Sales 5000 6

name	department	salary	row_num
Eve	Engineering	8000	1
David	Engineering	7000	2
Bob	Sales	6000	3
Frank	Marketing	5500	4
Alice	Sales	5000	5
Charlie	Sales	5000	6

RANK()

의미: 순위를 매기되, 동점자에게는 같은 순위를 부여. 단, 다음 순위는 동점자 수를 건너뛰고 계산한다.
사용법: RANK() OVER (PARTITION BY department ORDER BY salary DESC)

예시: 부서별 급여 순위 (동점자 같은 순위, 중간 순위 비움)

SELECT
	name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees;

결과:

name department salary rnk
Eve Engineering 8000 1
David Engineering 7000 2
Frank Marketing 5500 1
Bob Sales 6000 1
Alice Sales 5000 2
Charlie Sales 5000 2

[참고] Sales 부서에서 1위(Bob) 다음 2위가 2명(Alice, Charlie)이므로 다음 순위인 3위는 없고 1, 2, 2가 된다.
만약 4위가 있었다면 4위가 된다.

name	department	salary	rnk
Eve	Engineering	8000	1
David	Engineering	7000	2
Frank	Marketing	5500	1
Bob	Sales	6000	1
Alice	Sales	5000	2
Charlie	Sales	5000	2

DENSE_RANK()

의미: RANK()와 같지만, 다음 순위를 건너뛰지 않는다. (예: 1, 2, 2, 3)
사용법: DENSE_RANK() OVER (ORDER BY salary DESC)

예시: 전체 직원 급여 순위 (동점자 같은 순위, 중간 순위 비우지 않음)

SELECT
	name, department, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

결과:

name department salary dense_rnk
Eve Engineering 8000 1
David Engineering 7000 2
Bob Sales 6000 3
Frank Marketing 5500 4
Alice Sales 5000 5
Charlie Sales 5000 5

name	department	salary	dense_rnk
Eve	Engineering	8000	1
David	Engineering	7000	2
Bob	Sales	6000	3
Frank	Marketing	5500	4
Alice	Sales	5000	5
Charlie	Sales	5000	5

4-2. 집계함수 (Aggregate Functions)

SUM, AVG, COUNT, MAX, MIN 등 기존 집계 함수를 OVER()와 함께 사용.

SUM() OVER ()

의미: 윈도우 내의 합계를 구한다.
사용법 1 (누적 합계): SUM(salary) OVER (PARTITION BY department ORDER BY id)
사용법 2 (그룹 전체 합계): SUM(salary) OVER (PARTITION BY department)

예시: 부서별 급여 합계 및 누적 급여

SELECT
	name, department, salary,
	-- 부서별(PARTITION BY)로 총 급여 합계 (ORDER BY 없음)
	SUM(salary) OVER (PARTITION BY department) AS dept_total_salary,
	-- 부서별(PARTITION BY)로 id 순서 (ORDER BY)대로 누적 급여
	SUM(salary) OVER (PARTITION BY department ORDER BY id) AS running_total
	FROM employees;

결과:

name	department	salary	dept_total_salary	running_total
David	Engineering	7000	15000	7000
Eve	Engineering	8000	15000	15000
Frank	Marketing	5500	5500	5500
Alice	Sales	5000	16000	5000
Bob	Sales	6000	16000	11000
Charlie	Sales	5000	16000	16000

AVG() OVER ()

의미: 윈도우 내의 평균을 구합니다.
사용법: AVG(salary) OVER (PARTITION BY department)

예시: 각 직원의 급여와 해당 직원이 속한 부서의 평균 급여를 함께 보기

SELECT
	name, department, salary,
	AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM emplyees;

결과: (모든 Sales 부서 직원은 동일한 dept_avg_salary 5333.33을 가짐

name department salary dept_avg_salary
David Engineering 7000 7500.00
Eve Engineering 8000 7500.00
Frank Marketing 5500 5500.00
Alice Sales 5000 5333.33
Bob Sales 6000 5333.33
Charlie Sales 5000 5333.33

name	department	salary	dept_avg_salary
David	Engineering	7000	7500.00
Eve	Engineering	8000	7500.00
Frank	Marketing	5500	5500.00
Alice	Sales	5000	5333.33
Bob	Sales	6000	5333.33
Charlie	Sales	5000	5333.33

4-3. 값 함수 (Value Functions)

윈도우 내에서 특정 위치의 값(예: 이전 행, 다음 행)을 가져온다. ORDER BY가 필수.

1.RAG()

의미: 윈도우 내에서 이전(Previous) 행의 값을 가져옵니다.
사용법: LAG(가져올 컬럼, [몇 칸 앞인지, 기본값 1], [이전 행이 없을 때 기본값]) OVER (ORDER BY ...)

예시: 부서별로 정렬했을 때, 바로 이전 직원의 급여 가져오기

SELECT
	name, department, salary,
	LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) 	AS prev_salary
FROM employees;

결과: (Engineering의 David, Marketing의 Frank, Sales의 Alice/Charlie는 부서 내 첫 번째 순서이므로 prev_salary가 0)

name department salary prev_salary
David Engineering 7000 0
Eve Engineering 8000 7000
Frank Marketing 5500 0
Alice Sales 5000 0
Charlie Sales 5000 5000
Bob Sales 6000 5000

name	department	salary	prev_salary
David	Engineering	7000	0
Eve	Engineering	8000	7000
Frank	Marketing	5500	0
Alice	Sales	5000	0
Charlie	Sales	5000	5000
Bob	Sales	6000	5000

2.LEAD()

의미: 윈도우 내에서 다음(Next) 행의 값을 가져온다.
사용법: LEAD(가져올 컬럼, [몇 칸 뒤인지, 기본값 1], [다음 행이 없을 때 기본값]) OVER (ORDER BY ...)

예시: 부서별로 정렬했을 때, 바로 다음 직원의 급여 가져오기

SELECT
	name, department, salary,
    LEAD(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS next_salary
FROM employees;

결과: (Engineering의 Eve, Marketing의 Frank, Sales의 Bob은 부서 내 마지막 순서이므로 next_salary가 0)

name department salary next_salary
David Engineering 7000 8000
Eve Engineering 8000 0
Frank Marketing 5500 0
Alice Sales 5000 5000
Charlie Sales 5000 6000
Bob Sales 6000 0

name	department	salary	next_salary
David	Engineering	7000	8000
Eve	Engineering	8000	0
Frank	Marketing	5500	0
Alice	Sales	5000	5000
Charlie	Sales	5000	6000
Bob	Sales	6000	0

Jinyoung Cheon

데이터를 향해, 한 걸음씩 천천히.

이전 포스트