[MariaDB] 계층형 쿼리

Oracle에서의 계층형 쿼리는 START WITH와 CONNET BY를 중심으로 구성된다.

 

하지만 MariaDB의 경우 WITH RECURSIVE ~ 라는 WITH 구문 형태로 계층형 쿼리를 작성한다. RECURSIVE라는 이름에서 알 수 있 듯, 재귀 쿼리를 의미한다.

 

WITH RECURSIVE comments AS (
    SELECT *, 1 AS LEVEL
      FROM comment
     WHERE target_comment_sn IS NULL
    UNION ALL
    SELECT c.*, cs.LEVEL + 1 AS LEVEL
      FROM comment c
     INNER JOIN comments cs on c.target_comment_sn = cs.comment_sn
)
SELECT *
  FROM comments;

 

 

위 재귀 쿼리의 흐름을 설명하자면

 

1. comments라는 임시 테이블을 만들고

2. 첫 번째 SELECT에서 target_comment_sn이 NULL인 행을 찾고

3. 두 번째 SELECT가 실행. 앞서 찾은 target_comment_sn이 NULL인 행의 comment_sn에 일치하는 target_comment_sn을 가진 행을 comment 테이블에서 찾은 다음 UNION ALL을 수행하여 임시 테이블 통합

4. 재귀 호출되어 comment 테이블에서 target_comment_sn이 방금 통합된 임시 테이블의 comment_sn에 일치하는 행을 찾고 UNION ALL을 수행

5. 자식 노드가 없을 때까지 3 ~ 4단계를 반복 수행

 

인 것 같다...

 

만약 역방향 계층형 쿼리를 원할 경우, 위쪽 SELECT의 조건절에 부모 노드의 값(IS NULL) 대신 시작하고자 하는 자식 노드의 값을 사용하고 아래 INNER JOIN의 조건절에서 부모 노드와 자식 노드의 순서를 바꾸면 된다.

 

WITH RECURSIVE comments AS (
    SELECT *, 1 AS LEVEL
      FROM comment
     WHERE comment_sn = [자식 노드 값]
    UNION ALL
    SELECT c.*, cs.LEVEL + 1 AS LEVEL
      FROM comment c
     INNER JOIN comments cs on cs.target_comment_sn = c.comment_sn
)
SELECT *
  FROM comments;

 

 

MariaDB의 재귀 호출 계층형 쿼리를 나에게 익숙한 OracleDB와 비교해보면 다음과 같다.

 

START WITH ~ = 첫 번째 SELECT의 WHERE절

CONNECT BY PRIOR ~ = 두 번째 SELECT의 JOIN 조건절

 

OracleDB에서 'CONNECT BY PRIOR 부모 노드 = 자식 노드'는 순방향인데

MariaDB의 '자식 a INNER JOIN 임시테이블(부모) b ON a.자식 노드 = b.부모 노드'로 같이 순방향이다.

 

 

* 계층형 쿼리 정렬하기

MariaDB에서는 계층형 쿼리를 정렬하기 위해 별도의 컬럼을 만들어주어야한다. 식별자가 되는 번호를 사용해 계층 구성을 가리키는 컬럼을 만들고 이를 사용해 ORDER BY하면 된다.

 

WITH RECURSIVE comments AS (
    SELECT *, 1 AS LEVEL,
           CONVERT(cmmt_id, char) AS path
      FROM comment
     WHERE target_comment_sn IS NULL
    UNION ALL
    SELECT c.*, cs.LEVEL + 1 AS LEVEL,
           CONCAT(cs.path, '-', c.cmmt_id) AS path
      FROM comment c
     INNER JOIN comments cs on c.target_comment_sn = cs.comment_sn
)
SELECT *
  FROM comments
 ORDER BY CONVERT(SUBSTRING_INDEX(path, '-', 1), UNSIGNED), cmmt_id;

 

 

* SUBSTRING_INDEX(문자열, 구분자, 구분자 순번)

문자열을 구분자로 구분하고 구분자를 기준으로 잘라낼 수 있는 함수이다.

 

SELECT SUBSTRING_INDEX('1,2,3,4', ',', 1); -- 1
SELECT SUBSTRING_INDEX('1,2,3,4', ',', 2); -- 1,2
SELECT SUBSTRING_INDEX('1,2,3,4', ',', 3); -- 1,2,3

-- 구분자의 개수보다 높으면 문자열을 잘라내지 않는다.
SELECT SUBSTRING_INDEX('1,2,3,4', ',', 4); -- 1,2,3,4
SELECT SUBSTRING_INDEX('1,2,3,4', ',', 5); -- 1,2,3,4

-- 음수는 문자열의 우측에서부터 잘라낸다.
SELECT SUBSTRING_INDEX('1,2,3,4', ',', -1); -- 4
SELECT SUBSTRING_INDEX('1,2,3,4', ',', -2); -- 3,4
SELECT SUBSTRING_INDEX('1,2,3,4', ',', -3); -- 2,3,4