꿈꾸는 개발자, DBA 커뮤니티 구루비

계층형구조에서 부모의 이름찾기

by 트집이 [Oracle 기초] oracle plsql [2023.06.01 10:13:15]

WITH T AS (
SELECT 101 ID,	1 IDX ,	1 LVL,	'AAAAA' P_CODE,	'BBBBB' C_CODE,	'강호동' NAME FROM DUAL
UNION ALL
SELECT 102,	2,	2,	'BBBBB',	'CCCCC',	'차인표' FROM DUAL
UNION ALL
SELECT 103,	3,	3,	'CCCCC',	'JJJJJ',	'강부자' FROM DUAL
UNION ALL
SELECT 104,	4,	3,	'CCCCC',	'DDDDD',	'유재석' FROM DUAL
UNION ALL
SELECT 105,	5,	4,	'DDDDD',	'KKKKK',	'이효리' FROM DUAL
UNION ALL
SELECT 106,	6,	4,	'DDDDD',	'LLLLL',	'김민희' FROM DUAL
UNION ALL
SELECT 107,	7,	3,	'CCCCC',	'EEEEE',	'김혜자' FROM DUAL
UNION ALL
SELECT 108,	8,	4,	'EEEEE',	'FFFFF',	'고두심' FROM DUAL
UNION ALL
SELECT 109,	9,	5,	'FFFFF',	'MMMMM',	'이영자' FROM DUAL
UNION ALL
SELECT 110,	10,	4,	'EEEEE',	'NNNNN',	'전현무' FROM DUAL
UNION ALL
SELECT 111,	11,	4,	'EEEEE',	'OOOOO',	'나혜심' FROM DUAL
UNION ALL
SELECT 112,	12,	4,	'EEEEE',	'PPPPP',	'고수'   FROM DUAL
UNION ALL
SELECT 113,	13,	4,	'EEEEE',	'GGGGG',	'차두리' FROM DUAL
UNION ALL
SELECT 114,	14,	5,	'GGGGG',	'MMMMM',	'이영자' FROM DUAL
UNION ALL
SELECT 115,	15,	4,	'EEEEE',	'HHHHH',	'이영표' FROM DUAL
UNION ALL
SELECT 116,	16,	5,	'HHHHH',	'MMMMM',	'이영자' FROM DUAL
UNION ALL
SELECT 117,	17,	3,	'CCCCC',	'QQQQQ',	'손흥민' FROM DUAL
UNION ALL
SELECT 118,	18,	3,	'CCCCC',	'IIIII',	'김민재' FROM DUAL
UNION ALL
SELECT 119,	19,	4,	'IIIII',	'MMMMM',	'이영자' FROM DUAL
UNION ALL
SELECT 120,	20,	4,	'IIIII',	'RRRRR',	'이경규' FROM DUAL
UNION ALL
SELECT 121,	21,	1,	'AAAAA',	'UUUUU',	'박지성' FROM DUAL
UNION ALL
SELECT 122,	22,	2,	'UUUUU',	'ZZZZZ',	'곽부성' FROM DUAL
UNION ALL
SELECT 123,	23,	3,	'ZZZZZ',	'SSSSS',	'원빈' FROM DUAL
UNION ALL
SELECT 124,	24,	3,	'ZZZZZ',	'TTTTT',	'이나영' FROM DUAL
)
SELECT T1.ID, T1.IDX, T1.LVL, T1.P_CODE, T1.C_CODE, T1.NAME, T2.NAME P_NAME
FROM T T1
	, (
		SELECT 	C_CODE
			  , NAME
		FROM 	T
		GROUP BY 
				C_CODE 
			  , NAME
	) T2
WHERE 	T1.P_CODE = T2.C_CODE(+)
ORDER BY 
		IDX;

위와 같이 계층형 구조의 데이터가 있다고 가정합니다.

ID는 데이터이 PK값이고 IDX는 전체 데이터의 순번입니다.

전체 데이터의 구조가 LVL이 1이고 P_CODE가 'AAAAA'인 데이터가 전체데이터를 보면 두군데가 존재합니다.

실제로는 더 많이 존재합니다.

요점은 하나의 Row에 2개의 컬럼(P_NAME, 부모코드의 ID(PK)값)을 추가해서 표현하고 싶습니다.

이때 제약조건이 있는데 부모를 찾아가는 과정에서 자신의 가장 최상위 부모인 'AAAAA'코드를 만나면 그 범위를 벗어나면 안 됩니다.

즉, 위 sample 데이터 기준으로 보면 IDX 1 ~20까지가 하나의 데이터셋이고 21~24까지가 또 하나의 데이터셋이라고 보고 부모의 이름을 그 범위안에서 찾아야 한다 입니다.

제가 글로 설명이 잘 되었는지 모르겠습니다. ㅠㅠ

고수님들의 도움 부탁드립니다.

참고로 실제 데이터의 크기는 대략 2000건정도입니다.

감사합니다.

by 마농 [2023.06.01 10:50:22]

1. 그룹을 나누어야 한다고 하셨는데?
- 샘플 데이터만 보면 두 그룹이 서로 전혀 겹치질 않고 있습니다.
- 굳이 그룹을 나눈다고 설명하신 이유가 있는지? 겹치는 데이터가 존재하는지?
2. c_code 가 중복되는데? (MMMMM, 이영자)
- 중복되는 것 맞나요? 계층구조에서는 중복이 없어야 하는데요?
- 지금은 이영자가 최하위라 문제가 없을 수도 있지만
- 최하위가 아닌 중간단계라면? 계층구조가 꼬일수 있습니다.

by 트집이 [2023.06.01 13:28:00]

