Oracle - OVER 예제5

[ Oracle - OVER 예제5 ]

 

☞ Windowing절은 반드시 OVER()함수 내부에 ORDER BY 가 존재 해야 됨    RANGE절 : 논리적인 상대 번지에 의해 윈도우 크기 지정.
	WITH T AS (             SELECT 'GS몰'   AS SHOP, '201405' AS YM, 22000 AS AMT FROM DUAL     UNION ALL SELECT 'GS몰'          , '201409'      , 55000        FROM DUAL     UNION ALL SELECT 'GS몰'          , '201412'      , 55000        FROM DUAL     UNION ALL SELECT '양동점'        , '201401'      , 10000        FROM DUAL     UNION ALL SELECT '양동점'        , '201401'      , 20000        FROM DUAL     UNION ALL SELECT '양동점'        , '201401'      , 11000        FROM DUAL     UNION ALL SELECT '양동점'        , '201403'      , 22000        FROM DUAL     UNION ALL SELECT '양동점'        , '201405'      , 33000        FROM DUAL ) SELECT SHOP, YM      , SUM(AMT) OVER(ORDER BY SHOP, YM                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW                      )                                                 AS SUM_AMT1 --     , SUM(AMT) OVER(ORDER BY SHOP, YM  --                     RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING --                     )                                               AS SUM_AMT2  --오류 발생      , ROWNUM      , SUM(AMT) OVER(ORDER BY ROWNUM                      RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING                      )                                                 AS SUM_AMT3   FROM T  ORDER BY SHOP, YM ;