데이터베이스 질문과 답변
with t(dt, tp, onhand, qty) as
(
select '20170115', 'IN' , 100, 110 from dual union all
select '20170116', 'OUT', 100, -50 from dual union all
select '20170208', 'IN' , 100, 30 from dual union all
select '20170225', 'OUT', 100, -50 from dual union all
select '20170228', 'OUT', 100, -33 from dual union all
select '20170313', 'IN' , 100, 15 from dual union all
select '20170323', 'OUT', 100, -7 from dual
)
select dt
,qty
,onhand + sum(qty) over(order by dt, qty desc) ov
from t
order by dt
조회결과를 보시면 아래와같이 나옵니다.
DT QTY ONHAND OV
20170115 110 100 210
20170116 -50 100 160
20170208 30 100 190
20170225 -50 100 140
20170228 -33 100 107
20170313 15 100 122
20170323 -7 100 115
이것은 누적으로 표현된 것이고, 여기서부터 막히게 되었습니다.
마지막라인이 최종적으로 남은 수량이 표시되는데요.
최종적으로 아래표처럼 남은 수량의 라인들만 보이게 하고 싶습니다.
설명을드리면, 기존수량이 100개가 있고, 일자별로 수량이 들어가고 나옵니다.
선입선출 개념으로 기존수량 100개 부터 먼저 빠져나가고,
100개가 전부 빠지게되면, 다음차례로 1/15일 들어온 수량이 빠져나가는 식 입니다.
쿼리로 구현이 가능할까요?
부족한내용은 댓글로 추가하겠습니다. 읽어주셔서 감사합니다.
DT QTY
20170115 70
20170208 30
20170313 15
이해력이 떨어져서 그런지 아래 표가 어떻게 출력되는 건지 잘 모르겠네요.
1월 15일 70은 어떻게 계산되어서 나오는 거죠?
다시보니 제가 설명을 잘못 올렸네요. 죄송합니다. 다시 덧글로 설명 추가해놓겠습니다!!
모바일로 작성할수밖에 없어서 양해부탁드려요.
[ 기준쿼리 ]
with t(dt, onhand, qty) as
(
select '20170115', 30, 110 from dual union all
select '20170116', 30, -50 from dual union all
select '20170208', 30, 30 from dual union all
select '20170225', 30, -50 from dual union all
select '20170228', 30, -33 from dual union all
select '20170313', 30, 15 from dual union all
select '20170323', 30, -7 from dual
)
select dt
,onhand
,qty
from t
order by dt
[표설명]
발생일자 발생수량 최종수량 기존수량 기존수량+발생수량 선입선출 수량변화
DT QTY ONHAND T1 T2
20170115 110 30 15 125
20170116 -50 30 125 75 기존수량 15 소진 || 1/15 잔량 75
20170208 30 30 75 105 01/15 잔량 75 || 02/08 잔량 30
20170225 -50 30 105 55 01/15 잔량 25 || 02/08 잔량 30
20170228 -33 30 55 22 01/15 소진 25 || 02/08 잔량 22
20170313 15 30 22 37 02/08 잔량 22 || 03/13 잔량 15
20170323 -7 30 37 30 02/08 잔량 15 || 03/13 잔량 15
T1,2 는 흐름을 보여드리기 위해 작성한 부분입니다.
여기서 최종수량 30개를 역산해보면 쿼리에서 표현되지 않은 1월이전의 15개 수량이 존재하게 됩니다.
이 수량까지 감안해서 선입선출 개념을 적용해 기존수량을 순차적으로 빼면서
발생일자 기준으로 남아있는 수량을 표현하고 싶습니다.
[ 최종결과물 ]
DT QTY
20170208 15
20170313 15
최종수량이 별개의 라인으로 나오는게 편하시다면 이걸로 써도 될거 같습니다.
with t(dt, qty, remark) as
(
select '20170115', 110, '' from dual union all
select '20170116', -50, '' from dual union all
select '20170208', 30, '' from dual union all
select '20170225', -50, '' from dual union all
select '20170228', -33, '' from dual union all
select '20170313', 15, '' from dual union all
select '20170323', -7, '' from dual union all
select '49991231', 30, '최종수량' from dual
)
select dt
,qty
,remark
from t
order by dt
댓글 읽어보니 무지 어려운 문제군요; 제 능력으로 풀 수 있을지 모르겠네요.
아닙니다... 봐주시는 것만으로도 감사합니다.
저는 엄두도 못내고 있습니다..
프로시저로 한다해도 고민이네요..
흠.. 업무 중간 중간 생각해보긴하는데 어렵네요. 머리가 안 좋아서.. 아.. 마농님은 오늘 휴가신가;;
나도 머리가 나빠진건가.. 이해가 힘듬... ㅡㅡ;
WITH
PSI(DT, TP, ONH_QTY, QTY) AS
(
SELECT '20170115', 'P', 30, 110 FROM DUAL
UNION ALL SELECT '20170116', 'S', 30, 50 FROM DUAL
UNION ALL SELECT '20170208', 'P', 30, 30 FROM DUAL
UNION ALL SELECT '20170225', 'S', 30, 50 FROM DUAL
UNION ALL SELECT '20170228', 'S', 30, 33 FROM DUAL
UNION ALL SELECT '20170313', 'P', 30, 15 FROM DUAL
UNION ALL SELECT '20170323', 'S', 30, 7 FROM DUAL
)
SELECT A.GR_DT
, A.GR_QTY - SUM(LEAST(NVL(LA.GI_EDQTY-A.GR_STQTY,0), NVL(A.GR_EDQTY-LA.GI_STQTY,0), A.GR_QTY, NVL(LA.GI_QTY,0))) AS REM_QTY
FROM (
-- "입고 (일자별 입고수량 범위)"
SELECT A.DT AS GR_DT, A.QTY AS GR_QTY
, SUM(A.QTY) OVER (ORDER BY A.DT) - A.QTY AS GR_STQTY
, SUM(A.QTY) OVER (ORDER BY A.DT) AS GR_EDQTY
FROM (
-- 추가 입고분
SELECT '00000000' AS DT, 'I' AS TP, A.ONH_QTY - SUM(DECODE(A.TP,'P',A.QTY,'S',-A.QTY)) AS QTY
FROM PSI A
GROUP BY A.ONH_QTY
UNION ALL
-- 입고분
SELECT A.DT, A.TP, A.QTY
FROM PSI A
WHERE A.TP = 'P'
) A
) A
LEFT OUTER JOIN (
-- "출고 (일자별 출고수량 범위)"
SELECT A.DT AS GI_DT, A.QTY AS GI_QTY
, SUM(A.QTY) OVER (ORDER BY A.DT) - A.QTY AS GI_STQTY
, SUM(A.QTY) OVER (ORDER BY A.DT) AS GI_EDQTY
FROM PSI A
WHERE A.TP = 'S'
) LA
ON A.GR_STQTY < LA.GI_EDQTY
AND A.GR_EDQTY > LA.GI_STQTY
GROUP BY A.GR_DT, A.GR_QTY
ORDER BY 1
;
와~~ 간단하게 처리되네요-0-!
내공이 상당하신듯~
쿼리 분석 좀 해봐야겠네요^^
--------------------------------------------------------------------------------------------
아.. 분석하다보니 예전에 마농님도 입출고에 대한 비슷한 쿼리를 만드셨던 것 같은데..
그때도 이해가 잘 안 되어서 보다가 퇴근했던 것 같네요;
안 좋은 머리로 이해하기란 참 어렵습니다. 한계인가... 털썩 OTL..
계속 봐도 잘 이해가...
조인절이 날짜 기간 탐색 원리인 것 같은데..
이전입고량~현재입고량, 이전출고량~현재출고량 교차 비교..
재고량에서 왜 이게 탐색이 되는지 이해가 잘...;;
설명해주실 분 없나요?ㅠㅠ
아~ 그림 그려놓고 보니 이해가 되었어요.ㅎㅎ
기간 탐색이랑 원리가 똑같네요.
저는 자꾸 날짜하고 연관해서 생각하다보니 이해를 못한 거네요.
그냥 총량으로 봤을 때
재고량 증가 구간 별로 출고가 이뤄지니 출고가 어느 구간에 속하는지만 탐색하면 되었던 거네요.
흑ㅠ.. 세상엔 참 똑똑하신 분들이 많습니다.
-- 윗분들 말씀처럼 제가 문제를 잘 이해한지 모르겠지만
-- 마농님 부재를 틈타 올만에 급히 한번.
with t# (dt, onhand, qty) as
(
select '20170115', 30, 110 from dual union all
select '20170116', 30, -50 from dual union all
select '20170208', 30, 30 from dual union all
select '20170225', 30, -50 from dual union all
select '20170228', 30, -33 from dual union all
select '20170313', 30, 15 from dual union all
select '20170323', 30, -7 from dual
)
,t (dt, qty) as
(
select '11111111', max(onhand)-sum(qty) from t#
union all
select dt, qty from t#
)
select dt
,decode(row_number() over (order by dt),1,o_t_qty + i_s_qty,qty) qty
from
(
select dt
,qty
,sum(case when qty < 0 then qty end) over() o_t_qty
,sum(case when qty > 0 then qty end) over(order by dt) i_s_qty
from t
)
where o_t_qty + i_s_qty > 0
and qty > 0
;
이건 잘 이해가 되네요ㅋㅋ
저도 이 방법으로 풀었는데..
재고량이 총사용량보다 커지는 순간부터 출력하면 되겠다 싶었거든요.
2월 8일 남은 재고 15를 어떻게 표시해야 되나 고민했는데
백면서생님 쿼리보니 너무 생각을 많이 했네요ㅋㅋ
댓글 달아주신 분들께 감사드립니다!!
예제라서 실테이블에 적용도 해야봐야하는데
속도가 잘 나왔으면 좋겠어요!!
다시한번 너무 감사드립니다!
WITH t(dt, tp, onhand, qty) AS
(
SELECT '20170115', 'IN' , 100, 110 FROM dual
UNION ALL SELECT '20170116', 'OUT', 100, -50 FROM dual
UNION ALL SELECT '20170208', 'IN' , 100, 30 FROM dual
UNION ALL SELECT '20170225', 'OUT', 100, -50 FROM dual
UNION ALL SELECT '20170228', 'OUT', 100, -33 FROM dual
UNION ALL SELECT '20170313', 'IN' , 100, 15 FROM dual
UNION ALL SELECT '20170323', 'OUT', 100, -7 FROM dual
)
, t1 AS
(
SELECT dt, tp, onhand
, DECODE(ROW_NUMBER() OVER(ORDER BY dt), 1, onhand, 0) + ABS(qty) qty
, DECODE(tp, 'IN', onhand, 0) + SUM(ABS(qty)) OVER(PARTITION BY tp ORDER BY dt) qty_s
FROM t
)
SELECT a.dt
, a.qty
- NVL(SUM(LEAST(a.qty, b.qty, b.qty_s - a.qty_s + a.qty, a.qty_s - b.qty_s + b.qty)), 0) r_qty
FROM t1 a
LEFT OUTER JOIN t1 b
ON a.qty_s - a.qty < b.qty_s
AND b.qty_s - b.qty < a.qty_s
AND b.tp = 'OUT'
WHERE a.tp = 'IN'
GROUP BY a.dt, a.qty
ORDER BY dt
;
마농님 댓글 감사드립니다.!
이부분도 참고하여 확인해보겠습니다!
어쩐지 본 거 같더라니.. 퀴즈에 있었군요. 복습해야겠네요ㅎㅎ
-- 마농님. 재고 합이 100이 되어야 할듯요.^^
(아래 마농님 댓글 후 댓글이 꼬여서 기존글 수정으로.^^)
인식의 차이 였군요. 저는 onhand를 현 재고량으로 생각하고 기존 재고는 역산으로 가져온다고 생각해서.^^
백면서생님. 기존재고(onhand) 100 에 이후 입출고 내역을 기준으로 작성한 쿼리입니다.
저는 기존재고 100 을 최초입고(20170115)에 포함시켜서 계산했습니다.
제가 이해한 바로는 문제 없는걸로 보입니다.
댓글 순서가 꼬이는군요.
저는 최초 질문의 예시로 풀었는데... (기존수량 100)
댓글의 수정 질문의 예는 다른 예시였네요. (최종수량 30)
실제 데이터에 준해서 질문을 해주셔야 할 듯 하네요.
아마도 최종인지? 기존인지 수량은 컬럼 형태가 아닌 별도 행이나 별도 테이블로 존재할 듯 하구요.
최종 재고로 기존 재고를 역산하는 개념이 실제로 적용되는 개념인지 의문이구요.
보통 특정 시점으로 재고 정산하고, 그시점 이후부터 다시 계산하지 않을까? 싶네요.
최종수량(30)을 기준으로 일자별 남은수량 가져오는 경우라면.
기존 잔여수량(15)를 역산하거나 입출고를 짜맞출 필요 없이
입고내역만 가지고 최근일자로부터 최종수량(30)만 채우면 될 듯.
WITH t(dt, qty, remark) AS
(
SELECT '20170115', 110, '' FROM dual UNION ALL
SELECT '20170116', -50, '' FROM dual UNION ALL
SELECT '20170208', 30, '' FROM dual UNION ALL
SELECT '20170225', -50, '' FROM dual UNION ALL
SELECT '20170228', -33, '' FROM dual UNION ALL
SELECT '20170313', 15, '' FROM dual UNION ALL
SELECT '20170323', -7, '' FROM dual UNION ALL
SELECT '49991231', 30, '최종수량' FROM dual
)
SELECT dt
, CASE WHEN s_qty > r_qty THEN r_qty - s_qty + qty ELSE qty END qty
FROM (SELECT dt, qty
, SUM(DECODE(remark, '', qty)) OVER(ORDER BY dt DESC) s_qty
, SUM(DECODE(remark, '최종수량', qty)) OVER() r_qty
FROM t
WHERE qty > 0
)
WHERE s_qty - qty < r_qty
ORDER BY dt
;
) 버튼을 클릭하여 작성 하시면 됩니다.