ORACLE WINDOWING FUNCTION , RATIO_TO_REPORT

Windowing 함수 ?

기존에 있던 모든 그룹 Function들에 대해서

Moving & Cumulative Processing을 지원하는 함수이다

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

* 사원들의 급여와 같은 매니저를 두고 있는 사원들의 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건 모두 출력 ,mgr로 묶어서 출력했지만 순위를 매길수없는 동일값은 같이 출력

      7566 SCOTT            3000 << 

      7698 ALLEN            1600

      7782 MILLER           1300

      7788 ADAMS            1100

      7839 JONES            2975

      7902 SMITH             800

           KING             5000

SELECT MGR, ENAME, SAL, MAX(SAL) OVER  ( PARTITION BY MGR order by sal ) AS MGR_MAX

     FROM   EMP ;

order by를 안하면 sort가 안되어서 그룹내 모든애들을 비교해버린다.

order by 하면 현재 row기준으로 검사를 한다

sELECT MGR, ENAME, HIREDATE, SAL,

            ROUND( AVG(SAL) OVER ( PARTITION BY MGR ORDER BY HIREDATE ROWS  BETWEEN unbounded PRECEDING AND unbounded FOLLOWING)) AS MGR_AVG

     FROM EMP ;

BETWEEN unbounded PRECEDING AND unbounded FOLLOWING 기준 행 수치를 안적어주면 그룹내 모든애들을 기준앞뒤를

계산해버린다

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

over - 쿼리 result set을 이용해 동작하는 함수

patition by - over( ) 함수 안 partition by을 쓰면 지정값을 분할해주는 역할

order by - partition 내에서 DATA가 어떤 값을 기준으로 정렬될 것인가를 지정(오름,내림차순)

NULLS FIRST | NULLS LAST

NULL이 포함된 ROW가 순서상 제일 앞에 위치할것인지 제일뒤에 위치할 것인지 지정

BETWEEN ... AND -자료의 범위를 결정

UNBOUNDED PRECEDING - 지정된값 이전의 모든 ROW를 포함

UNBOUNDED FOLLOWING - 지정된 값 이후의 모든 ROW를 포함

CURRENT ROW - 현재 ROW를 시작 값 또는 마지막값으로 이용할때 사용함`

INTERVAL - 수행되는 쿼리문을(DATE타입) 전 후를 지정할 수 있다.

EX)RANGE INTERVAL '2' DAY PRECEDING - 2일 전까지의 범위

[따라하기 1-2]

SELECT SALE_DATE , SALE_SITE , SUM(SALE_AMT),

      AVG(SUM(SALE_AMT)) OVER -- 한거를 합계내서 3으로 나눔 (2일전+현재일이니 3으로 나누어야함)

                        (PARTITION BY SALE_SITE --SITE 별로 그룹핑을 함

                          ORDER BY SALE_DATE -- 날짜를 오름차순

                          RANGE INTERVAL '2' DAY PRECEDING ) AS "3일 평균매출액" -- 현재부터 2일전까지 출력

FROM SALE_HIST

GROUP BY SALE_DATE , SALE_SITE;

[따라하기 1-3]

SELECT EMP_ID,SALARY,

      SUM(SALARY) OVER (ORDER BY EMP_iD ROWS 3 PRECEDING) AS "4명급여합계", -- 현재ROW의 앞 3명의 합계

      COUNT(SALARY) OVER (ORDER BY  EMP_ID ROWS 3 PRECEDING) AS "카운트", -- 자기 포함 앞의 카운트

      CEIL(AVG(SALARY) OVER (ORDER BY EMP_ID ROWS 3 PRECEDING)) AS "4명평균" -- 4명의 평균

FROM TEMP;

[따라하기 1-4]

SELECT SALE_DATE , SALE_ITEM , SALE_SITE , SALE_AMT,

        

        FIRST_VALUE(SALE_AMT) OVER -- 마지막 그룹핑한 애들의 첫번째 값을 뽑아옴

                    (PARTITION BY SALE_DATE , SALE_ITEM -- 날짜와 아이템으로 그룹핑한다

                    ORDER BY SALE_AMT-- 그것을 오름차순으로 정렬

                    ROWS BETWEEN UNBOUNDED PRECEDING -- 그룹핑한 애들의 전 후 범위를 지정

                          AND     UNBOUNDED FOLLOWING) AS "첫번째금액",

                          

                                  LAST_VALUE(SALE_AMT) OVER -- 마지막 그룹핑한 애들의 마지막 값을 뽑아옴

                    (PARTITION BY SALE_DATE , SALE_ITEM -- 날짜와 아이템으로 그룹핑한다

                    ORDER BY SALE_AMT -- 그것을 오름차순으로 정렬

                    ROWS BETWEEN UNBOUNDED PRECEDING -- 그룹핑한 애들의 전 후 범위를 지정

                          AND     UNBOUNDED FOLLOWING) AS "마지막금액"

FROM SALE_HIST;

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

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 )  -- 자신의 sal 이 5600중 얼마나차지하는지 나옴 이 row는 29% 차지

WARD             1250        .22       5600 << ( 1250/5600 ) 

MARTIN           1250        .22       5600 << ( 1250/5600 ) 

TURNER           1500        .27       5600 << ( 1500/5600 )