이번 퀴즈로 배워보는 SQL 시간에는 시작좌표와 끝좌표를 가진 파이프들 끼리의 연결 관계를 파악하는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.
진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.
문제
- [리스트 1] 원본리스트
-
CREATE TABLE t AS SELECT '01' id, 1 s_x, 3 s_y, 4 s_z, 1 e_x, 2 e_y, 4 e_z FROM dual UNION ALL SELECT '02', 2, 8, 7, 8, 8, 7 FROM dual UNION ALL SELECT '03', 1, 4, 6, 1, 4, 5 FROM dual UNION ALL SELECT '04', 1, 6, 7, 1, 5, 7 FROM dual UNION ALL SELECT '05', 9, 1, 4, 3, 1, 4 FROM dual UNION ALL SELECT '06', 6, 1, 1, 7, 1, 0 FROM dual UNION ALL SELECT '07', 4, 1, 3, 5, 1, 2 FROM dual UNION ALL SELECT '08', 2, 8, 7, 1, 6, 7 FROM dual UNION ALL SELECT '09', 1, 5, 7, 1, 4, 6 FROM dual UNION ALL SELECT '10', 8, 8, 7, 8, 7, 7 FROM dual UNION ALL SELECT '11', 1, 2, 4, 2, 1, 4 FROM dual UNION ALL SELECT '12', 3, 1, 4, 4, 1, 3 FROM dual UNION ALL SELECT '13', 5, 1, 2, 6, 1, 1 FROM dual UNION ALL SELECT '14', 1, 4, 5, 1, 3, 4 FROM dual; SELECT * FROM t;
- [표 1] 원본테이블
-
ID S_X S_Y S_Z E_X E_Y E_Z ---- ---------- ---------- ---------- ---------- ---------- ---------- 01 1 3 4 1 2 4 02 2 8 7 8 8 7 03 1 4 6 1 4 5 04 1 6 7 1 5 7 05 9 1 4 3 1 4 06 6 1 1 7 1 0 07 4 1 3 5 1 2 08 2 8 7 1 6 7 09 1 5 7 1 4 6 10 8 8 7 8 7 7 11 1 2 4 2 1 4 12 3 1 4 4 1 3 13 5 1 2 6 1 1 14 1 4 5 1 3 4
- [표 2] 결과테이블
-
GR_I SEQ PATH ---- ---------- -------------------- 02 1 02 02 2 02-10 05 1 05 05 2 05-12 05 3 05-12-07 05 4 05-12-07-13 05 5 05-12-07-13-06 08 1 08 08 2 08-04 08 3 08-04-09 08 4 08-04-09-03 08 5 08-04-09-03-14 08 6 08-04-09-03-14-01 08 7 08-04-09-03-14-01-11
문제설명
<표 1>은 파이프의 정보를 가진 원본테이블입니다. 파이프의 고유 식별번호인 ID와 시작점과 끝점의 좌표를 가지고 있습니다.
3차원 좌표이므로, 컬럼은 아래와 같습니다. 파이프번호(ID), 시작점 좌표(S_X, S_Y, S_Z), 끝점 좌표(E_X, E_Y, E_Z) 각각의 파이프는 서로 연결되어 있으며 연결된 두 파이프를 생각해보면, 하나의 파이프의 끝점은 연결된 다른 파이프의 시작점과 일치할 것입니다.
각각의 파이프들의 연결 관계를 파악하고 <표 2>의 결과를 도출하는 SQL을 작성하는 문제입니다.
<표 2>의 결과는 서로 연결되어 있는 파이프끼리 동일한 그룹ID(GR_ID)를 부여하고, 연결된 순서(SEQ)를 표시하고, 마지막으로 연결경로(PATH)를 순차적으로 보여주는 것입니다.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트 2] 정답 리스트
SELECT CONNECT_BY_ROOT(id) gr_id
, LEVEL seq
, SUBSTR(SYS_CONNECT_BY_PATH(id, '-'), 2) path
FROM t a
START WITH NOT EXISTS (SELECT 1
FROM t
WHERE e_x = a.s_x
AND e_y = a.s_y
AND e_z = a.s_z
)
CONNECT BY PRIOR e_x = s_x
AND PRIOR e_y = s_y
AND PRIOR e_z = s_z
ORDER SIBLINGS BY id
;
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
이번 문제는 파이프의 좌표정보를 이용해 서로간의 연결 관계를 파악해내는 문제입니다. 이전 파이프의 끝점이 다음 연결된 파이프의 시작점이 될 것입니다.
또한 연결된 파이프의 끝점은 또다시 다다음 파이프의 시작점이 됩니다. 이렇게 파이프는 꼬리에 꼬리를 물면서 연결이 될 텐데요. 일반적인 두 테이블 간의 연결은 조인으로 풀어내면 됩니다.
하지만 이 경우는 두 테이블이 아닌 하나의 테이블 안에서의 연결 관계를 풀어내야 하며 또한 단 한 번의 연결 관계로 끝나는 것이 아니라 꼬리에 꼬리를 물면서 계속해서 연결 관계가 발생될 수 있는 경우입니다.
이런 문제는 일반적인 조인으로는 풀어낼 수가 없으며 계층쿼리를 이용해 풀어내야 합니다. 이 문제에 접근하기 위해 우선 계층쿼리 구문의 일반적인 사용법을 살펴보겠습니다.
- [리스트 3] 계층쿼리 사용법
-
WITH test AS ( SELECT 'A' 코드, '' 부모코드 FROM dual UNION ALL SELECT 'B', 'A' FROM dual UNION ALL SELECT 'C', 'B' FROM dual ) SELECT 코드 , 부모코드 , LEVEL 레벨 , SYS_CONNECT_BY_PATH(코드, '-') 계층경로 FROM test START WITH 부모코드 IS NULL -- 계층전개 시작조건 CONNECT BY PRIOR 코드 = 부모코드 -- 계층 연결조건 ;
- [표 3] 계층쿼리 사용법
-
코드 부모코드 레벨 계층경로 ---------- ---------- ---------- ---------- A 1 -A B A 2 -A-B C B 3 -A-B-C
<리스트 3>의 쿼리를 이용해 <표 3>의 결과를 얻었습니다.
테이블은 코드와 부모코드로 구성되어 있으며 START WITH 구문을 통해 계층구조의 시작조건을 기술해 주고 CONNECT BY 절에서 부모와 자식 간의 연결고리 조건을 기술해 줍니다.
이때 사용되는 PRIOR 는 상위 노드의 값을 참조하겠다는 의미입니다. 즉, PRIOR 코드 = 부모코드 조건은 상위노드의 코드가 하위노드의 부모코드와 같다는 조건이 됩니다.
이렇게 코드와 직상위코드의 연결구조만 있는 테이블에 계층구조 쿼리를 이용하게 되면 상위코드에 하위코드가 연결되고 또 그 하위코드에 하위코드가 연결되는 방식의 결과를 얻을 수 있습니다.
SELECT 절에서 사용된 LEVEL은 계층의 깊이를 의미하고, CONNECT_BY_PATH 는 해당 레벨까지 도달하는 연결 경로를 모두 표현해 줍니다.
이러한 계층구조 쿼리는 다양한 분야에서 많이 사용이 되고 있지만, 대부분의 개발자들은 정확한 의미를 모른 채 기본 형식만을 사용하고 이를 응용하지 못하는데요. 이번 퀴즈 시간에는 그 틀을 깨보고자 합니다.
문제에서 주어진 테이블 <표 1> 에는 <표 3> 처럼 코드와 부모코드를 가지고 있지 않습니다. 하지만 시작좌표와 끝좌표가 이 역할을 대신 할 것입니다.
CONNECT BY PRIOR 끝좌표 = 시작좌표
그런데 문제가 있네요. 시작좌표와 끝좌표가 각각 1개의 컬럼이 아닌 3개의 항목으로 이루어져 있습니다. CONNECT BY 절의 계층 전개조건은 항상 1개 조건만 가능할까요?
아닙니다. 부모 자식 간의 연결 관계를 명확하게 표현해 줄 수만 있다면 어떤 형태의 조건이 와도 상관이 없습니다.
즉, X, Y, Z 3가지 좌표 조건을 모두 적어줘도 무방한 것이지요. WHERE 절에 여러 조건 기술할 때 AND 나 OR 를 사용하듯이 CONNECT BY 절에서도 AND 를 이용해 조건을 나열해 주면 됩니다.
CONNECT BY PRIOR e_x = s_x AND PRIOR e_y = s_y AND PRIOR e_z = s_z
계층전개 연결조건 구문인 CONNECT BY 구문이 완성되었습니다. 이번에는 계층전개 시작조건 구문인 START WITH 구문을 만들어 볼까요?
<리스트 3> 에서는 부모코드가 NULL 인 자료를 시작으로 계층전개를 했습니다. 그러나, <표 1> 에서는 이러한 자료가 보이질 않습니다. 파이프는 항상 시작좌표와 끝좌표를 가지고 있기 때문입니다.
그렇다면 이 14개 파이프들 중에 어떤 것이 시작파이프 인지를 알 수 있는 방법이 있을까요? 어떤 파이프의 시작좌표와 동일한 끝좌표를 가진 파이프가 존재한다면?
이 동일한 끝좌표를 가진 파이프는 부모 파이프가 될 것입니다. 반대로 특정 파이프의 시작좌표와 동일한 끝좌표를 가진 파이프가 존재하지 않는다면? 이 파이프는 무모가 없는 파이프가 될 것입니다.
다시 말하면 이 파이프는 최상위 무모 파이프가 되는 것을 의미하며, 바로 이 “파이프의 시작좌표와 동일한 끝좌표를 가진 파이프가 존재하지 않는다.” 라는 조건이 계층전개 시작조건이 되면 됩니다.
존재여부 체크는 EXISTS 서브쿼리로 체크가 가능합니다.
- [리스트 4] 최상위 부모 찾기
-
SELECT * FROM t a WHERE NOT EXISTS (SELECT 1 FROM t WHERE e_x = a.s_x AND e_y = a.s_y AND e_z = a.s_z ) ;
- [표 4] 최상위 부모 찾기
-
ID S_X S_Y S_Z E_X E_Y E_Z ---- ---------- ---------- ---------- ---------- ---------- ---------- 05 9 1 4 3 1 4 08 2 8 7 1 6 7 02 2 8 7 8 8 7
<리스트 4>의 쿼리를 이용해 <표 4>의 결과를 얻었습니다. NOT EXISTS 서브쿼리를 통해 현재 파이프의 시작좌표와 동일한 끝좌표가 존재하지 않는 자료를 검색합니다.
‘02’, ‘05’, ‘08’ 3개의 파이프가 연결된 파이프 그룹의 시작 파이프가 됩니다. 이 시작 파이프 검색 조건을 START WITH 에 그대로 적용할 수 있습니다.
계층 쿼리의 가장 중요한 START WITH(시작조건) 및 CONNECT BY(연결조건) 절이 완성되었습니다. 이 두 가지 조건을 적용시켜 계층 쿼리를 완성시켜 보면 <리스트 5>와 같습니다.
- [리스트 5] 계층 쿼리
-
SELECT LEVEL lv , SYS_CONNECT_BY_PATH(id, '-') path , id, s_x, s_y, s_z, e_x, e_y, e_z FROM t a START WITH NOT EXISTS (SELECT 1 FROM t WHERE e_x = a.s_x AND e_y = a.s_y AND e_z = a.s_z ) CONNECT BY PRIOR e_x = s_x AND PRIOR e_y = s_y AND PRIOR e_z = s_z ;
- [표 5] 계층 쿼리
-
LV PATH ID S_X S_Y S_Z E_X E_Y E_Z ---- --------------------- ---- ------ ------ ----- ----- ------ ------ 1 -08 08 2 8 7 1 6 7 2 -08-04 04 1 6 7 1 5 7 3 -08-04-09 09 1 5 7 1 4 6 4 -08-04-09-03 03 1 4 6 1 4 5 5 -08-04-09-03-14 14 1 4 5 1 3 4 6 -08-04-09-03-14-01 01 1 3 4 1 2 4 7 -08-04-09-03-14-01-11 11 1 2 4 2 1 4 1 -02 02 2 8 7 8 8 7 2 -02-10 10 8 8 7 8 7 7 1 -05 05 9 1 4 3 1 4 2 -05-12 12 3 1 4 4 1 3 3 -05-12-07 07 4 1 3 5 1 2 4 -05-12-07-13 13 5 1 2 6 1 1 5 -05-12-07-13-06 06 6 1 1 7 1 0
<리스트 5>의 쿼리를 이용해 <표 5>의 결과를 얻었습니다. LEVEL 을 이용해 연결된 파이프끼리의 순서(SEQ)를 표시했습니다.
SYS_CONNECT_BY_PATH 구문을 이용해 연결경로(PATH)를 표시했습니다. <표 5>의 결과를 보면 <표 2>의 결과에 비해 두 가지가 부족합니다.
하나는 서로 연결되어 있는 파이프끼리 동일한 그룹ID(GR_ID) 를 지정하는 것이고 하나는 출력 순서를 조정하는 것입니다.
서로 연결되어 있는 파이프끼리 동일한 그룹ID(GR_ID) 를 지정해야 하는데요. ‘08’로 시작하는 연결된 파이프들(‘08’, ‘04’, ‘09’, ‘03’, ‘14’, ‘01’, ‘11’)의 공통항목을 찾아야 합니다.
PATH 항목을 보면 모두 ‘08’로 시작하는 것을 알 수 있습니다. 즉, 서로 연결된 파이프들의 공통점은 바로 최상위(ROOT)가 동일하다는 것입니다. 계층 쿼리에서 루트 값은 CONNECT_BY_ROOT 를 이용해 구할 수 있습니다.
CONNECT_BY_ROOT(id) gr_id
연결된 파이프끼리 동일한 그룹ID(GR_ID) 지정하는 구문이 완성되었습니다. 마지막 정렬 문제입니다. 계층 쿼리에서의 정렬은 ORDER SIBLINGS BY 구문을 사용합니다.
ORDER SIBLINGS BY id
완성된 각각의 구문들을 하나로 적용시키면 최종 정답 쿼리 <리스트 2>가 완성됩니다.
이번 퀴즈로 배우는 SQL 시간에 다룬 내용을 정리해 볼까요?
- - EXISTS 구문을 이용해 계층 시작 조건 (START WITH) 완성
- - 시작좌표, 끝좌표를 계층 전개 조건(CONNECT BY) 에 적용시키기
- - CONNECT_BY_ROOT 를 이용해 그룹ID 구하기
- - LEVEL 을 이용해 연결 그룹 내 순번 구하기
- - SYS_CONNECT_BY_PATH 를 이용해 연결경로 표시
- - ORDER SIBLINGS BY 구문을 이용한 계층 정렬
- 강좌 URL : http://www.gurubee.net/lecture/2912
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
--어렵네요...
--postgreSQL에서 작성했습니다.
CREATE TABLE GR_ID_T
AS
WITH RECURSIVE pipe_t (id,st,ed, LEVEL, PATH, CYCLE)
as(
select id,st,ed ,0,array[id],false
from
(
select id
, CONCAT(s_x,',',s_y,',',s_z) st
, CONCAT(e_x,',',e_y,',',e_z) ed
from data_t
group by id,s_x,s_y,s_z,e_x,e_y,e_z
order by s_x,s_y,s_z,e_x,e_y,e_z
)a
where id in ('02','08','05')
union all
select a.id,a.st,a.ed ,level+1,path|| a.id,a.id=any(path)
from
(
select id
, CONCAT(s_x,',',s_y,',',s_z) st
, CONCAT(e_x,',',e_y,',',e_z) ed
from data_t
group by id,s_x,s_y,s_z,e_x,e_y,e_z
order by id,s_x,s_y,s_z,e_x,e_y,e_z
)a
,pipe_t b
where a.st = b.ed and NOT CYCLE
)
,result_t ( GR_ID,SEQ,PATH_ID )AS
(
SELECT case when level+1 = 1 then id else replace(id,id,a.path[1]) end gr_id
,level+1 SEQ, UNNEST(PATH)
FROM pipe_t a
group by id,level ,a.path
ORDER BY id,level
)
SELECT GR_ID,SEQ,PATH_ID
FROM result_t
;
SELECT GR_ID,SEQ,STRING_AGG(PATH_ID,'-')
FROM GR_ID_T
GROUP BY GR_ID,SEQ
ORDER BY GR_ID,SEQ
;
) 버튼을 클릭하여 작성 하시면 됩니다.