데이터베이스 질문과 답변
문자열 파싱관련 재문의 드립니다.
2
by 도겸아빠 [2018.10.18 12:18:55]
안녕하세요.
SELECT '20181001' AS DT, 'AA0288' AS FLT, 'ORD/YHZ/ANC/ICN' AS STR FROM DUAL UNION ALL SELECT '20181001' AS DT, 'BB0288' AS FLT, 'ICN/NRT' AS STR FROM DUAL UNION ALL SELECT '20181001' AS DT, 'CC0288' AS FLT, 'ICN/NRT/LAX' AS STR FROM DUAL
샘플 자료가 위와 같을때 아래와 같이 자료를 풀고 싶습니다.
| DT | FLT | SEQ | ORG | DEP |
| 20181001 | AA0288 | 1 | ORD | YHZ |
| 20181001 | AA0288 | 2 | YHZ | ANC |
| 20181001 | AA0288 | 3 | ANC | ICN |
| 20181001 | AA0288 | 4 | ORD | ANC |
| 20181001 | AA0288 | 5 | ORD | ICN |
| 20181001 | AA0288 | 6 | YHZ | ICN |
| 20181001 | BB0288 | 1 | ICN | NRT |
| 20181001 | CC0288 | 1 | ICN | NRT |
| 20181001 | CC0288 | 2 | NRT | LAX |
| 20181001 | CC0288 | 3 | ICN | LAX |
* SEQ는 되도록이면 아래 그림과 같이 부여되면 좋겠지만 힘들면 유니크하게만 만들어지면 됩니다.
* 조건은 DT를 주면 해당 날짜의 위 표의 자료로 풀리면 됩니다.
미리 감사드립니다.
by 마농
[2018.10.18 13:18:10]
WITH t AS
(
SELECT '20181001' dt, 'AA0288' flt, 'ORD/YHZ/ANC/ICN' str FROM dual
UNION ALL SELECT '20181001', 'BB0288', 'ICN/NRT' FROM dual
UNION ALL SELECT '20181001', 'CC0288', 'ICN/NRT/LAX' FROM dual
)
SELECT dt, flt
, ROW_NUMBER() OVER(PARTITION BY dt, flt ORDER BY s1, s2) seq
, org, dep
FROM (SELECT dt, flt
, CONNECT_BY_ROOT(org) org
, dep
, DECODE(LEVEL, 1, 1, 2) s1
, SYS_CONNECT_BY_PATH(lv, '-') s2
FROM (SELECT dt, flt, lv
, REGEXP_SUBSTR(str, '[^/]+', 1, lv ) org
, REGEXP_SUBSTR(str, '[^/]+', 1, lv + 1) dep
FROM t
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9)
WHERE lv <= REGEXP_COUNT(str, '/')
AND dt = '20181001'
)
CONNECT BY PRIOR dt = dt
AND PRIOR flt = flt
AND PRIOR lv = lv - 1
)
;
WITH t AS
(
SELECT '20181001' dt, 'AA0288' flt, 'ORD/YHZ/ANC/ICN' str FROM dual
UNION ALL SELECT '20181001', 'BB0288', 'ICN/NRT' FROM dual
UNION ALL SELECT '20181001', 'CC0288', 'ICN/NRT/LAX' FROM dual
)
SELECT dt, flt
, ROW_NUMBER() OVER(PARTITION BY dt, flt ORDER BY s1, s2, s3) seq
, org, dep
FROM (SELECT dt, flt
, PRIOR dep org
, dep
, DECODE(PRIOR lv + 1, lv, 1, 2) s1
, PRIOR lv s2
, lv s3
FROM (SELECT dt, flt, lv
, REGEXP_SUBSTR(str, '[^/]+', 1, lv) dep
FROM t
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9)
WHERE lv <= REGEXP_COUNT(str, '[^/]+')
AND dt = '20181001'
)
WHERE LEVEL = 2
CONNECT BY PRIOR dt = dt
AND PRIOR flt = flt
AND PRIOR lv < lv
AND LEVEL = 2
)
;
by 도겸아빠
[2018.10.18 13:26:02]
와~~ 놀랍습니다.
프로그램에 적용해 보겠습니다.
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.