Oracle - 순차적으로 차감하기1

농부지기 2017. 1. 19. 23:20

-- 10G MODEL 절을 이용한 풀이 --
WITH tab AS
(
              SELECT 'AA' gubun, 'ABC' model, '20140101' yyyy, 3000 inv_qty, 1000 s_qty FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140102'      , 1000        , 2000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140103'      ,    0        , 2000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140105'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140106'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140109'      , 1000        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140110'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'BB'      , 'AAA'     , '20140106'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'BB'      , 'AAA'     , '20140109'      , 1000        , 1000       FROM dual
)
, t1 AS
(
SELECT gubun, model, yyyy, inv_qty, s_qty
     , ROW_NUMBER() OVER(PARTITION BY gubun, model ORDER BY yyyy) rn
  FROM tab
)
SELECT *
  FROM t1
 MODEL
 PARTITION BY (gubun, model)
 DIMENSION BY (rn)
 MEASURES (yyyy, inv_qty, s_qty, 0 net_qty)
 IGNORE NAV
 RULES AUTOMATIC ORDER ( net_qty[ANY] = s_qty[CV()] - inv_qty[CV()] + LEAST(net_qty[CV()-1], 0) )
 ORDER BY gubun, model, rn
;

-- 11G Recursive SQL 을 이용한 풀이 --
WITH tab AS
(
              SELECT 'AA' gubun, 'ABC' model, '20140101' yyyy, 3000 inv_qty, 1000 s_qty FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140102'      , 1000        , 2000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140103'      ,    0        , 2000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140105'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140106'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140109'      , 1000        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140110'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'BB'      , 'AAA'     , '20140106'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'BB'      , 'AAA'     , '20140109'      , 1000        , 1000       FROM dual
)
, t1 AS ( SELECT gubun, model, yyyy, inv_qty, s_qty
               , ROW_NUMBER() OVER(PARTITION BY gubun, model ORDER BY yyyy) rn
            FROM tab
        )
, t2(gubun, model, rn, yyyy, inv_qty, s_qty, net_qty) AS
    (
        SELECT gubun, model, rn, yyyy, inv_qty, s_qty
             , s_qty - inv_qty AS net_qty
          FROM t1
         WHERE rn = 1
         UNION ALL
        SELECT a.gubun, a.model, a.rn, a.yyyy, a.inv_qty, a.s_qty
             , a.s_qty - a.inv_qty + LEAST(b.net_qty, 0) AS net_qty
          FROM t1 a
             , t2 b
         WHERE a.gubun = b.gubun
           AND a.model = b.model
           AND a.rn = b.rn + 1
    )
    SELECT *
      FROM t2
     ORDER BY gubun, model, rn
;
--URL : http://www.gurubee.net/article/62179
--제가(마농) 예전에 냈던 퀴즈와 유사한 형태의 문제네요.
--http://www.gurubee.net/article/26172
--http://www.gurubee.net/lecture/2203
--SQL 은 구조적 질의어로 절차적 언어의 한계를 극복한 언어이죠.
--절차적인 처리 대신 구조적인 질의만으로 결과를 얻을 수 있죠.
--대부분의 절차적인 처리는 구조적 질의로 변경 가능합니다만...
--그러나 이 문제의 경우엔 구조적 질의로 변경 불가능하며, 절차적인 처리만 가능한 문제입니다.
--분석함수는 절차적인 함수가 아닙니다. 집합적인 함수입니다.
--분석함수로는 이 문제를 풀 수 없습니다.
--집합적 SQL 의 이런 한계를 극복할 수 있는것이 10G 의 Model 절입니다.
--11G 에서는 Recursive SQL 로 절차적 처리가 가능합니다.
--저도 Model 절을 접하기 전엔, 분석함수로 이 문제를 풀어보려 애썼던 적이 있었네요.
--결국 포기했었다가, MODEL절을 알고 난뒤 이 문제를 다시 풀게 되었습니다.

저작자표시 (새창열림)