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

윈도우함수는 Group By와 비슷하게 데이터를 그룹화하여 집계해준다. 하지만 Group By는 집계된 결과만 보여주는 반면, 윈도우함수는 기존 데이터에 집계된 값을 추가하여 나타낸다.

 

위와 같은 데이터가 있을 때

 

[ Group By를 사용했을 경우 ]

집계된 값만 나타난다.

 

 

[ 윈도우함수를 사용했을 경우 ]

기존 데이터에 집계된 값이 추가되어 나타난다.

 

기본형태

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

PARTITION BY : Group By와 같은 기능

ORDER BY : Order By와 같은 기능(DESC : 내림차순)

 

ex) 국가별 profit의 합

- 윈도우함수 : SELECT SUM(profit) OVER (PARTITION BY country) FROM table

                               ↑                               ↑

- Group By : SELECT SUM(profit) FROM table GROUP BY country

 

 

1. 집계 함수

SUM() : 합 / MIN() : 최소값 / MAX() : 최대값 / AVG() : 평균 / COUNT() : 갯수

MAX(열) OVER (PARTITION BY 그룹열)  -- 예시) 최대값
 
SUM(열) OVER (ORDER BY 순서열) -- 누적합
SUM(열) OVER (ORDER BY 순서열 PARTITION BY 그룹열) -- 그룹끼리 누적합

→ PARTITION BY에 그룹할 기준을 지정한다.

→ ORDER BY에 정렬 기준을 지정한다.

 

 

최대값 : MAX(Salary) OVER (PARTITION BY DepartmentId) As MaxSalary

 

누적합 : SUM(kg) OVER (ORDER BY Line) AS CumSum

 

누적합 & Group: SUM(kg) OVER (ORDER BY Line PARTITION BY Id) AS CumSum

 

 

2. 순위

1) ROW_NUMBER() : 중복 없는 순위. 행 번호

2) RANK() : 중복 가능. 공동순위만큼 건너뛴다.

3) DENSE_RANK()

   - 중복가능

   - 공동순위가 있더라도 1, 2, 3 순차적으로 순위가 매겨진다.

   - 동일한 순위는 하나의 순위로 취급

 

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 table

→ ORDER BY에 순위의 기준을 지정한다.

→ 인자가 들어가지 않는다.

 

 

 

3. 데이터 위치 바꾸기

1) LAG(열, n, 결측값 채울 값) : n칸 미루기

- LAG(열, n) OVER (PARTITION BY 그룹열 ORDER BY 순서열)

 

2) LEAD(열, n, 결측값 채울 값) : n칸 당기기

- LEAD(열, n) OVER (PARTITION BY 그룹열 ORDER BY 순서열)

 

SELECT Id
    , RecordDate  -- ORDER 열
    , Temperature  -- 대상 열
 
    -- 미루기
    , LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag'
 
    -- 당기기
    , LEAD(Temperature) OVER (ORDER BY RecordDate) AS 'lead'
 
FROM table

→ PARTITION BY에 그룹할 기준을 지정한다.

→ ORDER BY에 정렬 기준을 지정한다.

 

 

 

Reference