데이터베이스 질문과 답변

[질문]연결 고리 찾기 쿼리??? 0 6 3,377

by 에모토 [SQL Query] DENSE_RANK [2012.03.16 00:49:29]


안녕하세요 우연히 네이버 검색하다 좋은 사이트를 알게 되어 가입했습니다^^

제목대로 연결 고리 찾는 쿼리를 만들고 싶은데 여간해서 안 되네요 ㅠㅠ

내용인즉슨, 아래와 같은 컬럼이 4개(C1, C2, C3, C4) 있습니다.

C1 C2  C3  C4
-----------------------
1 A B C
2 A S    T
3 A C D
4 B C D
5 D E F
6 E G H
7 X Y Z
8 K A B
9 L M N
10  Q P R

연결고리를 찾으려고 하는데요,

위에 값 중에 A가 1, 2, 3, 8 번에 있습니다.

그럼 그 옆에 있는 1번의 B, C, 2번의 S, T, 3번의 C, D, 8번의 K, B는 A와 관계를 갖는 값이 되고
아래와 같이 표현하고 싶습니다.(중복제거)

A A - 항상 대표가 되는 값은 관계를 갖는 값을 가져야 함.
A B
A C
A D
A K
A S
A T


그 다음 B의 값은 1, 4, 8번에 있습니다.
1번의 A, C, 4번의 C, D, 8번의 K, A는 B와 관계를 갖는 값이 되고

위와 같은 방식으로 표현하고 싶지만, C, D, K, A는 모두 위의 A와 관계를 맺고 있기 때문에 B의 관계에서 제외합니다.

C의 경우도 같은 형태여서 생략하고, D의 경우를 보겠습니다.

D의 값은 3, 4, 5번에 있습니다.

3번의 A, C, 4번의 B, C, 5번의 E, F와 관계를 갖는 값이 되는데, 3번과 4번은 이미 A와 관계를 맺고 있기 때문에 제외하고

5번의 E, F에 대해서만 위와 같은 형태로 표현하면 아래와 같이 됩니다.

D E
D F

D의 값은 A와 관계를 맺고 있는 값 중에 하나이기 때문에 위의 E, F 또한 A와 관계를 맺는 형태로 표현하고 싶습니다.

A A - 항상 대표가 되는 값은 관계를 갖는 값을 가져야 함.
A B
A C
A D
A K
A S
A T
A E - D와 관계를 맺는 값이나 D의 대표가 A이므로 A와 연결
A F - D와 관계를 맺는 값이나 D의 대표가 A이므로 A와 연결

이런 식으로 6번을 보면 E는 G와 H랑 관계를 맺고 있고, E의 대표는 A이므로 G와 H 또한 A를 대표로 하는 관계가 됩니다.

A A - 항상 대표가 되는 값은 관계를 갖는 값을 가져야 함.
A B
A C
A D
A K
A S
A T
A E - D와 관계를 맺는 값이나 D의 대표가 A이므로 A와 연결
A F - D와 관계를 맺는 값이나 D의 대표가 A이므로 A와 연결
A G - E와 관계를 맺는 값이나 E의 대표가 A이므로 A와 연결
A H - E와 관계를 맺는 값이나 E의 대표가 A이므로 A와 연결

...(중략)...

나머지 7, 9, 10번의 세 값들은 나머지 행들과 관계를 맺는 값이 없으므로 자기 행에서 아무 값이나 대표로 해서 관계를 만듭니다.

X X
X Y
X Z
L L
L M
L N
Q Q
Q P
Q R

최종 결과 값은 아래와 같습니다.


A A - 항상 대표가 되는 값은 관계를 갖는 값을 가져야 함.
A B
A C
A D
A K
A S
A T
A E - D와 관계를 맺는 값이나 D의 대표가 A이므로 A와 연결
A F - D와 관계를 맺는 값이나 D의 대표가 A이므로 A와 연결
A G - E와 관계를 맺는 값이나 E의 대표가 A이므로 A와 연결
A H - E와 관계를 맺는 값이나 E의 대표가 A이므로 A와 연결
X X
X Y
X Z
L L
L M
L N
Q Q
Q P
Q R

이런 식의 결과값을 출력하는 쿼리를 만들 수 있을까요?^^

한가지 덧붙이자면, 오라클에서만 사용할 수 있는 오라클 전용함수를 사용하지 않는 쿼리로도 가능할까요?

