[MYSQL] 윈도우 함수 정리

▤ 목차

1. 윈도우 함수란?

윈도우함수(Window Function)란 행과 행 간의 관계를 정의하기 위해 제공되는 함수를 의미한다. 

집계함수(AVG, SUM, COUNT) 혹은 윈도우 함수 전용 함수(ROW NUMBER, LEAD, LAG)로 분류된다. 
윈도우 함수를 통해 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.

1. 윈도우 함수의 구조

Window Function(함수_적용_열) OVER (PARTITION BY 그룹열 ORDER BY 순서열)

-- Window Function : 순위함수(RANK, DENSE_RANK 등)나 집계함수(SUM, MAX 등)
-- PARTION BY : 소그룹으로 분류 
-- ORDER BY : 분류된 소그룹 정렬
-- 순서열 : 정렬 기준 행 설정

2. 윈도우 함수 종류

분류	예시
그룹내 순위 함수	RANK, DENSE_RANK, ROW_NUMBER
그룹내 집계 함수	SUM, MAX, MIN, COUNT, AVG
데이터 위치 바꾸기	LAG, LEAD

2. 윈도우 함수와 GROUP BY의 차이점

윈도우 함수(PARTITION BY)와 GROUP BY는 데이터를 그룹으로 묶어 반환하고 집계함수를 사용할 수 있다는 점에서 유사한 특징을 가지고 있다.

그러나 데이터를 하나로 합쳐주는 과정에서 큰 차이점이 존재한다.

GROUP BY는 기존의 상세 데이터들을 잃어버리지만 PARTITION BY는 기존 행의 세세한 정보들이 사라지지 않고 그대로 유지된다.   

1.GROUP BY

GROUP BY 절은 특정 칼럼을 기준으로 집계 함수를 사용하여 건수(COUNT), 합계(SUM), 평균(AVG) 등의 집계데이터를 추출할 수 있다.

쿼리의 작동순서는  from - where - group by - having - select - orderby - limit이다.

즉 GROUP BY는 집계 함수를 사용하여 기존 행에 있던 값들을 계산한 후 새로운 행에 입력해 주는데, 그룹화를 하면서 기존의 상세 데이터들을 잃게 된다.

때문에 GROUP BY 뒤에 오는 SELECT/ ORDER BY에서 개별데이터를 사용하게 되면 에러가 발생한다.

2. PARTITION BY

PARTITION BY는 SELECT절에서 사용되기 때문에 GROUP BY와는 달리 기존 행의 세세한 정보들은 사라지지 않고 그대로 유지된다.

즉 기존의 데이터의 수가 줄어들지 않으며, 집계된 값을 같이 볼 수 있도록 해준다. 

요약하자면
GROUP BY -- 행이 집약되기 때문에 세부데이터를 볼 수 없다.
PARTITION BY  -- 행이 집약되지 않기 때문에 세부 데이터를 볼 수 있다.  

3. 윈도우 함수 활용

1. 순위함수

SELECT score
     , RANK() OVER (ORDER BY score DESC) AS rank
     , DENSE_RANK() OVER (ORDER BY score DESC) as d_rank
     , ROW_NUMBER() OVER (ORDER BY score DESC) as rownum
   FROM score

• RANK() :  공동순위 다음 동점의 수만큼 순위를 뒤로 밀어냄
• DENSE_RANK() : 공동순위 다음 순위를 밀어내지 않음
• ROWNUM() : 정렬된 순서로 순번을 매김

score	rank	d_rank	rownum
100	1	1	1
95	2	2	2
95	2	2	3
90	4	3	4

2. 집계함수

SELECT  score,
	subject,
	SUM(score) OVER() AS total_score,  -- 누적합
	SUM(score) OVER(PARTITION BY math) AS math_score  -- 그룹별 누적합
FROM score

• SUM() : 윈도우 내에서 지정된 열의 합계
• AVG() : 평균
• MIN() : 최솟값
• MAX() : 최댓값
• COUNT() : 개수

그냥 OVER()로 사용하게 되면 모든 row 집합을 대상으로 값을 구하며, 전체 행의 score값을 더해 total_score 값을 구한다.
두 번째 OVER()에서는 math 컬럼으로 partition하여 score 값의 합을 구해 주었다.  

score	subject	total_score	math_score
100	Math	380	190
95	English	380	190
95	English	380	190
90	Math	380	190

3. 데이터 위치 바꾸기

SELECT 
	LAG(score, 1 ,0) OVER (ORDER BY score) AS prev_score -- 1행 이전값
	,LEAD(score, 1 ,0) OVER (ORDER BY score) AS next_score -- 1행 이후값
FROM score

• LAG(열, n, null값 대체이름) : n칸 미룬 값 가져오기
• LEAD(열, n, null값 대체이름) : n칸 당긴 값 가져오기

 

score	prev_score	next_score
100	0	95
95	100	90
90	95	0

 

https://jie0025.tistory.com/298

[MySQL] GROUP BY, PARTITION BY 차이점

https://mooonstar.tistory.com/entry/MYSQL%EC%9C%88%EB%8F%84%EC%9A%B0%ED%95%A8%EC%88%98Window-Function

[MYSQL]윈도우함수(Window Function)

https://mizykk.tistory.com/121

[MySQL] 윈도우함수(Window Function)

잘못된 내용이 있다면 지적부탁드립니다. 방문해주셔서 감사합니다.