윈도우 함수

윈도우 함수 개요

기존 관계형 데이터베이스는 칼럼과 칼럼 간의 연산, 비교, 연결이나 집합에 대한 집계는 쉬운 반면, 행과 행간의 관계를 정의하거나 행과 행간의 비교, 연산하는 것을 하나의 SQL 문으로 처리하는 것은 매우 어려운 문제였다.

분석 함수나 순위 함수로도 알려진 윈도우 함수 표준은 데이터 웨어하우스에서 발전한 기능이다.

윈도우 함수는 다른 함수와는 달리 중첩해서 사용하지 못하지만, 서브 쿼리에서는 사용 가능하다.

윈도우 함수 종류
윈도우 함수의 종류는 크게 5개의 그룹으로 분류할 수 있다.

1. 그룹 내 순위 관련 함수
   RANK, DENSE_RANK, ROW_NUMBER.
2. 그룹 내 집계 관련 함수
   SUM, MAX, MIN, AVG, COUNT
3. 그룹 내 행 순서 관련 함수
   FIRST_VALUE, LAST_VALUE, LAG, LEAD.
4. 그룹 내 비율 관련 함수
   CUME_DIST, PERCENT_RANK, NTILE, RATION_TO_REPORT
5. 선형 분석을 포함한 통계 분석 관련 함수
   CORR, COVAR_POP, COVAR_SAMP, STDDEV...

WINDOW FUNCTION SYNTAX
윈도우 함수에는 OVER 문구가 키워드로 필수 포함된다.

SELECT WINDOW_FUNCTION (ARGUMENTS)
	   OVER([PARTITION BY 칼럼][ORDER BY 절][WINDOWING 절]
FROM 테이블 명;

• WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다.
• ARGUMENTS (인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.
• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술
• WINDOWING 절 : 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.

ROWS는 물리적인 행의 수를, RANGE는 논리적인 값에 대한 범위를 나타낸다. 둘 중 하나를 선택해서 사용할 수 있다.

[BETWEEN 사용 타입]
ROWS | RANGE BETWEEN UNBOUNDED PRECEDING
| CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

AND UNBOUNDED PRECEDING
| CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

[BETWEEN 미사용 타입]
ROWS | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

그룹 내 순위 함수

가. RANK 함수
동일한 값에 대해서는 동일한 순위를 부여한다.

SELECT JOB, ENAME, SAL
	 , RANK () OVER (ORDER BY SAL DESC) AS ALL_RK
     , RANK () OVER (PARTITION BY JOB ORDER BY SAL DESC) AS JOB_RK
FROM EMP;

하나의 SQL 문장에 ORDER BY SAL DESC 조건과 PARTITION BY JOB 조건이 충돌났기 때문에 JOB별로는 정렬되지 않고 ORDER BY SAL DESC 조건으로 정렬됐다.

나. DENSE_RANK 함수
RANK와 유사하나 동일한 순위를 하나의 건수로 취급한다.

다. ROW_NUMBER 함수
동일한 값이여도 고유한 순위를 부여한다.

일반 집계함수

가. SUM 함수
파티션별 윈도우의 합.

SELECT MGR, ENAME, SAL
	 , SUM(SAL) OVER(PARTITION BY MGR) AS SAL_SUM
FROM EMP;

나. MAX 함수
파티션별 윈도우의 최댓값.

다. MIN 함수
파티션별 윈도우의 최솟값.

라. AVG 함수
AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통곗값.

마. COUNT 함수
COUNT 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통곗값.

SELECT ENAME, SAL
	 , COUNT(*) OVER(ORDER BY SAL
     	RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS EMP_CNT
FROM EMP;

그룹 내 행 순서 함수

가. FIRST_VALUE 함수
파티션별 윈도우에서 가장 먼저 나온 값을 구한다.

SELECT DEPTNO, ENAME, SAL
	 , FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO
     	ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS ENAME_FV
FROM EMP;

나. LAST_VALUE 함수
파티션별 윈도우에서 가장 나중에 나온 값을 구한다.

다. LAG 함수
파티션별 윈도우에서 이전 몇 번째 값을 가져올 수 있다.

SELECT ENAME, HIREDATE, SAL
	 , LAG(SAL) OVER(ORDER BY HIREDATE) AS LAG_SAL
FROM EMP
WHERE JOB = 'SALESMAN';

라. LEAD 함수
파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.

5. 그룹 내 비율 함수
   가. RATIO_TO_REPORT 함수
   파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. 결과 값은 0보다 크고 1보다 같거나 같은 범위를 가진다. 개별 RATIO의 합은 1이다.

SELECT ENAME, SAL
	 , ROUND(RATIO_TO_REPORT(SAL) OVER(), 2) AS SAL_RR
FROM EMP
WHERE JOB = 'SALESMAN';

나. PERCENT_RANK 함수
파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 해서, 값이 아닌 행의 순서별 백분율을 구한다. 결과 값은 0보다 같거나 크고 1보다 같거나 작다.

다. CUME_DIST 함수
파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다. 결과 값은 0보다 크고 1보다 같거나 작다.

SELECT DEPTNO, ENAME, SAL
	 , ROUND(CUME_DIST()
     	OVER(PARTITION BY DEPTNO
     	ORDER BY SAL DESC), 2) AS CD
FROM EMP;

라. NTILE 함수
파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다.

SELECT ENAME, SAL
	 , NTILE(4) OVER(ORDER BY SAL DESC) AS NT
FROM EMP;

NTILE(4)로 전체를 4개의 그룹으로 나눈다. 나머지가 있으면 앞에부터 하나씩 추가한다.