증감률(성장률) 계산하기 - LAG OVER

참고 ) https://yahwang.github.io/posts/74

SELECT A.MONTH
       , A.TOTAL_SALES
       , CASE WHEN A.GROWTH_RATE <> 0 THEN ROUND(A.GROWTH_RATE, 2) || '%' 
              ELSE '-'
         END AS GR_RATE
  FROM
  (
  	WITH MONTH_SUMMARY AS 
    (
        SELECT EXTRACT(MONTH FROM DT) AS MONTH, SUM(SALES) AS TOTAL_SALES
        FROM SAMPLE
        GROUP BY EXTRACT(MONTH FROM DT) 
	)
	SELECT MONTH, TOTAL_SALES, 
		   100 * (TOTAL_SALES - LAG(TOTAL_SALES,1) OVER (ORDER BY MONTH)) / LAG(TOTAL_SALES,1) OVER (ORDER BY MONTH)  AS GROWTH_RATE
	FROM MONTH_SUMMARY
  ) A 
;

-- 1. 월별 합계 구하기
SELECT EXTRACT(MONTH FROM DT) AS MONTH, SUM(SALES) AS TOTAL_SALES
FROM SAMPLE
GROUP BY EXTRACT(MONTH FROM DT);

--2. 전월 데이터를 현재 월의 ROW에 연결하기
--LAG FUNCTION을 사용하여 이전 월의 데이터를 현재 월의 데이터 ROW로 가져온다.

WITH MONTH_SUMMARY AS (
    SELECT EXTRACT(MONTH FROM DT) AS MONTH, SUM(SALES) AS TOTAL_SALES
    FROM SAMPLE
    GROUP BY EXTRACT(MONTH FROM DT) 
)
SELECT MONTH, TOTAL_SALES,
       LAG(TOTAL_SALES,1,'0') OVER (ORDER BY MONTH) AS PREV_SALES
FROM MONTH_SUMMARY;

--3. 최종 : 증감률(성장률) 계산하기
--PREV_SALES와 TOTAL_SALES 데이터를 활용해 계산만 하면 된다.

WITH MONTH_SUMMARY AS (
    SELECT EXTRACT(MONTH FROM DT) AS MONTH, SUM(SALES) AS TOTAL_SALES
	FROM SAMPLE
	GROUP BY EXTRACT(MONTH FROM DT) 
)
SELECT MONTH, TOTAL_SALES, 
	   100 * (TOTAL_SALES - LAG(TOTAL_SALES,1) OVER (ORDER BY MONTH)) / LAG(TOTAL_SALES,1) OVER (ORDER BY MONTH) || '%' AS GROWTH_RATE
FROM MONTH_SUMMARY;

CREATE TABLE SAMPLE
(
	DT	DATE,
	SALES VARCHAR(4)
)

SELECT * FROM SAMPLE;

INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190101', '50');
INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190102', '70');
INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190103', '30');

INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190201', '60');
INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190202', '80');
INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190203', '40');

INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190301', '100');
INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190302', '120');
INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190303', '80');

INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190401', '130');
INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190402', '150');
INSERT INTO ORA_USER.SAMPLE (DT, SALES) VALUES('20190403', '135');