꿈꾸는 개발자, DBA 커뮤니티 구루비

누적차감 인데 머리가 터질 것 같습니다.

by 패대기 [SQL Query] [2014.03.26 18:43:26]

안녕하세요. 누적 차감 쿼리 질문있습니다.
그냥 쭉 차감하면 쉬운데, 중간에 예외가 있어서 힘이 듭니다.

어떤거냐면...

WITH tab AS(
SELECT 'AA' GUBUN, 'ABC' MODEL, '20140101' YYYY, 3000 INV_QTY, 1000 S_QTY FROM DUAL UNION ALL
SELECT 'AA' GUBUN, 'ABC' MODEL, '20140102' YYYY, 1000 INV_QTY, 2000 S_QTY FROM DUAL UNION ALL
SELECT 'AA' GUBUN, 'ABC' MODEL, '20140103' YYYY, 0 INV_QTY, 2000 S_QTY FROM DUAL UNION ALL
SELECT 'AA' GUBUN, 'ABC' MODEL, '20140105' YYYY, 0 INV_QTY, 1000 S_QTY FROM DUAL UNION ALL
SELECT 'AA' GUBUN, 'ABC' MODEL, '20140106' YYYY, 0 INV_QTY, 1000 S_QTY FROM DUAL UNION ALL
SELECT 'AA' GUBUN, 'ABC' MODEL, '20140109' YYYY, 1000 INV_QTY, 1000 S_QTY FROM DUAL UNION ALL
SELECT 'AA' GUBUN, 'ABC' MODEL, '20140110' YYYY,    0 INV_QTY, 1000 S_QTY FROM DUAL UNION ALL
SELECT 'BB' GUBUN, 'AAA' MODEL, '20140106' YYYY,    0 INV_QTY, 1000 S_QTY FROM DUAL UNION ALL
SELECT 'BB' GUBUN, 'AAA' MODEL, '20140109' YYYY,    1000 INV_QTY, 1000 S_QTY FROM DUAL
)
SELECT GUBUN, MODEL, YYYY
, INV_QTY, S_QTY
--, SUM(INV_QTY) OVER(PARTITION BY GUBUN, MODEL ORDER BY YYYY) INV_ACCUM
--, SUM(S_QTY) OVER(PARTITION BY GUBUN, MODEL ORDER BY YYYY) S_ACCUM
, SUM(S_QTY) OVER(PARTITION BY GUBUN, MODEL ORDER BY YYYY) -
   SUM(INV_QTY) OVER(PARTITION BY GUBUN, MODEL ORDER BY YYYY) NET_QTY
FROM TAB A
;

여기서 NET_QTY를 제대로 표현을 못하겠습니다.
어떻게 나와야 하냐면..
GUBUN, MODEL단위, YYYY순서로 INV_QTY를 S_QTY만큼 누적 차감하는데, 차감할게 없으면 S_QTY로 보여줘야 합니다.
그리고 다시 INV_QTY가 나오면 다시 차감되어야 하는데, 어떻게 해야 할지 모르겠습니다.

GUBUN	MODEL	YYYY	INV_QTY	S_QTY	올바른 값
AA	ABC	20140101	3000	1000	-2000	당일 S_QTY에서 당일 재고 3000을빼면 재고 2000남음
AA	ABC	20140102	1000	2000	-1000	당일 S_QTY 2000에서, 앞전 재고 2000과 당일 재고 1000합한 3000을 뺌. 재고 1000남음
AA	ABC	20140103	0	2000	1000	당일 S_QTY 2000에서 .앞전재고 1000을 빼면, 1000이 남고, 재고가 없어짐
AA	ABC	20140105	0	1000	1000	앞전 재고가 없어졌기 때문에 당일 S_QTY값이 나와야 함
AA	ABC	20140106	0	1000	1000	앞전 재고가 없어졌기 때문에 당일 S_QTY값이 나와야 함
AA	ABC	20140109	1000	1000	0	당일 S_QTY가 1000에서 당일재고 1000 빼면 0가 되며, 재고모두 소진
AA	ABC	20140110	0	1000		앞전과 당일재고가 없으므로 S_QTY 1000
BB	AAA	20140106	0	1000	1000
BB	AAA	20140109	1000	1000	0

위 표와 같이 올바른 값이 나와야 합니다.
LAG쓰고, DECODE쓰고 해도 잘안되네요..

MODEL절은 가독성 때문에 지양하고 있습니다. 분석함수만으로 하고 싶습니다.

고수님들 도와주세요..

by 마농 [2014.03.27 00:07:47]

제가 예전에 냈던 퀴즈와 유사한 형태의 문제네요.
http://www.gurubee.net/article/26172
http://www.gurubee.net/lecture/2203
SQL 은 구조적 질의어로 절차적 언어의 한계를 극복한 언어이죠.
절차적인 처리 대신 구조적인 질의만으로 결과를 얻을 수 있죠.
대부분의 절차적인 처리는 구조적 질의로 변경 가능합니다만...
그러나 이 문제의 경우엔 구조적 질의로 변경 불가능하며, 절차적인 처리만 가능한 문제입니다.
분석함수는 절차적인 함수가 아닙니다. 집합적인 함수입니다.
분석함수로는 이 문제를 풀 수 없습니다.
집합적 SQL 의 이런 한계를 극복할 수 있는것이 10G 의 Model 절입니다.
11G 에서는 Recursive SQL 로 절차적 처리가 가능합니다.
저도 Model 절을 접하기 전엔, 분석함수로 이 문제를 풀어보려 애썼던 적이 있었네요.
결국 포기했었다가, MODEL절을 알고 난뒤 이 문제를 다시 풀게 되었습니다.

by 마농 [2014.03.27 00:11:21]

-- 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
;

by 패대기 [2014.03.27 08:22:24]

마농님 정말 감사합니다. 이제야 분석함수로 왜 안되는지 알겠네요..모델절과 두번째 쿼리는여기서는 적용하기가 힘들것 같습니다. 죄송합니다.
마농님 말씀처럼 절차적으로 풀것을 결정했습니다.
감사합니다. ^^

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변