제 6절 윈도함수

1. 윈도 함수 개요

• 윈도 함수는 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 함수이다.
• 윈도 함수를 OLAP 함수라고 한다.
• 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수이다.

1.1 윈도 함수 구문

SELECT 함수명(파라미터)
	OVER
   ([PARTITION BY 컬럼1, ...]
   [ORDER BY 컬럼A, ...])
   FROM 테이블명

• OVER 구문이 필수적으로 필요
• PARTITION BY(선택 항목)은 순위를 정할 대상 범위의 컬럼을 설정(이를 통해 구분된 레코드 집합을 윈도라고 함)
• ORDER BY 뒤에는 SORT 컬럼을 입력(어떤 열을 어떤 순서로 순위를 정할지를 지정)

1.2 윈도 함수 종류

분류	설명	종류
순위 함수	레코드의 순위를 계산하는 함수	RANK, DENSE_RANK, ROW_NUMBER
행 순서 함수	레코드에서 가장 먼저 나오거나 가장 뒤에 나오는 값, 이전/이후의 값들을 출력하는 함수 Oracle에서만 지원	FIRST_VALUE, LAST_VALUE, LAG, LEAD
그룹내 비율 함수	백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수	RATIO_TO_REPORT, PERCENT_RANK

2. 순위 함수

순위 함수	설명
RANK	- 특정 항목에 대한 순위를 구하는 함수 - 동일 순위의 레코드 존재시 후순위 넘어감(2위가 3개인 레코드: 2위2위2위5위6위)
DENSE_RANK	- 레코드의 순위를 계산 - 동일 순위의 레코드 존재시 후순위 넘어가지 않음(2위가 3개인 레코드: 2위2위2위3위4위)
ROW_NUMBER	- 레코드의 순위를 계산 - 동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여(2위가 3개인 레코드: 2위3위4위5위6위)

SELECT NAME, SALARY,
RANK() OVER(ORDER BY SALARY DESC) A,
DENSE_RANK() OVER(ORDER BY SALARY DESC) B,
ROW_NUMBER() OVER(ORDER BY SALARY DESC) C
FROM EMP;

3. 행순서 함수

행순서 함수	설명
FIRST_VALUE	- 파티션별 윈도에서 가장 먼저 나오는 값을 찾음 - 집계 함수의 MIN과 동일한 결과를 출력
LAST_VALUE	- 파티션별 윈도에서 가장 늦게 나오는 값을 찾음 - 집계 함수의 MAX와 동일한 결과를 출력
LAG	- 파티션별 윈도에서 이전 로우의 값 반환 - SQL Server에서 지원하지 않음
LEAD	- 파티션별 윈도에서 이후 로우의 값 반환 - SQL Server에서 지원하지 않음

SELECT NAME, SALARY,
	FIRST_VALUE(NAME) OVER(ORDER BY SALARY) A,
    LAST_VALUE(NAME) OVER(ORDER BY SALARY) B,
    LAG(NAME) OVER(ORDER BY SALARY DESC) C,
    LEAD(NAME) OVER(ORDER BY SALARY DESC) D
FROM EMP;

4. 그룹 내 비율 함수

그룹 내 비율 함수	설명
RATIO_TO_REPORT	- 주어진 그룹에 대해 합을 기준으로 각 로우의 상대적 비율을 반환하는 함수 - 결괏값은 0~1의 범위 값을 가짐 - OVER 괄호 안에 컬럼 생략 시 그룹은 테이블 전체가 대상
PERCENT_RANK	- 주어진 그룹에 대해 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구하는 함수 - 결괏값은 0~1의 범위값을 가짐 - SQL Server에서 지원하지 않음

SELECT NAME, SALARY,
	RATIO_TO_REPORT(SALARY) OVER() A,
    PERCENT_RANK() OVER(ORDER BY SALARY DESC) B
FROM EMP;