Dev. Database/Oracle
증감률(성장률) 계산하기 - LAG OVER
devock
2019. 7. 14. 15:59
참고 ) 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');