농부지기

☞ SUM() 집계함수를 사용하게 되면 GROUP BY를 반드시 사용해야 된다.

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) AS SUM_AMT
  FROM T
 GROUP BY SHOP, YM
 ORDER BY SHOP, YM ;

☞ OVER() 분석 함수를 사용하게 되면 GROUP BY절을 사용해도 되고, 안해도 동일함
   OVER() 함수 괄호한에 아무 절도 없으면 모든 결과 레코드를 대상으로 SUM()이 수행 됨

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()                AS SUM_AMT
  FROM T
 --GROUP BY SHOP, YM, AMT
 ORDER BY SHOP, YM ;

☞   LAST_VALUE  

1. last_value(c1 ignore nulls) over(order by  C1)