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

6.윈도우 함수(WINDOW FUNCTION)




목차

1. WINDOW FUNCTION 개요
2. 그룹 내 순위 함수
3. 일반 집계 함수
4. 그룹 내 행 순서 함수
5. 그룹 내 비율 함수

1. WINDOW FUNCTION 개요

  • 행과 행간의 관계를 쉽게 정의 하기 위해 만든 함수를 이름
  • 분석 함수(ANALYTIC FUNCTION)이나 순위 함수(RANK FUNCTION)로도 알려짐
  • 기존 집계 함수, 새로이 WINDOW 함수 전용 존재
  • 서브쿼리 사용 가능, 중첩(NEST)해서 사용 불가

1.1. WINDOW FUNCTION 종류

1. 그룹 내 순위(RANK) 관련 함수

  • RANK, DENSE_RANK, ROW_NUMBER
  • ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS 에서 지원

2. 그룹 내 집계(AGGREGATE) 관련 함수

  • SUM ,MAX, MIN, AVG, COUNT
  • ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS 에서 지원
  • SQL Server 의 경우 집계 함수는 뒤에서 설명할 OVER 절의 내의 ORDER BY 지원 하지 않음

3. 그룹 내 행 순서 관련 함수

  • FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수
  • ORACLE 에서만 지원

4. 그룹 내 비율 관련 함수

  • CUME_DIST, PERCENT_RANK - ANSI/ISO SQL 표준과 Oracle DBMS 에서 지원
    [ 정정. RATIO_TO_REPORT - ORACLE 만 지원, PRECENT_RANK, CUME_DIS, NTILE 모두 MS SQL 지원 확인 ]
  • NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server 에서 지원
  • RATIO_TO_REPORT - Oracle 에서만 지원

5. 선형 분석을 포함한 분석관련 함수 - 생략

1.2. WINDOW FUNCTION SYNTAX

WINDOW FUNCTION SYNTAX

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

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

 BETWEEN 미사용 타입
 ROW | RANGE
 UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

2. 그룹 내 순위 함수

  • 순위 함수 요약
    RANK       : 1 2 2 4
    DENSE_RANK : 1 2 2 3
    ROW_NUMBER : 1 2 3 4 
    

가. RANK 함수

  • ORDER BY 를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수
  • 특정 범위(PARTITION) 내에서 혹은 전체 데이터에 대한 순위 구함
  • 동일한 값에 대해서는 동일한 순위를 부여함
* 사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력
* 동일 SALARY 에 대해서 같은 순위 부여
* ORDER BY SAL DESC 와 PARTITION BY JOB ORDER BY SAL DESC 가 충돌 하여 ORDER BY SAL DESC 기준으로 정렬 

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 << 동일 SALARY(명시적으로 ORDER BY 절 추가)
ANALYST   SCOTT            3000          2          1 << 동일 SALARY
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.

* 전체 SALARY 순위 구하는 ALL_RANK 칼럼 제외, 업무별로 SALARY 순서를 구하는 JOB_RANK 만 출력
* 결과적으로 JOB 과 SALARY 별로 정렬

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.


나. DENSE_RANK 함수

  • RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급
  • 특정 범위(PARTITION) 내에서 혹은 전체 데이터에 대한 순위 구함
  • 동일한 값에 대해서는 동일한 순위를 부여함

