오라클 재귀쿼리를 이용한 종속건수 구하기

오라클의 재귀쿼리를 이용한 하위트리(종속트리) 건수 구하기이다.

다단계처럼 자기 밑에 있는 하위 항목(건수)이 몇 개인지 구한다.

-- USER_ID : 회원아이디, P_ID : 추천아이디, IN_TYPE : 유입경로
-- 회원가입 시 추천아이디를 적을 수 있으며, 다단계처럼 최초 추천자 아이디에 계속해서 실적(추천받아 들어온 회원가입자 수)을 누적 해준다.
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;

-- 결과
USER_ID P_ID IN_TYPE      A_CNT      B_CNT  TOTAL_CNT
------- ---- ------- ---------- ---------- ----------
0001         A                3          1          4
0002    0001 A                1          1          2
0003    0002 B                1          0          1
0004    0003 A                0          0          0
0005         B                0          0          0
0006    0001 A                0          0          0

6 rows selected.

-- 또다른 방법
-- 출처 : http://www.gurubee.net/article/82419
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
       , COUNT(DECODE(IN_TYPE, 'A', 1)) CNT_A
       , COUNT(DECODE(IN_TYPE, 'B', 1)) CNT_B
    FROM (
                 SELECT CONNECT_BY_ROOT USER_ID USER_ID
                      , IN_TYPE
                   FROM CUSTOMER
                  WHERE LEVEL > 1
             CONNECT BY PRIOR USER_ID = P_ID
         )
GROUP BY USER_ID;

--결과
USER_ID      CNT_A      CNT_B
------- ---------- ----------
0001             3          1
0002             1          1
0003             1          0

3 rows selected.