꿈꾸는 개발자, DBA 커뮤니티 구루비

마농님이 풀어주신 연결고리 찾기(?) 쿼리 추가 질문입니다.ㅠㅠ

by 에모토 [SQL Query] [2012.03.16 16:44:12]

한번에 제대로 질문을 못 드려서 죄송합니다

마농님이 풀어주신 MS-SQL은 Recursive 쿼리에서 full scalar 어쩌구 오류가 발생해서

오라클 쿼리를 조금 변경해서 사용했는데요...

아래와 같이 조금 복잡한 가상 데이터를 만들어서 돌려보면 값이 제대로 안 나오네요 ㅠㅠ

WITH T(NO, C1, C2, C3) AS
    (--원본 테이블
SELECT 1, 'B', 'B', 'A' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 2, 'C', 'B', 'C' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 3, 'B', 'B', 'C' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 4, 'B', 'B', 'B' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 5, 'B', 'B', 'D' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 6, 'D', 'B', 'B' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 7, 'D', 'B', 'D' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 8, 'D', 'D', 'B' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 9, 'E', 'B', 'E' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 10, 'F', 'B', 'F' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 11, 'C', 'G', 'C' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 12, 'D', 'D', 'H' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 13, 'D', 'D', 'D' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 14, 'D', 'D', 'E' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 15, 'D', 'D', 'I' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 16, 'D', 'D', 'F' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 17, 'J', 'H', 'H' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 18, 'J', 'J', 'H' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 19, 'G', 'K', 'G' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 20, 'L', 'J', 'L' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 21, 'K', 'K', 'K' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 22, 'K', 'K', 'M' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 23, 'K', 'M', 'K' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 24, 'J', 'J', 'L' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 25, 'J', 'J', 'J' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 26, 'J', 'J', 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 27, 'J', 'J', 'O' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 28, 'P', 'J', 'P' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 29, 'N', 'J', 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 30, 'Q', 'K', 'Q' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 31, 'O', 'J', 'O' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 32, 'L', 'L', 'L' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 33, 'R', 'P', 'R' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 34, 'R', 'P', 'P' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 35, 'P', 'R', 'P' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 36, 'N', 'N', 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 37, 'M', 'M', 'M' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 38, 'Q', 'M', 'Q' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 39, 'R', 'R', 'R' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 40, 'R', 'R', 'S' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 41, 'R', 'R', 'T' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 42, 'U', 'R', 'U' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 43, 'T', 'R', 'T' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 44, 'U', V', 'W' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 45, 'X', 'Y', 'Z' FROM SYSIBM.SYSDUMMY1
    )

DB2라서 SYSIBM.SYSDUMMY1을 썼습니다.(여기는 오라클 옵션을 막아 놓은 것 같네요..)

참고로 제가 만든 쿼리는 아래와 같습니다.
WITH DATA_TEMP(NO, C1, C2, C3) AS
    (SELECT 1, 'B', 'B', 'A' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 2, 'C', 'B', 'C' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 3, 'B', 'B', 'C' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 4, 'B', 'B', 'B' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 5, 'B', 'B', 'D' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 6, 'D', 'B', 'B' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 7, 'D', 'B', 'D' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 8, 'D', 'D', 'B' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 9, 'E', 'B', 'E' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 10, 'F', 'B', 'F' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 11, 'C', 'G', 'C' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 12, 'D', 'D', 'H' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 13, 'D', 'D', 'D' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 14, 'D', 'D', 'E' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 15, 'D', 'D', 'I' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 16, 'D', 'D', 'F' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 17, 'J', 'H', 'H' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 18, 'J', 'J', 'H' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 19, 'G', 'K', 'G' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 20, 'L', 'J', 'L' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 21, 'K', 'K', 'K' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 22, 'K', 'K', 'M' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 23, 'K', 'M', 'K' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 24, 'J', 'J', 'L' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 25, 'J', 'J', 'J' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 26, 'J', 'J', 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 27, 'J', 'J', 'O' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 28, 'P', 'J', 'P' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 29, 'N', 'J', 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 30, 'Q', 'K', 'Q' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 31, 'O', 'J', 'O' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 32, 'L', 'L', 'L' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 33, 'R', 'P', 'R' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 34, 'R', 'P', 'P' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 35, 'P', 'R', 'P' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 36, 'N', 'N', 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 37, 'M', 'M', 'M' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 38, 'Q', 'M', 'Q' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 39, 'R', 'R', 'R' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 40, 'R', 'R', 'S' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 41, 'R', 'R', 'T' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 42, 'U', 'R', 'U' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 43, 'T', 'R', 'T' FROM SYSIBM.SYSDUMMY1
    )
   , LVL_TEMP(LV) AS
    (SELECT   1 AS LV
   FROM SYSIBM.SYSDUMMY1
   UNION ALL
   SELECT   LV + 1
   FROM LVL_TEMP
   WHERE    LV < 3
  )
