[DATABASE] 데이터베이스 윈도우 함수 사용해보기
윈도우 함수 개요
기존 관계형 데이터베이스는 컬럼과 컬럼간의 연산, 비교, 연결이나 집합에 대한 집계는 쉬운 반면,
행과 행간의 관계를 정의하거나, 비교, 연산하는 것을 하나의 SQL 문으로 처리하는 것은 매우 어렵습니다.
PL/SQL 같은 절차형 프로그램을 사용하거나, INLINE VIEW 를 이용해 복잡한 SQL 문을 작성해야 하던 것을 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 윈도우 함수입니다.
분석함수(ANALYTIC FUNCTION)나 순위함수(RANK FUNCTION)로도 알려져 있는 윈도우 함수는 데이터웨어하우스에서 발전한 기능입니다.
윈도우 함수는 다른 함수와는 달리 중첩해서 사용하지는 못하지만, 서브쿼리에서는 사용할 수 있습니다.
윈도우 함수의 종류 및 문법
윈도우 함수의 종류는 크게 다섯 개의 그룹으로 분류할 수 있습니다.
여기에서는 통계 분석 함수는 다루지 않습니다.
그룹 내 순위(RANK) 함수 : RANK, DSENSE_RANK, ROW_NUMBER
그룹 내 집계(AGGREGATE) 함수 : SUM, MAX, MIN, AVG, COUNT
그룹 내 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
그룹 내 비율 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
통계 분석 함수 : CORR, COVAR_POP, STDDEV, STDDEV_POP, STDDEV_SAMP 등
윈도우 함수는 OVER 문구가 키워드로 필수 포함됩니다.
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼] [ORDER BY 칼럼] [WINDOWING 절] ) FROM 테이블 명;
그룹 내 순위 함수
RANK
RANK 함수는 ORDER BY 절을 포함한 QUERY 문에서 특정 항목에 대한 순위를 구하는 함수입니다.
RANK 를 사용하여 사원 테이블에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력하는 방법입니다.
SQL> SELECT JOB, ENAME, SAL, 2 RANK() OVER (ORDER BY SAL DESC) ALL_RANK, 3 RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK 4 FROM EMP; JOB ENAME SAL ALL_RANK JOB_RANK --------- ---------- ---------- ---------- ---------- PRESIDENT KING 5000 1 1 ANALYST FORD 3000 2 1 ANALYST SCOTT 3000 2 1 MANAGER JONES 2975 4 1 MANAGER BLAKE 2850 5 2 MANAGER CLARK 2450 6 3 SALESMAN ALLEN 1600 7 1 SALESMAN TURNER 1500 8 2 CLERK MILLER 1300 9 1 SALESMAN WARD 1250 10 3 SALESMAN MARTIN 1250 10 3 JOB ENAME SAL ALL_RANK JOB_RANK --------- ---------- ---------- ---------- ---------- CLERK ADAMS 1100 12 2 CLERK JAMES 950 13 3 CLERK SMITH 800 14 4 14 rows selected.
ALL_RANK 는 급여가 높은 순으로 번호를 부여합니다.
JOB_RANK 는 직업별로 묶은뒤 각 직업별로 급여가 높은 순위로 번호를 부여합니다.
DSENSE_RANK
RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 다릅니다.
이해가 되시나요? ALL_RANK 에서 2, 2 는 2개고 다음 숫자가 4 이지만 DENSE_RANK 는 2, 2 다음의 숫자가 3 입니다.
SQL> SELECT JOB, ENAME, SAL, 2 RANK() OVER (ORDER BY SAL DESC) ALL_RANK, 3 DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK 4 FROM EMP; JOB ENAME SAL ALL_RANK DENSE_RANK --------- ---------- ---------- ---------- ---------- PRESIDENT KING 5000 1 1 ANALYST FORD 3000 2 2 ANALYST SCOTT 3000 2 2 MANAGER JONES 2975 4 3 MANAGER BLAKE 2850 5 4 MANAGER CLARK 2450 6 5 SALESMAN ALLEN 1600 7 6 SALESMAN TURNER 1500 8 7 CLERK MILLER 1300 9 8 SALESMAN WARD 1250 10 9 SALESMAN MARTIN 1250 10 9 JOB ENAME SAL ALL_RANK DENSE_RANK --------- ---------- ---------- ---------- ---------- CLERK ADAMS 1100 12 10 CLERK JAMES 950 13 11 CLERK SMITH 800 14 12 14 rows selected.
ROW_NUMBER
동일한 값이라도 고유한 순위를 부여합니다.
같은 값에 대해서는 어떤 순서가 정해질지 알 수 없습니다. ORACLE 의 경우에는 ROWID 가 적은 행이 먼저 나옵니다.
이 부분은 벤더별로 다른 결과가 나올 수 있기 때문에 ORDER BY 절을 이용하여 정렬 기준을 정의해야 합니다.
SQL> SELECT JOB, ENAME, SAL, 2 RANK() OVER (ORDER BY SAL DESC) ALL_RANK, 3 ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER 4 FROM EMP; JOB ENAME SAL ALL_RANK ROW_NUMBER --------- ---------- ---------- ---------- ---------- PRESIDENT KING 5000 1 1 ANALYST FORD 3000 2 2 ANALYST SCOTT 3000 2 3 MANAGER JONES 2975 4 4 MANAGER BLAKE 2850 5 5 MANAGER CLARK 2450 6 6 SALESMAN ALLEN 1600 7 7 SALESMAN TURNER 1500 8 8 CLERK MILLER 1300 9 9 SALESMAN WARD 1250 10 10 SALESMAN MARTIN 1250 10 11 JOB ENAME SAL ALL_RANK ROW_NUMBER --------- ---------- ---------- ---------- ---------- CLERK ADAMS 1100 12 12 CLERK JAMES 950 13 13 CLERK SMITH 800 14 14 14 rows selected.
그룹 내 집계 함수
SUM
SUM 함수를 이용하여 파티션별로 윈도우의 합을 구할 수 있습니다.
MGR_SUM 을 보면 각 MGR 별로 합산된 값을 확인할 수 있습니다.
SQL> SELECT MGR, ENAME, SAL,
2 SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
3 FROM EMP;
MGR ENAME SAL MGR_SUM
---------- ---------- ---------- ----------
7566 FORD 3000 6000
7566 SCOTT 3000 6000
7698 JAMES 950 6550
7698 ALLEN 1600 6550
7698 WARD 1250 6550
7698 TURNER 1500 6550
7698 MARTIN 1250 6550
7782 MILLER 1300 1300
7788 ADAMS 1100 1100
7839 BLAKE 2850 8275
7839 JONES 2975 8275
MGR ENAME SAL MGR_SUM
---------- ---------- ---------- ----------
7839 CLARK 2450 8275
7902 SMITH 800 800
KING 5000 5000
14 rows selected.MAX
MAX 함수를 이용하여 파티션별 윈도우의 최대값을 구할 수 있습니다.
MGR_MAX 를 보면 각 MGR 별로 최대 값이 출력되는 것을 확인할 수 있습니다.
SQL> SELECT MGR, ENAME, SAL,
2 MAX(SAL) OVER (PARTITION BY MGR) MGR_MAX
3 FROM EMP;
MGR ENAME SAL MGR_MAX
---------- ---------- ---------- ----------
7566 FORD 3000 3000
7566 SCOTT 3000 3000
7698 JAMES 950 1600
7698 ALLEN 1600 1600
7698 WARD 1250 1600
7698 TURNER 1500 1600
7698 MARTIN 1250 1600
7782 MILLER 1300 1300
7788 ADAMS 1100 1100
7839 BLAKE 2850 2975
7839 JONES 2975 2975
MGR ENAME SAL MGR_MAX
---------- ---------- ---------- ----------
7839 CLARK 2450 2975
7902 SMITH 800 800
KING 5000 5000
14 rows selected.MIN, AVG, COUNT 함수도 위의 방법과 동일하므로 더는 작성하지 않겠습니다.
그룹 내 행 순서
FIRST_VALUE
파티션별 윈도우에서 가장 먼저 나온 값을 구합니다.
부서별 직원들을 연봉이 높은 수서로 정렬하고, 부서별로 가장 먼저 나온 값을 출력합니다.
SQL> SELECT DEPTNO, ENAME, SAL,
2 FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
3 ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
4 FROM EMP;
DEPTNO ENAME SAL DEPT_RICH
---------- ---------- ---------- ----------
10 KING 5000 KING
10 CLARK 2450 KING
10 MILLER 1300 KING
20 SCOTT 3000 SCOTT
20 FORD 3000 SCOTT
20 JONES 2975 SCOTT
20 ADAMS 1100 SCOTT
20 SMITH 800 SCOTT
30 BLAKE 2850 BLAKE
30 ALLEN 1600 BLAKE
30 TURNER 1500 BLAKE
DEPTNO ENAME SAL DEPT_RICH
---------- ---------- ---------- ----------
30 MARTIN 1250 BLAKE
30 WARD 1250 BLAKE
30 JAMES 950 BLAKE
14 rows selectedUNBOUNDED PRECEDING 은 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정합니다.
DEPTNO 20 의 SCOTT 와 FORD 의 SAL 은 같은 값을 가집니다.
이 중 어느 사람이 최고 급여자로 선택될지는 SQL 문만 가지고는 판단할 수 없습니다.
FIRST_VALUE 는 다른 함수와 달리 공동 등수를 인정하지 않고 처음 나온 행만을 처리합니다.
위처럼 공동 등수가 있을 경우에 의도적으로 세부 항목을 정렬하려는 경우 ORDER BY 절에 컬럼을 추가해야 합니다.
SQL> SELECT DEPTNO, ENAME, SAL,
2 FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC
3 ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
4 FROM EMP;
DEPTNO ENAME SAL DEPT_RICH
---------- ---------- ---------- ----------
10 KING 5000 KING
10 CLARK 2450 KING
10 MILLER 1300 KING
20 FORD 3000 FORD
20 SCOTT 3000 FORD
20 JONES 2975 FORD
20 ADAMS 1100 FORD
20 SMITH 800 FORD
30 BLAKE 2850 BLAKE
30 ALLEN 1600 BLAKE
30 TURNER 1500 BLAKE
DEPTNO ENAME SAL DEPT_RICH
---------- ---------- ---------- ----------
30 MARTIN 1250 BLAKE
30 WARD 1250 BLAKE
30 JAMES 950 BLAKE
14 rows selected.LAST_VALUE
파티션별로 윈도우에서 가장 나중에 나온 값을 구합니다.
SQL> SELECT DEPTNO, ENAME, SAL,
2 LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
3 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS DEPT_POOR
4 FROM EMP;
DEPTNO ENAME SAL DEPT_POOR
---------- ---------- ---------- ----------
10 KING 5000 MILLER
10 CLARK 2450 MILLER
10 MILLER 1300 MILLER
20 SCOTT 3000 SMITH
20 FORD 3000 SMITH
20 JONES 2975 SMITH
20 ADAMS 1100 SMITH
20 SMITH 800 SMITH
30 BLAKE 2850 JAMES
30 ALLEN 1600 JAMES
30 TURNER 1500 JAMES
DEPTNO ENAME SAL DEPT_POOR
---------- ---------- ---------- ----------
30 MARTIN 1250 JAMES
30 WARD 1250 JAMES
30 JAMES 950 JAMES
14 rows selected.ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 은 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정합니다.
FIRST_VALUE 와 속성은 비슷하므로 세부 항목의 컬럼을 정렬해야 하는 경우 ORDER BY 를 작성합니다.
LAG
파티션별로 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있습니다.
입사일자가 빠른 순으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력합니다.
SQL> SELECT ENAME, HIREDATE, SAL, 2 LAG(SAL) OVER (ORDER BY HIREDATE) AS PERV_SAL 3 FROM EMP 4 WHERE JOB = 'SALESMAN'; ENAME HIREDATE SAL PERV_SAL ---------- -------- ---------- ---------- ALLEN 81-02-11 1600 WARD 81-02-23 1250 1600 TURNER 81-08-21 1500 1250 MARTIN 81-09-10 1250 1500
LAG 함수는 3개의 인자를 사용할 수 있는데, 두 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것, 세 번째 인자는 예를 들어 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데 이 경우 다른 값으로 바꿔줍니다.
SQL> SELECT ENAME, HIREDATE, SAL, 2 LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) AS PERV_SAL 3 FROM EMP 4 WHERE JOB = 'SALESMAN'; ENAME HIREDATE SAL PERV_SAL ---------- -------- ---------- ---------- ALLEN 81-02-11 1600 0 WARD 81-02-23 1250 0 TURNER 81-08-21 1500 1600 MARTIN 81-09-10 1250 1250
LEAD
파티션별로 윈도우에서 이후 몇 번째의 행의 값을 가져올 수 있습니다.
입사일자가 빠른 순으로 정렬하고, 본인보다 입사일자가 한 명 뒤인 사원의 입사일짜를 함께 출력합니다.
SQL> SELECT ENAME, HIREDATE, 2 LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) AS NEXTHIRED 3 FROM EMP; ENAME HIREDATE NEXTHIRE ---------- -------- -------- SMITH 80-12-09 81-02-11 ALLEN 81-02-11 81-02-23 WARD 81-02-23 81-04-01 JONES 81-04-01 81-05-01 BLAKE 81-05-01 81-05-09 CLARK 81-05-09 81-08-21 TURNER 81-08-21 81-09-10 MARTIN 81-09-10 81-11-17 KING 81-11-17 81-12-11 JAMES 81-12-11 81-12-11 FORD 81-12-11 82-01-11 ENAME HIREDATE NEXTHIRE ---------- -------- -------- MILLER 82-01-11 82-12-22 SCOTT 82-12-22 83-01-15 ADAMS 83-01-15 14 rows selected.
그룹 내 비율 함수
RATIO_TO_REPORT
파티션 내 전체 SUM 값에 대한 행별 컬럼 값의 백분율을 소수점으로 구할 수 있습니다.
결과 값은 >0 과 <=1 사이의 범위를 가지며, 개별 RATIO 의 합을 구하면 1 이 됩니다.
SQL> SELECT ENAME, SAL, 2 ROUND(RATIO_TO_REPORT(SAL) OVER(), 2) AS RTR 3 FROM EMP 4 WHERE JOB = 'SALESMAN'; ENAME SAL RTR ---------- ---------- ---------- MARTIN 1250 .22 ALLEN 1600 .29 TURNER 1500 .27 WARD 1250 .22
PERCENT_RANK
파티션별 윈도우에서 제일 먼저 나온 것을 0 으로 제일 늦게 나오는 것을 1 로 하여 값이 아닌 행의 순서별 백분율을 구합니다.
예를 들어서 DEPTNO 10 의 CLARK 는 DEPTNO 10 의 중간위치에 속하므로 0.5 가 출력됩니다.
SQL> SELECT DEPTNO, ENAME, SAL,
2 PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS PR
3 FROM EMP;
DEPTNO ENAME SAL PR
---------- ---------- ---------- ----------
10 KING 5000 0
10 CLARK 2450 .5
10 MILLER 1300 1
20 SCOTT 3000 0
20 FORD 3000 0
20 JONES 2975 .5
20 ADAMS 1100 .75
20 SMITH 800 1
30 BLAKE 2850 0
30 ALLEN 1600 .2
30 TURNER 1500 .4
DEPTNO ENAME SAL PR
---------- ---------- ---------- ----------
30 MARTIN 1250 .6
30 WARD 1250 .6
30 JAMES 950 1
14 rows selected.CUME_DIST
파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구합니다.
DEPTNO 10 의 CLARK 는 DEPTNO 3건 중 중간에 위치하므로 0.666666 이 출력됩니다.
DEPTNO 20 의 SCOTT 와 FORD 는 5건중 2건이므로 0.4 가 출력됩니다.
SQL> SELECT DEPTNO, ENAME, SAL,
2 CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CD
3 FROM EMP;
DEPTNO ENAME SAL CD
---------- ---------- ---------- ----------
10 KING 5000 .333333333
10 CLARK 2450 .666666667
10 MILLER 1300 1
20 SCOTT 3000 .4
20 FORD 3000 .4
20 JONES 2975 .6
20 ADAMS 1100 .8
20 SMITH 800 1
30 BLAKE 2850 .166666667
30 ALLEN 1600 .333333333
30 TURNER 1500 .5
DEPTNO ENAME SAL CD
---------- ---------- ---------- ----------
30 MARTIN 1250 .833333333
30 WARD 1250 .833333333
30 JAMES 950 1
14 rows selected.NTILE
파티션별 전체 건수를 인자값으로 N 등분한 결과를 구할 수 있습니다.
SQL> SELECT DEPTNO, ENAME, SAL,
2 NTILE(4) OVER (ORDER BY SAL DESC) AS NTILE_4
3 FROM EMP;
DEPTNO ENAME SAL NTILE_4
---------- ---------- ---------- ----------
10 KING 5000 1
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 1
30 BLAKE 2850 2
10 CLARK 2450 2
30 ALLEN 1600 2
30 TURNER 1500 2
10 MILLER 1300 3
30 WARD 1250 3
30 MARTIN 1250 3
DEPTNO ENAME SAL NTILE_4
---------- ---------- ---------- ----------
20 ADAMS 1100 4
30 JAMES 950 4
20 SMITH 800 4
14 rows selected.이 내용은 'SQL 전문가 가이드, 한국데이터베이스 진흥원' 에서 공부한 내용입니다.