고수님들 후배 좀 살려주세요. ㅠㅠ (계층 쿼리 단계별 적층 쿼리) 0 10 190

by 지금부터 [Oracle Tuning] 계층 쿼리 connect by [2020.01.12 18:57:17]


안녕하세요?

3박4일 이걸로 씨름하다 아이디어가 고갈나서 도움 요청 드립니다.

이거 정말 약올라서 미쳐버리겠습니다.

먼저 테이블 구조는 이렇습니다.

설명드리자면 기존 회원이 몇 명의 신규 회원을 유치했는지를 구하는 겁니다. 

단 신규 회원이 기존회원의 블로그에 있는 코드를 입력하고 가입했느냐 또는 유튜브에 있는 코드를 보고 가입했느냐 입니다.

신규 회원 가입시 추천인 ID, 유입 경로 구분을 필수값으로 받고 있습니다.

Table 이름:  CUSTOMER    
회원 ID 추천인 ID 유입 경로 구분 A(블로그) B(유튜브)
00010 00001 A    
00011 00002 B    

제가 만든 쿼리의 순서는 이렇습니다.

  신규 회원의 추천인과 유입경로구분을 찾는다.  --> 추천인  ID 행으로 이동한다 --> A 또는 B에 1을 더해준다. --> 추천인은 이제 신규 회원이 되고 다시 이 회원을  추천한 사람은 누군지 찾는다.(connect by)

--> 반복한다.

그리고 쿼리는 이렇습니다.

컬럼은 이렇습니다.

(설명: 컬럼명)

회원ID:Userid 

추천인ID:P_id

구매 금액대:user_rank 

유입경로구분(블로그 or 유튜브): 유입경로구분

블로그: Type_A

유튜브:Type_B 

=================================================================

BEGIN
       FOR i IN ( SELECT Userid,P_ID,user_rank,유입경로구분,type_a,type_b FROM customer
        START WITH userid in (SELECT USERID FROM customer)
                CONNECT BY userid = PRIOR P_ID
        ORDER SIBLINGS BY user_rank) 
        LOOP
            IF  i.user_rank>=10 and  i.유입경로구분 = 'a' THEN UPDATE customer SET type_a = type_a + 1 WHERE Userid = i.P_ID; 
            ELSIF i.user_rank>=10 and i.유입경로구분 = 'b' THEN UPDATE customer SET type_b = type_b + 1 WHERE Userid = i.P_ID;            
            END IF;
        END LOOP;
END;

===========================================================================================

이거 대체 뭐가 문제일까요?

아예 잘못 가고 있는 걸까요?

이제 자신도 없습니다.

도와주세요.ㅠㅠ 

by 마농 [2020.01.13 10:29:23]
-- 계층 쿼리가 필요한가요?
SELECT p_id
     , COUNT(DECODE(유입경로구분, 'A', 1)) cnt_a
     , COUNT(DECODE(유입경로구분, 'B', 1)) cnt_b
  FROM customer
 GROUP BY p_id
;

 


by 지금부터 [2020.01.13 11:21:32]

네, 계층쿼리가 필요합니다.

왜냐하면 자기가 데려온 사람이 또다시 신규 회원을 유치해오는 경우 최초 당사자 실적에도 카운트를 해줘야하기 때문입니다.

예를 들어, 철수가 영희를 신규회원으로 유치했고 영희가 다시 범수를 신규회원으로 유치한 경우

철수의 실적은 +2이고 영희는 +1, 범수는 0이 되기 때문입니다.  


by 신이만든지기 [2020.01.13 12:00:53]
WITH
    CUSTOMER AS
        (SELECT '0001' USER_ID, '' P_ID, 'A' IN_TYPE FROM DUAL
         UNION ALL
         SELECT '0002' USER_ID, '0001' P_ID, 'A' IN_TYPE FROM DUAL
         UNION ALL
         SELECT '0003' USER_ID, '0002' P_ID, 'B' IN_TYPE FROM DUAL
         UNION ALL
         SELECT '0004' USER_ID, '0003' P_ID, 'A' IN_TYPE FROM DUAL
         UNION ALL
         SELECT '0005' USER_ID, '' P_ID, 'B' IN_TYPE FROM DUAL
         UNION ALL
         SELECT '0006' USER_ID, '0001' P_ID, 'A' IN_TYPE FROM DUAL)
    SELECT USER_ID
         , P_ID
         , IN_TYPE
         , (    SELECT COUNT(*)
                  FROM CUSTOMER
                 WHERE IN_TYPE = 'A'
            START WITH P_ID = A.USER_ID
            CONNECT BY PRIOR USER_ID = P_ID)
               A_CNT
         , (    SELECT COUNT(*)
                  FROM CUSTOMER
                 WHERE IN_TYPE = 'B'
            START WITH P_ID = A.USER_ID
            CONNECT BY PRIOR USER_ID = P_ID)
               B_CNT
         , (    SELECT COUNT(*)
                  FROM CUSTOMER
            START WITH P_ID = A.USER_ID
            CONNECT BY PRIOR USER_ID = P_ID)
               TOTAL_CNT
      FROM CUSTOMER A;

 


by 신이만든지기 [2020.01.13 12:01:33]

아래 마농님 강좌에 이미 원하시는 답변이 준비되어 있었네요. 

저는 응용만 했습니다.

http://www.gurubee.net/lecture/2681


by 마농 [2020.01.13 12:19:52]
SELECT userid
     , COUNT(DECODE(유입경로구분, 'A', 1)) cnt_a
     , COUNT(DECODE(유입경로구분, 'B', 1)) cnt_b
  FROM (SELECT CONNECT_BY_ROOT userid userid
             , 유입경로구분
          FROM customer
         WHERE LEVEL > 1
         CONNECT BY PRIOR userid = p_id
        )
 GROUP BY userid
;

 


by 지금부터 [2020.01.13 21:27:13]

아흐 ㅠㅠ

ORA-01436: 루프 발생 에러가 뜹니다. ㅠㅠ

이거 정말 미치겠네요.


by 마농 [2020.01.14 08:54:32]

해당 오류는 데이터 오류입니다.
계층 자료에 루푸를 발생시키는 자료가 존재하는 거죠.
루프를 발생시키는 자료를 찾아 보세요. (예 : a - b - c - a)
Userid, P_id 외에 그룹 구분 컬럼이 더 있는 것은 아닌지?


by 지금부터 [2020.01.14 12:57:10]

네, 컬럼은 등록일, 탈퇴일, 회원등급 등 15개쯤 더 있습니다. 

a-b-c-a로 루프가 도는 게 있다면 그 회원을 where 절로 빼버리고 돌리면 될까요?

 


by 우리집아찌 [2020.01.14 13:28:45]

nocycle 이용해보세요

http://www.gurubee.net/lecture/2681


by 마농 [2020.01.14 13:45:09]

오류 원인이 되는 자료를 찾았다는 건가요?
오류를 피하는 방법 보다는 오류를 제거해야 하는게 아닐까? 생각되네요.

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