마농님 계층쿼리 하나더 질문좀 드릴게요. 0 21 3,819

by 열심히 [SQL Query] [2014.01.22 21:54:17]


한가지 새로운 데이터를 발견하게 됬습니다.
다른부모를 갖는 동일 데이터가 존재한다는것이였습니다.

임시데이터에 이걸추가하면 c1100 은 이미 있는데 부모가 달라서 실행출력값에 값이 튀어나와야 하는상황입니다.
요놈 ==>   select 'c1100' , 'c2000' , 7 from dual union all
그룹바이에 걸려 아마도 안나올듯 싶은데...

주신쿼리는 찾아보아 어느정도 이해는 했으나... 참 어렵습니다. 하나이해하면 하나만 이해가 되네요
둘.3.4 10개를 이해해야 하는데.

저 다른부모를 갖는 동일 자식이 있어도 출력이 가능할런지요..

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 'c1100' , 'c2000' , 7 from dual union all    <---- 요놈
select 'c3000' , 'm1001' , 2 from dual union all
select 'c3000' , 'm1003' , 2 from dual
)
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    -- 루트노드의 ID값이 1인 것만 조회
, CONNECT_BY_ROOT(cnt) cnt
, LTRIM(SUBSTR(pcd, 3), 0) pcd
  FROM (SELECT DISTINCT *
  FROM test
WHERE LEVEL > 1
START WITH pcd = 'xxxxx'   -- 스타트 지점은 xxxxx 부터 전개
CONNECT BY PRIOR scd = pcd   -- 상위부터 자식노드로 전개
    )
WHERE CONNECT_BY_ISLEAF = 1  -- 자식이 있는지 여부 
CONNECT BY PRIOR pcd = scd
    )
 GROUP BY scd, lv

해주신 결과
---------------------------------------------------------------
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


이결과값에 하나가 더추가될듯합니다.
 2레벨  c1100 1,2,3   7,7,7

by 마농 [2014.01.23 08:48:46]
-- 기존 쿼리(http://www.gurubee.net/article/61668)에 scd2 를 추가했습니다.
-- 간략한 설명 추가합니다.
SELECT ROW_NUMBER() OVER(ORDER BY scd, scd2) 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     -- 시작점 scd
             , CONNECT_BY_ROOT(cnt) cnt     -- 시작점 cnt
             , LTRIM(SUBSTR(pcd, 3), 0) pcd -- 종료점 pcd 의 숫자만
             , scd scd2                     -- 종료점 scd
          FROM (SELECT DISTINCT *           -- 중복제거
                  FROM test 
                 WHERE LEVEL > 1            -- 시작점은 제외
                 START WITH pcd = 'xxxxx'   -- 시작점 지정
                 CONNECT BY PRIOR scd = pcd -- 상위에서 하위로 계층 전개
                ) 
         WHERE CONNECT_BY_ISLEAF = 1 -- 종료점만 추출(pcd : m1001, m1002, m1003)
         -- START WITH 시작점을 지정하지 않음. 모든 행이 시작점
         CONNECT BY PRIOR pcd = scd  -- 하위에서 상위로 계층 전개
        ) 
 GROUP BY scd, lv, scd2
;

by 손님 [2014.01.23 13:35:26]
상세한설명도 붙여주시고 ^^ 감사합니다.
추가된데이터가 자기부모 밑으로 나올수는 없나요?
c2000 밑에 c1100 이 나오면 좋울텐데... 점심맛나게드드세요 ^^

by 마농 [2014.01.23 13:59:00]
SELECT ROW_NUMBER() OVER(ORDER BY scd2, lv, scd) rn

by 손님 [2014.01.23 15:19:26]
아 정말 신기하네요... 컬럼절에 저것만 넣었는데 어떻게 정렬이되지 ㅠㅠ 신기신기.
v1컬럼에 위치값이 나와야 하는데 그냥 m1001 m1002 m1003 의 맨뒤에 숫자가 나오네요
요 최상위코드들은 정렬되있구요 여기서 m1003을
m1004 라고 가정을 한다면 m1004 의 위치값은 3입니다 몇번째에 있는놈인지 구별...
1.2.3
1.3

