[MySQL] 윈도우 함수

윈도우 함수

• sum, max, min 등을 그룹별로 구할 수 있는 함수 <-> GROUP BY와는 좀 다르다
• 한 줄로 요약을 해서 보여주는 게 아니라 각각의 row에 결과물들을 보여준다

사용할 수 있는 함수 🔗

AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

형태

함수(컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)

 

집계 함수

MAX(컬럼) OVER (PARTITION BY 컬럼)

각 부서별로 가장 많이 버는 사람 찾기

SELECT Id
     , Name
     , Salary
     , DepartmentId
     , MAX(Salary) OVER (PARTITION BY DepartmentId) AS MaxSalary
FROM Employee

SUM(컬럼) OVER (ORDER BY 컬럼)

• 누적합을 구해주는 결과
• sum을 하되 Line컬럼을 순서로 해서 차곡차곡 더하기

SELECT Id
     , Name
     , kg
     , Line
     , SUM(kg) OVER (ORDER BY Line) AS CumSum
FROM Elevator

SUM(컬럼) OVER (ORDER BY 컬럼 PARTITION BY 컬럼)

• Id가 A일 때는 Line별로 차근차근 더하고, B일 때는 누적합이 초기화되서 다시 차근차근 더하게 됨

SELECT Id
     , Name
     , kg
     , Line
     , SUM(kg) OVER (ORDER BY Line PARTITION BY Id) AS CumSum
FROM Elevator

id	name	kg	line	cumsum
A	aaa	70	1	70
A	bbb	91	2	161
A	ccc	79	3	240
B	ddd	100	4	100

집계 함수 이외에 윈도우 함수에만 지원되는 함수

순위 정하기 (ROW_NUMBER(), RANK(), DENSE_RANK())

• val 컬럼을 기준으로 해서 순위를 지정해달라
• 괄호 안에는 아무 인자도 들어가지 않음

SELECT val
     , ROW_NUMBER() OVER (ORDER BY val) AS 'row_number'
     , RANK() OVER (ORDER BY val) AS 'rank'
     , DENSE_PARK() OVER (ORDER BY val) AS 'dense_rank'
FROM sample

val	row_number	rank	dense_rank
1	1	1	1
1	2	1	1
2	3	3	2
3	4	4	3
3	5	4	3
3	6	4	3
4	7	7	4

ROW_NUMBER()	val가 커짐에 따라 순위지정 중복되는 순위 X
RANK()	val가 똑같을 때 같은 순위를 줌 1위가 2번이면 그 다음 순위는 3위 중복되는 순위 O
DENSE_RANK()	val가 똑같을 때 같은 순위를 줌 1위가 2번이면 그 다음 순위는 2위 중복되는 순위 X

데이터 위치 바꾸기 (LEAD(), LAG())

• 데이터를 몇 칸씩 밀거나 몇 칸씩 당겨오는 함수
• LAG()는 데이터를 밀고 LEAD()는 데이터를 당긴다

SELECT Id
	 , RecordDate
     , Temperature
     -- Temperature 컬럼을 recordDate순으로 해서 뒤로 밀어달라
     , LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag'
     -- LAG()의 반대 > Temperature 컬럼을 recordDate순으로 해서 데이터를 당겨달라
     , LEAD(Temperature) OVER (ORDER BY RecordDate) AS 'lead'
FROM sample

SELECT Id
	 , RecordDate
     , Temperature
     -- 기본적으로는 한칸씩 밀고 당기지만 2칸을 밀고 당기고 싶다면 아래와 같이 넣어줄 수 있다
     , LAG(Temperature, 2) OVER (ORDER BY RecordDate) AS 'lag'
     , LEAD(Temperature, 2) OVER (ORDER BY RecordDate) AS 'lead'
FROM sample

Id	RecordDate	Temperature	lag	lead
1	2015-01-01	10	NULL	25
2	2015-01-02	25	10	20
3	2015-01-03	20	25	30
4	2015-01-04	30	20	28
5	2015-01-05	28	30	NULL

• 맨 끝에 0은 디폴트 값으로 null 대신에 채워지는 값이다

LAG(Temperature, 2, 0) 
LEAD(Temperature, 2, 0)

🔥 심화(윈도우 함수 구하지 않고 누적합 구하기)

elavator 테이블

id	name	kg	line	cumsum
A	aaa	70	1	70
A	bbb	91	2	161
A	ccc	79	3	240
B	ddd	100	4	100

JOIN 활용 + Group By

SELECT *
FROM Elavator e1
INNER JOIN Elavator e2 ON e1.Id = e2.Id -- PARTITION
and e1.Line >= e2.Line

• e1의 line이 1일 때에는 e2의 line이 1
• e2의 line이 2일 때에는 e2의 line은 1, 2 ...

SELECT e1.Id
     , e1.Name
     , e1.kg
     , e1.line
     , SUM(e2.kg) AS CumSum
FROM Elavator e1
	INNER JOIN Elavator e2 ON e1.Id = e2.Id -- PARTITION
  	AND e1.Line >= e2.Line
GROUP BY e1.Id, e1.Name, e1.kg, e1.line

• group by를 활용하여 id별 합산

SELECT절 서브쿼리 활용

SELECT e1.Id
	 , e1.Name
     , e1.kg
     , e1.Line
     , (SELECT SUM(e2.kg)
     	  FROM Elevator e2
         WHERE e1.Id = e2.Id
           AND e1.Line >= e2.Line) AS CumSum
FROM Elevator e1

• e1의 line보다 작거나 같은 데이터를 가져와서 SUM해주는 서브쿼리

관련 문제

• [Algorithm/MySQL] - [LeetCode] 180. Consecutive Numbers (MySQL)
• [Algorithm/MySQL] - [LeetCode] 184. Department Highest Salary (MySQL)
• [Algorithm/MySQL] - [LeetCode] 185. Department Top Three Salaries (MySQL)

출처

[백문이불여일타] 데이터 분석을 위한 고급 SQL - 인프런 | 강의