데이터베이스 질문과 답변
by 푸릉이 [2018.07.26 09:15:09]
WITH T AS
(
SELECT '20170119' ILJA, '1' EVENT, '10' CNT FROM DUAL UNION ALL
SELECT '20170123' ILJA, '1' EVENT, '9' CNT FROM DUAL UNION ALL
SELECT '20180523' ILJA, '0' EVENT, '8' CNT FROM DUAL UNION ALL
SELECT '20180717' ILJA, '0' EVENT, '5' CNT FROM DUAL UNION ALL
SELECT '20170927' ILJA, '1' EVENT, '15' CNT FROM DUAL UNION ALL
SELECT '20170905' ILJA, '0' EVENT, '7' CNT FROM DUAL UNION ALL
SELECT '20170815' ILJA, '0' EVENT, '9' CNT FROM DUAL UNION ALL
SELECT '20171022' ILJA, '0' EVENT, '2' CNT FROM DUAL UNION ALL
SELECT '20170726' ILJA, '1' EVENT, '12' CNT FROM DUAL UNION ALL
SELECT '20170929' ILJA, '0' EVENT, '9' CNT FROM DUAL UNION ALL
.............................
SELECT '20171031' ILJA, '2' EVENT, '11' CNT FROM DUAL
)
SELECT * FROM T
이런 데이터가 있는데 만약 현재 7월달에 조회하면
전년도 1월부터 달별로 EVENT, CNT를 합산하고
이번년도 1월부터 현재달까지 합산하고 싶습니다.
컬럼은
BEFORE_MON BEF_EVENT(=SUM(EVENT)의미) BEF_CNT(=SUM(CNT)의미) THIS_MON THIS_EVENT THIS_CNT
201701 XXX XX 201801 XXX XX
201702 XXX XXX 201802 XXX XX
............
201707 XXX XXX 201807 XX XXX
이런식으로 구하고 싶은데
현재 두번 SELECT 해서 억지로 하긴했는데
좀더 심플하게 구하는 방법이 있을까요?
WITH T AS (
SELECT '20170119' ILJA, '1' EVENT, '10' CNT FROM DUAL UNION ALL
SELECT '20170123' ILJA, '1' EVENT, '9' CNT FROM DUAL UNION ALL
SELECT '20180523' ILJA, '0' EVENT, '8' CNT FROM DUAL UNION ALL
SELECT '20180717' ILJA, '0' EVENT, '5' CNT FROM DUAL UNION ALL
SELECT '20170927' ILJA, '1' EVENT, '15' CNT FROM DUAL UNION ALL
SELECT '20170905' ILJA, '0' EVENT, '7' CNT FROM DUAL UNION ALL
SELECT '20170815' ILJA, '0' EVENT, '9' CNT FROM DUAL UNION ALL
SELECT '20171022' ILJA, '0' EVENT, '2' CNT FROM DUAL UNION ALL
SELECT '20170726' ILJA, '1' EVENT, '12' CNT FROM DUAL UNION ALL
SELECT '20170929' ILJA, '0' EVENT, '9' CNT FROM DUAL UNION ALL
SELECT '20171031' ILJA, '2' EVENT, '11' CNT FROM DUAL
) , YYYYMM_T AS (
SELECT BB.YYYY || AA.MM AS YYYYMM
, AA.MM AS MM
, BB.LV AS GRP
FROM ( SELECT LPAD(LEVEL,2,'0') AS MM FROM DUAL CONNECT BY LEVEL <= 12 ) AA
, ( SELECT TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE , -12 * ( LEVEL - 1 ) ),'YEAR'),'YYYY' ) YYYY , LEVEL LV FROM DUAL CONNECT BY LEVEL <= 2 ) BB
WHERE MM <= SUBSTR(TO_CHAR(SYSDATE,'YYYYMM'),5,2)
)
SELECT MIN(CASE WHEN B.GRP = 2 THEN B.YYYYMM END) AS BEFORE_MON
, MIN(CASE WHEN B.GRP = 2 THEN A.SUM_EVT END) AS BEF_EVENT
, MIN(CASE WHEN B.GRP = 2 THEN A.SUM_CNT END) AS BEF_CNT
, MIN(CASE WHEN B.GRP = 1 THEN B.YYYYMM END) AS THIS_MON
, MIN(CASE WHEN B.GRP = 1 THEN A.SUM_EVT END) AS THIS_EVENT
, MIN(CASE WHEN B.GRP = 1 THEN A.SUM_CNT END) AS THIS_CNT
FROM ( SELECT SUBSTR(ILJA,1,6) YYYYMM
, SUM(EVENT) SUM_EVT
, SUM(CNT) SUM_CNT
FROM T
WHERE ILJA BETWEEN TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE , -12),'YEAR'),'YYYYMM' ) AND TO_CHAR(SYSDATE,'YYYYMM' )
GROUP BY SUBSTR(ILJA,1,6)
) A
, YYYYMM_T B
WHERE A.YYYYMM(+) = B.YYYYMM
GROUP BY B.MM
ORDER BY B.MM
응용할때가 많은 쿼리네요 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.