데이터베이스 질문과 답변
SELECT 'A' AS M, 'A' AS CODE, 5 AS WT, '20141001' DT FROM DUAL
UNION ALL SELECT 'A', 'B', 10, '20141006' FROM DUAL
UNION ALL SELECT 'B', 'A', 15, '20141009' FROM DUAL
UNION ALL SELECT 'B', 'B', 0, '20141015' FROM DUAL
UNION ALL SELECT 'B', 'C', 0, '20141021' FROM DUAL
UNION ALL SELECT 'B', 'D', 7, '20141028' FROM DUAL
;
위의 데이터를 아래와 같은 형태로 나타내고 싶습니다.
M컬럼을 그룹으로 구분하여 WT가 0보다 큰 행만 이전행의 DT와 비교하여
GAP만큼 행을 복제하고 WT를 GAP으로 나누어서 배분하고 싶습니다.
도움 부탁드립니다.
SELECT 'A' AS M, 'A' AS CODE, 5 AS WT, '20141001' DT FROM DUAL
UNION ALL SELECT 'A', 'B1', 2, '20141002' FROM DUAL
UNION ALL SELECT 'A', 'B2', 2, '20141003' FROM DUAL
UNION ALL SELECT 'A', 'B3', 2, '20141004' FROM DUAL
UNION ALL SELECT 'A', 'B4', 2, '20141005' FROM DUAL
UNION ALL SELECT 'A', 'B5', 2, '20141006' FROM DUAL
UNION ALL SELECT 'B', 'A', 15, '20141009' FROM DUAL
UNION ALL SELECT 'B', 'B', 0, '20141015' FROM DUAL
UNION ALL SELECT 'B', 'C', 0, '20141021' FROM DUAL
UNION ALL SELECT 'B', 'D1', 1, '20141022' FROM DUAL
UNION ALL SELECT 'B', 'D2', 1, '20141023' FROM DUAL
UNION ALL SELECT 'B', 'D3', 1, '20141024' FROM DUAL
UNION ALL SELECT 'B', 'D4', 1, '20141025' FROM DUAL
UNION ALL SELECT 'B', 'D5', 1, '20141026' FROM DUAL
UNION ALL SELECT 'B', 'D6', 1, '20141027' FROM DUAL
UNION ALL SELECT 'B', 'D7', 1, '20141028' FROM DUAL
;
WITH t AS ( SELECT 'A' m, 'A' code, 5 wt, '20141001' dt FROM dual UNION ALL SELECT 'A', 'B', 10, '20141006' FROM dual UNION ALL SELECT 'B', 'A', 15, '20141009' FROM dual UNION ALL SELECT 'B', 'B', 0, '20141015' FROM dual UNION ALL SELECT 'B', 'C', 0, '20141021' FROM dual UNION ALL SELECT 'B', 'D', 7, '20141028' FROM dual ) SELECT m , code , wt / NVL(edt - sdt, 1) wt , NVL(TO_CHAR(sdt + lv, 'yyyymmdd'), dt) dt FROM (SELECT m, code, wt, dt , TO_DATE(dt, 'yyyymmdd') edt , CASE WHEN wt > 0 THEN LAG(TO_DATE(dt, 'yyyymmdd')) OVER(PARTITION BY m ORDER BY dt) END sdt FROM t ) , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) WHERE lv <= NVL(edt - sdt, 1) ORDER BY m, dt, lv ;
역시~ 마농님이십니다.
단일 행복제는 해봤는데 조건별로 행복제를 할줄 몰라서 질문을 올린건데
WHERE 절에서 처리하면 되는군요.
오늘도 많이 배우고 갑니다. 댓글 감사드립니다^^
) 버튼을 클릭하여 작성 하시면 됩니다.