Window 함수

행과 행 간의 관계를 정의하기 위해서 제공되는 함수

• 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.

SELECT WINDOW_FUNCTION(arguments)
			 OVER (PARTITION BY col1 
						 ORDER BY col2
						 windowing절)
FROM TABLE1;

• ROWS
• UNBOUNDED PRECEDING
  • 첫번째 행
• UNBOUNDED FOLLOWING
  • 마지막 행
• CURRENT ROW
  • 현재 행

Example

• 임직원의 월급 오름차순으로 정렬하고, 누적 월급 합을 표시한다.

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER (ORDER BY SAL 
																				 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ACC_SAL
FROM EMP;

순위 함수 (RANK Function)

RANK

• 동일한 순위는 동일한 등수가 부여된다.
• 동일한 등수가 여럿 존재하면, 그 다음 등수는 그 숫자에 영향을 받는다.

DENSE_RANK

• 동일한 순위는 동일한 등수가 부여된다.
• 동일한 등수 여럿 존재하는 걸 고려하지 않는다.
• 즉 순위 값이 1부터 차례대로 부여된다.

ROW_NUMBER

• 행 순서대로 행 번호를 부여한다.

Example

SELECT ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) RANK,
                   DENSE_RANK() OVER (ORDER BY SAL DESC) RANK_INDEX,
									 ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUM
FROM EMP;

집계 함수 (Aggregation Function)

• SUM
• AVG
• COUNT
• MAX, MIN

Example

SELECT ENAME, SAL, MGR, SUM(SAL) OVER (PARTITION BY MGR) 담당_매니저가_같은_사원들의_SAL_합
FROM EMP;

행 순서 관련 함수

FIRST_VALUE

• FIRST_VALUE(COL1)

LAST_VALUE

• LAST_VALUE(COL1)

LAG

• 이전 행의 값을 리턴
• LAG(COL1)
• LAG(COL1, 1)
• LAG(COL1, 1, 'default value')

LEAD

• 다음 행의 값을 리턴
• LEAD(COL1)
• LEAD(COL1, 1)
• LEAD(COL1, 1, 'default value')

Example

SELECT DEPTNO, ENAME, SAL, LAG(SAL) OVER(ORDER BY SAL DESC) AS PREV_SAL
                           LEAD(SAL) OVER(ORDER BY SAL DESC) AS NEXT_SAL
FROM EMP;

비율 관련 함수

CUME_DIST

• 누적분포
• 누적 백분율을 조회한다.
• 0~1 사이의 값을 가진다.

PERCENT_RANK

• 행의 순서별 백분율을 조회한다.
• 파티션에서 제일 먼저 나온 것을 0
• 파티션에서 제일 늦게 나온 것을 1

NTILE

• 파티션별로 N등분해서 등분한 순번을 조회한다.

RATIO_TO_REPORT

• SUM한 컬럼 값의 백분율을 소수점까지 조회한다.

Example

SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS PERCENT_SAL_IN_DEPT
                           NTILE(5) OVER(ORDER BY SAL DESC) AS SAL_LEVEL
FROM EMP;