데이터베이스 질문과 답변
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;
===========================================================================================
이거 대체 뭐가 문제일까요?
아예 잘못 가고 있는 걸까요?
이제 자신도 없습니다.
도와주세요.ㅠㅠ
-- 계층 쿼리가 필요한가요?
SELECT p_id
, COUNT(DECODE(유입경로구분, 'A', 1)) cnt_a
, COUNT(DECODE(유입경로구분, 'B', 1)) cnt_b
FROM customer
GROUP BY p_id
;
네, 계층쿼리가 필요합니다.
왜냐하면 자기가 데려온 사람이 또다시 신규 회원을 유치해오는 경우 최초 당사자 실적에도 카운트를 해줘야하기 때문입니다.
예를 들어, 철수가 영희를 신규회원으로 유치했고 영희가 다시 범수를 신규회원으로 유치한 경우
철수의 실적은 +2이고 영희는 +1, 범수는 0이 되기 때문입니다.
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;
감사합니다.
많이 배웠습니다.
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
;
아흐 ㅠㅠ
ORA-01436: 루프 발생 에러가 뜹니다. ㅠㅠ
이거 정말 미치겠네요.
해당 오류는 데이터 오류입니다.
계층 자료에 루푸를 발생시키는 자료가 존재하는 거죠.
루프를 발생시키는 자료를 찾아 보세요. (예 : a - b - c - a)
Userid, P_id 외에 그룹 구분 컬럼이 더 있는 것은 아닌지?
네, 컬럼은 등록일, 탈퇴일, 회원등급 등 15개쯤 더 있습니다.
a-b-c-a로 루프가 도는 게 있다면 그 회원을 where 절로 빼버리고 돌리면 될까요?
nocycle 이용해보세요
네, 잘 활용했습니다.
감사합니다~~
오류 원인이 되는 자료를 찾았다는 건가요?
오류를 피하는 방법 보다는 오류를 제거해야 하는게 아닐까? 생각되네요.
마농님 감사합니다.
덕분에 어떻게든 뽑았습니다.
새해 복 많이 받으세요~
) 버튼을 클릭하여 작성 하시면 됩니다.