[SQL] WINDOW 함수

WINDOW 함수

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

AVG, CORR, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN, NTH_VALUE, STDDEV, SUM, VARIANCE 등 함수와 사용가능

FIRST_VALUE(EXPR)

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

SELECT  DEPARTMENT_ID, EMP_NAME, HIRE_DATE, SALARY
        , FIRST_VALUE(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ALL_SALARY
        , FIRST_VALUE(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FR_ST_TO_CURRENT_SAL
        , FIRST_VALUE(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE
                                    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS FR_CURRENT_TO_END_SAL
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID IN (30, 90);

LAST_VALUE(EXPR)

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

SELECT  DEPARTMENT_ID, EMP_NAME, HIRE_DATE, SALARY
        , LAST_VALUE(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ALL_SALARY
        , LAST_VALUE(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FR_ST_TO_CURRENT_SAL
        , LAST_VALUE(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE
                                    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS FR_CURRENT_TO_END_SAL
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID IN (30, 90);

NTH_VALUE(MEASURE_EXPR, N)

주어진 그룹에서 N번째 로우에 해당하는 MEASURE_EXPR 값을 반환한다.

SELECT  DEPARTMENT_ID, EMP_NAME, HIRE_DATE, SALARY
        , NTH_VALUE(SALARY, 2) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ALL_SALARY
        , NTH_VALUE(SALARY, 2) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FR_ST_TO_CURRENT_SAL
        , NTH_VALUE(SALARY, 2) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE
                                    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS FR_CURRENT_TO_END_SAL
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID IN (30, 90);