[SQLD : Ⅳ. SQL 활용] 6. 윈도우 함수

* WINDOW FUNCTION 개요

 - 인라인 뷰를 이용해 복잡하게 작성해야 하던 것을 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

 - WINDOW 함수는 다른 함수와는 달리 중첩(NEST)해서 사용하지는 못하지만, 서브쿼리에서는 사용할 수 있다.

 

* WINDOW FUNCTION 종류

 - 첫 번째, 그룹 내 순위(RANK) 관련 함수는 RANK, DENSE_RANK, ROW_NUMBER 함수

 - 두 번째, 그룹 내 집계(AGGREGATE) 관련 함수는 일반적으로 많이 사용하는 SUM, MAX, MIN, AVG, COUNT 함수

 - 세 번째, 그룹 내 행 순서 관련 함수는 FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수

 - 네 번째, 그룹 내 비율 관련 함수는 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 함수

 - 다섯 번째, 선형 분석을 포함한 통계 분석 관련 함수

 

* WINDOW FUNCTION SYNTAX

 - WINDOW 함수에는 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 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.

                           ROWS, RANGE 중 하나를 선택해서 사용할 수 있다.  SQL Server에서는 지원하지 않는다.

 

* 그룹 내 순위 함수

 - RANK 함수 : ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수

                    PARTITION으로 구분한 JOB_RANK의 경우 같은 업무 내 범위에서만 순위를 부여

  [예제] 사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력한다.

SELECT JOB, ENAME, SAL,
       RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK, 
       RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK 
FROM EMP;

  [예제] 앞의 SQL문의 결과는 JOB과 SALARY 기준으로 정렬이 되어있지 않다.

          새로운 SQL에서는 전체 SALARY 순위를 구하는 ALL_RANK 칼럼은 제외하고,

          업무별로 SALARY 순서를 구하는 JOB_RANK만 알아보도록 한다.

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

 

 - DENSE_RANK 함수 : RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 틀린 점

  [예제] 사원데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력한다.

SELECT JOB, ENAME, SAL, 
       RANK( ) OVER (ORDER BY SAL DESC) RANK,
       DENSE_RANK( ) OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;

 

 - ROW_NUMBER 함수 : 동일한 값이라도 고유한 순위를 부여

  [예제] 사원데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력한다.

SELECT JOB, ENAME, SAL, 
       RANK( ) OVER (ORDER BY SAL DESC) RANK, 
       ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER 
FROM EMP;

  

* 일반 집계 함수

 - SUM 함수 : 파티션별 윈도우의 합

    [예제] 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합을 구한다.

SELECT MGR, ENAME, SAL, 
       SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM 
FROM EMP; 
PARTITION BY MGR 구문을 통해 매니저별로 데이터를 파티션화 한다.

  [예제] OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고

          이전 SALARY 데이터까지의 누적값을 출력한다.

          (SQL Server의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.)

SELECT MGR, ENAME, SAL, 
       SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM 
FROM EMP RANGE UNBOUNDED PRECEDING 
: 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

 

 - MAX 함수 :  파티션별 윈도우의 최대값

  [예제] 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값을 같이 구한다.

SELECT MGR, ENAME, SAL,
       MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX 
FROM EMP;

  파티션 내의 최대값을 파티션 내 모든 행에서 MGR_MAX라는 칼럼 값으로 가짐

  [예제] 추가로, INLINE VIEW를 이용해 파티션별 최대값을 가진 행만 추출할 수도 있다.

SELECT MGR, ENAME, SAL 
FROM (SELECT MGR, ENAME, SAL, 
             MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL 
      FROM EMP) 
WHERE SAL = IV_MAX_SAL ;

 

 - MIN 함수 : 파티션별 윈도우의 최소값

   [예제] 사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자를 기준으로 정렬하고,

            SALARY 최소값을 같이 구한다.

SELECT MGR, ENAME, HIREDATE, SAL, 
       MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN 
FROM EMP;

 

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

  [예제] EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데,

          조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다.

SELECT MGR, ENAME, HIREDATE, SAL,
       ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS 
                             BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG 
FROM EMP; 
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 
현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다. 
(ROWS는 현재 행의 앞뒤 건수를 말하는 것임)

 

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

  [예제] 사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하거나 150 이하로 많은 급여를 받는 인원수를 출력

 SELECT ENAME, SAL, 
        COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT 
FROM EMP; 
RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : 
현재 행의 급여값을 기준으로 급여가 -50에서 +150의 범위 내에 포함된 모든 행이 대상이 된다. 
 (RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시하는 것임)

 

* 그룹 내 행 순서 함수

 - FIRST_VALUE 함수 : 파티션별 윈도우에서 가장 먼저 나온 값

  [예제] 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력한다.

SELECT DEPTNO, ENAME, SAL, 
       FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH
FROM EMP; 
RANGE UNBOUNDED PRECEDING : 
현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

  공동 등수를 인정하지 않고 처음 나온 행만을 처리

  세부 항목을 정렬하고 싶다면 별도 정렬 조건 가진 인라인 뷰를 사용하거나, OVER () 내의 ORDER BY 절에 칼럼 추가

  [예제] 앞의 SQL 문장에서 같은 값을 가진 FIRST_VALUE를 처리하기 위해 ORDER BY 정렬 조건을 추가한다.

SELECT DEPTNO, ENAME, SAL, 
       FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) as RICH_EMP 
FROM EMP;

 

 - LAST_VALUE 함수 : 파티션별 윈도우에서 가장 나중에 나온 값

  [예제] 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력한다.

SELECT DEPTNO, ENAME, SAL, 
       LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO 
                               ORDER BY SAL DESC ROWS 
                               BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR 
FROM EMP; 
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 
현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

 

 - LAG 함수 : 파티션별 윈도우에서 이전 몇 번째 행의 값

  [예제] 직원들을 입사일자가 빠른 기준으로 정렬을 하고,

          본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력한다.

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

  [예제] LAG 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데,

          두 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이고 (DEFAULT 1),

          세 번째 인자는 예를 들어 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데

          이 경우 다른 값으로 바꾸어 줄 수 있다. 결과적으로 NVL이나 ISNULL 기능과 같다.

 SELECT ENAME, HIREDATE, SAL, 
        LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL
 FROM EMP
 WHERE JOB = 'SALESMAN' ;
 LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 
 가져올 값이 없는 경우는 0으로 처리한다.

 

 - LEAD 함수 : 파티션별 윈도우에서 이후 몇 번째 행의 값

  [예제] 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 바로 다음에 입사한 인력의 입사일자를 함께 출력한다.

SELECT ENAME, HIREDATE,
       LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED" 
FROM EMP;

 

* 그룹 내 비율 함수

 - RATIO_TO_REPORT 함수 :  파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다

                                      결과 값은 > 0 & <= 1 의 범위를 가진다. 그리고 개별 RATIO의 합을 구하면 1이 됨

  [예제] JOB이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력한다.

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

 

 - PERCENT_RANK 함수 : 파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로, 순서별 백분율

  [예제] 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력

SELECT DEPTNO, ENAME, SAL, 
       PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R 
FROM EMP;

 

 - CUME_DIST 함수 : 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구함

  [예제] 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지

           0과 1 사이의 값으로 출력한다.

SELECT DEPTNO, ENAME, SAL,
       CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST 
FROM EMP;

 

 - NTILE 함수 : 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과

  [예제] 전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류한다.

SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE 
FROM EMP