[MySQL] WINDOW함수

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

 

[SQL] 윈도우 함수 (WINDOW FUNCTION)

 

 

WINDOW FUNCTION이란?

윈도우 함수는 GROUP BY와 비슷하게 데이터를 그룹화하여 집계해줍니다.

하지만, GROUP BY는 집계된 단일행 결과만을 보여주는 반면, 윈도우 함수는 기존 데이터에 집계된 값을 추가하여 나타내는 것이 장점입니다. 

 

WINDOW FUNCTION의 종류

1. 그룹 내 순위 관련 함수

    - ROW_NUMBER()

    - RANK()

    - DENSE_RANK()

 

2. 그룹 내 집계 관련 함수    - SUM, MAX, MIN, AVG, COUNT()

 

3. 그룹 내 행 순서 관련 함수    - FIRST_VALUE()    - LAST_VALUE()    - LAG()    - LEAD ()

 

4. 그룹 내 비율 관련 함수    - CUM_DIST    - PERCENT_RANK    - NTILE    - RATIO_TO_REPORT

 

5. 선형 분석을 포함한 통계 분석 함수     - 생략... 

 

WINDOW FUNCTION의 기본 문법 

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

ex : 
SUM(Salary) OVER (PARTITION BY Department_id)...

 

• PARTITION BY : 어떤 컬럼을 기준으로 그룹화 할 것인지 기술한다. 
• ORDER BY : 어떤 항목에 대해 순위를 지정할 지 기술한다. (집계함수에서는 잘 사용하지 않음) 

 

WINDOW FUMCTION의 집계함수 

 

1. MAX 

- 파티션 별 윈도우의 최대값을 구할 수 있다.

# 기본 구조 
MAX(컬럼명) OVER (PARTITION BY 컬럼명)

# 예시 : 사원들의 급여와 같은 매니저를 두고 있는 사원들의 급여 중 최대값을 구하자.
SELECT MGR
       , ENAME
       , SAL
       , MAX(SAL) OVER (PARTITION BY MGR) MGR_MAX
FROM EMP;

 

- INLINE VIEW를 이용하여 파티션별 최대값을 가진 행만 추출할 수 있다. 

SELECT MGR, ENAME, SAL
FROM (SELECT MGR
            , ENAME 
            , SAL
            , MAX(SAL) OVER (PARTITION BY MGR) IV_MAX_SAL
      FROM EMP)
WHERE SAL = IV_MAX_SAL;

 

- LEETCODE 해당 문제를 윈도우 함수를 이용해서 풀어보자 

 

2. SUM 

- SUM 함수를 이용하여 파티션별 윈도우의 누적합을 구할 수 있다. 

# LINE을 기준으로 한 사람들의 몸무게 누적합 

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

# PARTITION BY를 추가할 수도 있다. 

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

 

? 윈도우 함수를 이용하면 이렇게 쉽게 누적합을 구할 수 있는데 왜 몇몇 코딩 테스트에서는 윈도우 함수를 쓰지 않고 구하라 하나? 

-> 윈도우 함수를 사용하지 못하는 환경이 간혹 있기 때문입니디. (EX : MySQL 구버전) 

 

---

WINDOW FUNCTION _ 순위 정하기 

 

• 1. ROW_NUMBER( )
  • 동일한 값이라도 고유한 순위를 부여한다
  • ex: 1(10), 2(10), 3(9), 4(8)
• 2. RANK( )
  • 동일한 값에 대해서는 동일한 순위를 부여한다.
  • ex: 1(10), 1(10), 3(9), 4(8)... 
• 3. DENSE_RANK( )
  • 동일한 순위를 하나의 건수로 취급한다
  • ex : 1(10), 1(10), 2(9), 3(8)... 

SELECT val
	,ROW_NUMBER() OVER (ORDER BY val) AS 'row_number'
	,RANK() OVER (ORDER BY val) AS 'rank'
	,DENSE_RANK() 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
4	8	7	4
5	9	9	5

 

---

WINDOW FUNCTION _ 데이터 위치 바꾸기 

• LAG( )
  • 한 칸씩 값이 뒤로 밀린다 .
• LEAD( )
  • 값이 한 칸씩 앞으로 당겨진다.

SELECT Id
	,RecordDate
	,Temperature
	,LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag'
	,LEAD(Temperature) OVER (ORDER BY RecordDate) As 'lead'
FROM sample

-- LAG : recordDate를 따라 밀어달라 -> 한 칸씩 뒤로 밀린다.
-- LEAD : RecordDate를 따라 땡겨달라 -> 값이 한 칸씩 앞으로 땡겨온다.

 

 

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

 

특정 값 만큼 값을 이동해야하는 경우 / NULL대신 특정 값으로 채우고 싶은 경우 

SELECT Id
	,RecordDate
	,Temperature
	,LAG(Temperature,2)  OVER (ORDER BY RecordDate) AS 'lag'
	,LEAD(Temperature,2) OVER (ORDER BY RecordDate) As 'lead'
FROM sample

/*
NULL(디폴트값) 대신 특정한 값으로 채워야 할 때 
SELECT Id
	,RecordDate
	,Temperature
	,LAG(Temperature,2,0)  OVER (ORDER BY RecordDate) AS 'lag'
	,LEAD(Temperature,2,0) OVER (ORDER BY RecordDate) As 'lead'
FROM sample
*/