데이터베이스 질문과 답변
by 금린이 [SQL Query] Oracle 선입선출 [2021.04.12 17:14:26]
안녕하세요, 선입선출 관련 기존 게시글을 보고 공부하고 있는데 해결이 어려워 질문을 드립니다.
기존 들어온 금액을 모두 차감해야 다음 금액을 차감하는 테이블을 만드는 문제입니다.
IN/OUT도 기록이 따라가야 하는데, 고수님들의 의견 부탁드립니다...
(http://www.gurubee.net/article/65129 쿼리를 참고하여 공부중에 있습니다 ㅜㅜ)
아래는 데이터 형태와 결과물 입니다.
WITH TEMP_TABLE AS
(
SELECT '20210101' AS DT, 'IN' AS IO, 10000 AS AMT FROM DUAL UNION ALL
SELECT '20210102', 'IN', 5000 FROM DUAL UNION ALL
SELECT '20210103', 'IN', 8000 FROM DUAL UNION ALL
SELECT '20210104', 'IN', 2000 FROM DUAL UNION ALL
SELECT '20210105', 'OUT', -3000 FROM DUAL UNION ALL
SELECT '20210106', 'OUT', -5000 FROM DUAL UNION ALL
SELECT '20210107', 'OUT', -7000 FROM DUAL UNION ALL
SELECT '20210108', 'OUT', -3000 FROM DUAL UNION ALL
SELECT '20210109', 'OUT', -1000 FROM DUAL UNION ALL
SELECT '20210110', 'OUT', -1000 FROM DUAL
)
SELECT *
FROM TEMP_TABLE
;
데이터 테이블
| DT | IO | AMT |
| 20210101 | IN | 10000 |
| 20210102 | IN | 5000 |
| 20210103 | IN | 8000 |
| 20210104 | IN | 2000 |
| 20210105 | OUT | -3000 |
| 20210106 | OUT | -5000 |
| 20210107 | OUT | -7000 |
| 20210108 | OUT | -3000 |
| 20210109 | OUT | -1000 |
| 20210110 | OUT | -1000 |
결과출력물
| DT | IO | AMT | CAL_AMT |
| 20210101 | IN | 10000 | 10000 |
| 20210105 | OUT | -3000 | 7000 |
| 20210106 | OUT | -5000 | 2000 |
| 20210107 | OUT | -2000 | 0 |
| 20210102 | IN | 5000 | 5000 |
| 20210107 | OUT | -5000 | 0 |
| 20210103 | IN | -7000 | 8000 |
| 20210108 | OUT | -3000 | 5000 |
| 20210109 | OUT | -1000 | 4000 |
| 20210110 | OUT | -1000 | 3000 |
| (차후에 OUT이 들어오면 3000에 대하여 먼저 차감) | |||
| 20210104 | IN | 2000 | 2000 |
WITH TEMP_TABLE AS
(
SELECT 'IN' IO, '20210101' DT, 10000 QTY FROM DUAL
UNION ALL SELECT 'IN', '20210102', 5000 FROM DUAL
UNION ALL SELECT 'IN', '20210103', 8000 FROM DUAL
UNION ALL SELECT 'IN', '20210104', 2000 FROM DUAL
UNION ALL SELECT 'OUT', '20210105', 3000 FROM DUAL
UNION ALL SELECT 'OUT', '20210106', 5000 FROM DUAL
UNION ALL SELECT 'OUT', '20210107', 7000 FROM DUAL
UNION ALL SELECT 'OUT', '20210108', 3000 FROM DUAL
UNION ALL SELECT 'OUT', '20210109', 1000 FROM DUAL
UNION ALL SELECT 'OUT', '20210110', 1000 FROM DUAL
)
SELECT I.QTY AS IN_QTY
, O.QTY AS OUT_QTY
, I.IN_DT AS I_DT
, O.OUT_DT AS O_DT
, O.IO
, LEAST( I.QTY
, O.QTY
, I.S_QTY - O.S_QTY + O.QTY
, O.S_QTY - I.S_QTY + I.QTY
) AS QTY
FROM (SELECT DT AS IN_DT, QTY, IO
, SUM(QTY) OVER(ORDER BY DT) S_QTY
FROM TEMP_TABLE
WHERE IO = 'IN'
) I
, (SELECT DT AS OUT_DT, QTY, IO
, SUM(QTY) OVER(ORDER BY DT) S_QTY
FROM TEMP_TABLE
WHERE IO = 'OUT'
) O
WHERE I.S_QTY > O.S_QTY - O.QTY
AND O.S_QTY > I.S_QTY - I.QTY
ORDER BY I.IN_DT, O.OUT_DT
;
http://www.gurubee.net/article/65129의 댓글에 있는 쿼리를 참고해서 연구해보았는데요.
IN을 OUT위로 올리는게 어렵네요...
아오...Where 절에서 왜
WHERE I.S_QTY > O.S_QTY - O.QTY
AND O.S_QTY > I.S_QTY - I.QTY
이렇게 했는지 아직도 이해 못하는 1인...ㅠㅠ
입금내역을 누적했을 때
임금전 금액(i.s_amt - i.amt)과 입금후 금액(i.s_amt)을
구간정보(시작~종료) 라고 생각하면 됩니다.
입금 구간과 출금 구간이 겹치는지를 확인하는 것입니다.
구간 끼리의 조인은 시작과 종료를 서로 교차하여 비교합니다.
AND i.시작 < o.종료
AND i.종료 > o.시작
http://gurubee.net/article/45391 <- 구간검색
http://gurubee.net/lecture/2837 <- 입출금 배분
WITH temp_table AS
(
SELECT '20210101' dt, 'IN' io, 10000 amt FROM dual
UNION ALL SELECT '20210102', 'IN' , 5000 FROM dual
UNION ALL SELECT '20210103', 'IN' , 8000 FROM dual
UNION ALL SELECT '20210104', 'IN' , 2000 FROM dual
UNION ALL SELECT '20210105', 'OUT', -3000 FROM dual
UNION ALL SELECT '20210106', 'OUT', -5000 FROM dual
UNION ALL SELECT '20210107', 'OUT', -7000 FROM dual
UNION ALL SELECT '20210108', 'OUT', -3000 FROM dual
UNION ALL SELECT '20210109', 'OUT', -1000 FROM dual
UNION ALL SELECT '20210110', 'OUT', -1000 FROM dual
)
SELECT DECODE(io, 'IN', i_dt, dt) dt
, io
, amt
, DECODE(io, 'IN', amt
, SUM(amt) OVER(ORDER BY i_dt, dt) ) cal_amt
FROM (SELECT dt i_dt
, dt
, io
, amt
FROM temp_table
WHERE io = 'IN'
UNION ALL
SELECT i.dt i_dt
, o.dt
, o.io
, - LEAST( i.amt
, o.amt
, i.s_amt - o.s_amt + o.amt
, o.s_amt - i.s_amt + i.amt
) AS amt
FROM (SELECT dt, io
, -amt amt
, SUM(-amt) OVER(ORDER BY dt) s_amt
FROM temp_table
WHERE io = 'OUT'
) o
, (SELECT dt, io, amt
, SUM(amt) OVER(ORDER BY dt) s_amt
FROM temp_table
WHERE io = 'IN'
) i
WHERE i.s_amt > o.s_amt - o.amt
AND o.s_amt > i.s_amt - i.amt
)
;
저도 UNION으로 올렸는데 그게 답이었네요! 감사합니다 !
마농님..Where절을 왜
WHERE i.s_amt > o.s_amt - o.amt
AND o.s_amt > i.s_amt - i.amt
이렇게 하는지 그리고 LEAST 함수로 왜 가장 작은 값을 저렇게 가져오는지 설명 가능 하신지요?
제가 산수도 못하나 봅니다..ㅠㅠ
----------------------------
아 위에 답글 주셨네요..감사합니다...꾸벅...ㅠㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.