by 마농 [2014.01.23 16:00:09]
SELECT ROW_NUMBER() OVER(ORDER BY scd2, lv, scd) rn
     , lv
     , scd
     , LISTAGG(rk , ',') WITHIN GROUP(ORDER BY rk) v1
     , LISTAGG(cnt, ',') WITHIN GROUP(ORDER BY rk) v2
     , COUNT(*) cnt
  FROM (SELECT LEVEL lv
             , CONNECT_BY_ROOT(scd) scd
             , CONNECT_BY_ROOT(cnt) cnt
             , rk
             , scd scd2
          FROM (SELECT DISTINCT a.scd, a.pcd, a.cnt, b.rk
                  FROM test a
                     , (SELECT scd
                             , RANK() OVER(ORDER BY scd) rk -- 최상위 코드 순번 구하기
                          FROM test
                         WHERE pcd = 'xxxxx'
                        ) b
                 WHERE a.pcd = b.scd(+)
                   AND LEVEL > 1
                 START WITH a.pcd = 'xxxxx'
                 CONNECT BY PRIOR a.scd = a.pcd
                )
         WHERE CONNECT_BY_ISLEAF = 1
         CONNECT BY PRIOR pcd = scd
        )
 GROUP BY scd2, lv, scd
;

by 손님 [2014.01.27 11:00:01]
마농님 추가질문 드립니다 start with 컬럼 in ('xxxxx','zzzzz')
이렇게 n개의 스타트 지점이 생길수가 있는데 이런경우도 보내주신 쿼리가 n개 부모와 하위 자식들이정상적으로 출력이 되나요?

by 손님 [2014.01.27 11:01:20]
물론 where pcd in ('xxxxx', 'zzzzz') 도 족건이 동일하게 들어가구요

by 손님 [2014.01.23 17:08:37]
아... 감사합니다^^

by 마농 [2014.01.27 11:41:37]
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 'c1100', 'c2000', 7 FROM dual
UNION ALL SELECT 'c3000', 'm1001', 2 FROM dual
UNION ALL SELECT 'c3000', 'm1003', 2 FROM dual
----
UNION ALL SELECT 'M1001', 'zzzzz', 1 FROM dual
UNION ALL SELECT 'M1002', 'zzzzz', 1 FROM dual
UNION ALL SELECT 'M1003', 'zzzzz', 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 'C1100', 'C2000', 7 FROM dual
UNION ALL SELECT 'C3000', 'M1001', 2 FROM dual
UNION ALL SELECT 'C3000', 'M1003', 2 FROM dual
)
SELECT ROW_NUMBER() OVER(ORDER BY pcd2, scd2, lv, scd) rn
     , pcd2
     , lv
     , scd
     , LISTAGG(rk , ',') WITHIN GROUP(ORDER BY rk) v1
     , LISTAGG(cnt, ',') WITHIN GROUP(ORDER BY rk) v2
     , COUNT(*) cnt
  FROM (SELECT LEVEL lv
             , CONNECT_BY_ROOT(scd) scd
             , CONNECT_BY_ROOT(cnt) cnt
             , rk
             , pcd2
             , scd scd2
          FROM (SELECT DISTINCT CONNECT_BY_ROOT(pcd) pcd2
                     , a.scd, a.pcd, a.cnt, b.rk
                  FROM test a
                     , (SELECT scd
                             , RANK() OVER(PARTITION BY pcd ORDER BY scd) rk
                          FROM test
                         WHERE pcd IN ('xxxxx', 'zzzzz')
                        ) b
                 WHERE a.pcd = b.scd(+)
                   AND LEVEL > 1
                 START WITH a.pcd IN ('xxxxx', 'zzzzz')
                 CONNECT BY PRIOR a.scd = a.pcd
                )
         WHERE CONNECT_BY_ISLEAF = 1
         CONNECT BY PRIOR pcd2 = pcd2
                AND PRIOR pcd  = scd
                )
  GROUP  BY  pcd2,  scd2,  lv,  scd
;

by 열심히 [2014.02.11 21:58:43]


위치값 1,2,3 에 해당값 1,1,1 이것을
화면에서 실데이터로 뿌리니 모레시계가 되더라구요.
주신쿼리가 잘못되서 그런게 아니고 제가 판단을 잘못했습니다.ㅠㅠ
화면에서 위치값에 값을 넣어주려면 조건판단을 로우개수 * 컬럼개수
예를 들어 2000천건 * 컬럼이 200-500개 정도 되니까 먹통수준이 되서

실제 컬럼이 몇백개 되는경우가 10%정도 있습니다.
X1ABCFD9588G  에해당하는 9588GX1001 9588GX1002 9588GX1003 요놈들이 실제 컬럼으로 요놈들이 카운트되어 각각 뿌려주는것인데...
제가 생각을 잘못하여... 결론은 동적으로 컬럼을 넘겨서 카운트를 뽑을수있게 되야되는 구조네요
크게 두가지 유형으로

