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

-- 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절을 알고 난뒤 이 문제를 다시 풀게 되었습니다.