데이터베이스 질문과 답변
안녕하세요,
쿼리 - 값 rank와 2개 컬럼정보를 하나로 합치고 싶을 때 질문입니다.
TABLE1
| key_code | created_ymd |
| A1 | 20200501 |
| A2 | 20200401 |
TABLE2
| base_ymd | seq | id_before | id_after | key_code |
| 20200601 | 01 | AAA | BBB | A1 |
| 20200601 | 2 | BBB | CCC | A1 |
| 20200605 | 01 | CCC | DDD | A1 |
| 20200605 | 01 | YYY | ZZZ | A2 |
위와 같은 테이블이 있는데 다음 형태로 변환하고 싶습니다.
1. 조건은 key_code가 동일한 경우 base_ym과 seq를 합쳐서 새로운 time seq생성, 이때 seq는 자릿수 감안하여 2자리로 (2 ->02)
2. key_code가 동일한 경우 1번에서 생성된 새로운 time seq 기반으로 가장 오래된 row 찾기
3.가장 오래된 row의 id_before와 그 외 모든 id_after를 모아서 id 컬럼을 생성하기, 이 때 가장 오래된 row의 id_before date는 TABLE1의 created_ymd를 가져오기
참고로 현재 seq는 같은 key_code와 같은 base_ymd인 경우 01 -> 02 -> 03.. 순으로 생성되나 02가 2로 잘못생성되어있는 상황
예상 output 형태
| base_ymd | time seq | id | key_code |
| 20200501 | 01 | AAA | A1 |
| 20200601 | 02 | BBB | A1 |
| 20200601 | 03 | CCC | A1 |
| 20200605 | 04 | DDD | A1 |
| 20200401 | 01 | YYY | A2 |
| 20200605 | 02 | ZZZ | A2 |
초보라서 모르는게 너무 많네요ㅡㅜ
도움을 부탁드리며, 미리 감사드립니다!
by 마농
[2020.07.01 08:40:48]
테이블 설계가 좀 이상한데요.
애초에 table1 에 최초 id 컬럼이 있었어야 하지 않나요?
아니면 table1 의 정보를 table2 에 합칠 수도 있을 것 같네요.
WITH table1 AS
(
SELECT 'A1' key_code, '20200501' created_ymd FROM dual
UNION ALL SELECT 'A2', '20200401' FROM dual
)
, table2 AS
(
SELECT '20200601' base_ymd, '01' seq, 'AAA' id_before, 'BBB' id_after, 'A1' key_code FROM dual
UNION ALL SELECT '20200601', '2' , 'BBB', 'CCC', 'A1' FROM dual
UNION ALL SELECT '20200605', '01', 'CCC', 'DDD', 'A1' FROM dual
UNION ALL SELECT '20200605', '01', 'YYY', 'ZZZ', 'A2' FROM dual
)
, tmp AS
(
SELECT a.created_ymd base_ymd
, '' id_before
, MIN(b.id_before) KEEP(DENSE_RANK FIRST ORDER BY b.base_ymd, LPAD(seq, 2, '0')) id_after
, a.key_code
FROM table1 a
, table2 b
WHERE a.key_code = b.key_code
GROUP BY a.key_code, a.created_ymd
UNION ALL
SELECT base_ymd
, id_before
, id_after
, key_code
FROM table2
)
SELECT base_ymd
, LPAD(LEVEL, 2, '0') time_seq
, id_after id
, key_code
FROM tmp
START WITH id_before IS NULL
CONNECT BY PRIOR key_code = key_code
AND PRIOR id_after = id_before
;
by 춘
[2020.07.01 14:19:48]
WITH t1 AS
(
SELECT 'A1' key_code, '20200501' created_ymd FROM dual
UNION ALL SELECT 'A2', '20200401' FROM dual
)
, t2 AS
(
SELECT '20200601' base_ymd, '01' seq, 'AAA' id_before, 'BBB' id_after, 'A1' key_code FROM dual
UNION ALL SELECT '20200601', '2' , 'BBB', 'CCC', 'A1' FROM dual
UNION ALL SELECT '20200605', '01', 'CCC', 'DDD', 'A1' FROM dual
UNION ALL SELECT '20200605', '01', 'YYY', 'ZZZ', 'A2' FROM dual
)
SELECT
created_ymd
, LPAD(ROW_NUMBER() OVER (PARTITION BY key_code ORDER BY null), 2, 0) AS time_seq
, DECODE(id_before, NULL, NVL(id_before, LEAD(id_before) OVER (ORDER BY NULL)), id_after) AS id
, key_code
FROM
(
SELECT
key_code
, created_ymd
, '' AS id_before
, '' AS id_after
, '' AS seq
FROM t1
UNION ALL
SELECT
key_code
, base_ymd
, id_before
, id_after
, seq
FROM t2
)
ORDER BY key_code, created_ymd
;
by 둥둥맨
[2020.07.01 17:00:16]
table1에는 ID info 없이 저장이 되어 있어서 제가 봐도 현재구조가 이상하긴 합니다. 고맙습니다!
) 버튼을 클릭하여 작성 하시면 됩니다.