윈도우 함수 (Window Function)

정의

• 윈도우 함수란 행과 행 간의 관계를 정의하기 위해 제공되는 함수 입니다. 여기에서의 관계를 정의한다는 것은 비교, 연산, 행 위치 조작을 말한다.
• 비교, 연산 : SUM,AVG,MIN,MAX
• 행 위치조작 : ROWS, RANGE

SUM, AVG, MIN, MAX에 대해서는 Group BY에서 자주 사용했으므로 설명을 생략하겠다.

윈도우 절의 구조

ROWS	물리적인 행
RANGE	논리적인 행
UNBOUNED PRECEDING	첫 번째 행
UNBOUNED FOLLOWING	마지막 행
CURRENT ROW	현재 행
PRECEDING	이전 행 (위치)
FOLLOWING	다음 행 (위치)

CURRENT ROW

현재행을 가르킵니다.

ROWS

ROW BETWEEN <CONDITION1> AND <CONDITON2>

ROWS는 물리적인 행위치를 기준으로 정의합니다.

예제 : 현재 행을 포함하여 이전 2행까지의 합계 계산

SELECT sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rolling_sum
FROM sales;

RANGE

RANGE BETWEEN <CONDITION1> AND <CONDITON2>

RANGE는 논리적인 행을 기준으로 윈도우를 정의합니다. 이는 ORDER BY 절에 지정된 칼럼의 값에 따라 결정됩니다.

예제 : 현재 행의 sale_date와 같은 모든 행의 합계를 계산 ( 정확히 같은 날짜만 )

SELECT sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           RANGE BETWEEN CURRENT ROW AND CURRENT ROW
       ) AS same_day_sum
FROM sales;

예제 : 현재 행의 sale_date와 같은 행과 그 이전의 date행을 모두 합산 계산 ( 이전 날짜도 포함 )

SELECT sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS sum_same_day
FROM sales;

 

ROW VS RANGE와 차이점

💡 그렇다면 ROW와 RANGE의 차이점을 정리하고 넘어가야할텐데 차이점은 아래와 같습니다.

ROW

• ROW는 물리적인 행위치를 기즌으로 윈도우의 범위를 정의합니다.
• 앞이나 뒤로 포함시키는 명확한 행의 개수를 기준으로 윈도우를 설정합니다.

RANGE

• RANGE는 논리적인 값의 범위를 기준으로 윈도우의 범위를 정의합니다.
• ORDER BY절에서 지정한 컬럼의 값에 따라서 윈도우를 지정합니다. 즉, 현재 행과 동일한 값을 가진 모든 행들을 윈도우에 포함시킵니다.

정리

RANGE는 값의 범위를 기준으로 사용하고, ROWS는 단순하게 위치를 기준으로 설정한다.

UNBOUNED PRECEDING

파티션의 시작부터 현재 행가지 윈도우를 정의합니다.

예제 : 파티션 시작부터 현재 행까지의 합계 계산

SELECT sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_sum
FROM sales;

UNBOUNDED FOLLOWING

현재 행부터 파티션의 끝까지 윈도우를 정의합니다.

예제 : 현재 행부터 파이션 끝까지의 합계 계산

SELECT sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
       ) AS sum_to_end
FROM sales;

PRECEDING

현재 행에서 지정된 수만큼 이전 행을 윈도우에 포함시킵니다.

예제 : 현재 행을 포함하여 이전 1행의 합계 계산

SELECT sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS sum_last_two
FROM sales;

FOLLOWING

현재 행에서 지정된 수만큼 다음의 행을 윈도우에 포함합니다.

예제 : 현재 행을 포함하여 다음 1행의 합계 계산

SELECT sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
       ) AS sum_next_two
FROM sales;

윈도우 함수의 종류

• 그룹내 집계 함수 : SUM, AVG, COUNT, MAX, MIN를 윈도우 함수로 사용
• 그룹내 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER 등의 윈도우 전용 함수

윈도우 함수 구문

<윈도우함수> OVER (PARTITION BY < 열리스트>, ORDER BY <소트용 열 리스트>,)

ROW , RANGE도 끝에 추가로 사용한다.

중요한 키워드는 PARTITION BY와 ORDER BY 이다.

대표적인 윈도우 함수

• RANK : 랭킹을 산출하되, 같은 순위의 레코드가 복수 존재하면 후순위를 건너뛴다.
• DENSE_RANK : 랭킹을 산출하되, 같은 순위의 레코드가 복수 존재해도 후순위를 건너뛰지 않는다.
• ROW_NUMBER : 순위에 상관없이 연속 번호를 부여한다. 레코드에 연속 번호를 부여하고 싶을때 사용

추가 참고

• 윈도우 함수는 SELECT 구에서만 사용 할 수 있다. ( WHERE, GROUP BY에서 사용 불가 )
• 여러개의 윈도우 함수를 사용 할 수 있다.

SELECT name
     , score
     , RANK() OVER (ORDER BY score DESC) AS rnk
     , DENSE_RANK() OVER (ORDER BY score DESC) as drnk
     , ROW_NUMBER() OVER (ORDER BY score DESC) as rownum
   FROM record;

• MYSQL 8.0 버전부터 WINDOW 함수가 도입되었습니다.
• 윈도우 함수는 OLAP 함수 라고도 한다.
• OLAP란, Online Analytical Processing의 약자로, 데이터베이스를 사용한 실시간(온라인) 데이터 분석처리다. 윈도우함수는 이 OLAP 용도로 사용하기 위해 표준 SQL에 추가된 기능이다.