1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 상반기 - 대용량 데이터 베이스 스터디
  4. 제6절 윈도우 함수(WINDOW FUNCTION)

제6절 윈도우 함수(WINDOW FUNCTION) (by nakamori) [2013.03.24]

제6절 윈도우 함수(WINDOW FUNCTION)

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 바로 WINDOW FUNCTION이다.
윈도우 함수를 활용하면 복잡한 프로그램을 하나의 SQL 문장으로 쉽게 해결할 수 있다.
분석 함수(ANALYTIC FUNCTION)나 순위 함수(RANK FUNCTION)로도 알려져 있는 윈도우 함수 (ANSI/ISOSQL 표준은 WINDOW FUNCTION이란 용어를 사용함)는 데이터웨어하우스에서 발전한 기능이다.

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

  • WINDOW FUNCTION 종류
구분종류종류
순위(RANK) 관련RANK, DENSE_RANK, ROW_NUMBER대부분 지원
집계(AGGREGATE) 관련SUM, MAX, MIN, AVG, COUNTSQL Server 경우 Over절 내 Orderby 지원 못함
순서 관련 함수FIRST_VALUE, LAST_VALUE, LAG, LEADORACLE 만 지원
그룹 내 비율 관련 함수CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORTPERCENT_RANK 함수는 ANSI/ISO SQL 표준과 Oracle DBMS에서 지원하고 있으며, NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server에서 지원하고 있다. RATIO_TO_REPORT 함수는 Oracle에서만 지원되는 함수(현업에서 유용).
선형분석을 포함한 통계분석 함수CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY특화되어있으므로 생략

WINDOW FUNCTION SYNTAX

  • WINDOW 함수에는 OVER 문구가 키워드로 필수 포함된다.

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


BETWEEN 사용 타입
 ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING AND UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