* 사원데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력 

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  <== RANK(1,2,2,4)와 DENSE_RANK(1,2,2,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.

다. ROW_NUMBER 함수

  • RANK 나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는대 반해,
  • 동일한 값이라도 고유한 순위를 부여함

* 사원데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력 
* 같은 SALARY 에서는 어떤 순서가 정해질지 알수 없다.(Oracle 의 경우 rowid 가 적은 행이 먼저 나옴 )
* DBMS(Oracle rowid) 에 의해 정렬을 원지 않는다면, 명시적으로 ORDER BY 추가 할 것 

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 << RANK(1,2,2,4) 와 ROW_NUMBER(1,2,3,4) 차이점
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 << RANK(10,10) 와 ROW_NUMBER(10,11) 차이점
CLERK     ADAMS            1100         12         12
CLERK     JAMES             950         13         13
CLERK     SMITH             800         14         14

14 rows selected.

3. 일반 집계 함수

가. SUM 함수

  • SUM 함수를 이용해 파티션별로 윈도우의 합 구하기
* 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합을 구함 
* PARTITION BY MGR 구문을 통해 매니저별로 데이터를 파티션화 

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.

* OVER 절 내의 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고
  이전 SALARY 데이터까지의 누적값을 출력 ( <<< ORDER BY SAL 추가 )
* SQL Server 의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.( !!!! ) 
* RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정 
* 동일 순위의 모든 값에 대한 총합계로 누적됨 ( 아래 << 950 + 1250 + 1250 = 3450  )

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 << 950 + 1250 + 1250 = 3450 
      7698 MARTIN           1250       3450 << 950 + 1250 + 1250 = 3450 
      7698 TURNER           1500       4950 << 950 + 1250 + 1250 + 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 절을 지원하지 않는다.( !!!! ) 
==> MS SQL 2012 에서 수행 결과 에러 없이, ORDER BY 되어서 결과가 정상적으로 출력됨 
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
------ ---------- --------------------------------------- ---------------------------------------
NULL   KING       5000.00                                 5000.00
7566   FORD       3000.00                                 6000.00
7566   SCOTT      3000.00                                 6000.00
7698   JAMES      950.00                                  950.00
7698   MARTIN     1250.00                                 3450.00
7698   WARD       1250.00                                 3450.00
7698   TURNER     1500.00                                 4950.00
7698   ALLEN      1600.00                                 6550.00
7782   MILLER     1300.00                                 1300.00
7788   ADAMS      1100.00                                 1100.00
7839   CLARK      2450.00                                 2450.00
7839   BLAKE      2850.00                                 5300.00
7839   JONES      2975.00                                 8275.00
7902   SMITH      800.00                                  800.00

(14개 행이 영향을 받음)

나. MAX 함수

  • MAX 함수를 이용해 파티션별로 윈도우의 최대값 구하기

* 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값 구함

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.

* 추가로, INLINE VIEW 를 이용해 파티션별로 최대값을 가진 행만 추출

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 << 최대값 가지는 ROW 가 2건 모두 출력
      7566 SCOTT            3000 << 
      7698 ALLEN            1600
      7782 MILLER           1300
      7788 ADAMS            1100
      7839 JONES            2975
      7902 SMITH             800
           KING             5000

8 rows selected.

다. MIN 함수

  • MAX 함수를 이용해 파티션별로 윈도우의 최소값 구하기

* 사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자 기준으로 정렬하고, SALARY 최소값을 구함

SQL> SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER ( PARTITION BY MGR ORDER BY HIREDATE ) AS MIN_SAL
     FROM  EMP ;

       MGR ENAME      HIREDATE               SAL    MIN_SAL
---------- ---------- --------------- ---------- ----------
      7566 FORD       03-DEC-81             3000       3000
      7566 SCOTT      13-JUL-87             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      13-JUL-87             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. 

라. AVG 함수

  • AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구함

* EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY 구함
* 평균 SALARY 의 제한 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 지원만을 대상으로 함 
* ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행을 기준으로 파티션 내에서 앞의 한건, 현재 행, 뒤의 한 건을 범위로 지정 
* ROWS 는 현재 행의 앞뒤 ROWS 를 말함

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      13-JUL-87             3000       3000
---------------------------------------------------------------
      7698 ALLEN      20-FEB-81             1600       1425 << ( 앞 로우 없음 + 1600 + 1250 )/2 = 1425
      7698 WARD       22-FEB-81             1250       1450 
      7698 TURNER     08-SEP-81             1500       1333 << ( 1250 + 1500 + 1250 )/3 = 1333
      7698 MARTIN     28-SEP-81             1250       1233
      7698 JAMES      03-DEC-81              950       1100 << ( 1250 + 950 + 뒤 로우 없음 )/2 = 1100
---------------------------------------------------------------
      7782 MILLER     23-JAN-82             1300       1300
---------------------------------------------------------------
      7788 ADAMS      13-JUL-87             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
---------------------------------------------------------------
14 rows selected.

마. COUNT 함수

  • COUNT 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구함

* 사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받은 인원수를 출력
* RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING  : 현재 행(Row)의 급여값을 기준으로 급여가 -50 에서 +150 의 범위 내에 포함된 모든 행이 대상 
* RANGE 는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시

SQL>     SELECT ENAME, SAL,
                COUNT(*) OVER ( ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING ) AS SIM_CNT,
                TO_CHAR(SAL-50)||'->'||TO_CHAR(SAL+150) AS "RANGE" 
         FROM EMP ;

ENAME             SAL    SIM_CNT RANGE
---------- ---------- ---------- ------------------
SMITH             800          2 750->950
JAMES             950          2 900->1100
ADAMS            1100          3 1050->1250 << 1100, 1250, 1250  ==> 3 
WARD             1250          3 1200->1400
MARTIN           1250          3 1200->1400
MILLER           1300          3 1250->1450
TURNER           1500          2 1450->1650
ALLEN            1600          1 1550->1750
CLARK            2450          1 2400->2600
BLAKE            2850          4 2800->3000
JONES            2975          3 2925->3125
SCOTT            3000          3 2950->3150
FORD             3000          3 2950->3150
KING             5000          1 4950->5150

14 rows selected.

4. 그룹 내 행 순서 함수

가. FIRST_VALUE 함수

  • FIRST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.
  • SQL Server 에서는 지원하지 않는 함수 ==> MS SQL 2012 지원됨 ( 하단 참조 )
  • MIN 을 활용해도 같은 결과 얻음

* 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력 
* ROWS UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정 
* FIRST_VALUE 는 다른 함수와 달리 공동 등수를 인정하지 않고 처음 나온 행만을 처리함
* DEPTNO = 20 파티션의 최고 연봉은 3000 이고, SCOTT 과 FORD 가 대상  ==> 이중 하나인 SCOTT 만 처리됨
* 명시적인 정렬을 위해서 INLINE VIEW 나, OVER () 내의 ORDER BY 절에 칼럼 추가 할 것 

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 << DEPTNO = 20 
        20 FORD             3000 SCOTT << DEPTNO = 20 
        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.

* MSSQL 2012 [ 이젠 지원됨 확인 ] 

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.00                                 KING
10     CLARK      2450.00                                 KING
10     MILLER     1300.00                                 KING
20     FORD       3000.00                                 FORD
20     SCOTT      3000.00                                 FORD
20     JONES      2975.00                                 FORD
20     ADAMS      1100.00                                 FORD
20     SMITH      800.00                                  FORD
30     BLAKE      2850.00                                 BLAKE
30     ALLEN      1600.00                                 BLAKE
30     TURNER     1500.00                                 BLAKE
30     WARD       1250.00                                 BLAKE
30     MARTIN     1250.00                                 BLAKE
30     JAMES      950.00                                  BLAKE

(14개 행이 영향을 받음)


* FIRST_VALUE 를 처리하기 위해 ORDER BY 정렬 조건을 추가 

SQL> SELECT DEPTNO, ENAME, SAL,
            FIRST_VALUE(ENAME) OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC  << 임의의 SCOTT 이 아닌 FORD 를 추출 하기 위해 ASC( F > S ) 정렬 필요함
            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 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.

나. LAST_VALUE 함수

  • LAST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.
  • SQL Server 에서는 지원하지 않는 함수 ( ==> 정정 지원됨 )
  • MAX 을 활용해도 같은 결과 얻음

* 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력
* ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  : 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정 
* 공동 등수를 인정하기 않고 가장 나중에 나온 행만 처리
* 명시적인 정렬을 위해서 INLINDE VIEW 나 OVER() 내의 ORDER BY 조건 컬럼 명시 

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.

* MS SQL 2012 ( 지원됨 )

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     MILLER     1300.00                                 MILLER
10     CLARK      2450.00                                 MILLER
10     KING       5000.00                                 MILLER
20     SMITH      800.00                                  SMITH
20     ADAMS      1100.00                                 SMITH
20     JONES      2975.00                                 SMITH
20     SCOTT      3000.00                                 SMITH
20     FORD       3000.00                                 SMITH
30     JAMES      950.00                                  JAMES
30     WARD       1250.00                                 JAMES
30     MARTIN     1250.00                                 JAMES
30     TURNER     1500.00                                 JAMES
30     ALLEN      1600.00                                 JAMES
30     BLAKE      2850.00                                 JAMES

(14개 행이 영향을 받음)

다. LAG 함수

  • 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져 올 수 있다.
  • SQL Server 에서는 지원하지 않는 함수 ( ==> MS SQL 2012 지원됨 )

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

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

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

* LAG(인수1,인수2,인수3 )
* 인수1 - 입력 칼럼 
* 인수2 - 몇 번째 앞의 행을 가져올지 결정(DEFAULT 1)
* 인수3 - 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력 

* MS SQL 2012

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

ENAME      HIREDATE   JOB       SAL                                     PREV_SAL
---------- ---------- --------- --------------------------------------- ---------------------------------------
ALLEN      1981-02-20 SALESMAN  1600.00                                 NULL
WARD       1981-02-22 SALESMAN  1250.00                                 1600.00
TURNER     1981-09-08 SALESMAN  1500.00                                 1250.00
MARTIN     1981-09-28 SALESMAN  1250.00                                 1500.00

(4개 행이 영향을 받음)


* 2 행 앞의 SALARY 가져오기, 가져올 값이 없으면 0으로 처리 

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

SQL> 






라. LEAD 함수

  • 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져 올 수 있다.
  • SQL Server 에서는 지원하지 않는 함수 ( ==> MS SQL 2012 지원됨 )


SQL> SELECT ENAME, HIREDATE,
            LEAD(HIREDATE) 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       13-JUL-87
SCOTT      13-JUL-87       13-JUL-87
ADAMS      13-JUL-87

14 rows selected.

* LEAD(인수1,인수2,인수3 )
* 인수1 - 입력 칼럼 
* 인수2 - 몇 번째 후의 행을 가져올지 결정(DEFAULT 1)
* 인수3 - 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력 

* MS SQL 2012 

SELECT ENAME, HIREDATE,
            LEAD(HIREDATE) OVER ( ORDER BY HIREDATE ) AS "NEXTHIRED"
FROM EMP ;
ENAME      HIREDATE   NEXTHIRED
---------- ---------- ----------
SMITH      1980-12-17 1981-02-20
ALLEN      1981-02-20 1981-02-22
WARD       1981-02-22 1981-04-02
JONES      1981-04-02 1981-05-01
BLAKE      1981-05-01 1981-06-09
CLARK      1981-06-09 1981-09-08
TURNER     1981-09-08 1981-09-28
MARTIN     1981-09-28 1981-11-17
KING       1981-11-17 1981-12-03
JAMES      1981-12-03 1981-12-03
FORD       1981-12-03 1982-01-23
MILLER     1982-01-23 1987-07-13
ADAMS      1987-07-13 1987-07-13
SCOTT      1987-07-13 NULL

(14개 행이 영향을 받음)


5. 그룹 내 비율 함수

* 칼럼 값에 대한 백분률 -> RATIO_TO_REPORT
* 행의 순서에 대한 (0부터 1사이 값) 백분률 -> PERCENT_RANK 
* 1/(파티션)전체 건수로 표현하는 백분률 -> CUME_DIST 

가. 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,
            SUM(SAL) Over ( Order by JOB  ) AS "Total"
     FROM EMP
     WHERE JOB='SALESMAN' ; 

ENAME             SAL        R_R      Total
---------- ---------- ---------- ----------
ALLEN            1600        .29       5600 << ( 1600/5600 ) 
WARD             1250        .22       5600 << ( 1250/5600 ) 
MARTIN           1250        .22       5600 << ( 1250/5600 ) 
TURNER           1500        .27       5600 << ( 1500/5600 ) 

나. PERCENT_RANK 함수

  • 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것으로 1로하여
  • 값이 아닌 행의 순서별 백분율을 구함
  • 0 < 백분율 < 1
  • SQL Server 에서는 지원하지 않는 함수 ( ==> MS SQL 2012 지원됨 )
* 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번재 위치쯤에 있는지 0과 1사이의 값으로 출력 
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.

* MS SQL 2012 
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.00                                 0
10     CLARK      2450.00                                 0.5
10     MILLER     1300.00                                 1
20     FORD       3000.00                                 0
20     SCOTT      3000.00                                 0
20     JONES      2975.00                                 0.5
20     ADAMS      1100.00                                 0.75
20     SMITH      800.00                                  1
30     BLAKE      2850.00                                 0
30     ALLEN      1600.00                                 0.2
30     TURNER     1500.00                                 0.4
30     WARD       1250.00                                 0.6
30     MARTIN     1250.00                                 0.6
30     JAMES      950.00                                  1

(14개 행이 영향을 받음)

다. CUME_DIST 함수

  • 파티션별 윈도우에서 전체건수에 현재 행보다 작거나 같은 건수에 대한 누적백분율 구함
  • 0 < 백분율 < 1
  • SQL Server 에서는 지원하지 않는 함수 ( ==> MS SQL 2012 지원됨 )

* 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력

SQL> SELECT DEPTNO, ENAME, SAL,
           CUME_DIST() OVER ( PARTITION BY DEPTNO ORDER BY SAL ) AS A
    FROM   EMP ;


    DEPTNO ENAME             SAL          A
---------- ---------- ---------- ----------
        10 MILLER           1300 .333333333
        10 CLARK            2450 .666666667
        10 KING             5000          1
        20 SMITH             800         .2
        20 ADAMS            1100         .4
        20 JONES            2975         .6
        20 SCOTT            3000          1
        20 FORD             3000          1
        30 JAMES             950 .166666667
        30 MARTIN           1250         .5
        30 WARD             1250         .5
        30 TURNER           1500 .666666667
        30 ALLEN            1600 .833333333
        30 BLAKE            2850          1

14 rows selected.

* MS SQL 2012
SELECT DEPTNO, ENAME, SAL,
           CUME_DIST() OVER ( PARTITION BY DEPTNO ORDER BY SAL ) AS A
FROM   EMP ;

DEPTNO ENAME      SAL                                     A
------ ---------- --------------------------------------- ----------------------
10     MILLER     1300.00                                 0.333333333333333
10     CLARK      2450.00                                 0.666666666666667
10     KING       5000.00                                 1
20     SMITH      800.00                                  0.2
20     ADAMS      1100.00                                 0.4
20     JONES      2975.00                                 0.6
20     SCOTT      3000.00                                 1
20     FORD       3000.00                                 1
30     JAMES      950.00                                  0.166666666666667
30     WARD       1250.00                                 0.5
30     MARTIN     1250.00                                 0.5
30     TURNER     1500.00                                 0.666666666666667
30     ALLEN      1600.00                                 0.833333333333333
30     BLAKE      2850.00                                 1

(14개 행이 영향을 받음)


라. NTILE 함수

  • 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과 구함
SQL> 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. 10월 26, 2013

    장태길 says:

    http://m.blog.naver.com/PostView.nhn?blogId=minis24&logNo=80100582246&is...