데이터베이스 질문과 답변
기초 입니다. 로직을 더 간단하게 정리할 수 있을 것 같은데 도움 부탁드립니당
0
3
3,311
by 새싹 [Oracle 기초] 오라클 SQL [2023.11.12 03:26:59]
여러 케이스를 접해보느라 공부중입니당
뭔가 로직을 간추릴 수 있을것같은데.. 정리가 안되어 도움 요청드려봅니당
테이블 EXP - 예상
| 제품 | 날짜 | 수량 |
| A | 20231106 | 50 |
| A | 20231107 | 50 |
| A | 20231109 | 50 |
테이블 RES - 판매결과
| 제품 | 날짜 | 수량 |
| A | 20231106 | 20 |
| A | 20231107 | 20 |
날짜별 예상 테이블과 결과 테이블이 있고,
날짜 구간동안 예상 수량만큼 판매하지 못했다면,
(예상수량 - 판매수량) 을 다음 예상이 있는 날짜에
더해주는 방식 입니다.
아래 결과테이블을 보면 다음 예상이 있는 날짜인 9일에
50 + 60 ( 6일 7일에 채우지 못했던 판매 갯수) = 110 이 됩니다.
원하는결과
| 제품 | 날짜 | 수량 |
| A | 20231106 | 50 |
| A | 20231107 | 50 |
| A | 20231109 | 110 |
로직을 아래처럼 짜봤습니다..
예상테이블을 3번이나 써서 로직을 짜놓으니.. 뭔가 줄일수있을것같은데 정리가 어렵습니다.. 가능한방법이 있을까요
WITH EXP AS (
SELECT 'A' 제품, '50' 수량, '20231106' 날짜 FROM dual
UNION ALL SELECT 'A' 제품, '50' 수량, '20231107' 날짜 FROM dual
UNION ALL SELECT 'A' 제품, '50' 수량, '20231109' 날짜 FROM dual;
)
, RES AS (
SELECT 'A' 제품, '20' 수량, '20231106' 날짜 FROM dual
UNION ALL SELECT 'A' 제품, '20' 수량, '20231107' 날짜 FROM dual
)
SELECT A.제품, A.날짜, NVL(B.수량,A.수량) , A.수량 원래수량, B.잔여수량 부족수량
FROM EXP A,
(
SELECT 제품, 날짜, (수량 + 잔여수량) 수량, 잔여수량
FROM (
SELECT A.제품, A.날짜, A.수량, B.RES수량, B.잔여수량
, ROW_NUMBER() OVER(PARTITION BY A.제품 ORDER BY A.날짜) RNK
FROM EXP A
,(
SELECT A.제품, B.날짜, RES수량, (EXP수량 - RES수량) 잔여수량
FROM (
SELECT 제품, MAX(날짜) 날짜, SUM(수량) EXP수량
FROM EXP
WHERE 날짜 BETWEEN '20231106' AND '20231107'
GROUP BY 제품
) A
,(
( SELECT 제품, MAX(날짜) 날짜, SUM(수량) RES수량
FROM RES
WHERE 날짜 BETWEEN '20231106' AND '20231107'
GROUP BY 제품
)
) B
) B
WHERE A.날짜 > B.날짜
)
WHERE RNK = 1
) B
WHERE A.제품 = B.제품(+)
AND A.날짜 = B.날짜(+)
by 마농
[2023.11.13 00:30:24]
WITH exp AS
(
SELECT 'A' 제품, 50 수량, '20231106' 날짜 FROM dual
UNION ALL SELECT 'A', 50, '20231107' FROM dual
UNION ALL SELECT 'A', 50, '20231109' FROM dual
)
, res AS
(
SELECT 'A' 제품, 20 수량, '20231106' 날짜 FROM dual
UNION ALL SELECT 'A', 20, '20231107' FROM dual
)
SELECT a.제품
, a.날짜
, CASE WHEN a.날짜 > '20231107'
THEN SUM(a.수량 - NVL(b.수량, 0)) OVER(PARTITION BY a.제품)
ELSE a.수량 END 수량
FROM exp a
LEFT OUTER JOIN res b
ON a.제품 = b.제품
AND a.날짜 = b.날짜
AND b.날짜 <= '20231107'
WHERE a.날짜 >= '20231106'
AND a.날짜 <= (SELECT MIN(날짜) FROM exp WHERE 날짜 > '20231107')
;
by 새싹
[2023.11.13 02:47:54]
항상 감사합니다 마농님 ^^
만약 EXP 테이블이 다수의 데이터를 가졌고, 결과 계획에 추가된 갯수와, 그 이후 날짜들의 데이터들도
이전 계획대로 데이터가 다 나오게 하려면 지금 예시로 주신 SUM OVER 말고 다른방법을 사용해야하지요?
테스트하다보니 그 후에도 SUM OVER로 계속 누적추가되는것같아서 해보는중입니당
| A | 20231106 | 50 |
| A | 20231107 | 50 |
| A | 20231109 | 110 |
| A | 20231110 | 50 |
| A | 20231111 | 50 |
| A | 20231112 | 50 |
| A | 20231113 | 50 |
WITH exp AS
(
SELECT 'A' 제품, 50 수량, '20231106' 날짜 FROM dual
UNION ALL SELECT 'A', 50, '20231107' FROM dual
UNION ALL SELECT 'A', 50, '20231109' FROM dual
UNION ALL SELECT 'A', 50, '20231110' FROM DUAL
UNION ALL SELECT 'A', 50, '20231111' FROM DUAL
UNION ALL SELECT 'A', 50, '20231112' FROM DUAL
UNION ALL SELECT 'A', 50, '20231113' FROM DUAL
)
, res AS
(
SELECT 'A' 제품, 20 수량, '20231106' 날짜 FROM dual
UNION ALL SELECT 'A', 20, '20231107' FROM dual
)
SELECT a.제품
, a.날짜
, CASE WHEN a.날짜 > '20231107'
THEN SUM(a.수량 - NVL(b.수량, 0)) OVER(ORDER BY A.날짜)
ELSE a.수량 END 수량
FROM exp a, res b
WHERE a.제품 = b.제품(+)
AND a.날짜 = b.날짜(+)
AND a.날짜 >= '20231106'
-- AND a.날짜 <= (SELECT MIN(날짜) FROM exp WHERE 날짜 > '20231107')
by 마농
[2023.11.13 08:27:22]
WITH exp AS
(
SELECT 'A' 제품, 50 수량, '20231106' 날짜 FROM dual
UNION ALL SELECT 'A', 50, '20231107' FROM dual
UNION ALL SELECT 'A', 50, '20231109' FROM dual
UNION ALL SELECT 'A', 50, '20231110' FROM dual
UNION ALL SELECT 'A', 50, '20231111' FROM dual
UNION ALL SELECT 'A', 50, '20231112' FROM dual
UNION ALL SELECT 'A', 50, '20231113' FROM dual
)
, res AS
(
SELECT 'A' 제품, 20 수량, '20231106' 날짜 FROM dual
UNION ALL SELECT 'A', 20, '20231107' FROM dual
)
SELECT a.제품
, a.날짜
, CASE WHEN LAG(a.날짜) OVER(PARTITION BY a.제품 ORDER BY a.날짜) = '20231107'
THEN SUM(a.수량 - NVL(b.수량, 0)) OVER(PARTITION BY a.제품 ORDER BY a.날짜)
ELSE a.수량 END 수량
FROM exp a
LEFT OUTER JOIN res b
ON a.제품 = b.제품
AND a.날짜 = b.날짜
AND b.날짜 <= '20231107'
WHERE a.날짜 >= '20231106'
;
) 버튼을 클릭하여 작성 하시면 됩니다.