, LST_TEMP AS
    (SELECT   C, PC
FROM    (SELECT   C, PC
, DENSE_RANK() OVER(PARTITION BY C
ORDER BY CASE
WHEN PC IS NULL THEN '2'
ELSE '1'
   END ) AS DR
FROM    (SELECT   NO
, C
   , CASE
    WHEN MAX(C) OVER(PARTITION BY NO
ORDER BY    C
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = C THEN NULL
ELSE MAX(C) OVER(PARTITION BY NO
ORDER BY    C
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
END AS PC
   FROM    (SELECT   NO
   , CASE LV
WHEN 1 THEN C1
WHEN 2 THEN C2
WHEN 3 THEN C3
END C
    FROM DATA_TEMP F
   ,(SELECT   *
FROM LVL_TEMP
    )LV
   )LG
)PC
GROUP BY C
, PC
)DR
WHERE    DR = 1
  )
   , RZLT(LV, C, PC, RT) AS
   (SELECT   0 AS LV
, CAST(C AS VARCHAR(100)) AS C
, PC
  , '' AS RT
   FROM LST_TEMP
   WHERE    PC IS NULL
   UNION ALL
   SELECT   LV + 1, T.C, T.PC, T.C||'|'||R.PC
   FROM RZLT R
  , LST_TEMP T
   WHERE    R.C = T.PC
  )
SELECT   DISTINCT
C
   , CASE
WHEN PC IS NULL THEN C
  WHEN RT IS NULL THEN PC
  ELSE SUBSTR(RT, LOCATE('|', RT) +1)
END
FROM RZLT
WHERE    LV <=2
ORDER BY 2, 1
FETCH FIRST 100 ROWS ONLY
WITH UR;

by 마농 [2012.03.16 17:22:50]

원래 질문에서는 c1, c2, c3 가 모두 달랐었는데요.
지금 질문에서는 c1, c2, c3 중 같은 것이 존재하여 최대 2가지 종류만 있네요?
2가지만 있다면 훨씬더 간결해 질듯 한데요? 정말 2가지만 있나요?
질문하실때 자료 예시는 될수 있으면 원본자료 전체를 대신할 수 있는 대표적인 자료여야 합니다.
그리고 값이 제대로 안나온다고 하셨는데? 어떻게 나오나요? 어떤 결과를 원하나요?

by 에모토 [2012.03.16 17:43:56]

아.. 제가 너무 무성의하게 질문을 했군요...

전체를 대신할 수 있는 대표적인 자료를 올리지 못해서 죄송합니다.

본문 글에 빨강색 바탕칠한 게 외적인 부분을 추가한 것입니다.

제가 원하는 결과는

A A
B A
C A
D A
E A
F A
G A
H A
I A
J A
K A
L A
M A
N A
O A
P A
Q A
R A
S A
T A
U A -- 43번까지 처리했을 때 원하는 결과 값
V A
W A
X X
Y X
Z X -- 45번까지 처리했을 때 원하는 결과 값
입니다.

제가 위의 쿼리로 43번까지 처리했을 때 나온 결과값은
A A
B A
C A
D A
E A
F A

여기까지입니다. 아무래도 DB2이다보니 CONNECT_BY_ROOT 절을 제대로 구현하지 못한 게 아닌가 하는 생각입니다. ㅠ

by 마농 [2012.03.16 17:46:10]

LST_TEMP 까지의 결과는 제대로 나오는지요?
제 생각에는 잘 나왔을 것 같구요.
마지막 재귀쿼리에서 자료가 잘못 나오지 않을까 싶네요.
재귀쿼리에 보면 제가 했던 방식에 있는 RC 가 없네요?
제가 했던 방식으로 하면 될것 같은데요?

by 에모토 [2012.03.16 18:22:31]

아.. 제가 있는 곳의 DB2에서는 CONNECT_BY_ROOT를 사용하지 못해서요..

이 부분을 어떻게 구현해야 하는지 고민 중이긴 합니다만... 아직 해답이 없네요...

그리고, MS-SQL 용을 이용해서 DB2에서 실행하면 아래와 같은 오류가 나서

제가 마지막 재귀쿼리를 나름대로 작성해 봤습니다.

SQL0345N The fullselect of the recursive common table expression "T3" must be the UNION of two or more fullselects and cannot include column functions, GROUP BY clause, HAVING clause, ORDER BY clause, or an explicit join including an ON clause. SQLSTATE=42836

오라클 쿼리로 질문을 올려서 죄송합니다.ㅠㅠ

by 마농 [2012.03.16 18:26:07]

Connect_By_Root 를 사용하란 말이 아니구요.
제가 MSSQL 용쿼리에서 사용한 재귀쿼리 사용하시면 되요.
위에서도 그렇게 하셨네요..그런데 변형을 가하셨네요.
제가 작성했던 그대로 하심 될것 같습니다.

by 에모토 [2012.03.16 19:03:34]

마농님 깊은 관심 가져주셔서 대단히 감사합니다.

위에서 말씀드린 것처럼 MS-SQL용 쿼리에서는 fullselect ~ 오류가 발생하여 수정을 가할 수 밖에 없었습니다.

그나마 마농님께서 기본적인 큰 그림을 그려주셔서 제가 조금 더 고민해서 해결해야할 것 같습니다^^;

소중한 시간 제 질문에 답하기 위해 할애해 주셔서 다시 한 번 감사의 말씀 드립니다(__)

그럼, 비가 오는 금요일이지만 즐거운 주말 보내시기 바랍니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변