[SQL기본] 윈도우 함수

1. 윈도우 함수 구성

MySQL의 윈도우 함수는 레코드 집합 전체 또는 특정 파티션에 대해 계산을 수행함녀서도 각 행을 그대로 유지하는 기능입니다. 주로 누적 합계, 순위, 이동 평균, 비율 등을 구할 때 사용됩니다.

함수명() OVER( PARTITION BY  ..... ORDER BY.... ROWS BETWEEN ...AND..)

 

윈도우 함수는  <함수명> 뒤에 OVER 절이 옵니다. OVER 절 안에는 PARTITION BY/ ORDER BY/ ROWS BETWEN...AND 절에 올 수 있는데요, 각각의 기능은 다음과 같습니다.

구성 요소	기능	사용 목적
PARTITION BY	데이터를 그룹별로 나눔	부서별, 지역별, 고객별 등 그룹 단위 계산
ORDER BY	그룹 내에서 정렬 기준 지정	순위, 누적합, 이동 평균 등 순서 기반 함수 계산에 필수
ROWS BETWEEN	정렬된 데이터에서 계산할 범위 지정 (프레임 절)	슬라이딩 평균, 누적값 등에서 어디부터 어디까지 계산할지 명시

 

---

2. 윈도우 함수 종류

분석 목적	함수	설명	예시
누적 합계	SUM(column)	지정된 윈도우 내의 합계를 계산	SUM(sales) OVER (ORDER BY date)
평균	AVG(column)	윈도우 범위 내 평균값을 계산	AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
누적 개수	COUNT(column)	윈도우 내의 행 수를 카운트	COUNT(*) OVER (PARTITION BY dept)
최소 / 최대값	MIN(column) / MAX(column)	윈도우 내 최소/최대값 반환	MAX(salary) OVER (PARTITION BY dept)
순위	RANK()	동순위 인정, 순위 건너뜀 (1,1,3...)	RANK() OVER (ORDER BY score DESC)
	DENSE_RANK()	동순위 인정, 순위 연속 (1,1,2...)	DENSE_RANK() OVER (ORDER BY score DESC)
고유 순번	ROW_NUMBER()	모든 행에 고유 순번 부여 (중복 없음)	ROW_NUMBER() OVER (ORDER BY score DESC)
그룹 나누기	NTILE(n)	전체를 n개의 그룹으로 나눔 (동일한 행 수로 분할)	NTILE(4) OVER (ORDER BY score)
이전 값 참조	LAG(column, offset, default)	지정한 이전 행의 값을 반환	LAG(score, 1) OVER (ORDER BY id)
다음 값 참조	LEAD(column, offset, default)	지정한 다음 행의 값을 반환	LEAD(score, 1) OVER (ORDER BY id)
첫 번째 값	FIRST_VALUE(column)	파티션 또는 정렬 범위 내 첫 번째 값 반환	FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC)
마지막 값	LAST_VALUE(column)	파티션 또는 정렬 범위 내 마지막 값 반환	LAST_VALUE(salary) OVER (...) ※ 프레임 지정 필요
N번째 값	NTH_VALUE(column, n)	파티션 내 n번째 값 반환	NTH_VALUE(salary, 2) OVER (...)
누적 분포	CUME_DIST()	해당 값 이하의 비율 반환 (0~1), 하위 몇 %에 속하는가?	CUME_DIST() OVER (ORDER BY salary) "상위 10% 고객 찾기" 에서 사용할 수 있음
백분위 순위	PERCENT_RANK()	백분위 순위 (0부터 시작, 최댓값 1)	PERCENT_RANK() OVER (ORDER BY salary)
전체 대비 비율	SUM() + 나눗셈	전체 합 대비 현재 값의 비율 (수동 계산)	salary / SUM(salary) OVER ()

 

 

---

2023년 4월 기준 전국 주요 시/도의 연령대별 인구수 합계를 구하고, 연령대별 인구 비율 구하기

✅step1 :2023년 4월 기준 전국 주요 시/도의 연령대별 인구수 합계를 구하기

SELECT AGRDE_SE_CD, sum(POPLTN_CNT) pop_cnt
FROM tb_popltn
WHERE ADMINIST_ZONE_NO LIKE '__00000000'
AND POPLTN_SE_CD = 'T'
AND STD_MT = '202304'
GROUP BY AGRDE_SE_CD
ORDER BY AGRDE_SE_CD;

 

 

 

✅step2: step1의 테이블을 from 절에 subquery로 놓고 sum() over()를 사용해 집계.

** sum(pop_cnt) over() : pop_cnt 컬럼의 전체 합을 구할 수 있다.

SELECT *, pop_cnt/pop_total as pop_ratio
FROM 
(SELECT *, SUM(pop_cnt) OVER() as pop_total
FROM (SELECT AGRDE_SE_CD, sum(POPLTN_CNT) as pop_cnt
FROM tb_popltn
WHERE STD_MT = '202304'
AND ADMINIST_ZONE_NO LIKE '__00000000'
AND POPLTN_SE_CD = 'T'
GROUP BY AGRDE_SE_CD
ORDER BY AGRDE_SE_CD) A) B;

 SUM() OVER()가 뭔데?

SUM()은 주어진 열의 값을 합하는 데 사용되며 OVER()가 함께 사용될 때 윈도우 함수로 동작한다.

SUM() 함수를 OVER() 절과 함께 사용하면 특정 윈도우(ex. 파티션 or 순서) 내에서 합계를 계산할 수 있다. 

여기서는 OVER() 안에 PARTITION BY나 ORDER BY 등을 사용하지 않았기에 pop_cnt 컬럼 전체의 합이 산출된 것.

 

자, 여기서 다시 한 번 예시를 들어볼까?

아래는 도시별 인구에 대한 표이다.

 

이 표로부터 국가별 인구 수 합계를 알고싶다면?? SUM() OVER()를 사용하면 된다.

SELECT * ,SUM(Population) OVER(PARTITION BY CountryCode) as country_pop
FROM city;

위의 표를 확인해보면 국가코드 별로, 즉 국가 별로 인구 수(population)의 합계인 country_pop 열이 생긴 것을 볼 수 있다.

만약.. 각 도시의 인구가 국가 전체에서 몇 %를 차지하는지 알고싶다면?

위의 쿼리를 subquery로 사용하면 되겠지?

 

SELECT *, Population/country_pop as pop_ratio
FROM (
		SELECT * ,SUM(Population) OVER(PARTITION BY CountryCode) as country_pop
		FROM city) A;

이전에 산출했던 쿼리를 from 절에 넣고, Population/country_pop 식을 SELECT 절에 추가하면, tada~~

전체 국가에서 각 도시의 인구가 차지하는 비율을 알 수가 있다!