[첫번째는] 최상위코드별
화면단에서 9588GX1001 9588GX1002 9588GX1003.... n개  상위코드를(컬럼이 될놈들을 미리 알고있음)
이런식으로 출력 만약 화면에서 9588GX1001  9588GX1003  이렇게 넘어오면 이두개만 컬럼절로ㅜ 3개면 3개컬럼..
화면에서 컬럼절에 들어갈 쿼리문을 만들어서 컬럼절에 찍어서 표현할거구요...
X1ABCFD9588G 에 대한 전개  ...은 들여쓰기가 잘안되서 임의로 넣은것이여요
rn   lv   scd  ..............9588GX1001  9588GX1002  9588GX1003   cnt
1    1    95800E1000  1    ...............1 .................1    ................3   
2    1    95800F1000  1    ...............1  ......................................2
3    1    95800F2000  1    .........................................................1
4    1    95800F3000  1    ...............1 ..................1   ................3
5    1    95800G1000  4    ...............4     ..................................2
6    2    95800G1100  3    ...............3     ..................................2
7    2    95800G1200  2    ...............2 ......................................2
8    3    95800G1110  6    ...............6     ..................................2
9    1    95800G2000  8    ...............4 .................2   ................3
10   2    95800G1100  7    ..............7 .................7    ...............3
11   3    95800G1110  6    ..............6 .................6    ...............3
12   1    95800G3000  2    ..............2    ...................................2
13   2    95800G3100  1    ..............1    ...................................2


[두번째는] X1ABCFD9588G X2ABCFD9588G ..n 개  이거별 로 카운트.  전에는 xxxxx zzzzz 였는데 구분하기 위해 X1ABCFD9588G X2ABCFD9588G 로 예시를 들었습니다.
이것도 화면에서 알수있구요 화면에서 컬럼절만 X1ABCFD9588G X2ABCFD9588G 카운트하는 것을 만들어 컬럼절에 찍을거구여.
조건은 이게 맞지만 pcd IN ('X1ABCFD9588G', 'X2ABCFD9588G')
X1ABCFD9588G 전개하고 X2ABCFD9588G 전개하는것이 아니고
출력한다면 이런식으로..

rn   lv   scd    X1ABCFD9588G  X2ABCFD9588G 
1    1    95800E1000    1    1    
2    1    95800F1000    1    1    
3    1    95800F2000    1    1    
4    1    95800F3000    1    1    
5    1    95800G1000    1    1    
6    2    95800G1100    0    0    
7    2    95800G1200    1    1    
8    3    95800G1110    1    1    
9    1    95800G2000    1    1    
10   2    95800G1100    1    1    
11   3    95800G1110    1    1    
12   1    95800G3000    1    1    
13   2    95800G3100    0    0


6번 13번 이 0 0 인것은 X1ABCFD9588G X2ABCFD9588G 카운트가 다르면 0 0 으로 표시
인조건으로 X1ABCFD9588G  X2ABCFD9588G X3ABCFD9588G 가 넘어온다면 3개중 하나라도 값이 틀리도 0 0 0
 

첫번째 두번째 모두 레벨이 1-10 레벨까지 존재할수있습니다.예시데이타가 현제 별로없어서
예를 많이 못들었는데 패턴이 있었습니다.몇자리씩 끊어서 최상위레벨에
X1ABCFD9588G  -> 9588G X1 001

자꾸질문드려 죄송합니다;;
시간이 되신다면 도움주시면 감사할게요.
나름해본다고 했는데... 잘안됬어요..1주째맨붕입니다 ㅠㅠ
회사에 인터넷이 안되서...집에서 샘플로 작성했습니다.
혹시나 데이터나 설명이 틀린점 있으면 말씀주세요...

