데이터베이스 질문과 답변
인터넷을 찾아보니 한 컬럼에 ,(콤마)나 |(버티컬) 등의 구분자로 묶여져 있는 값을
REGEXP_SUBSTR 함수를 사용하여 행으로 분리할 수 있다는 걸 알앗습니다.
예) COL1 값 : APPLE|ORANGE|GRAPE|BANANA
SELECT REGEXP_SUBSTR(COL1, '[^|]+', 1, LEVEL) AS RSLT
FROM (SELECT 'APPLE|ORANGE|GRAPE|BANANA' AS COL1
FROM DUAL) X
CONNECT BY REGEXP_SUBSTR(COL1, '[^|]+', 1, LEVEL) IS NOT NULL;
RSLT
----------
APPLE
ORANGE
GRAPE
BANANA
이렇게 결과가 나오는데요...
이 쿼리문은 한 컬럼에만 적용되는 듯 하더라고요..
제가 궁금한 것은 여러 컬럼에도 적용되는가 하는 것입니다
만약, NO, COL1, COL2, COL3 컬럼에 각각 이렇게 들어있다고 가정했을 때,
NO COL1 COL2 COL3
------------------------------------------------
1 APPLE|BANANA ORANGE|GRAPE PEACH|KIWI
이것을 아래와 같이 나오게 쿼리문을 작성할 수 있을까요?
NO COL1 COL2 COL3
------------------------------------
1 APPLE GRAPE KIWI
1 APPLE GRAPE PEACH
1 APPLE ORANGE KIWI
1 APPLE ORANGE PEACH
1 BANANA GRAPE KIWI
1 BANANA GRAPE PEACH
1 BANANA ORANGE KIWI
1 BANANA ORANGE PEACH
아래 쿼리는 제가 고민(?)한 끝에 만들어 본 쿼리입니다.
결과는 제대로 나오는데, 실 데이터에 적용해 보면 속도가 너무 느리네요 ㅠ
무언가 개선이 필요해 보이는데, 어떻게 접근해야 좋을지 감이 안 잡힙니다.
SELECT REGEXP_SUBSTR(COL1, '[^|]+', 1, B.LV) AS RSLT1
, RSLT2
, RSLT3
FROM (SELECT COL1
, REGEXP_SUBSTR(COL2, '[^|]+', 1, B.LV) AS RSLT2
, RSLT3
FROM (SELECT COL1
, COL2
, REGEXP_SUBSTR(COL3, '[^|]+', 1, B.LV) AS RSLT3
FROM (SELECT 'APPLE|BANANA' AS COL1
, 'ORANGE|GRAPE' AS COL2
, 'PEACH|KIWI' AS COL3
FROM DUAL
)X1
,(SELECT LEVEL AS LV
FROM DUAL
CONNECT BY LEVEL <= 2
)B
WHERE REGEXP_SUBSTR(COL3, '[^|]+', 1, B.LV) IS NOT NULL
)X2
,(SELECT LEVEL AS LV
FROM DUAL
CONNECT BY LEVEL <= 2
)B
WHERE REGEXP_SUBSTR(COL2, '[^|]+', 1, B.LV) IS NOT NULL
)X3
,(SELECT LEVEL AS LV
FROM DUAL
CONNECT BY LEVEL <= 2
)B
WHERE REGEXP_SUBSTR(COL1, '[^|]+', 1, B.LV) IS NOT NULL
ORDER BY 1, 2, 3 -- 결과를 보기 좋게 하기 위해 사용하였으며 굳이 없어도 되는 부분
;
제가 봐도 너무 절차식으로 짠 쿼리로 보이네요 ㅠ
도움주실 분에게 미리 감사의 말씀 드립니다...
by 마농
[2023.02.13 09:00:07]
느리다고 하셨는데?
그대로 복사해 실행해 보면 느리지 않습니다.
dual 을 이용한 1건짜리 자료에 대한 쿼리가 느릴리가 없습니다.
만약 느리다면? 실제 수행쿼리가 이게 아닐 듯 한데요?
dual 을 이용한 1건 처리가 아닌 여러건의 테이블에 수행하는게 아닐런지요?
실 사용 쿼리(느린 쿼리)를 보여주세요.
by 마농
[2023.02.13 09:05:05]
WITH t AS
(
SELECT 1 no, 'APPLE|BANANA' col1, 'ORANGE|GRAPE' col2, 'PEACH|KIWI' col3 FROM dual
UNION ALL SELECT 2, 'APPLE|BANANA', 'ORANGE|GRAPE', 'PEACH|KIWI' FROM dual
)
SELECT no
, REGEXP_SUBSTR(col1, '[^|]+', 1, x) c1
, REGEXP_SUBSTR(col2, '[^|]+', 1, y) c2
, REGEXP_SUBSTR(col3, '[^|]+', 1, z) c3
FROM t
, (SELECT LEVEL lv
, CEIL(LEVEL / 4) x
, MOD(CEIL(LEVEL / 2)-1, 2) + 1 y
, MOD(LEVEL-1, 2) + 1 z
FROM dual
CONNECT BY LEVEL <= 8
)
ORDER BY no, lv
;
by 에모토
[2023.02.13 14:42:18]
감사합니다 마농님 덕분에 잘 해결되었습니다. 우선은 실 사용 쿼리를 올리지 않은 상태로 질문을 남겨서 죄송합니다. 데이터도 그렇고 실 쿼리를 올리기 좀 그런 사정이 있어서 개념만 파악하고자 제가 만든 쿼리를 올렸습니다. 우선은 (SELECT LEVEL lv , CEIL(LEVEL / 4) x , MOD(CEIL(LEVEL / 2)-1, 2) + 1 y , MOD(LEVEL-1, 2) + 1 z FROM dual CONNECT BY LEVEL <= 8 ) 이 부분을 생각하지 못했는데, 좋은 방법을 배워갑니다. 다시 한 번 더 감사합니다^^
by 마농
[2023.02.13 14:48:30]
빠른 쿼리로 질문 올리면서 느려요 하시면 안됩니다.
올려 주시는 쿼리가 실제 쿼리가 아니더라도 실제 쿼리를 대변할 수 있는 쿼리를 올려주셔야 합니다.
올려주신 쿼리만 보고 그대로 개선하긴 했는데.
실제 데이터도 각 2개씩 정확하게 들어가는지?
1개 이거나 3개 이상인 자료는 혹시 없는지?
) 버튼을 클릭하여 작성 하시면 됩니다.