넘 어렵네요 저는 ㅠㅠ
by 마농 [2012.03.16 09:19:46]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
WITH t(no, c1, c2, c3) AS
(-- 원본 테이블 --
SELECT 1, 'A', 'B', 'C' FROM dual
UNION ALL SELECT 2, 'A', 'S', 'T' FROM dual
UNION ALL SELECT 3, 'A', 'C', 'D' FROM dual
UNION ALL SELECT 4, 'B', 'C', 'D' FROM dual
UNION ALL SELECT 5, 'D', 'E', 'F' FROM dual
UNION ALL SELECT 6, 'E', 'G', 'H' FROM dual
UNION ALL SELECT 7, 'X', 'Y', 'Z' FROM dual
UNION ALL SELECT 8, 'K', 'A', 'B' FROM dual
UNION ALL SELECT 9, 'L', 'M', 'N' FROM dual
UNION ALL SELECT 10, 'Q', 'P', 'R' FROM dual
)
, t1 AS
(-- 계층 구조를 만들기 위한 집합 --
SELECT c, pc
  FROM (SELECT c, pc
             , DENSE_RANK() OVER(PARTITION BY c ORDER BY NVL2(pc, 1, 2)) dr
          FROM (SELECT no
                     , c
                     , LAG(c) OVER(PARTITION BY no ORDER BY c) pc
                  FROM (SELECT no
                             , DECODE(lv, 1, c1, 2, c2, 3, c3) c
                          FROM t
                             , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3)
                        )
                )
         GROUP BY c, pc
        )
 WHERE dr = 1
)
-- 계층 구조 쿼리 --
SELECT DISTINCT
       CONNECT_BY_ROOT c rc
     , c
  FROM t1
 START WITH pc IS NULL
 CONNECT BY PRIOR c = pc
 ORDER BY rc, c
;
by 마농 [2012.03.16 10:03:24]
MSSQL Server 용
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
WITH t(no, c1, c2, c3) AS
(-- 원본 테이블 --
SELECT 1, 'A', 'B', 'C'
UNION ALL SELECT  2, 'A', 'S', 'T'
UNION ALL SELECT  3, 'A', 'C', 'D'
UNION ALL SELECT  4, 'B', 'C', 'D'
UNION ALL SELECT  5, 'D', 'E', 'F'
UNION ALL SELECT  6, 'E', 'G', 'H'
UNION ALL SELECT  7, 'X', 'Y', 'Z'
UNION ALL SELECT  8, 'K', 'A', 'B'
UNION ALL SELECT  9, 'L', 'M', 'N'
UNION ALL SELECT 10, 'Q', 'P', 'R'
)
, t1 AS
(-- 3개 행으로 복제 --
SELECT a.no
     , CASE b.no
       WHEN 1 THEN a.c1
       WHEN 2 THEN a.c2
       WHEN 3 THEN a.c3
        END c
 FROM t a
 CROSS JOIN (SELECT no FROM t WHERE no <= 3) b
)
, t2 AS
(-- 계층 구조를 만들기 위한 집합 --
SELECT c, pc
  FROM (
        SELECT a.c
             , b.c pc
             , DENSE_RANK() OVER(PARTITION BY a.c ORDER BY LEN(b.c) DESC) dr
          FROM t1 a
          LEFT OUTER JOIN t1 b
            ON a.no = b.no
           AND a.c > b.c
         GROUP BY a.c, b.c
        ) a
 WHERE dr = 1
)
, t3 AS
(-- 재귀 호출 쿼리 --
SELECT c rc
     , c
  FROM t2
 WHERE pc IS NULL
 UNION ALL
SELECT a.rc, b.c
  FROM t3 a
 INNER JOIN t2 b
    ON a.c = b.pc
)
SELECT DISTINCT rc, c
  FROM t3
;
by 손님 [2012.03.16 12:01:41]
마농님 대단!
by 손님 [2012.03.16 16:37:02]

정말 감사합니다 ㅠㅠ
소중한 시간 내주셔서...

제가 사용하는 DBMS가 DB2인데 마농님의 쿼리를 응용하여 만들어 보니 잘 됐습니다^^
by 에모토 [2012.03.16 16:38:47]

손님으로 댓글이 달려서^^

정말 감사합니다 ㅠㅠ
소중한 시간 내주셔서...

제가 사용하는 DBMS가 DB2인데 마농님의 쿼리를 응용하여 만들어 보니 잘 됐습니다^^

DB2로 쿼리를 만들려니 오라클의 CONNECT_BY_ROOT를, MS-SQL 쿼리를 이용하면 Full Scalar set인가 하는 오류가 발생해서 나름대로 수정해 봤습니다..
by 마농 [2012.03.16 16:47:14]

DB2 사용자를 위해 쿼리 공유해 주세요 ^^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입