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

관련된 고객에게 같은 번호 부여하기

by 꼬랑지 [SQL Query] [2013.05.10 11:54:44]

관련된 고객에 같은 번호를 부여하는 문제때문에 질문 올립니다.

WITH T AS (
SELECT 'S1' SVC,111 SBSC,111 CUST FROM DUAL UNION ALL
SELECT 'S1' SVC,111 SBSC,112 CUST FROM DUAL UNION ALL
SELECT 'S1' SVC,111 SBSC,113 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,112 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,114 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,115 CUST FROM DUAL UNION ALL
SELECT 'S3' SVC,116 SBSC,116 CUST FROM DUAL UNION ALL
SELECT 'S4' SVC,117 SBSC,117 CUST FROM DUAL
)

위와 같은 데이타가 있을시 명의자(SBSC) 111에 대하여 111,112,113 고객이 묶여 있으므로 이들은 같은 번호를 부여 받아야 합니다. 그런데 고객 112가 서비스 S2에 대하여 명의자로 되어 있고 S2에는 112,114,115가 묶여 있으므로 이들 또한 같은 번호를 부여 받아야 합니다. S3,S4의 경우 각각 다른 번호를 부여 받아야 합니다.

WITH T AS (
SELECT 'S1' SVC,111 SBSC,111 CUST FROM DUAL UNION ALL
SELECT 'S1' SVC,111 SBSC,112 CUST FROM DUAL UNION ALL
SELECT 'S1' SVC,111 SBSC,113 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,112 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,114 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,115 CUST FROM DUAL UNION ALL
SELECT 'S3' SVC,116 SBSC,114 CUST FROM DUAL UNION ALL
SELECT 'S4' SVC,117 SBSC,117 CUST FROM DUAL
)

두번째의 경우는 S3의 명의자 116에 대해 114가 연관고객이 된 경우입니다. 114는 선처리에 의해 111,112,113,114,115와 같은 번호를 부여 받았으므로 명의자 116도 같은 번호를 부여 받아야 합니다. 여전히 117은 별도 번호입니다.

WITH T AS (
SELECT 'S1' SVC,111 SBSC,111 CUST FROM DUAL UNION ALL
SELECT 'S1' SVC,111 SBSC,112 CUST FROM DUAL UNION ALL
SELECT 'S1' SVC,111 SBSC,113 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,112 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,114 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,115 CUST FROM DUAL UNION ALL
SELECT 'S3' SVC,116 SBSC,116 CUST FROM DUAL UNION ALL
SELECT 'S4' SVC,117 SBSC,116 CUST FROM DUAL
)

WITH T AS (
SELECT 'S1' SVC,111 SBSC,111 CUST FROM DUAL UNION ALL
SELECT 'S1' SVC,111 SBSC,112 CUST FROM DUAL UNION ALL
SELECT 'S1' SVC,111 SBSC,113 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,112 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,114 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,115 CUST FROM DUAL UNION ALL
SELECT 'S3' SVC,116 SBSC,118 CUST FROM DUAL UNION ALL
SELECT 'S4' SVC,116 SBSC,117 CUST FROM DUAL
)

세번째 경우는 S3,S4의 명의자를 각각 다르나 116이 공통으로 관련고객이므로 같은 번호를 부여받아야 하는 상황입니다.
네번째의 경우는 S3,S4의 관련고객은 118,117 각각이지만 두 서비스의 명의자가 116이므로 116,117,118 세명이 같은 번호를 부여 받아야 하는 상황입니다.

이런 처리를 해야 하는 고객이 약 4천만 정도입니다. 어떻게 하면 효과적으로 관련된 고객을 찾아 동일 번호를 부여할 수 있을까요? 물론 4천만에 대해 몽땅 같은 번호를 부여했으면 좋겠습니다만 1~4번에 골고루, 정확하게 1/4씩은 아니지만 적절히 분배를 해주어야 합니다. 제 머리로는 도저히 해결 방법이 안나오네요.

CONNECT BY문을 사용한다면 아래와 같은 경우 입니다.

CONNECT BY (PRIOR SBSC = CUST OR PRIOR SBSC = SBSC OR PRIOR CUST = SBSC OR PRIOR CUST= CUST)

by 마농 [2013.05.10 15:22:45]

-- 일단 만들긴 했는데...
-- 계층쿼리 특성상 대용량 자료 처리는 힘들지 않을까 생각됩니다.
-- rk 값에 따라 적당히 배분하시면 되겠습니다.
SELECT r, s, c
     , svc, sbsc, cust
     , RANK() OVER(ORDER BY r, s, c) rk
  FROM (
        SELECT svc, sbsc, cust
             , CONNECT_BY_ROOT(svc) r
             , CONNECT_BY_ROOT(s) s
             , CONNECT_BY_ROOT(c) c
          FROM (SELECT svc, sbsc, cust
                     , LEAST   (sbsc, cust) s
                     , GREATEST(sbsc, cust) c
                     , CASE
                       WHEN NOT EXISTS
                            (SELECT 1
                               FROM t
                              WHERE NOT(svc = a.svc AND sbsc = a.sbsc AND cust = a.cust)
                                AND GREATEST(sbsc, cust) = LEAST(a.sbsc, a.cust)
                             )
                        AND ROW_NUMBER() OVER(
                            PARTITION BY LEAST(a.sbsc, a.cust)
                            ORDER BY GREATEST(a.sbsc, a.cust)
                            ) = 1
                       THEN 1 ELSE 0 END root
                  FROM t a
                ) a
         START WITH root = 1
         CONNECT BY NOCYCLE (PRIOR s = s AND PRIOR c < c)
                         OR (PRIOR c = s AND PRIOR s < s)
        )
 GROUP BY r, s, c, svc, sbsc, cust
