[Oracle] 오라클 고급 JOIN 테크닉 - 누적 매출 구하기, 오라클 분석함수

누적 매출 구하기

SELECT T1.지점, T1.판매월, MAX(T1.매출) 매출, SUM(T2.매출) 누적매출

  FROM 월별지점매출 T1, 월별지점매출 T2

WHERE T1.지점 = T2.지점

    AND T1.판매월 >= T2.판매월    -- 범위 연산자 JOIN

GROUP BY T1.지점, T1.판매월

ORDER BY T1.지점, T1.판매월;

▼

SELECT 지점, 판매월, 매출,

 SUM(매출) OVER (PARTITION BY 지점 ORDER BY 판매월

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적매출

  FROM 월별지점매출;

ROWS	BETWEEN	UNBOUNDED PRECEDING	AND	CURRENT ROW
		N PRECEDING (ex : 10 PRECEDING)		UNBOUNDED FOLLOWING
RANGE		CURRENT ROW		N FOLLOWING (ex : 10 FOLLOWING)

- ROWS : 행 기준

- RANGE : Value 값 기준

- UNBOUNDED PRECEDING : 맨 앞부터

- N PRECEDING : N개 앞부터

- UNBOUNDED FOLLOWING : 맨 뒤부터

- N FOLLOWING : N개 뒤부터

 뭔가 아리송송!? Let's 실습!!

CREATE TABLE MINA.MONBRANCHSALES AS 

     SELECT (MOD(ROWNUM,4)+1)*10 BRANCH,

                 MOD(ROWNUM,12) +1 MONTH,

                 ROUND(DBMS_RANDOM.VALUE(100, 1000),0) SALES

        FROM DUAL

  CONNECT BY LEVEL <= 100 ;

- ROWS vs. RANGE

SELECT BRANCH, MON, SALES,

            SUM(SALES) OVER (PARTITION BY BRANCH ORDER BY MON

                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "ROWS_누적",

            SUM(SALES) OVER (PARTITION BY BRANCH ORDER BY MON

                RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "RANGE_누적"   

  FROM MINA.MONBRANCHSALES;

* RANGE_누적을 보면 ORDER BY 컬럼인 MON 컬럼값을 기준으로 나보다 작거나 같은 행의 매출을 모두 더한 것을 알 수 있음

- PRECEDING vs. FOLLOWING

SELECT BRANCH, MON, SALES,

            SUM(SALES) OVER (PARTITION BY BRANCH ORDER BY MON

                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "앞부터 누적",

            SUM(SALES) OVER (PARTITION BY BRANCH ORDER BY MON

                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) "뒤부터 누적",

            SUM(SALES) OVER (PARTITION BY BRANCH ORDER BY MON

                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "지점별총합"   

  FROM MINA.MONBRANCHSALES;

- N PRECEDING vs. N FOLLOWING

* N PRECEDING은 CURRENT ROW가 생략 가능, N FOLLOWING은 생략했더니 오류 남

SELECT BRANCH, MON, SALES,

            SUM(SALES) OVER (PARTITION BY BRANCH ORDER BY MON ROWS 1 PRECEDING) ROWS_1P,

            SUM(SALES) OVER (PARTITION BY BRANCH ORDER BY MON RANGE 1 PRECEDING) RANGE_1P,

            SUM(SALES) OVER (PARTITION BY BRANCH ORDER BY MON 

                ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) ROWS_1F,

            SUM(SALES) OVER (PARTITION BY BRANCH ORDER BY MON 

                RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) RANGE_1F

  FROM MINA.MONBRANCHSALES;