[Oracle] WINDOWNING 절

윈도우 함수 종류

1.그룹 내 순위(RANK) 관련 함수

 - RANK, DENSE_RANK, ROW_NUMBER 함수

2.그룹 내 집계(AGGREGATE) 관련 함수

 - SUM, MAX, MIN, AVG, COUNT 함수 

3.그룹 내 행 순서 관련 함수

 - FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수 

4.그룹 내 비율 관련 함수 

- CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 함수

윈도우 함수 문법

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

WINDONWING 절 : 분석함수의 대상이 되는 범위를 지정하면 ORDER BY 절에 종속적입니다.

BETWEEN 사용 시

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER ([PARTITION BY 컬럼]
                                        [ORDER BY 절]
                                        [[ROWS | RANGE BETWEEN [UNBOUNDED PRECEDING] | [n PRECEDING] | [CURRENT ROW]
                                                           AND [UNBOUNDED FOLLOWING] | [n FOLLOWING] | [CURRENT ROW]])
  FROM 테이블명;

BETWEEN 미사용 시

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER ([PARTITION BY 컬럼]
                                        [ORDER BY 절]
                                        [[ROWS | RANGE [UNBOUNDED PRECEDING] | [n PRECEDING] | [CURRENT ROW]])
  FROM 테이블명;

ROWS : 물리적인 ROW 단위로 행 집합을 지정합니다.

RANGE : 논리적인 상대번지로 행 집합을 지정합니다.

BETWEEN ~ AND 절 : 윈도우의 시작과 끝 위치를 지정합니다.

UNBOUNDED PRECEDING : PARTITION의 첫 번째 로우에서 윈도우가 시작합니다.

UNBOUNDED FOLLOWING : PARTITION의 마지막 로우에서 윈도우가 시작합니다.

CURRENT ROW : 윈도우의 시작이나 끝 위치가 현재 로우입니다.

[ROWS 예제]

1.부서별 급여 합계 조회

SELECT EMPNO
     , ENAME
     , DEPTNO
     , SAL
     , SUM(SAL) OVER(PARTITION BY DEPTNO 
                         ORDER BY EMPNO
                          ROWS BETWEEN UNBOUNDED PRECEDING 
                                   AND UNBOUNDED FOLLOWING) AS SUM_DEPTNO_SAL 
  FROM EMP;

2.부서별 급여 누계 조회  

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

3.부서별 급여 누계 조회 - 전 1개 행과 현재 행

SELECT EMPNO
     , ENAME
     , DEPTNO
     , SAL
     , SUM(SAL) OVER(PARTITION BY DEPTNO 
                         ORDER BY EMPNO
                          ROWS BETWEEN 1 PRECEDING 
                                   AND CURRENT ROW) AS SUM_DEPTNO_SAL 
  FROM EMP; 

4.부서별 급여 누계 조회 - 전 1개 행과 다음 1개 행

SELECT EMPNO
     , ENAME
     , DEPTNO
     , SAL
     , SUM(SAL) OVER(PARTITION BY DEPTNO 
                         ORDER BY EMPNO
                          ROWS BETWEEN 1 PRECEDING 
                                   AND 1 FOLLOWING) AS SUM_DEPTNO_SAL 
  FROM EMP; 

5.부서별 급여 누계 조회 - 현재 행과 다음 1개 행

SELECT EMPNO
     , ENAME
     , DEPTNO
     , SAL
     , SUM(SAL) OVER(PARTITION BY DEPTNO 
                         ORDER BY EMPNO
                          ROWS BETWEEN CURRENT ROW 
                                   AND 1 FOLLOWING) AS SUM_DEPTNO_SAL 
  FROM EMP;

[RANGE 예제]

1.본인의 급여보다 50 이하로 적거나 150 이하로 많은 급여를 받는 인원수 

SELECT EMPNO
     , ENAME     
     , SAL
     , SAL - 50 AS RANGE_PRECEDING_SAL
     , SAL + 150 AS RANGE_FOLLOWING_SAL
     , COUNT(*) OVER(ORDER BY SAL
                     RANGE BETWEEN 50 PRECEDING 
                               AND 150 FOLLOWING) AS RANGE_COUNT_SAL
  FROM EMP;