안녕하세요.
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를 주면 해당 날짜의 위 표의 자료로 풀리면 됩니다.
미리 감사드립니다.
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 ) ;