데이터베이스 질문과 답변

주문 수량을 박스입수 만큼 row를 분리 0 4 1,567

by 지현명 [SQL Query] [2019.05.20 10:11:06]

사용하는 DB : PostgreSQL 11.1

한상품의 주문 수량이 50일 경우 만약 그 상품의 박스 입수가 20이면

한줄로 입력된 50을 아래와 같이 해당 박스 입수 만큼 row를 만드는 쿼리를 어떻게 구현 할 수 있을까요? (켜서나 pl/pgsql반복문 사용하지 않고 query로만..)

CREATE TABLE public.prod_master (
    prodcd varchar(10) NULL,
    boxinqty int4 NULL  --박스입수
);

CREATE TABLE public.orders (
    shipdate date NULL,
    prodcd varchar(10) NULL,
    orderqty int4 NULL --주문수량
);


insert into prod_master
values('8809123', 20);  --한박스에 20개씩 

insert into orders
values('2019-05-20', '8809123', 50);  --총주문 수량 50개
 

이와 같이 주문이 들어 왔을 경우

As-Is
shipdate prodcd orderqty
2019-05-20 8809123 50

 

아래와 같이 row를 박스 입수 만큼 row를 분리 하는 쿼리가 가능한지...

To-Be
shipdate prodcd orderqty
2019-05-20 8809123 20
2019-05-20 8809123 20
2019-05-20 8809123 10
by 마농 [2019.05.20 10:48:05] 
WITH orders(shipdate, prodcd, orderqty) AS
(
SELECT '2019-05-20', '8809123', 50
UNION ALL SELECT '2019-05-20', '8888888', 50
)
, prod_master(prodcd, boxinqty) AS
(
SELECT '8809123', 20
UNION ALL SELECT '8888888', 30
)
SELECT o.shipdate
     , o.prodcd
     , o.orderqty
     , p.boxinqty
     , box
     , CASE WHEN p.boxinqty * box > o.orderqty
            THEN o.orderqty - p.boxinqty * (box - 1)
            ELSE p.boxinqty
        END box_qty
  FROM orders o
 INNER JOIN prod_master p
    ON o.prodcd = p.prodcd
 CROSS JOIN generate_series(1, CEIL(1.0 * o.orderqty / p.boxinqty)) box
;

 

by 지현명 [2019.05.20 11:21:19]

감사합니다. 

커셔에 loop로 했었는데 덕분에 깔끔하게 적용 할 수 있게 됐습니다. 

 

by 랑에1 [2019.05.20 10:54:46] 

WITH t1(shipdate, prodcd, orderqty) AS (
  SELECT '2019-05-20', '8809123', 50 FROM dual
),
t2(useqty) AS (
  SELECT 20 FROM dual
),
t3(shipdate, prodcd, orderqty, rmnqty) AS (
  SELECT shipdate, prodcd, LEAST(t1.orderqty, t2.useqty), t1.orderqty - LEAST(t1.orderqty, t2.useqty)
  FROM t1, t2
  UNION ALL 
  SELECT shipdate, prodcd, LEAST(t3.rmnqty, t2.useqty), t3.rmnqty - LEAST(t3.rmnqty, t2.useqty)
  FROM t3, t2
  WHERE t3.rmnqty >= t2.useqty OR t3.rmnqty > 0 
)

SELECT shipdate, prodcd, orderqty
FROM t3

-- 오라클 기준으로 해봤습니다

 

by 지현명 [2019.05.20 11:21:41]

오라클로 하게 되면 참고 하겠습니다. 

답글 감사합니다. 

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