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

할당에 대한 로직

by 야신 [SQL Query] 차감 할당 [2014.02.13 17:57:36]

주문과 예정공급으로 할당하는 로직을 구현중에 있습니다.
주문받은 수량과 일자를 기준으로 공급예정수량을 순차적으로 선입선출합니다.

먼저 제가 짠 프로그램은 아래와 같은데..

WITH 주문 AS

(

SELECT '배' item, '20140101' demand_date, 100 demand_qty FROM dual

UNION ALL SELECT '배', '20140102' demand_date, 200 FROM dual

)

, 예정공급 AS

(

SELECT '배' item , '20140102' supply_date, 50 qty FROM dual

UNION ALL SELECT '배' , '20140103', 80 FROM dual

UNION ALL SELECT '배' , '20140104', 100 FROM dual

)

SELECT NVL(t.item, x.item) AS 품목

, t.demand_qty AS 주민일자

, t.demand_date AS 주문일자

, t.demand_qty AS 주문수량

---------------------------------------------------

, x.supply_date AS 공급가능일자

, x.qty AS 공급수량

, GREATEST(0, t.demand_qty - x.sqty) AS 주문잔량

, LEAST(t.demand_qty, x.sqty) - (x.sqty - x.qty) AS 할당수량

FROM 주문 t

LEFT OUTER JOIN

(SELECT 예정공급.*

, SUM(qty) OVER(PARTITION BY item ORDER BY supply_date) sqty

FROM 예정공급

) x

ON x.item = t.item

ORDER BY 품목, demand_date, supply_date;

일단 베이스가 되는 주문이 하나가 아니라 여러건이라 건수가 많이져 할당이
정상적으로 적용되지 않네요 ㅡㅡ;;
이미 순차적프로그램으로 구현하였으니 속도상의 문제로 쿼리로 구현중인데 좋은 방법이 없을까요?

최종 원하는 데이타 모양은 아래와 같습니다.

결과	주문일자	수요	예정공급일자	공급수량	할당
배	2014-01-01	100	2014-01-02	50	50
배	2014-01-01	100	2014-01-03	80	50
배	2014-01-02	200	2014-01-03	80	30
배	2014-01-02	200	2014-01-04	100	100

by 마농 [2014.02.13 19:25:12]

SELECT NVL(t.item, x.item)                     AS 품목
     , t.demand_qty                            AS 주민일자
     , t.demand_date                           AS 주문일자
     , t.demand_qty                            AS 주문수량
     , x.supply_date                           AS 공급가능일자
     , x.qty                                   AS 공급수량
     , LEAST( t.demand_qty
            , x.qty
            , t.sqty - x.sqty + x.qty
            , x.sqty - t.sqty + t.demand_qty ) AS 할당수량
  FROM (SELECT 주문.*
             , SUM(demand_qty) OVER(PARTITION BY item ORDER BY demand_date) sqty
          FROM 주문
        ) t
  LEFT OUTER JOIN
       (SELECT 예정공급.*
             , SUM(qty) OVER(PARTITION BY item ORDER BY supply_date) sqty
          FROM 예정공급
        ) x
    ON x.item = t.item
   AND t.sqty > x.sqty - x.qty
   AND x.sqty > t.sqty - t.demand_qty
 ORDER BY 품목, 주문일자, 공급가능일자
;

by 야신 [2014.02.14 08:54:01]

정말 감사합니다. 마농님. ㅠㅠ
하루정도 고민하여 이리저리 하다가 안되서 올렸는데...
역시 고수가 되는 길은 엄청 멀군요.

댓글등록

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

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

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

데이터베이스 질문과 답변