over() 함수_partition

 - data table 

CREATE TABLE REPORTTEST (
 
 CUST_CODE      CHAR(3) NOT NULL,
 
 SALE_DATE      CHAR(8),
 
 SALE_AMT       NUMBER(8)
 
 );
 
INSERT INTO REPORTTEST VALUES ('100','20020301',100);
 
INSERT INTO REPORTTEST VALUES ('100','20020302',130);
 
INSERT INTO REPORTTEST VALUES ('100','20020303',1500);
 
INSERT INTO REPORTTEST VALUES ('100','20020304',900);
 
INSERT INTO REPORTTEST VALUES ('100','20020304',300);
 
INSERT INTO REPORTTEST VALUES ('100','20020305',2300);
 
INSERT INTO REPORTTEST VALUES ('200','20020301',500);
 
INSERT INTO REPORTTEST VALUES ('200','20020302',250);
 
INSERT INTO REPORTTEST VALUES ('200','20020303',100);
 
INSERT INTO REPORTTEST VALUES ('200','20020304',1500);
 
INSERT INTO REPORTTEST VALUES ('200','20020305',3500);
 
INSERT INTO REPORTTEST VALUES ('200','20020305',200);
 

OVER() :  OVER 절은 쿼리 결과 집합 내의 창 또는 사용자 지정 행 집합을 정의 

PARTITION BY : 쿼리 집합을 파티션으로 분할 

ex ) PARTITION BY CUST_CODE ---> CUST_CODE대로 분할

- sql :  ROWS(물리적인 위치) / RANGE(논리적인 위치 :  이전 및 다음 행은 ORDER BY 절의 순서에 따라 정의), 

UNBOUNDED PRECEDING, CURRENT ROW/ INTERVAL

SELECT  CUST_CODE, SALE_DATE, SALE_AMT,

    SUM(SALE_AMT) OVER (PARTITION BY CUST_CODE ORDER BY SALE_DATE
        ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ACC_AMT1,

    SUM(SALE_AMT) OVER (PARTITION BY CUST_CODE ORDER BY SALE_DATE
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ACC_AMT2,

    SUM(SALE_AMT) OVER (PARTITION BY CUST_CODE ORDER BY TO_DATE(SALE_DATE,'YYYYMMDD')
        RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL '0' DAY FOLLOWING) ACC_AMT3,

    SUM(SALE_AMT) OVER (PARTITION BY CUST_CODE ORDER BY SALE_DATE) ACC_AMT4

FROM  REPORTTEST;
 

result

CUS SALE_DAT  SALE_AMT  ACC_AMT1  ACC_AMT2  ACC_AMT3  ACC_AMT4

--- -------- ---------- ---------- ---------- ---------- ----------

100 20020301        100        100        100        100        100

100 20020302        130        230        230        230        230

100 20020303       1500       1730       1730       1730       1730

100 20020304        900       2630       2930       2930       2930

100 20020304        300       2930       2930       2930       2930

100 20020305       2300       5230       5230       5230       5230

200 20020301        500        500        500        500        500

200 20020302        250        750        750        750        750

200 20020303        100        850        850        850        850

200 20020304       1500       2350       2350       2350       2350

200 20020305       3500       5850       6050       6050       6050

200 20020305        200       6050       6050       6050       6050

- sql:  PRECEDING (UNBOUNDED PRECEDING과 차이점)

SELECT  CUST_CODE, SALE_DATE, SALE_AMT,
 
        SUM(SALE_AMT) OVER (PARTITION BY CUST_CODE ORDER BY SALE_DATE
            ROWS  BETWEEN 1 PRECEDING AND CURRENT ROW) ACC_AMT1,
 
        SUM(SALE_AMT) OVER (PARTITION BY CUST_CODE ORDER BY TO_DATE(SALE_DATE,'YYYYMMDD')
            RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) ACC_AMT2,
 
        SUM(SALE_AMT) OVER (PARTITION BY CUST_CODE ORDER BY TO_DATE(SALE_DATE,'YYYYMMDD') 
            RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW) ACC_AMT3
 
FROM  REPORTTEST;
 

result

CUS SALE_DAT   SALE_AMT   ACC_AMT1   ACC_AMT2   ACC_AMT3

--- -------- ---------- ---------- ---------- ----------

100  20020301        100        100        100        100

100  20020302        130        230        230        230

100  20020303       1500       1630       1630       1630

100  20020304        900       2400       2700       2700

100  20020304        300       1200       2700       2700

100  20020305       2300       2600       3500       3500

200  20020301        500        500        500        500

200  20020302        250        750        750        750

200  20020303        100        350        350        350

200  20020304       1500       1600       1600       1600

200  20020305       3500       5000       5200       5200

200  20020305        200       3700       5200       5200

- sql :  FOLLOWING

SELECT CUST_CODE, SALE_DATE, SALE_AMT,
 
        SUM(SALE_AMT) OVER (PARTITION BY CUST_CODE ORDER BY SALE_DATE
                ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ACC_AMT1,
 
        SUM(SALE_AMT) OVER (PARTITION BY CUST_CODE ORDER BY TO_DATE(SALE_DATE,'YYYYMMDD')
                RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ACC_AMT2,
 
        SUM(SALE_AMT) OVER (PARTITION BY CUST_CODE ORDER BY TO_DATE(SALE_DATE,'YYYYMMDD')
                RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING) ACC_AMT3
 
FROM  REPORTTEST;
 

result

CUS SALE_DAT   SALE_AMT   ACC_AMT1   ACC_AMT2   ACC_AMT3

--- -------- ---------- ---------- ---------- ----------

100  20020301        100        230        230        230

100  20020302        130       1730       1730       1730

100  20020303       1500       2530       2830       2830

100  20020304        900       2700       5000       5000

100  20020304        300       3500       5000       5000

100  20020305       2300       2600       3500       3500

200  20020301        500        750        750        750

200  20020302        250        850        850        850

200  20020303        100       1850       1850       1850

200  20020304       1500       5100       5300       5300

200  20020305       3500       5200       5200       5200

200  20020305        200       3700       5200       5200

https://msdn.microsoft.com/ko-kr/library/ms189461(v=sql.120).aspx