전월대비 증감액 조회

1. 복제를 이용한 방법

WITH SALE
AS  
(   SELECT 0   AS MON, 322794590   AS AMT  FROM DUAL   UNION ALL
    SELECT 1   AS MON, 312045670   AS AMT  FROM DUAL   UNION ALL
    SELECT 2   AS MON, 381020000   AS AMT  FROM DUAL   UNION ALL
    SELECT 3   AS MON, 412080000   AS AMT  FROM DUAL   UNION ALL
    SELECT 4   AS MON, 392010000   AS AMT  FROM DUAL   UNION ALL
    SELECT 5   AS MON, 428000020   AS AMT  FROM DUAL   UNION ALL
    SELECT 6   AS MON, 415020000   AS AMT  FROM DUAL   UNION ALL
    SELECT 7   AS MON, 394092000   AS AMT  FROM DUAL   UNION ALL
    SELECT 8   AS MON, 420010060   AS AMT  FROM DUAL   UNION ALL
    SELECT 9   AS MON, 431000000   AS AMT  FROM DUAL   UNION ALL
    SELECT 10  AS MON, 462000000   AS AMT  FROM DUAL   UNION ALL
    SELECT 11  AS MON, 428010000   AS AMT  FROM DUAL   UNION ALL
    SELECT 12  AS MON, 472860000   AS AMT  FROM DUAL
)

SELECT  C.NO                                                                    AS MON
,       SUM(DECODE(C.NO - S.MON, 0, AMT))                                       AS CUR_MON
,       SUM(DECODE(C.NO - S.MON, 0, AMT)) - SUM(DECODE(C.NO - S.MON, 1, AMT))   AS PRV_MON
FROM SALE S, (SELECT LEVEL AS NO 
              FROM DUAL 
              CONNECT BY LEVEL <= 12) C         
WHERE C.NO IN (MON, MON + 1)
GROUP BY C.NO
ORDER BY C.NO;

2. 재귀 조인을 이용한 방법

WITH SALE
AS  
(   SELECT 0   AS MON, 322794590   AS AMT  FROM DUAL   UNION ALL
    SELECT 1   AS MON, 312045670   AS AMT  FROM DUAL   UNION ALL
    SELECT 2   AS MON, 381020000   AS AMT  FROM DUAL   UNION ALL
    SELECT 3   AS MON, 412080000   AS AMT  FROM DUAL   UNION ALL
    SELECT 4   AS MON, 392010000   AS AMT  FROM DUAL   UNION ALL
    SELECT 5   AS MON, 428000020   AS AMT  FROM DUAL   UNION ALL
    SELECT 6   AS MON, 415020000   AS AMT  FROM DUAL   UNION ALL
    SELECT 7   AS MON, 394092000   AS AMT  FROM DUAL   UNION ALL
    SELECT 8   AS MON, 420010060   AS AMT  FROM DUAL   UNION ALL
    SELECT 9   AS MON, 431000000   AS AMT  FROM DUAL   UNION ALL
    SELECT 10  AS MON, 462000000   AS AMT  FROM DUAL   UNION ALL
    SELECT 11  AS MON, 428010000   AS AMT  FROM DUAL   UNION ALL
    SELECT 12  AS MON, 472860000   AS AMT  FROM DUAL
)
SELECT  S1.MON
,       S1.AMT              AS CUR_MON
,       S1.AMT - S2.AMT     AS PRV_MON
FROM SALE S1
JOIN SALE S2 ON S1.MON = S2.MON + 1;

3. 결과

MON CUR_MON PRV_MON
--- ------- -------
  1 312045670 -10748920 
  2 381020000 68974330 
  3 412080000 31060000 
  4 392010000 -20070000 
  5 428000020 35990020 
  6 415020000 -12980020 
  7 394092000 -20928000 
  8 420010060 25918060 
  9 431000000 10989940 
 10 462000000 31000000 
 11 428010000 -33990000 
 12 472860000 44850000