안녕하세요.
recursive 쿼리 관련하여 문의 드립니다.
아래와 같은 입력이 있을 경우 결과와 같이 구분하여 데이터를 만들고 싶은데 redshift로 옮겨야 하는 상황이라 recursive 쿼리로 변경을해야 하는데
고수님들의 도움이 필요하여 이렇게 글을 남깁니다.
참고로 redshift에서는 connect by가 지원되지 않아서 지원가능한 다른 쿼리로 전환이 필요합니다.
좋은 하루 되세요~
입력: SELECT 'A-B-C-D-E' as whole_itin FROM DUAL
결과:
A-B
B-C
C-D
D-E
A-C
A-D
A-E
B-D
B-E
C-E
WITH copy_t AS ( SELECT 1 lv FROM dual UNION ALL SELECT 2 FROM dual UNION ALL SELECT 3 FROM dual UNION ALL SELECT 4 FROM dual UNION ALL SELECT 5 FROM dual UNION ALL SELECT 6 FROM dual UNION ALL SELECT 7 FROM dual UNION ALL SELECT 8 FROM dual UNION ALL SELECT 9 FROM dual ) , tmp AS ( SELECT SUBSTR(whole_itin, lv * 4 - 3, 3) x FROM (SELECT 'ASR-LGG-ZAZ-JFK-ORD' whole_itin FROM dual) a , copy_t b WHERE lv <= (LENGTH(whole_itin) + 1) / 4 ) SELECT a.x ||'-'|| b.x x FROM tmp a , tmp b WHERE a.x < b.x ;
WITH copy_t AS ( SELECT 1 lv FROM dual UNION ALL SELECT 2 FROM dual UNION ALL SELECT 3 FROM dual UNION ALL SELECT 4 FROM dual UNION ALL SELECT 5 FROM dual UNION ALL SELECT 6 FROM dual UNION ALL SELECT 7 FROM dual UNION ALL SELECT 8 FROM dual UNION ALL SELECT 9 FROM dual ) , tmp AS ( SELECT lv , SUBSTR(whole_itin, lv * 4 - 3, 3) x FROM (SELECT 'ASR-LGG-ZAZ-JFK-ORD' whole_itin FROM dual) a , copy_t b WHERE lv <= (LENGTH(whole_itin) + 1) / 4 ) SELECT a.x ||'-'|| b.x x FROM tmp a , tmp b WHERE a.lv < b.lv ORDER BY SIGN(b.lv - a.lv - 1), a.lv, b.lv ;
WITH copy_t AS ( SELECT 1 lv FROM dual UNION ALL SELECT 2 FROM dual UNION ALL SELECT 3 FROM dual UNION ALL SELECT 4 FROM dual UNION ALL SELECT 5 FROM dual UNION ALL SELECT 6 FROM dual UNION ALL SELECT 7 FROM dual UNION ALL SELECT 8 FROM dual UNION ALL SELECT 9 FROM dual ) SELECT SUBSTR(a.whole_itin, b.lv * 4 - 3, 3) ||'-'|| SUBSTR(a.whole_itin, c.lv * 4 - 3, 3) x FROM (SELECT 'ASR-LGG-ZAZ-JFK-ORD' whole_itin FROM dual) a , copy_t b , copy_t c WHERE b.lv < c.lv AND c.lv <= (LENGTH(a.whole_itin) + 1) / 4 ORDER BY SIGN(c.lv - b.lv - 1), b.lv, c.lv ;