윈도우 함수 (Window Functions)

MySQL 튜토리얼 공식 문서 윈도우 함수 부분을 공부했다. 모르는 함수를 공부할 땐 블로그를 검색하는 것보다 공식 문서를 보는 게 낫다고 한다. 구글 번역기를 사용하더라도!

• MySQL Window Functions

샘플 테이블

직원 및 회계 연도별 매출을 저장하는 sales 테이블을 만들어 사용했다.

-- 테이블 생성
CREATE TABLE sales(
    sales_employee VARCHAR(50) NOT NULL,
    fiscal_year INT NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,fiscal_year)
);
-- 데이터 삽입
INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
       ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);
-- 테이블 확인
SELECT * FROM sales;

GROUP BY 집계함수와 윈도우 함수

윈도우 함수의 이해는 집계함수를 아는 것에서부터 시작된다.
집계함수는 여러 행의 데이터를 하나의 값, 즉 단일 행으로 반환한다.

• 예를 들어 SUM(sale)은 기록된 연도 모든 직원의 매출을 더한 단일 값을 반환한다.

SELECT SUM(sale)
FROM sales;

GROUP BY를 사용하면 행의 하위 집계에 집계함수를 적용할 수 있다. 지정된 열의 값이 같은 행이 하나의 그룹으로 묶여 집합함수를 적용, 활용범위를 늘릴 수 있는 것이다.

• 예를 들어 회계 연도별 총 판매액을 계산할 수 있다.

SELECT fiscal_year, SUM(sale)
FROM sales
GROUP BY fiscal_year;

두 예에서 집계함수는 쿼리에서 반환되는 행의 수를 줄인다.
GROUP BY 절이 있는 집계함수와 마찬가지로 윈도우 함수도 행의 하위 집합에서 작동하지만, 반환되는 행의 수를 줄이지는 않는다.

• 예를 들어 다음 쿼리는 회계 연도별 직원의 총 매출액과 함께 직원의 이름을 반환한다.

SELECT fiscal_year, sales_employee, sale,
	SUM(sale) OVER (PARTITION BY fiscal_year) total_sales
FROM sales;

이 예에서 SUM() 함수는 OVER 절의 조건으로 정의된 행 집합에서 작용하는 윈도우 함수로 작동한다.

• SUM() 함수가 적용되는 행 집합을 윈도우라고 한다.

윈도우 함수 SUM()은 GROUP BY 절이 있는 쿼리에서와 같이 회계 연도별 총 판매액을 반환할 뿐만 아니라 각 행까지 그대로 보인다.

윈도우 함수 구문

윈도우 함수를 호출하는 일반적인 구문은 다음과 같다.

WINDOW_FUNCION_NAME(expression) OVER (
								[PARTION_DEFINITION]
                                [ORDER_DEFINITION]
                                [FRAM_DEFINITION])

• 먼저 윈도우 함수 이름 괄호 안에 있는 표현식을 지정한다.
• 다음 파티션 정의 및 순서 정의, 프레임 정의, 세 가지 요소가 가능한 OVER 절을 지정한다. OVER 다음에는 표현식이 없더라도 괄호를 닫아줘야 한다.

WINDOW_FUNCTION_NAME(expression) OVER()

PARTITION 절

partition_clause은 행을 파티션으로 나눈다. 두 파티션은 파티션 경계로 구분된다.
윈도우 기능은 파티션 내에서 수행되며 파티션 경계를 넘을 때 다시 초기화된다.

• 구문은 다음과 같다.
• PARTITION BY 절에는 하나 이상의 칼럼, 표현식 지정할 수 있으며, 여러 칼럼은 쉼표로 구분한다.

PARTITION BY <expression>[{,<expression>}]

ORDER BY 절

ORDER BY 절은 파티션 내에서 행이 정렬되는 방식을 지정한다. 여러 키의 파티션 내에서 데이터를 정렬할 수 있으며, 각 키는 표현식으로 지정된다. 여러 표현식도 쉼표로 구분된다.

• PARTITION BY 절과 마찬가지로 ORDER BY 절도 모든 윈도우 함수에서 지원된다. 그러나 순서에 민감한 윈도우 함수에만 ORDER BY를 사용하는 게 좋다.

ORDER BY <expression> [ASC|DESC], [{, <expression>...}]

FRAME 절

프레임은 현재 파티션의 하위 집합이다. 하위 집합을 정의하려면 다음과 같이 프레임 절을 사용한다.

FRAME_UNIT {<frame_start>|<frame_between>}

프레임은 현재 행과 관련해 정의되며, 파티션 내에서 현재 행의 위치에 따라 프레임이 이동할 수 있다.
프레임 단위는 현재 행과 프레임 행 간의 유형을 지정한다.
이는 ROWS 또는 RANGE일 수 있다.
현재 행과 프레임 행의 오프셋은 프레임 단위가 ROWS이고 행 값이 프레임 단위가 RANGE인 경우의 행 번호이다.

• frame_start 및 frame_between은 프레임 경계를 정의한다.
• frame_start는 다음 중 하나가 포함된다.
  • UNBOUNDED PRECEDING : 프레임이 파티션의 첫 번째 행에서 시작
  • N PRECEDING : 첫 번째 현재 행 이전의 물리적 N 행. N은 리터럴 넘버이거나, 숫자로 표현되는 평가식일 수 있다.
  • CURRENT ROW : 현재 계산의 행
• frame_between은 다음과 같다.

BETWEEN frame_boundary_1 AND frame_boundary_2

frame_boundary_1과 frame_boundary_2는 각각 다음 중 하나를 포함할 수 있다.

• frame_start : 위의 내용
• UNBOUNDED FOLLWING : 프레임은 파티션의 마지막 행에서 끝난다.
• N FOLLOWING : 현재 행 뒤의 물리적 N개의 행

OVER 절에 FRAME_DEFINITION을 지정하지 않으면 MySQL은 기본적으로 다음 프레임을 사용한다.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROM

MySQL 윈도우 함수 리스트

이름	설명
CUME_DIST	값의 집합 내에서 값의 누적 분포를 계산
DENSE_RANK	ORDER BY 절에 따라 파티션 내의 모든 행에 순위를 할당. 동일한 값을 가진 행에 동일 순위를 할당한다. (순위 간격 X : 1,2,2,2,3,4 )
RANK	둘 이상의 동일한 값을 가진 행에 순위가 지정된다. 순위 간격이 있는 것을 제외하고 DENSE_RANK()와 유사하다. (순위 간격 O : 1,2,2,2,5)
ROW_RANK	파티션 내의 모든 행에 순차적으로 정수를 할당한다. (순서 없이 그냥 쭉 1,2,3,4, ...)