순환 참조에 걸린것 같습니다...ㅡㅡ; 0 3 3,212

by XX큰타이거 순환 PARTITION [2015.11.23 21:53:54]



아래와 같이 데이터가 출력 가능 할지 질문 드립니다. 

정확히 결정이 되는 수량은 입고수량, 입고단가, 입고금액, 출고수량 입니다. 

 

 

일자 제품코드

당일

기초수량

당일

기초단가

당일

기초금액

입고수량

입고단가 입고금액 출고수량 출고단가 출고금액 기말수량 기말단가 기말금액
20150903 A 0 0 0 500 120 60000 45 120 5400 455 120 54600
20150904 A 455 120 54600 100 130 13000 100 122 12180 455 122 55420
20150905 A 455 122 55420 0 0 0 0 0 0 455 122 55420
20150906 A 455 122 55420 300 140 42000 600 129 77420 155 129 20000
20150907 A 155 129 20000 0 0 0 0 0 0 155 129 20000
20150908 A 155 129 20000 150 130 19500 0 0 0 305 130 39500
20150909 A 305 130 39500 0 0 0 130 130 16836 175 130 22664
20150910 A 175 130 22664 0 0 0 0 0 0 175 130 22664
20150911 A 175 130 22664 0 0 0 0 0 0 175 130 22664
20150912 A 175 130 22664 0 0 0 0 0 0 175 130 22664
20150913 A 175 130 22664 100 80 8000 150 112 16726 125 112 13938
20150914 A 125 112 13938 0 0 0 0 0 0 125 112 13938
20150915 A 125 112 13938 0 0 0 0 0 0 125 112 13938

 

 

입고수량, 입고단가, 출고수량은 입출고테이블에서 SELECT 된 수량 및 금액이고 그 나머지 필드들은 아래 수식에 따라

계산되어야 합니다. (당일기초수량을 계산해야 출고단가를 계산할 수 있는데 출고단가가  기초금액, 기초수량과 관련되어 순환 참조가 일어나서 좀 난감하네요....ㅡㅡ;)

 1. 당일기초수량, 단가, 금액은 전일자 기말수량, 단가, 금액임. 
 2. 당일출고단가 = (당일기초금액 + 당일입고금액) / (당일기초수량+당일입고수량)
 3. 당일출고금액 = 당일출고단가 * 당일출고수량
 4. 당일기말수량 = 당일기초수량 + 당일입고수량 - 당일출고수량
 5. 당일기말금액 = 당일기초금액 + 당일입고금액 - 당일출고금액
 6. 당일기말단가 = 당일기말금액 / 당일기말수량

Procedure에서 LOOP 을 수행하긴 데이터량이 너무 부담되구요....ㅜㅜ

고수님들 조언해 주시면 감사하겠습니다...

위 내용을 엑셀 파일에 정리해 봤습니다.

 

by 마농 [2015.11.24 11:17:04]
WITH t AS
(
SELECT '20150903' dt, 'A' cd, 500 cnt_i, 120 prc_i, 45 cnt_o FROM dual
UNION ALL SELECT '20150904', 'A', 100, 130, 100 FROM dual
UNION ALL SELECT '20150905', 'A',   0,   0,   0 FROM dual
UNION ALL SELECT '20150906', 'A', 300, 140, 600 FROM dual
UNION ALL SELECT '20150907', 'A',   0,   0,   0 FROM dual
UNION ALL SELECT '20150908', 'A', 150, 130,   0 FROM dual
UNION ALL SELECT '20150909', 'A',   0,   0, 130 FROM dual
UNION ALL SELECT '20150910', 'A',   0,   0,   0 FROM dual
UNION ALL SELECT '20150911', 'A',   0,   0,   0 FROM dual
UNION ALL SELECT '20150912', 'A',   0,   0,   0 FROM dual
UNION ALL SELECT '20150913', 'A', 100,  80, 150 FROM dual
UNION ALL SELECT '20150914', 'A',   0,   0,   0 FROM dual
UNION ALL SELECT '20150915', 'A',   0,   0,   0 FROM dual
)
, tmp0 AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY cd ORDER BY dt) rn
     , dt, cd, cnt_i, prc_i, cnt_o
     , cnt_i * prc_i amt_i
  FROM t a
)
, tmp1( rn, dt, cd
      , cnt_s, prc_s, amt_s
      , cnt_i, prc_i, amt_i
      , cnt_o, prc_o, amt_o
      , cnt_e, prc_e, amt_e
      ) AS
(
SELECT b.rn
     , b.dt
     , b.cd
     , 0 cnt_s
     , 0 prc_s
     , 0 amt_s
     , b.cnt_i
     , b.prc_i
     , b.amt_i
     , b.cnt_o
     , ROUND((0 + b.amt_i) / (0 + b.cnt_i)) prc_o
     , b.cnt_o * ROUND((0 + b.amt_i) / (0 + b.cnt_i)) amt_o
     , 0 + b.cnt_i - b.cnt_o cnt_e
     , ROUND( (0 + b.amt_i - b.cnt_o * ROUND((0 + b.amt_i) / (0 + b.cnt_i)))
            / (0 + b.cnt_i - b.cnt_o)
            ) prc_e
     , 0 + b.amt_i - b.cnt_o * ROUND((0 + b.amt_i) / (0 + b.cnt_i)) amt_e
  FROM tmp0 b
 WHERE rn = 1
 UNION ALL
SELECT b.rn
     , b.dt
     , b.cd
     , a.cnt_e cnt_s
     , a.prc_e prc_s
     , a.amt_e amt_s
     , b.cnt_i
     , b.prc_i
     , b.amt_i
     , b.cnt_o
     , ROUND((a.amt_e + b.amt_i) / (a.cnt_e + b.cnt_i)) prc_o
     , b.cnt_o * ROUND((a.amt_e + b.amt_i) / (a.cnt_e + b.cnt_i)) amt_o
     , a.cnt_e + b.cnt_i - b.cnt_o cnt_e
     , ROUND( (a.amt_e + b.amt_i - b.cnt_o * ROUND((a.amt_e + b.amt_i) / (a.cnt_e + b.cnt_i)))
            / (a.cnt_e + b.cnt_i - b.cnt_o)
            ) prc_e
     , a.amt_e + b.amt_i - b.cnt_o * ROUND((a.amt_e + b.amt_i) / (a.cnt_e + b.cnt_i)) amt_e
  FROM tmp1 a
     , tmp0 b
 WHERE a.cd = b.cd
   AND a.rn + 1 = b.rn
)
SELECT *
  FROM tmp1
;

 


by 마농 [2015.11.24 13:59:58]

단가 계산식에 나눗셈이 포함되어 소수점 이하 발생하네요.
이를 보정하기 위해 반올림(ROUND) 했더니 오차가 발생하네요.
ROUND 처리를 하는게 맞는지? 빼는게 맞는지 고민해 보세요.


by XX큰타이거 [2015.11.24 14:23:38]

round 처리는 빼야 하구요...그 부분은 제가 조정합죠...

매번 도움을 많이 받습니다. 어찌 감사의 답을 드려야 할지 모르겠습니다. 

진심으로 커피라도 한잔 쏘고 싶은데요....ㅎㅎㅎ

감사합니다...

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입