아래는 샘플데이터 새로 만들었구요..
밑에 쿼리는 마지막에 주신 쿼리였습니다.
WITH test AS
(
SELECT '9588GX1001' scd, 'X1ABCFD9588G' pcd, 1 cnt FROM dual
UNION ALL SELECT '9588GX1002', 'X1ABCFD9588G', 1 FROM dual
UNION ALL SELECT '9588GX1003', 'X1ABCFD9588G', 1 FROM dual
UNION ALL SELECT '95800E1000', '9588GX1001', 1 FROM dual
UNION ALL SELECT '95800E1000', '9588GX1002', 1 FROM dual
UNION ALL SELECT '95800E1000', '9588GX1003', 1 FROM dual
UNION ALL SELECT '95800F1000', '9588GX1001', 1 FROM dual
UNION ALL SELECT '95800F1000', '9588GX1002', 1 FROM dual
UNION ALL SELECT '95800F2000', '9588GX1003', 1 FROM dual
UNION ALL SELECT '95800F3000', '9588GX1001', 1 FROM dual
UNION ALL SELECT '95800F3000', '9588GX1002', 1 FROM dual
UNION ALL SELECT '95800F3000', '9588GX1003', 1 FROM dual
UNION ALL SELECT '95800G1000', '9588GX1001', 4 FROM dual
UNION ALL SELECT '95800G1000', '9588GX1002', 4 FROM dual
UNION ALL SELECT '95800G1100', '95800G1000', 3 FROM dual
UNION ALL SELECT '95800G1110', '95800G1100', 6 FROM dual
UNION ALL SELECT '95800G1200', '95800G1000', 2 FROM dual
UNION ALL SELECT '95800G2000', '9588GX1001', 8 FROM dual
UNION ALL SELECT '95800G2000', '9588GX1002', 4 FROM dual
UNION ALL SELECT '95800G2000', '9588GX1003', 2 FROM dual
UNION ALL SELECT '95800G1100', '95800G2000', 7 FROM dual
UNION ALL SELECT '95800G3000', '9588GX1001', 2 FROM dual
UNION ALL SELECT '95800G3000', '9588GX1003', 2 FROM dual

UNION ALL SELECT '95800G3100', '95800G3000', 1 FROM dual
 
UNION ALL SELECT '9588GX2001', 'X2ABCFD9588G', 1 FROM dual
UNION ALL SELECT '9588GX2002', 'X2ABCFD9588G', 1 FROM dual
UNION ALL SELECT '9588GX2003', 'X2ABCFD9588G', 1 FROM dual
UNION ALL SELECT '95800E1000', '9588GX2001', 1 FROM dual
UNION ALL SELECT '95800E1000', '9588GX2002', 1 FROM dual
UNION ALL SELECT '95800E1000', '9588GX2003', 1 FROM dual
UNION ALL SELECT '95800F1000', '9588GX2001', 1 FROM dual
UNION ALL SELECT '95800F1000', '9588GX2002', 1 FROM dual
UNION ALL SELECT '95800F2000', '9588GX2003', 1 FROM dual
UNION ALL SELECT '95800F3000', '9588GX2001', 1 FROM dual
UNION ALL SELECT '95800F3000', '9588GX2002', 1 FROM dual
UNION ALL SELECT '95800F3000', '9588GX2003', 1 FROM dual
UNION ALL SELECT '95800G1000', '9588GX2001', 4 FROM dual
UNION ALL SELECT '95800G1000', '9588GX2002', 4 FROM dual
--UNION ALL SELECT '95800G1100', '95800G1000', 3 FROM dual
UNION ALL SELECT '95800G1110', '95800G1100', 6 FROM dual
UNION ALL SELECT '95800G1200', '95800G1000', 2 FROM dual
UNION ALL SELECT '95800G2000', '9588GX2001', 8 FROM dual
UNION ALL SELECT '95800G2000', '9588GX2002', 4 FROM dual
UNION ALL SELECT '95800G2000', '9588GX2003', 2 FROM dual
UNION ALL SELECT '95800G1100', '95800G2000', 7 FROM dual
UNION ALL SELECT '95800G3000', '9588GX2001', 2 FROM dual
UNION ALL SELECT '95800G3000', '9588GX2003', 2 FROM dual
)
SELECT ROW_NUMBER() OVER(ORDER BY scd2, lv, scd) rn
, lv
, scd
, LISTAGG(rk , ',') WITHIN GROUP(ORDER BY rk) v1
, LISTAGG(cnt, ',') WITHIN GROUP(ORDER BY rk) v2
, COUNT(*) cnt
  FROM (SELECT LEVEL lv
, CONNECT_BY_ROOT(scd) scd
, CONNECT_BY_ROOT(cnt) cnt
, rk
, scd scd2
  FROM (SELECT DISTINCT a.scd, a.pcd, a.cnt, b.rk
  FROM test a
, (SELECT scd
, RANK() OVER(ORDER BY scd) rk -- 최상위 코드 순번 구하기
  FROM test
WHERE pcd = 'X1ABCFD9588G'
    ) b
WHERE a.pcd = b.scd(+)
   AND LEVEL > 1
START WITH a.pcd = 'X1ABCFD9588G'
CONNECT BY PRIOR a.scd = a.pcd
    )
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR pcd = scd
    )
 GROUP BY scd2, lv, scd


by 마농 [2014.02.12 09:50:04]

