데이터베이스 질문과 답변

고민고민하다 안되서 쿼리 조언 부탁드립니다.ㅜㅜ 0 5 728

by 아이린 [SQL Query] [2019.02.22 10:27:21]

WITH T AS (
    SELECT '1005'  AS 주문번호, '2' AS 주문상세번호, '13' AS 주문구분코드, 'A001'  AS 제품코드, '960'  AS 주문수량, '960'   AS 가용재고수량, '960'   AS 누적수량, '960'  AS 분배수량 FROM DUAL UNION ALL
    SELECT '1007'  AS 주문번호, '1' AS 주문상세번호, '11' AS 주문구분코드, 'A001'  AS 제품코드, '480'  AS 주문수량, '2080'  AS 가용재고수량, '1440'  AS 누적수량, '480'  AS 분배수량 FROM DUAL UNION ALL
    SELECT '1008'  AS 주문번호, '1' AS 주문상세번호, '11' AS 주문구분코드, 'A001'  AS 제품코드, '1600' AS 주문수량, '2080'  AS 가용재고수량, '3040'  AS 누적수량, '640'  AS 분배수량 FROM DUAL UNION ALL
    SELECT '1012'  AS 주문번호, '1' AS 주문상세번호, '11' AS 주문구분코드, 'A001'  AS 제품코드, '13'   AS 주문수량, '2080'  AS 가용재고수량, '3053'  AS 누적수량, '0'    AS 분배수량 FROM DUAL UNION ALL
    SELECT '1806'  AS 주문번호, '1' AS 주문상세번호, '21' AS 주문구분코드, 'A001'  AS 제품코드, '960'  AS 주문수량, '2080'  AS 가용재고수량, '4013'  AS 누적수량, '0'    AS 분배수량 FROM DUAL UNION ALL
    SELECT '1839'  AS 주문번호, '1' AS 주문상세번호, '21' AS 주문구분코드, 'A001'  AS 제품코드, '960'  AS 주문수량, '2080'  AS 가용재고수량, '4973'  AS 누적수량, '0'    AS 분배수량 FROM DUAL UNION ALL
    SELECT '1872'  AS 주문번호, '1' AS 주문상세번호, '21' AS 주문구분코드, 'A001'  AS 제품코드, '960'  AS 주문수량, '2080'  AS 가용재고수량, '5933'  AS 누적수량, '0'    AS 분배수량 FROM DUAL UNION ALL
    SELECT '1905'  AS 주문번호, '1' AS 주문상세번호, '21' AS 주문구분코드, 'A001'  AS 제품코드, '960'  AS 주문수량, '2080'  AS 가용재고수량, '6893'  AS 누적수량, '0'    AS 분배수량 FROM DUAL UNION ALL
    SELECT '1938'  AS 주문번호, '1' AS 주문상세번호, '21' AS 주문구분코드, 'A001'  AS 제품코드, '960'  AS 주문수량, '2080'  AS 가용재고수량, '7853'  AS 누적수량, '0'    AS 분배수량 FROM DUAL UNION ALL
    SELECT '1971'  AS 주문번호, '1' AS 주문상세번호, '21' AS 주문구분코드, 'A001'  AS 제품코드, '960'  AS 주문수량, '2080'  AS 가용재고수량, '8813'  AS 누적수량, '0'    AS 분배수량 FROM DUAL UNION ALL
    SELECT '2020'  AS 주문번호, '1' AS 주문상세번호, '21' AS 주문구분코드, 'A001'  AS 제품코드, '960'  AS 주문수량, '2080'  AS 가용재고수량, '9773'  AS 누적수량, '0'    AS 분배수량 FROM DUAL
)
SELECT  *
FROM    T;

 

 

befor              
주문번호 주문상세번호 주문구분코드 제품코드 주문수량 가용재고수량 누적수량 분배수량
1005 2 13 A001 960 960 960 960
1007 1 11 A001 480 2080 1440 480
1008 1 11 A001 1600 2080 3040 640
1012 1 11 A001 13 2080 3053 0
1806 1 21 A001 960 2080 4013 0
1839 1 21 A001 960 2080 4973 0
1872 1 21 A001 960 2080 5933 0
1905 1 21 A001 960 2080 6893 0
1938 1 21 A001 960 2080 7853 0
1971 1 21 A001 960 2080 8813 0
2020 1 21 A001 960 2080 9773 0

 

 

 

after                  
주문번호 주문상세번호 주문구분코드 제품코드 주문수량 가용재고수량 가용재고 총수량 누적수량 분배수량 주문구분코드의 분배수량이 된 가용재고수량
1005 2 13 A001 960 960 3040 960 960 960
1007 1 11 A001 480 2080 3040 1440 480 2080
1008 1 11 A001 1600 2080 3040 3040 1600 2080
1012 1 11 A001 13 2080 3040 3053 0 2080
1806 1 21 A001 960 2080 3040 4013 0 0
1839 1 21 A001 960 2080 3040 4973 0 0
1872 1 21 A001 960 2080 3040 5933 0 0
1905 1 21 A001 960 2080 3040 6893 0 0
1938 1 21 A001 960 2080 3040 7853 0 0
1971 1 21 A001 960 2080 3040 8813 0 0
2020 1 21 A001 960 2080 3040 9773 0 0