1. 그룹을 나누는 이유는 C_CODE가 'RRRRR'라는 데이터를 보면

SELECT 120, 20, 4, 'IIIII', 'RRRRR', '이경규' FROM DUAL --> 부모의 PK는 118

부모를 찾아과는 과정에서 'IIII'를 C_CODE로 가지는 데이터가 해당 그룹을 벗어나서 다른 그룹에도 존재할수 있기 때문입니다.

2. C_CODE는 중복되는 것이 맞습니다. 실제 데이터가 그렇게 구성되어 있어요.

제가 계층구조라는 표현을 써서 오해의 소지가 있었던거 같아요. 데이터 구조는 마치 계층형 구조처럼 Level 부모코드, 자식코드로 이루어져 있지만 자식코드(C_CODE)만 놓고 보면 중복데이터가 있습니다.

하지만 (부모 + 자식)을 하나로 놓고 보면 중복데이터가 없어요.

샘플 데이터를 놓고 보면 최상위 노드가

SELECT 101 ID, 1 IDX , 1 LVL, 'AAAAA' P_CODE, 'BBBBB' C_CODE, '강호동' NAME FROM DUAL

SELECT 121, 21, 1, 'AAAAA', 'UUUUU', '박지성' FROM DUAL

이렇게 2개가 존재하고 이 노드를 기준으로 계층형(?) 구조를 가지고 있어요. 또 계층형 구조라는 표현을 썼는데 달리 표현할 말이 떠오르지 않아서요.

전체 데이터를 놓고 보면 하나의 그룹(?)을 복사 붙여놓고 최상위 부모 아래로 다시 데이터를 구성했다고 보면 되는 것 같아요. 제가 쓰면서도 무슨말인지.... ;;

아무튼 감사합니다.

by 마농 [2023.06.01 14:09:23]

1. 그룹별 겹치는 자료가 존재하는 군요?
- 겹치지 않은 평범한 샘플보다는, 이러한 특수한 상황을 반영해주는 겹치는 샘플을 주셔야 합니다.
- 그룹을 나누는 이유도 겹치는 자료 때문인 듯 하네요?

by 마농 [2023.06.01 14:15:38]

WITH t AS
(
SELECT 101 id, 1 idx, 1 lvl, 'AAAAA' p_code, 'BBBBB' c_code, '강호동' name FROM dual
UNION ALL SELECT 102,  2, 2, 'BBBBB', 'CCCCC', '차인표' FROM dual
UNION ALL SELECT 103,  3, 3, 'CCCCC', 'JJJJJ', '강부자' FROM dual
UNION ALL SELECT 104,  4, 3, 'CCCCC', 'DDDDD', '유재석' FROM dual
UNION ALL SELECT 105,  5, 4, 'DDDDD', 'KKKKK', '이효리' FROM dual
UNION ALL SELECT 106,  6, 4, 'DDDDD', 'LLLLL', '김민희' FROM dual
UNION ALL SELECT 107,  7, 3, 'CCCCC', 'EEEEE', '김혜자' FROM dual
UNION ALL SELECT 108,  8, 4, 'EEEEE', 'FFFFF', '고두심' FROM dual
UNION ALL SELECT 109,  9, 5, 'FFFFF', 'MMMMM', '이영자' FROM dual
UNION ALL SELECT 110, 10, 4, 'EEEEE', 'NNNNN', '전현무' FROM dual
UNION ALL SELECT 111, 11, 4, 'EEEEE', 'OOOOO', '나혜심' FROM dual
UNION ALL SELECT 112, 12, 4, 'EEEEE', 'PPPPP', '고수'   FROM dual
UNION ALL SELECT 113, 13, 4, 'EEEEE', 'GGGGG', '차두리' FROM dual
UNION ALL SELECT 114, 14, 5, 'GGGGG', 'MMMMM', '이영자' FROM dual
UNION ALL SELECT 115, 15, 4, 'EEEEE', 'HHHHH', '이영표' FROM dual
UNION ALL SELECT 116, 16, 5, 'HHHHH', 'MMMMM', '이영자' FROM dual
UNION ALL SELECT 117, 17, 3, 'CCCCC', 'QQQQQ', '손흥민' FROM dual
UNION ALL SELECT 118, 18, 3, 'CCCCC', 'IIIII', '김민재' FROM dual
UNION ALL SELECT 119, 19, 4, 'IIIII', 'MMMMM', '이영자' FROM dual
UNION ALL SELECT 120, 20, 4, 'IIIII', 'RRRRR', '이경규' FROM dual
UNION ALL SELECT 121, 21, 1, 'AAAAA', 'UUUUU', '박지성' FROM dual
UNION ALL SELECT 122, 22, 2, 'UUUUU', 'ZZZZZ', '곽부성' FROM dual
UNION ALL SELECT 123, 23, 3, 'ZZZZZ', 'SSSSS', '원빈'   FROM dual
UNION ALL SELECT 124, 24, 3, 'ZZZZZ', 'TTTTT', '이나영' FROM dual
)
SELECT a.id, a.idx, a.lvl, a.p_code, a.c_code, a.name
     , MAX(b.idx) p_idx
     , MAX(b.id)   KEEP(DENSE_RANK FIRST ORDER BY b.idx DESC) p_id
     , MAX(b.name) KEEP(DENSE_RANK FIRST ORDER BY b.idx DESC) p_name
  FROM t a
  LEFT OUTER JOIN t b
    ON a.idx > b.idx
   AND a.p_code = b.c_code
 GROUP BY a.id, a.idx, a.lvl, a.p_code, a.c_code, a.name
;

1. 부모코드를 셀프조인 하는데
2. idx 가 작은 부분만 조인
3. 그중에서 idx 가 가장 큰것만 추출

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변