전혀 다른 문제가 되었네요.
애초에 이렇게 질문을 하셨으면 더 쉽게 접근했을지도...


그런데 주어진 예시 자료가 오류입니다.
위 아래 최상위 부모(1,2 레벨)를 달리하여 예시를 주었으나...
1,2 레벨을 지나 3레벨 부터는 구별이 가질 않습니다.
즉 똑같은 자료가 두건씩 들어 있는 형태입니다.
즉 하나의 자식이 두개의 부모를 갖는 형태가 되어 버리게 되고
또한 하나의 부모가 똑같은 자식을 둘씩 가지게 됩니다.
자료가 꼬이게 됩니다.


아래 쪽에 주석처리한것처럼 아예 똑같은 자료를 모두 없애야 합니다.
이렇게 되면 하나의 자식이 두개의 부모를 갖게 되지만
하나의 부모가 똑같은 자식을 둘씩 가지지는 않죠.
즉 같은 자료는 중복되지 않아야 합니다.
이렇게 되면 예를 드신 6,13 라인도 0,0 이 아닌 1,1 이 나오게 되겠지요.


똑같은 것이 중복될수밖에 없는 구조라면?
같지 않음을 구별할 수 있도록 테이블 설계가 변경되어야 합니다.
제생각에는 2레벨을 1레벨로 올리고
1레벨은 별도 최상위구분 컬럼으로 가야 하지 않을까? 생각되네요.


