[SQL] 분석 함수

분석 함수

테이블에 있는 로우에 대해 특정 그룹별로 집계값을 산출할 때 사용한다.

GROUP BY 절이 그룹별로 로우 수가 줄어들게 하는데 반해, 집계 함수는 로우 손실 없이 집계값을 산출 할 수 있다.

분석 함수에서 사용하는 로우별 그룹을 WINDOW 라고 하는데, 집계값 계산을 위한 로우의 범위를 결정하는 역할을 한다.

형태

분석함수(매개변수) OVER    (PARTITION BY EXPR1, EXPR2, ...
                          ORDER BY EXPR3, EXPR4, ...
                            WINDOW ...)

종류

AVG / SUM / MAX / MIN / COUNT / CUME_DIST / DENSE_RANK / PERCENT_RANK / FIRST / LAST / LAG / LEAD / ROW_NUMBER 등

절 설명

PARTITION BY     분석 함수로 계산될 대상 로우 그룹(파티션)을 지정한다.

ORDER BY           파티션 안에서의 순서를 지정

WINDOW ...        파티션으로 분할된 그룹에서 더 상세한 그룹으로 분할할 때 사용

 AVG / CORR / COUNT / COVAR_POP / COVAR_SAMP / FIRST_VALUE / LAST_VALUE / MAX / MIN / NTH_VALUE / STDDEV 등

ROW_NUMBER

파티션으로 분할된 그룹별로 각 로우에 대한 순번을 반환한다.

SELECT  DEPARTMENT_ID
        , EMP_NAME
        , ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID
                             ORDER BY DEPARTMENT_ID, EMP_NAME) DEP_ROWS
FROM    EMPLOYEES;

RANK

파티션별 순위를 반환

SELECT  DEPARTMENT_ID
        , EMP_NAME
        , SALARY
        , RANK() OVER (PARTITION BY DEPARTMENT_ID
                       ORDER BY SALARY) DEP_RANK
FROM    EMPLOYEES;

DENSE_RANK

RANK와 비슷하나, 같은 순위가 나오면 다음 순위가 한 번 건너뛰지 않고 매겨진다.

SELECT  *
FROM    (   SELECT  DEPARTMENT_ID
                    , EMP_NAME
                    , SALARY
                    , DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID
                                         ORDER BY SALARY) DEP_RANK
            FROM    EMPLOYEES
        )
WHERE   DEP_RANK <= 3;

CUME_DIST

주어진 그룹에 대한 상대적인 누적분포도 값을 반환한다. 0 초과 1 이하의 값을 반환한다.

SELECT  DEPARTMENT_ID
        , EMP_NAME
        , SALARY
        , CUME_DIST() OVER (PARTITION BY DEPARTMENT_ID
                            ORDER BY SALARY) CUME_DIST
FROM    EMPLOYEES;

PERCENT_RANK

해당 그룹 내의 백분위 순위를 반환하며, 0 이상 1 이하의 값을 반환한다.

백분위 순위는 그룹 안에서 해당 로우의 값보다 작은 값의 비율을 의미한다.

SELECT  DEPARTMENT_ID
        , EMP_NAME
        , SALARY
        , PERCENT_RANK() OVER (PARTITION BY DEPARTMENT_ID
                               ORDER BY SALARY) PERCENTILE
FROM    EMPLOYEES;

NTILE(EXPR)

파티션별로 EXPR에 명시된 값만큼 분할한 결과를 반환한다.

SELECT  DEPARTMENT_ID
        , EMP_NAME
        , SALARY
        , NTILE(4) OVER (PARTITION BY DEPARTMENT_ID
                               ORDER BY SALARY) NTILES
FROM    EMPLOYEES;

LAG(EXPR, OFFSET, DEFAULT_VALUE)

LEAD(EXPR, OFFSET, DEFAULT_VALUE)

LAG 는 OFFSET의 수 만큼의 선행 로우의 값을, LEAD 는 OFFSET의 수 만큼의 후행 로우의 값을 반환한다.

SELECT  EMP_NAME
        , HIRE_DATE
        , SALARY
        , LAG(SALARY, 1, 0) OVER (ORDER BY HIRE_DATE) AS PREV_SAL
        , LEAD(SALARY,1, 0) OVER (ORDER BY HIRE_DATE) AS NEXT_SAL
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID = 30;

SELECT  EMP_NAME
        , HIRE_DATE
        , SALARY
        , LAG(SALARY, 2, 0) OVER (ORDER BY HIRE_DATE) AS PREV_SAL
        , LEAD(SALARY,2, 0) OVER (ORDER BY HIRE_DATE) AS NEXT_SAL
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID = 30;

WIDTH_BUCKET(EXPR, MIN_VALUE, MAX_VALUE, NUM_BUCKETS)

EXPR 값에 따라 최솟값과 최대값을 주어 NUM_BUCKETS 수만큼 분할한다.

SELECT  DEPARTMENT_ID, EMP_NAME, SALARY
        , NTILE(4) OVER (PARTITION BY DEPARTMENT_ID
                         ORDER BY SALARY ) NTILES
        , WIDTH_BUCKET (SALARY, 1000, 10000, 4) WIDTH_BUCKETS
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID IN (60);

FIRST, LAST

MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV 집계함수와 같이 사용되어 주어진 그룹에 대해 내부적으로 순위를 매겨 결과를 산출한다.

WITH BASIS AS ( SELECT  PERIOD, REGION, SUM(LOAN_JAN_AMT) JAN_AMT
                FROM    KOR_LOAN_STATUS
                GROUP BY PERIOD, REGION ),
     BASIS2 AS( SELECT  PERIOD, MIN(JAN_AMT) MIN_AMT, MAX(JAN_AMT) MAX_AMT
                FROM    BASIS
                GROUP BY PERIOD )
SELECT  A.PERIOD
        , B.REGION "최소지역"
        , B.JAN_AMT "최소금액"
        , C.REGION "최대지역"
        , C.JAN_AMT "최대금액"
FROM    BASIS2 A, BASIS B, BASIS C
WHERE   A.PERIOD = B.PERIOD
        AND A.MIN_AMT = B.JAN_AMT
        AND A.PERIOD = C.PERIOD
        AND A.MAX_AMT = C.JAN_AMT
ORDER BY 1, 2;

WITH BASIS AS ( SELECT  PERIOD, REGION, SUM(LOAN_JAN_AMT) JAN_AMT
                FROM    KOR_LOAN_STATUS
                GROUP BY PERIOD, REGION )
SELECT  A.PERIOD
        , MIN(A.REGION) KEEP (DENSE_RANK FIRST ORDER BY JAN_AMT) "최소지역"
        , MIN(JAN_AMT) "최소금액"
        , MAX(A.REGION) KEEP (DENSE_RANK LAST ORDER BY JAN_AMT) "최대지역"
        , MAX(JAN_AMT) "최대금액"
FROM    BASIS A
GROUP BY A.PERIOD
ORDER BY 1, 2;

RATIO_TO_REPORT(EXPR)

주어진 그룹에 대해 EXPR 값의 합을 기준으로 각 로구의 상대적 비율을 반환한다.

SELECT  DEPARTMENT_ID, EMP_NAME, HIRE_DATE, SALARY
        , ROUND(RATIO_TO_REPORT(SALARY) OVER (PARTITION BY DEPARTMENT_ID), 2) * 100 AS SALARY_PERCENT
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID IN (30, 90);