계층쿼리... 이런게 가능할지... 1 7 1,879

by 열심히 [SQL Query] [2014.01.22 07:27:59]



아래와 같은 모자관계 데이터가 있습니다.
SYS_CONNECT_BY_PATH 구문으로 데이터를 표현하다..
마구마구 꼬여서 못 풀었는데요... 혹시 이게 가능할런지...

scd : 코드
pcd : 상위코드
cnt : 갯수
※ 여기서 상위코드 xxxxx 값을 가진 m1001,m1002,m1003
요놈들이 화면상에서 컬럼으로 사용될거구요 뒷자리 3자리로보면
001,002,003...n개 이런식이 됩니다
(물론 지금 데이터는 xxxxx 에 해당하는 데이터가 3건 있다고
예를든것이고 n 개일수있습니다. 이것은 쿼리로 불가능하기때문에
프로그램상에서 처리할거구요.)
이부분을 처리하기 위해
SYS_CONNECT_BY_PATH 로
자식 코드에 해당하는 상위코드 값의 위치와 갯수를 1,2,3 1,1,1
이런식으로 표현하고 싶습니다.

with test as(
select 'm1001' scd , 'xxxxx' pcd, 1 cnt from dual union all
select 'm1002' , 'xxxxx' , 1 from dual union all
select 'm1003' , 'xxxxx' , 1 from dual union all

select 'a1000' , 'm1001' , 1 from dual union all
select 'a1000' , 'm1002' , 1 from dual union all
select 'a1000' , 'm1003' , 1 from dual union all

select 'b1000' , 'm1001' , 1 from dual union all
select 'b1000' , 'm1002' , 1 from dual union all

select 'b2000' , 'm1003' , 1 from dual union all

select 'b3000' , 'm1001' , 1 from dual union all
select 'b3000' , 'm1002' , 1 from dual union all
select 'b3000' , 'm1003' , 1 from dual union all

select 'c1000' , 'm1001' , 4 from dual union all
select 'c1000' , 'm1002' , 4 from dual union all

select 'c1100' , 'c1000' , 3 from dual union all

select 'c1110' , 'c1100' , 6 from dual union all

select 'c1200' , 'c1000' , 2 from dual union all

select 'c2000' , 'm1001' , 8 from dual union all
select 'c2000' , 'm1002' , 4 from dual union all
select 'c2000' , 'm1003' , 2 from dual union all

select 'c3000' , 'm1001' , 2 from dual union all
select 'c3000' , 'm1003' , 2 from dual
)


위에 데이터로 추출하고싶은 결과값은. 아래와 같습니다.
단. 컬럼에 001 002 003 3개의 상위코드
(m1001,m1002,m1003 요놈 뒷자리)만 있다고 가정하고
보고 이놈들 레벨은 0으로 잡고,
아래에는 표현하진 않았습니다.

------------------------------------------------------------
번호 | 레벨 | scd | VAL1 | VAL2 | CNT
------------------------------------------------------------
1 | 1 | a1000 | 1,2,3 | 1,1,1 | 3
2 | 1 | b1000 | 1,2 | 1,1 | 2
3 | 1 | b2000 | 3 | 1 | 1
4 | 1 | b3000 | 1,2,3 | 1,1,1 | 3
5 | 1 | c1000 | 1,2 | 4,4 | 2
6 | 2 | c1100 | 1,2 | 3,3 | 2
7 | 3 | c1110 | 1,2 | 6,6 | 2
8 | 2 | c1200 | 1,2 | 2,2 | 2
9 | 1 | c2000 | 1,2,3 | 8,4,2 | 3
10 | 1 | c3000 | 1,3 | 2,2 | 2
-------------------------------------------------------------

val1 은 scd 에 대한 상위코드
001,002,003 요놈들의 위치를 표현한겁니다. 화면상에서 컬럼으로
표현되기때문에,
1번행을 설명하자면
val1이 1,2,3 이렇게 되있음 001,002,003 에 속하여 있는것이고
val2가 1,1,1 이렇게 나온건 각각의 위치에 갯수입니다.

6번,7번행보시면 이상할겁니다. 이것은
상위코드를 따라갔다고 보시면됩니다. c1100 은 상위코드가 c1000
이고 갯수가 3 이라고 데이터가 하나 있었는데 표현된것은
상위코드 c1000 의 위치를 그대로 가져가고 3값이 각각 그위치에
찍혔습니다.
7번도 같은경우구요.

CNT 는 VAL1 의 카운트입니다.
1,2,3 => 3
1,2 => 2
1,3 => 2
2 => 1