BETWEEN 미사용 타입
 ROWS | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING
  • WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다. - ARGUMENTS (인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.
  • PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
  • ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.
  • WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다. 다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다.

그룹 내 순위함수.

3.1 RANK 함수
  • RANK 함수는 ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이다.
  • 이때 특정 범위(PARTITION) 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다. 또한 동일한 값에 대해서는 동일한 순위를 부여하게 된다.

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;

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
CLERK     ADAMS            1100         12          2
CLERK     JAMES             950         13          3
CLERK     SMITH             800         14          4

14 rows selected.


------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    14 |   364 |     5  (40)| 00:00:01 |
|   1 |  WINDOW SORT                |      |    14 |   364 |     5  (40)| 00:00:01 |
|   2 |   WINDOW SORT               |      |    14 |   364 |     5  (40)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------


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

JOB       ENAME             SAL   JOB_RANK
--------- ---------- ---------- ----------
ANALYST   FORD             3000          1
ANALYST   SCOTT            3000          1
CLERK     MILLER           1300          1
CLERK     ADAMS            1100          2
CLERK     JAMES             950          3
CLERK     SMITH             800          4
MANAGER   JONES            2975          1
MANAGER   BLAKE            2850          2
MANAGER   CLARK            2450          3
PRESIDENT KING             5000          1
SALESMAN  ALLEN            1600          1
SALESMAN  TURNER           1500          2
SALESMAN  MARTIN           1250          3
SALESMAN  WARD             1250          3

14 rows selected.
3.2 DENSE_RANK 함수


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

JOB       ENAME             SAL       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
CLERK     ADAMS            1100         12         10
CLERK     JAMES             950         13         11
CLERK     SMITH             800         14         12

14 rows selected.
3.3 ROW_NUMBER 함수
  • ROW_NUMBER 함수는 RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여한다. 


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

JOB       ENAME             SAL       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
CLERK     ADAMS            1100         12         12
CLERK     JAMES             950         13         13
CLERK     SMITH             800         14         14

14 rows selected.

일반 집계 함수

3.4 SUM 함수
  • SUM 함수를 이용해 파티션별 윈도우의 합을 구할 수 있다.


SELECT MGR, ENAME, SAL
     , SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM 
  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
      7839 CLARK            2450       8275
      7902 SMITH             800        800
           KING             5000       5000

14 rows selected.


SELECT MGR, ENAME, SAL
     , SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM 
  FROM EMP;

       MGR ENAME             SAL    MGR_SUM
---------- ---------- ---------- ----------
      7566 SCOTT            3000       6000
      7566 FORD             3000       6000
      7698 JAMES             950        950
      7698 WARD             1250       3450
      7698 MARTIN           1250       3450
      7698 TURNER           1500       4950
      7698 ALLEN            1600       6550
      7782 MILLER           1300       1300
      7788 ADAMS            1100       1100
      7839 CLARK            2450       2450
      7839 BLAKE            2850       5300
      7839 JONES            2975       8275
      7902 SMITH             800        800
           KING             5000       5000

14 rows selected.

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

SELECT MGR, ENAME, SAL
     , MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX 
  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
      7839 CLARK            2450       2975
      7902 SMITH             800        800
           KING             5000       5000

14 rows selected.

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 ; 

       MGR ENAME             SAL
---------- ---------- ----------
      7566 FORD             3000
      7566 SCOTT            3000
      7698 ALLEN            1600
      7782 MILLER           1300
      7788 ADAMS            1100
      7839 JONES            2975
      7902 SMITH             800
           KING             5000

8 rows selected.
3.6 MIN 함수

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

       MGR ENAME      HIREDATE         SAL    MGR_MIN
---------- ---------- --------- ---------- ----------
      7566 FORD       03-DEC-81       3000       3000
      7566 SCOTT      09-DEC-82       3000       3000
      7698 ALLEN      20-FEB-81       1600       1600
      7698 WARD       22-FEB-81       1250       1250
      7698 TURNER     08-SEP-81       1500       1250
      7698 MARTIN     28-SEP-81       1250       1250
      7698 JAMES      03-DEC-81        950        950
      7782 MILLER     23-JAN-82       1300       1300
      7788 ADAMS      12-JAN-83       1100       1100
      7839 JONES      02-APR-81       2975       2975
      7839 BLAKE      01-MAY-81       2850       2850
      7839 CLARK      09-JUN-81       2450       2450
      7902 SMITH      17-DEC-80        800        800
           KING       17-NOV-81       5000       5000

14 rows selected.
3.7 AVG 함수
  • 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; 

       MGR ENAME      HIREDATE         SAL    MGR_AVG
---------- ---------- --------- ---------- ----------
      7566 FORD       03-DEC-81       3000       3000
      7566 SCOTT      09-DEC-82       3000       3000
      7698 ALLEN      20-FEB-81       1600       1425
      7698 WARD       22-FEB-81       1250       1450
      7698 TURNER     08-SEP-81       1500       1333
      7698 MARTIN     28-SEP-81       1250       1233
      7698 JAMES      03-DEC-81        950       1100
      7782 MILLER     23-JAN-82       1300       1300
      7788 ADAMS      12-JAN-83       1100       1100
      7839 JONES      02-APR-81       2975       2913
      7839 BLAKE      01-MAY-81       2850       2758
      7839 CLARK      09-JUN-81       2450       2650
      7902 SMITH      17-DEC-80        800        800
           KING       17-NOV-81       5000       5000
3.8 COUNT 함수

SELECT ENAME, SAL
     , COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT
  FROM EMP; 

ENAME             SAL    SIM_CNT
---------- ---------- ----------
SMITH             800          2
JAMES             950          2
ADAMS            1100          3
WARD             1250          3
MARTIN           1250          3
MILLER           1300          3
TURNER           1500          2
ALLEN            1600          1
CLARK            2450          1
BLAKE            2850          4
JONES            2975          3
SCOTT            3000          3
FORD             3000          3
KING             5000          1

14 rows selected.

그룹 내 행 순서 함수

3.9 FIRST_VALUE 함수
  • FIRST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.
  • SQL Server에서는 지원하지 않는 함수이다. MIN 함수를 활용하여 같은 결과를 얻을 수도 있다. 

SELECT DEPTNO, ENAME, SAL
     , FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH 
  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
        30 MARTIN           1250 BLAKE
        30 WARD             1250 BLAKE
        30 JAMES             950 BLAKE


14 rows selected.

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; 

    DEPTNO ENAME             SAL RICH_EMP
---------- ---------- ---------- ----------
        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
        30 MARTIN           1250 BLAKE
        30 WARD             1250 BLAKE
        30 JAMES             950 BLAKE

14 rows selected.
3.9 LAST_VALUE 함수
  • LAST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.
  • SQL Server에서는 지원하지 않는 함수이다. MAX 함수를 활용하여 같은 결과를 얻을 수도 있다. 

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;

    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
        30 MARTIN           1250 JAMES
        30 WARD             1250 JAMES
        30 JAMES             950 JAMES

14 rows selected.
3.9 LAG 함수
  • LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. SQL Server에서는 지원하지 않는 함수이다. 


직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력한다. 


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

ENAME      HIREDATE         SAL   PREV_SAL
---------- --------- ---------- ----------
ALLEN      20-FEB-81       1600
WARD       22-FEB-81       1250       1600
TURNER     08-SEP-81       1500       1250
MARTIN     28-SEP-81       1250       1500


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

ENAME      HIREDATE         SAL   PREV_SAL
---------- --------- ---------- ----------
ALLEN      20-FEB-81       1600          0
WARD       22-FEB-81       1250          0
TURNER     08-SEP-81       1500       1600
MARTIN     28-SEP-81       1250       1250
  • LAG 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데,
  • 두 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이고 (DEFAULT 1),
  • 세 번째 인자는 예를 들어 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데
  • 이 경우 다른 값으로 바꾸어 줄 수 있다. 결과적으로 NVL이나 ISNULL 기능과 같다.
3.10 LEAD 함수
  • LEAD 함수를 이용해 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.
  • 참고로 SQL Server에서는 지원하지 않는 함수이다. 


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

ENAME      HIREDATE  NEXTHIRED
---------- --------- ---------
SMITH      17-DEC-80 20-FEB-81
ALLEN      20-FEB-81 22-FEB-81
WARD       22-FEB-81 02-APR-81
JONES      02-APR-81 01-MAY-81
BLAKE      01-MAY-81 09-JUN-81
CLARK      09-JUN-81 08-SEP-81
TURNER     08-SEP-81 28-SEP-81
MARTIN     28-SEP-81 17-NOV-81
KING       17-NOV-81 03-DEC-81
JAMES      03-DEC-81 03-DEC-81
FORD       03-DEC-81 23-JAN-82
MILLER     23-JAN-82 09-DEC-82
SCOTT      09-DEC-82 12-JAN-83
ADAMS      12-JAN-83

14 rows selected.
  • LEAD 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데,
  • 두 번째 인자는 몇 번째 후의 행을 가져올지 결정하는 것이고 (DEFAULT 1),
  • 세 번째 인자는 예를 들어 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데
  • 이 경우 다른 값으로 바꾸어 줄 수 있다. 결과적으로 NVL이나 ISNULL 기능과 같다

그룹 내 비율 함수

3.11 RATIO_TO_REPORT 함수
  • RATIO_TO_REPORT 함수를 이용해 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다.
  • 결과 값은 > 0 & <= 1 의 범위를 가진다.
  • 그리고 개별 RATIO의 합을 구하면 1이 된다. SQL Server에서는 지원하지 않는 함수이다.

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



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

ENAME             SAL        R_R
---------- ---------- ----------
ALLEN            1600        .29
WARD             1250        .22
MARTIN           1250        .22
TURNER           1500        .27
3.11 PERCENT_RANK 함수
  • PERCENT_RANK 함수를 이용해 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로,
  • 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구한다.
  • 결과 값은 >= 0 & <= 1 의 범위를 가진다. 참고로 SQL Server에서는 지원하지 않는 함수이다. 


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

    DEPTNO ENAME             SAL        P_R
---------- ---------- ---------- ----------
        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
        30 MARTIN           1250         .6
        30 WARD             1250         .6
        30 JAMES             950          1

14 rows selected.
3.11 CUME_DIST 함수
  • CUME_DIST 함수를 이용해 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다.
  • 결과 값은 > 0 & <= 1 의 범위를 가진다. 참고로 SQL Server에서는 지원하지 않는 함수이다. 


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

    DEPTNO ENAME             SAL  CUME_DIST
---------- ---------- ---------- ----------
        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
        30 MARTIN           1250 .833333333
        30 WARD             1250 .833333333
        30 JAMES             950          1

14 rows selected.
3.11 NTILE 함수
  • NTILE 함수를 이용해 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구할 수 있다. 


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

ENAME             SAL  QUAR_TILE
---------- ---------- ----------
KING             5000          1
FORD             3000          1
SCOTT            3000          1
JONES            2975          1
BLAKE            2850          2
CLARK            2450          2
ALLEN            1600          2
TURNER           1500          2
MILLER           1300          3
WARD             1250          3
MARTIN           1250          3
ADAMS            1100          4
JAMES             950          4
SMITH             800          4

14 rows selected.
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 상반기 - 대용량 데이터 베이스 스터디
  4. 제6절 윈도우 함수(WINDOW FUNCTION)