현재 befor 의 데이터중 after의 빨간색 표시된 부분에 대해서 데이터를 구할려다보니 도저희 고민하고 머리를 싸매도 안나와서 도움요청 드립니다. ㅜㅜ

현재 주문번호단위의 전량 분배 방식이며, 가용재고 수량은 주문코드별 조건에 의해서 가용할수 있는 수량입니다. 

by 랑에1 [2019.02.22 13:09:46] 
SELECT 주문번호
     , 주문상세번호
     , 주문구분코드
     , 제품코드
     , 주문수량
     , 가용재고수량
     , SUM(DECODE(rn, 1, 가용재고수량, 0)) OVER() AS "가용재고 총수량"
     , 누적수량
     , 분배수량
FROM 
(
	SELECT T.*
	     , ROW_NUMBER() OVER(PARTITION BY T.주문상세번호 ORDER BY T.주문번호) AS rn      
	FROM   T
) T
ORDER BY 주문번호

가용재고 총수량은 이렇게 하시면 될 듯 한데.
분배수량과 마지막 컬럼은 무슨말씀이신지 잘 모르겠어요;
주문번호 1008의 분배수량이 befor일때는 640이고 after에서는 1600인데 왜그런걸까요?

 

by 아이린 [2019.02.22 13:53:34]

랑에1님 답변 정말 감사합니다. 질문에 내용설명이 부족한거 같네요 

#저값에 대한 전체 적인 조건은 주문구분코드 별 가용할수 있는 조건을 일괄 분배방식입니다.

(13에 조건에 만족한 가용재고 960개 이며 11,21에 만족한 가용재고 2080개라고 보시면 됩니다.)

1. 분배수량의 조건은 아래  2.같이 구한 값이며,  befor의 값인 가용재고 총수량이 960이랑 2080 더한 총수량을 주문번호먼저인거 부터 주문수량을 일괄분배 해야하는데 가용재고 총수량을 못구해서 값이 맞지 않았던  값이였으며,(가용재고 총수량)

설명을 드리면 데이터는 처음의 가용할수 있는 수량은 주문구분 코드13은 960개와 주문구분코드 11,21은 2080개를 가용할수 있는 값이구요
이 주문코드 조건에 의한 가용할수 있는 수량을 일괄분배를 해서 분배 수량을 구한겁니다.
그 분배 수량을 구한 값은 조건 주문코드에 의해서 13은 960개
11,21은 2080개를 가용할수 있는데 주문번호가 먼저인게 분배가 우선됐기에 마지막 컬럼은 가용할수 있는 수량은  11인 구분코드가 2080개를 배분할수 있는거에 
대해서 표현을 할려고 했던겁니다.(주문구분코드의 분배수량이 된 가용재고수량)

가용재고 총수량
3040으로 960 + 480 +1600 = (주문구본코드13만 가용할수있는값 960개
주문구분코드 11, 21이 가용할수 있는건 2080개이지만 주문번호가 우선순위인게 먼저 분배가 되기게
11부터 일괄 분배가 된거입니다.

설명이 이해가 되실지 모르겠네요, 설명을 좀 잘 못해서...

 

2. 
--case a. 누적주문수량 <= 가용재고 ==> 주문수량 
--case b. 이전 누적 주문수량 < 가용재고 AND 가용재고 < 누적 주문수량 ==> 가용재고 - 이전 누적 주문수량

by 랑에1 [2019.02.22 14:36:19] 
SELECT 주문번호
     , 주문상세번호
     , 주문구분코드
     , 제품코드
     , 주문수량
     , 가용재고수량
     , 가용재고총수량     
     , 누적수량
     , 분배수량
     , SUM(t.분배수량) OVER(PARTITION BY t.주문구분코드) AS "분배수량이된 가용재고수량"
FROM 
(
    SELECT t.*
         , CASE WHEN 가용재고총수량 >= 누적수량 THEN 주문수량 ELSE '0' END AS 분배수량
    FROM 
    (
        SELECT t.*
             , SUM(DECODE(t.rn, 1, 가용재고수량, 0)) OVER() AS 가용재고총수량
        FROM 
        (
            SELECT t.*
                 , ROW_NUMBER() OVER(PARTITION BY t.주문상세번호 ORDER BY t.주문번호) AS rn      
            FROM   t
        ) t
    ) t
) t
ORDER BY t.주문번호

원하시는게 이게 맞는지 모르겠네요;
by 아이린 [2019.02.22 15:02:27]

랑에님 정말 감사합니다. 데이터는 맞는거 같습니다. 참조해서 쿼리 다시 짜봐야겠네요^^

by 마농 [2019.02.26 08:36:42]

제일 먼저 "가용재고총수량" 이 구해져야 그다음 단계를 진행할 텐데요.
"가용재고총수량"이 어디서 어떻게 나오는 건지?
13 은 960 이고, 11,21 은 2080 이라는 것은? 1 대 1 로 매칭되는 것도 아닌 이상한 기준이네요.

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