읽어주시느라 고생하셨습니다^^;;
아 이게 좀 어렵네여;;
이해가 되신다면 도움부탁드립니다.
by 용근님 [2014.01.22 10:34:20]
with test as( 
select 'm1001' scd , 'xxxxx' pcd, 1 cnt from dual union all 
select 'm1002' , 'xxxxx' , 1 from dual union all 
select 'm1003' , 'xxxxx' , 1 from dual union all 
select 'a1000' , 'm1001' , 1 from dual union all 
select 'a1000' , 'm1002' , 1 from dual union all 
select 'a1000' , 'm1003' , 1 from dual union all 
select 'b1000' , 'm1001' , 1 from dual union all 
select 'b1000' , 'm1002' , 1 from dual union all 
select 'b2000' , 'm1003' , 1 from dual union all 
select 'b3000' , 'm1001' , 1 from dual union all 
select 'b3000' , 'm1002' , 1 from dual union all 
select 'b3000' , 'm1003' , 1 from dual union all 
select 'c1000' , 'm1001' , 4 from dual union all 
select 'c1000' , 'm1002' , 4 from dual union all 
select 'c1100' , 'c1000' , 3 from dual union all 
select 'c1110' , 'c1100' , 6 from dual union all 
select 'c1200' , 'c1000' , 2 from dual union all 
select 'c2000' , 'm1001' , 8 from dual union all 
select 'c2000' , 'm1002' , 4 from dual union all 
select 'c2000' , 'm1003' , 2 from dual union all 
select 'c3000' , 'm1001' , 2 from dual union all 
select 'c3000' , 'm1003' , 2 from dual 
)        
SELECT lv
     , scd
     , val1
     , NVL ( val2, REGEXP_REPLACE ( val1, '[0-9]+', nul_v ) ) val2
     , cnt
  FROM (
          SELECT lv
               , scd
               , LTRIM (
                 CASE WHEN col1 = 1 THEN        '1'   END
              || CASE WHEN col2 = 1 THEN ',' || '2'   END
              || CASE WHEN col3 = 1 THEN ',' || '3'   END
                 , ',' ) val1
               , LTRIM (
                 CASE WHEN col1 = 1 THEN        cnt_a END
              || CASE WHEN col2 = 1 THEN ',' || cnt_b END
              || CASE WHEN col3 = 1 THEN ',' || cnt_c END 
                 , ',' ) val2
               , COL1 + COL2 + COL3 cnt
               , nul_v
            FROM (
                   SELECT MAX ( lv ) - 1 lv
                       , scd
                       , MAX ( COUNT ( DECODE ( SUBSTR ( pcd, 3 ), '001', 1 ) ) ) OVER ( PARTITION BY REGEXP_SUBSTR ( MAX ( path ), '[^/]+', 1, 2 ) ) col1
                       , MAX ( COUNT ( DECODE ( SUBSTR ( pcd, 3 ), '002', 1 ) ) ) OVER ( PARTITION BY REGEXP_SUBSTR ( MAX ( path ), '[^/]+', 1, 2 ) ) col2
                       , MAX ( COUNT ( DECODE ( SUBSTR ( pcd, 3 ), '003', 1 ) ) ) OVER ( PARTITION BY REGEXP_SUBSTR ( MAX ( path ), '[^/]+', 1, 2 ) ) col3
                       , MAX ( DECODE ( SUBSTR ( pcd, 3 ), '001', cnt ) ) cnt_a
                       , MAX ( DECODE ( SUBSTR ( pcd, 3 ), '002', cnt ) ) cnt_b
                       , MAX ( DECODE ( SUBSTR ( pcd, 3 ), '003', cnt ) ) cnt_c
                       , MAX ( cnt ) nul_v
                    FROM (
                           SELECT scd
                                , pcd
                                , cnt
                                , SYS_CONNECT_BY_PATH ( scd, '/' ) path
                                , level lv
                             FROM test a
                            START WITH pcd = 'xxxxx'
                          CONNECT BY PRIOR scd = pcd
                   ) 
                   WHERE lv >=  2
                   GROUP BY scd
          ) 
)
 ORDER BY scd
        , lv

by 마농 [2014.01.22 10:55:40]
SELECT ROW_NUMBER() OVER(ORDER BY scd) rn
     , lv
     , scd
     , LISTAGG(pcd, ',') WITHIN GROUP(ORDER BY pcd) v1
     , LISTAGG(cnt, ',') WITHIN GROUP(ORDER BY pcd) v2
     , COUNT(*) cnt
  FROM (SELECT LEVEL lv
             , CONNECT_BY_ROOT(scd) scd
             , CONNECT_BY_ROOT(cnt) cnt
             , LTRIM(SUBSTR(pcd, 3), 0) pcd
          FROM test
         WHERE CONNECT_BY_ISLEAF = 1
         START WITH pcd != 'xxxxx'
         CONNECT BY pcd != 'xxxxx'
           AND PRIOR pcd = scd
        )
 GROUP BY scd, lv
;

by 용근님 [2014.01.22 11:04:12]
감탄해버렸네요 ㅋ

by 열심히 [2014.01.22 11:05:36]

마농님 용근님 우왕.... 대단하심다. ^^
감사요~~~~^^


by 손님 [2014.01.22 12:33:59]
아.. 근데 xxxxx 가 아닌 것으로 스타트되고
xxxxx 가 아니고 상위에서 자식으로 전개되는 조건이
실데이타에서는 엄청많은 건수가 나오네요..
위에 임시데이터가 좀 부족하나봅니다..
결론은 xxxxx 에속한 애덜만 전개가 되야하는데.. 아닌아이들도 나오네요

by 마농 [2014.01.22 13:26:00]
SELECT ROW_NUMBER() OVER(ORDER BY scd) rn
     , lv
     , scd
     , LISTAGG(pcd, ',') WITHIN GROUP(ORDER BY pcd) v1
     , LISTAGG(cnt, ',') WITHIN GROUP(ORDER BY pcd) v2
     , COUNT(*) cnt
  FROM (SELECT LEVEL lv
             , CONNECT_BY_ROOT(scd) scd
             , CONNECT_BY_ROOT(cnt) cnt
             , LTRIM(SUBSTR(pcd, 3), 0) pcd
          FROM (SELECT DISTINCT *
                  FROM test
                 WHERE LEVEL > 1
                 START WITH pcd = 'xxxxx'
                 CONNECT BY PRIOR scd = pcd
                )
         WHERE CONNECT_BY_ISLEAF = 1
         CONNECT BY PRIOR pcd = scd
        )
 GROUP BY scd, lv
;

by 손님 [2014.01.22 13:31:07]
마농님 노고에 정말 감사드립니다...
꾸벅 --^^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입