출처 : http://blog.naver.com/cjj721004?Redirect=Log&logNo=120116296911
1. SYS_CONNECT_BY_PATH(column,char)은 데이터의 셀프조인에 의한 트리구조 데이테에 대해서 한row에서 표현할때 사용하는 것입니다.
간단히 말씀드리면, FIle시스템에서 파일이 위치하는 경로에 대해서 표현하는것과 같다고 생각하시면 됩니다.
2. "START WITH rnum = 1" 은 경로중에서 root에 해당하는 부분을 어디서 부터 할것인지 설정한는 조건을 말합니다.
3. "CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR a = a"은 계층을 만들기 위한 조인부분으로 경로의 다음 연결 경로를 지정하는 역할을 합니다.
즉, A -> B -> C 로 데이터의 연결을 만들기 위함입니다.
4. 이함수는 Oracle에서 사용하는 것으로, 특별한 환경 설정은 없으며, 단지 Data에 대해서 계층구조를 가지고 있다면 언제든지 사용을 할수 있습니다.
가장 대표적인 예제로는 회의의 조직도 Data가 되겠네요.. ^^
출처 : Tong - redyoon님의 DB통
간단한 샘플
SELECT A
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
GROUP BY A
다음의 실행하면 결과값은 다음과 같이 나올 것이다.
A | B
1 | 엄마,아빠,이모
2 | 삼촌,오빠
---------------
어떻게 만들어지는지 분석모드
SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL
A B
1 엄마
1 아빠
1 이모
2 삼촌
2 오빠
일단 기본 데이터를 정의한다.
SELECT A,B,
ROW_NUMBER() OVER( ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
A B RNUM
1 엄마 1
1 아빠 2
1 이모 3
2 삼촌 4
2 오빠 5
기본데이터를 ROW_NUMBER() OVER( ORDER BY A) 를 사용해서 A 컬럼을 기준으로 정렬을해서 RNUM 을 정의해준다.
SELECT A
,SUBSTR(SYS_CONNECT_BY_PATH(B, ','),2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
A B
1 엄마
1 엄마,아빠
1 엄마,아빠,이모
2 삼촌
2 삼촌,오빠
위에 나온데이터를 기준으로 SYS_CONNECT_BY_PATH 를 써줬다.
START WITH RNUM = 1 시작 기준점을 RNUM 1을 시작점으로 잡고
CONNECT BY PRIOR RNUM = RNUM - 1 RNUM과 하나작은 RNUM과 결합하면서,
AND PRIOR A = A A값을 기준으로 같은거 끼리 묶어줬다.
CONNECT BY PRIOR RNUM = RNUM - 1 를 이해할때는 하나의 BOM을 생각하면 될거다.
시작은 RNUM = 1 로 시작하고
RNUM 이 2이면 RNUM 1과 2를 합치면서 A와 A가 같은걸 결합
RNUM 이 3이면 RNUM 3과 2를 합치고 A와 A가 같은걸 결합
SELECT A
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
GROUP BY A
A B
1 엄마,아빠,이모
2 삼촌,오빠
그룹함수를 써서 A로 그룹지어서 가장큰 값을 뽑아오면 끝
[출처] SYS_CONNECT_BY_PATH , CONNECT BY|작성자 아기대장