by 손님 [2014.02.12 16:45:00]
아.... 근데 의도적으로 그렇게 만들었다네요..
문제가 되는부분은 이렇게 해결하면 된다고합니다 다른프로그램들도 이렇게 사용한다네요.
쿼리보시면 안쪽에 전개하는부분에서 풀 path 를
얻고 (sys_connect_path)로 그다음 한번감싸서 그룹바이할때 단일전개할때는 패스의 뒷자리결과 자른코드. scd. 디코드 (레벨.2.null.substr (pcd.1.5) 로그룹바이 하면 되더라구요 복수인자로 전개되는경우는
디코드 (레벨.1.scd.substr (pcd.1.5)||`=`||scd)

이런식으로 하면.. 되는데 여기임터넷이 안되서 참어렵네요 ㅠㅠ 자식은 다른부모 밑으로 중복될수있습니다
화면이 좀 특이해서 저런구조로 했다고 하네요. 디비구조는 변경불가인듯 합니다 이미 저런방식으로 사용되고있는 시스템이 존재한다네요 하나의 자식이 복수의 부모를 갖는구조입니다 실제로 쿼리결과가 나오구요 근데 그쿼리를 받을수없는상황이라서 다른시스템에 비슷한 화면이 있는데 줄수가없답니다.ㅡㅡ 제가말씀드린 결과값처럼 나올수 없는건가요?

by 마농 [2014.02.12 18:51:06]
결과도출은 가능합니다.
그러나 그전에 데이터 검증이 먼저입니다.

1. 물론 하나의 자식이 두개의 부모를 가질수 있습니다.
하지만 동일한 자식을 두개 만들면 안되죠.
카티션 프러덕이 발생됩니다.
UNION ALL SELECT '95800G1200', '95800G1000', 2 FROM dual
UNION ALL SELECT '95800G1200', '95800G1000', 2 FROM dual
동일한 이 두개 행을 하나로 만들어야 합니다.

2. 또한 하나의 자료가 하나의 부모에만 속하고 다른 부모에는 속하지 않을 수도 없습니다.
6,13라인 같은 경우죠. 원하시는 0,0 이 나올수가 없습니다.

3. 두개의 동일한 자료에 대해 최상위 부모가 다르다는 것을 알수있는 식별자가 있어야 합니다.
그렇지 않다면 위 1,2 에 언급한 현상이 발생됩니다.

by 손님 [2014.02.13 13:17:46]
마농님말씀이 맞네요... 제가 샘플데이터를 바꾸는 과정에서 잘못바꾸었습니다.
핸폰이라 샘플데이터를 수정할수없네요.
중복되는부분의 자식코드만 다른걸로 고쳐서
결과값이나오게 부탁드립니다 마농님 귀찮게해서 죄송요 ㅠㅠ

by 열심히 [2014.02.12 19:48:29]

마농님말씀이 맞는것 같습니다.. 제가 샘플데이터를 착오로 잘못만든것 같네요..
다시 확인해보고 샘플 다시 올리겠습니다.
감사합니다^^


by 마농 [2014.02.13 16:16:56]
WITH test AS
(
SELECT '9588GX1001' scd, 'X1ABCFD9588G' pcd, 1 cnt FROM dual
UNION ALL SELECT '9588GX1002', 'X1ABCFD9588G', 1 FROM dual
UNION ALL SELECT '9588GX1003', 'X1ABCFD9588G', 1 FROM dual
UNION ALL SELECT '95800E1000', '9588GX1001', 1 FROM dual
UNION ALL SELECT '95800E1000', '9588GX1002', 1 FROM dual
UNION ALL SELECT '95800E1000', '9588GX1003', 1 FROM dual
UNION ALL SELECT '95800F1000', '9588GX1001', 1 FROM dual
UNION ALL SELECT '95800F1000', '9588GX1002', 1 FROM dual
UNION ALL SELECT '95800F2000', '9588GX1003', 1 FROM dual
UNION ALL SELECT '95800F3000', '9588GX1001', 1 FROM dual
UNION ALL SELECT '95800F3000', '9588GX1002', 1 FROM dual
UNION ALL SELECT '95800F3000', '9588GX1003', 1 FROM dual
UNION ALL SELECT '95800G1000', '9588GX1001', 4 FROM dual
UNION ALL SELECT '95800G1000', '9588GX1002', 4 FROM dual
UNION ALL SELECT '95800G1100', '95800G1000', 3 FROM dual
UNION ALL SELECT '95800G1110', '95800G1100', 6 FROM dual
UNION ALL SELECT '95800G1200', '95800G1000', 2 FROM dual
UNION ALL SELECT '95800G2000', '9588GX1001', 8 FROM dual
UNION ALL SELECT '95800G2000', '9588GX1002', 4 FROM dual
UNION ALL SELECT '95800G2000', '9588GX1003', 2 FROM dual
UNION ALL SELECT '95800G1100', '95800G2000', 7 FROM dual
UNION ALL SELECT '95800G3000', '9588GX1001', 2 FROM dual
UNION ALL SELECT '95800G3000', '9588GX1003', 2 FROM dual
UNION ALL SELECT '95800G3100', '95800G3000', 1 FROM dual
)
SELECT ROW_NUMBER() OVER(ORDER BY pth, scd, lv) rn
     --, pth
     , lv, scd
     , MIN(DECODE(rcd, '9588GX1001', cnt)) "9588GX1001"
     , MIN(DECODE(rcd, '9588GX1002', cnt)) "9588GX1002"
     , MIN(DECODE(rcd, '9588GX1003', cnt)) "9588GX1003"
     , COUNT(*) cnt
  FROM (SELECT SYS_CONNECT_BY_PATH(scd, '-') pth
             , scd
             , LEVEL lv
             , CONNECT_BY_ROOT pcd rcd
             , cnt
          FROM test
         START WITH pcd IN ('9588GX1001', '9588GX1002', '9588GX1003')
         CONNECT BY PRIOR scd = pcd
        )
 GROUP BY pth, scd, lv
;
-- 두가지 결과를 요청하셨는데 첫번째 결과만 올립니다.
-- 두번째는 샘플과 결과를 다시 올려주세요.

by 열심히 [2014.02.13 23:53:03]

스타트 지점이 하나 일경우(물론 둘일때도 마찬가지입니다.) 
START WITH 스타트 지점은  X1ABCFD9588G  이것이 되야 합니다.
화면에서 인자로 스타트 지점이 X1ABCFD9588G  이놈레벨의 값이 넘어옴니다.
'9588GX1001', '9588GX1002', '9588GX1003' 이레벨은 갯수가 500개까지 들어옴니다.
9588GX1001', '9588GX1002', '9588GX1003' 이기준으로 컬럼이 생성되며 컬럼이 1개부터 ~ 500 사이이고
보통 1-100 개사이 10% 정도가 500개의 9588GX1001', '9588GX1002', '9588GX1003' ...n 이 있습니다.
1레벨 이지만 이놈은 출력대상이 아닌지라.. 이전에는 LEVEL > 1 로 해주셨네요.

두번째 예시로 샘플올릴게요. 엄청 햇갈리네요 ^^;; 이런형태가 첨이라서 ...
혹 데이터 문제가 또있다면 말씀주세요... 저도 푸는중입니다. 집합이 안되요 ㅠㅠ
좋은꿈꾸세요~

-- X1ABCFD9588G , X2ABCFD9588G  두개의 인자가 넘어올경우
WITH test AS
(
SELECT '9588GX1001' scd, 'X1ABCFD9588G' pcd, 1 cnt FROM dual
UNION ALL SELECT '9588GX1002', 'X1ABCFD9588G', 1 FROM dual
UNION ALL SELECT '9588GX1003', 'X1ABCFD9588G', 1 FROM dual
UNION ALL SELECT '11111A1000', '9588GX1001', 1 FROM dual
UNION ALL SELECT '11111A1000', '9588GX1002', 1 FROM dual
UNION ALL SELECT '11111A1000', '9588GX1003', 1 FROM dual
UNION ALL SELECT '11111B1000', '9588GX1001', 1 FROM dual
UNION ALL SELECT '11111B1000', '9588GX1002', 1 FROM dual
UNION ALL SELECT '11111C1000', '9588GX1003', 1 FROM dual
UNION ALL SELECT '11111D1000', '9588GX1001', 1 FROM dual
UNION ALL SELECT '11111D1000', '9588GX1002', 1 FROM dual
UNION ALL SELECT '11111D1000', '9588GX1003', 1 FROM dual
UNION ALL SELECT '11111E1000', '9588GX1001', 4 FROM dual
UNION ALL SELECT '11111E1000', '9588GX1002', 4 FROM dual
UNION ALL SELECT '11111E1100', '11111E1000', 3 FROM dual
UNION ALL SELECT '11111E1110', '11111E1100', 6 FROM dual
UNION ALL SELECT '11111E1200', '11111E1000', 2 FROM dual
UNION ALL SELECT '11111E2000', '9588GX1001', 8 FROM dual
UNION ALL SELECT '11111E2000', '9588GX1002', 4 FROM dual
UNION ALL SELECT '11111E2000', '9588GX1003', 2 FROM dual
UNION ALL SELECT '11111E1100', '11111E2000', 7 FROM dual
UNION ALL SELECT '11111E3000', '9588GX1001', 2 FROM dual
UNION ALL SELECT '11111E3000', '9588GX1003', 2 FROM dual

UNION ALL SELECT '11111E3100', '11111E3000', 1 FROM dual
 
UNION ALL SELECT '9588GX2001', 'X2ABCFD9588G', 1 FROM dual
UNION ALL SELECT '9588GX2002', 'X2ABCFD9588G', 1 FROM dual
UNION ALL SELECT '9588GX2003', 'X2ABCFD9588G', 1 FROM dual
UNION ALL SELECT '22222A1000', '9588GX2001', 1 FROM dual
UNION ALL SELECT '22222A1000', '9588GX2002', 1 FROM dual
UNION ALL SELECT '22222A1000', '9588GX2003', 1 FROM dual
UNION ALL SELECT '22222B1000', '9588GX2001', 1 FROM dual
UNION ALL SELECT '22222B1000', '9588GX2002', 1 FROM dual
UNION ALL SELECT '22222C2000', '9588GX2003', 1 FROM dual
UNION ALL SELECT '22222D3000', '9588GX2001', 1 FROM dual
UNION ALL SELECT '22222D3000', '9588GX2002', 1 FROM dual
UNION ALL SELECT '22222D3000', '9588GX2003', 1 FROM dual
UNION ALL SELECT '22222E1000', '9588GX2001', 4 FROM dual
UNION ALL SELECT '22222E1000', '9588GX2002', 4 FROM dual
UNION ALL SELECT '22222E1110', '22222E1100', 6 FROM dual
UNION ALL SELECT '22222E1200', '22222E1000', 2 FROM dual
UNION ALL SELECT '22222E2000', '9588GX2001', 8 FROM dual
UNION ALL SELECT '22222E2000', '9588GX2002', 4 FROM dual
UNION ALL SELECT '22222E2000', '9588GX2003', 2 FROM dual
UNION ALL SELECT '22222E1100', '22222E2000', 7 FROM dual
UNION ALL SELECT '22222E3000', '9588GX2001', 2 FROM dual
UNION ALL SELECT '22222E3000', '9588GX2003', 2 FROM dual
)



by 마농 [2014.02.14 12:14:27]

질문을 정리해 볼까요?

1. 스타트 지점은  X1ABCFD9588G  이것이 되야 합니다.
=> 가능합니다. 인라인 뷰 쿼리 조금만 손보면 됩니다.
단, 어차피 컬럼을 생성하는 기준은 그 다음 레벨입니다.
또한 컬럼을 생성하기 위해서는 이미 그 값들을 알고 있어야만 하구요.
위 질문에서도 그 값들은 이미 알고 있다고 하셨죠?
(주의. 오라클 버전이 어떻게 되는지? 예전에 MIN(DECODE( 많이 쓰면 에러 났었는데... 11G 테스트 결과 500개 이상 무)
시작점이 하나만 오는것 맞죠? 2레벨을 컬럼으로 구별하여 Sum 하기.
시작점이 두개 오는거는 두번째 전혀 다른 질문인거죠? 1레벨을 컬럼으로 구별하여 Count 하기.

2. 두번째 질문 관련
예시를 새로 올리셨는데요. 그에 대한 결과도 올려주세요.


by 마농 [2014.02.17 08:37:04]
새로운 질문은 새로운 게시글로 등록하시는게 좋을 듯 하구요...
길게 말로 쓰셨는데...전달이 부족해 보이네요.
그냥 쿼리랑 실행계획을 올리시는게...

by 열심히 [2014.02.17 09:36:20]
아 여기가 인터넷이안되요 ^^ 새로운질문은
나중에 새로 글을 등록하는걸로 하겠습니다.

그리고 예시데이터를 바꿔야 할것 같습니다
휴대폰으로 오전중에 쓰려고했는데 제약이있어서...

by 열심히 [2014.02.17 23:43:20]

아직 완성 안됬습니다 ^^;; 분명 이런식에 데이터인데 뭔가하나 빠트려서..

WITH TEST AS
(
-------------------------------------------------------------X1
SELECT '8520PX1001' SCD, 'X1ABCFD8520P' PCD, 1 CNT FROM DUAL UNION ALL
SELECT '8520PX1002', 'X1ABCFD8520P',   1 FROM DUAL UNION ALL
SELECT '8520PX1003', 'X1ABCFD8520P',   1 FROM DUAL UNION ALL

SELECT '852109B000', '8520PX1001', 1 FROM DUAL UNION ALL
SELECT '852209B000', '8520PX1002', 1 FROM DUAL UNION ALL
SELECT '852109B000', '8520PX1003', 1 FROM DUAL UNION ALL

SELECT '852167M000', '8520PX1001', 2 FROM DUAL UNION ALL
SELECT '852167M000', '8520PX1001', 2 FROM DUAL UNION ALL
SELECT '852167M000', '8520PX1002', 2 FROM DUAL UNION ALL
--------------------------------------------------------------X2
SELECT '8520PX2001', 'X2ABCFD8520P',   1 FROM DUAL UNION ALL
SELECT '8520PX2002', 'X2ABCFD8520P',   1 FROM DUAL UNION ALL
SELECT '8520PX2013', 'X2ABCFD8520P',   1 FROM DUAL UNION ALL
SELECT '8520PX2014', 'X2ABCFD8520P',   1 FROM DUAL UNION ALL
SELECT '8520PX2024', 'X2ABCFD8520P',   1 FROM DUAL UNION ALL


SELECT '852109B000', '8520PX2001', 1 FROM DUAL UNION ALL
SELECT '852209B000', '8520PX2001', 1 FROM DUAL UNION ALL
SELECT '852109B000', '8520PX2002', 1 FROM DUAL UNION ALL
SELECT '852109B000', '8520PX2013', 1 FROM DUAL UNION ALL
SELECT '852109B000', '8520PX2014', 1 FROM DUAL UNION ALL
SELECT '852209B000', '8520PX2014', 1 FROM DUAL UNION ALL
SELECT '852209B000', '8520PX2024', 1 FROM DUAL UNION ALL

SELECT '852167M000', '852109B000', 2 FROM DUAL UNION ALL
SELECT '852167M000', '852209B000', 2 FROM DUAL UNION ALL
SELECT '852167M000', '852109B000', 2 FROM DUAL UNION ALL
SELECT '852167M000', '852109B000', 2 FROM DUAL UNION ALL
SELECT '852167M000', '852209B000', 2 FROM DUAL UNION ALL
SELECT '852167M000', '852109B000', 2 FROM DUAL UNION ALL
SELECT '852167M000', '852209B000', 2 FROM DUAL UNION ALL

SELECT '852117M000', '852109B000', 2 FROM DUAL UNION ALL
SELECT '852117M000', '852109B000', 2 FROM DUAL UNION ALL
SELECT '852117M000', '852109B000', 2 FROM DUAL
)
SELECT  LEVEL LVL
   ,A.SCD
   ,A.PCD
   ,A.CNT
   ,SUBSTR( SYS_CONNECT_BY_PATH(SCD, '='), 2) PATH
  FROM TEST A
 WHERE 1=1
 AND LEVEL > 1
 START WITH A.PCD IN ('X1ABCFD8520P', 'X2ABCFD8520P')
 CONNECT BY PRIOR A.SCD = A.PCD

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