by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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




제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, COUNT SQL Server 경우 Over절 내 Orderby 지원 못함
순서 관련 함수 FIRST_VALUE, LAST_VALUE, LAG, LEAD ORACLE 만 지원
그룹 내 비율 관련 함수 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT PERCENT_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) 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다. 또한 동일한 값에 대해서는 동일한 순위를 부여하게 된다.

SQL>  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 |
------------------------------------------------------------------------------------

SQL> 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 함수

SQL> 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 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여한다.


SQL>  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 함수를 이용해 파티션별 윈도우의 합을 구할 수 있다.

SQL>  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.


SQL> 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 함수
SQL> 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.

SQL> 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 함수
SQL> 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를 구하는데,
조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다.


SQL> 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 함수
SQL>  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 함수를 활용하여 같은 결과를 얻을 수도 있다.

SQL> 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.

SQL> 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 함수를 활용하여 같은 결과를 얻을 수도 있다.

SQL> 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에서는 지원하지 않는 함수이다.


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


SQL>  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


SQL> 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에서는 지원하지 않는 함수이다.


SQL> 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인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력한다.


SQL> 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에서는 지원하지 않는 함수이다.


SQL> 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에서는 지원하지 않는 함수이다.


SQL>  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 등분한 결과를 구할 수 있다.


SQL> 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.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 3월 29, 2013

    이준우 says:

    그룹내 비율함수는 거의 써본적이 없는 듯하네요. 나머지는 정말 활용성 높은거 같습니다. 개발자 분들 중에서 윈도우 함수 쓸 줄 아시는 분들은 제...

    그룹내 비율함수는 거의 써본적이 없는 듯하네요.
    나머지는 정말 활용성 높은거 같습니다.
    개발자 분들 중에서 윈도우 함수 쓸 줄 아시는 분들은 제 주변에서 드물다는..^^

    1. 3월 29, 2013

      하봉래 says:

      유용한 기능이 많은데, 저도 조금밖에 안써봤네요.

      유용한 기능이 많은데, 저도 조금밖에 안써봤네요.

  2. 3월 29, 2013

    이가혜 says:

    근데. 의외로. ROW_NUM으로 부분 범위 처리 할려고 하시는 분들은 쫌 있는듯;;; 잘되는 경우도 있지만.. 대부분.ㅠ.ㅠ 잘못 처리 한다는...

    근데. 의외로. ROW_NUM으로 부분 범위 처리 할려고 하시는 분들은 쫌 있는듯;;;
    잘되는 경우도 있지만.. 대부분.ㅠ.ㅠ 잘못 처리 한다는.. ㅎㅎㅎㅎ

    Analystic Function 부분 범위 처리는 너무 어려워요.ㅠ.ㅠ

  3. 4월 02, 2013

    기민용 says:

    분석함수와 유사한 함수들, 하지만 분석함수는 아님 집계함수와 함께 사용되는 KEEP 구문 - 집계함수 MAX(ename) KEEP(D...

    분석함수와 유사한 함수들, 하지만 분석함수는 아님

    • 집계함수와 함께 사용되는 KEEP 구문 - 집계함수
      • MAX(ename) KEEP(DENSE_RANK LAST ORDER BY sal)
      • MAX(sal) 에 해당하는 사원을 출력하는 구문
    • WIDTH_BUCKET 함수 - 일반 함수
      • WIDTH_BUCKET(sal, 3000, 5000, 2)
      • 분석함수인 NTILE 과 유사한 기능을 갖는 일반함수
      • 급여(sal)을 2등급(1, 2)으로 나눔, 실제로는 4등급(0, 1, 2, 3)
      • 0 : 3000 미만
      • 1 : 3000 이상 4000 미만
      • 2 : 4000 이상 5000 미만
      • 3 : 5000 이상
    • ListAgg 함수 - 집계함수
      • ListAgg(ename) WITHIN GROUP(ORDER BY empno)
      • Group By 에서 문자열 합칠 때 사용
    1. 2월 06, 2015

      임상준 says:

      튜닝하다 검색타고 들어왔다 잘 보고 갑니다. 분석함수가 아니었군요ㅋㅋㅋ

      튜닝하다 검색타고 들어왔다 잘 보고 갑니다.
      분석함수가 아니었군요ㅋㅋㅋ

  4. 4월 04, 2013

    이재현 says:

    주관식 : STDDEV 실기 : SUM( ) OVER()을 사용하지않고 중첩( SUM ) 하라.( 셀프조인 )

    주관식 : STDDEV

    실기 : SUM( ) OVER()을 사용하지않고 중첩( SUM ) 하라.( 셀프조인 )

    1. 4월 04, 2013

      기민용 says:

      막연하게 표현하지 말고 좀 구체적으로 좀 알려줘~ 쪽집계로다가 ㅋㅋ

      막연하게 표현하지 말고 좀 구체적으로 좀 알려줘~
      쪽집계로다가 ㅋㅋ