WITH t1 AS
(--매입
SELECT 'A001' gds_no, '2012-05-01' dte, 50 qty, 180 buy_pric FROM dual
UNION ALL SELECT 'A001', '2012-05-10', 100, 200 FROM dual
UNION ALL SELECT 'A001', '2012-05-20', 150, 220 FROM dual
)
, t2 AS
(--매출
SELECT 'A001' gds_no, '2012-05-11' dte, 100 qty FROM dual
UNION ALL SELECT 'A001', '2012-05-21', 100 FROM dual
)
, t3 AS
(
SELECT gds_no, dte, '매입' gb, qty, buy_pric
  FROM t1
 UNION ALL
SELECT gds_no, dte, '매출', qty, 0
  FROM t2
)
SELECT *
  FROM t3
 MODEL
 PARTITION BY (gds_no)
 DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY gds_no ORDER BY dte) rn)
 MEASURES (dte, gb, qty, buy_pric, 0 v1, 0 v2, 0 v3, 0 v4)
 IGNORE NAV
 RULES AUTOMATIC ORDER
 ( v3[ANY] = v3[cv()-1] + DECODE(gb[cv()], '매출', -qty[cv()], qty[cv()])
 , v1[ANY] = ROUND( (buy_pric[cv()] * qty[cv()] + v1[cv()-1] * v3[cv()-1])
                  / DECODE(gb[cv()], '매출', v3[cv()-1], v3[cv()])
                  , 1)
 , v2[ANY] = DECODE(gb[cv()], '매출', v1[cv()] * qty[cv()])
 , v4[ANY] = v1[cv()] * v3[cv()]
 )
;