;

by 마농 [2013.05.10 15:42:53]

음... 루트노드를 구하는 로직에 문제가 있네요...
121, 123
122, 123
과 같이 sbsc 가 아닌 cust 만 같은 자료가 있으면 둘다 룰트가 되어버리네요.
다시 한번 고민을......

by 꼬랑지 [2013.05.10 15:48:25]

마농님 답변 감사합니다.
connect by는 ....답이 안나와서..고민입니다.
그래도 제가 생각하는 connect by 보단 속도가 나올 듯하네요.

꼭 한방 sql 이 아니어도 됩니다. 지금 tmp 테이블을 5단계 만들면서 테스트해 보고 있는데 보다 효율적인 방법을 찾습니다. ^^

by 마농 [2013.05.10 16:17:02]

SELECT r, s, c
     , svc, sbsc, cust
     , RANK() OVER(ORDER BY r, s, c) rk
  FROM (
        SELECT svc, sbsc, cust
             , CONNECT_BY_ROOT(svc) r
             , CONNECT_BY_ROOT(s) s
             , CONNECT_BY_ROOT(c) c
          FROM (SELECT svc, sbsc, cust
                     , LEAST   (sbsc, cust) s
                     , GREATEST(sbsc, cust) c
                     , CASE
                       WHEN NOT EXISTS
                            (SELECT 1
                               FROM t
                              WHERE NOT(svc = a.svc AND sbsc = a.sbsc AND cust = a.cust)
                                AND GREATEST(sbsc, cust) = LEAST(a.sbsc, a.cust)
                             )
                        AND ROW_NUMBER() OVER(
                            PARTITION BY LEAST(a.sbsc, a.cust)
                            ORDER BY GREATEST(a.sbsc, a.cust)
                            ) = 1
                        AND ROW_NUMBER() OVER(
                            PARTITION BY GREATEST(a.sbsc, a.cust)
                            ORDER BY LEAST(a.sbsc, a.cust)
                            ) = 1
                       THEN 1 ELSE 0 END root
                  FROM t a
                ) a
         START WITH root = 1
         CONNECT BY NOCYCLE (PRIOR s = s AND PRIOR c < c)
                         OR (PRIOR c = c AND PRIOR s < s)
                         OR (PRIOR c = s AND PRIOR s < s)
        )
 GROUP BY r, s, c, svc, sbsc, cust
;

by 꼬랑지 [2013.05.10 17:53:34]

마농님 대단하심. 올려주신 쿼리 이해하기가 힘드네요.. ㅎ
제가 한가지 빠트린게 있네요 그러고 보니..같은 명의자가 복수개의 서비스를 개통하면서 같은 고객을 관련자로 하는 겨우, 즉

WITH T AS (
SELECT 'S1' SVC,111 SBSC,111 CUST FROM DUAL UNION ALL
SELECT 'S1' SVC,111 SBSC,112 CUST FROM DUAL UNION ALL
SELECT 'S1' SVC,111 SBSC,113 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,112 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,114 CUST FROM DUAL UNION ALL
SELECT 'S2' SVC,112 SBSC,115 CUST FROM DUAL UNION ALL
SELECT 'S3' SVC,116 SBSC,116 CUST FROM DUAL UNION ALL
SELECT 'S4' SVC,116 SBSC,116 CUST FROM DUAL
)

S3,S4 같은 경우도 충분히 있을 수 있네요. 마농님의 주신 쿼리에서 살짝 수정하니 되네요.
답변 감사합니다. 이제 문제는 속도네요 ㅜㅜ

SELECT r, s, c
, svc, sbsc, cust
, RANK() OVER(ORDER BY r, s, c) rk
FROM (

    SELECT svc, sbsc, cust
, CONNECT_BY_ROOT(svc) r
, CONNECT_BY_ROOT(s) s
, CONNECT_BY_ROOT(c) c
FROM (

    SELECT svc, sbsc, cust
, LEAST   (sbsc, cust) s
, GREATEST(sbsc, cust) c
, CASE
   WHEN NOT EXISTS
    (SELECT 1
   FROM t
WHERE NOT(A.SVC = a.svc AND sbsc = a.sbsc AND cust = a.cust)
    AND GREATEST(sbsc, cust) = LEAST(a.sbsc, a.cust)
)
    AND ROW_NUMBER() OVER(
    PARTITION BY LEAST(a.sbsc, a.cust)
    ORDER BY A.SVC,GREATEST(a.sbsc, a.cust)
    ) = 1
    AND ROW_NUMBER() OVER(
    PARTITION BY GREATEST(a.sbsc, a.cust)
    ORDER BY A.SVC,LEAST(a.sbsc, a.cust)
    ) = 1
   THEN 1 ELSE 0
   END root
FROM t a

    ) a
START WITH root = 1
CONNECT BY NOCYCLE (PRIOR s = s AND PRIOR c < c)
OR (PRIOR c = c AND PRIOR s < s)
OR (PRIOR c = s AND PRIOR s < s)

)
GROUP BY r, s, c, svc, sbsc, cust
;

;

댓글등록

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

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

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

데이터베이스 질문과 답변