[MySQL] WITH RECURSIVE 계층 쿼리

 

 

 

Oracle에서는 트리 형태의 계층 구조를 가진 데이터를 쿼리로 뽑아낼 때 CONNECT BY / START WITH 구문으로 쉽게 작성할 수 있는데.. MySQL에서는 전용 구문을 제공하지 않아 백엔드 레벨에서 데이터를 가공하거나 여러 집계 쿼리를 사용해 우회해서 쿼리를 작성해야 됐다.

 

MySQL 8.0 버전부터는 Common Table Expression을 지원하고, 이 구문을 활용해 CONNECT BY / START WITH 구문 없이 재귀적 계층 쿼리를 작성할 수 있다. 

 

WITH RECURSIVE 구문으로 임시테이블을 정의하고 해당 테이블을 스스로 다시 참조하는 방식이다. 

 

WITH RECURSIVE TEMP_TABLE AS (
    -- 시작점 (앵커 쿼리)
    SELECT ... 
    FROM ...
    WHERE ...  -- 시작점 조건

    UNION ALL

    -- 재귀 쿼리
    SELECT ... 
    FROM TABLE
    JOIN TEMP_TABLE ON TABLE.HIGH = TABLE.DOWN
)
SELECT * FROM TEMP_TABLE;

 

 

처음 실행될 때 앵커 쿼리가 실행돼 임시 테이블의 초기 상태를 정의한다.

첫 실행 결과를 바탕으로 재귀 쿼리가 실행되고 재귀 쿼리는 임시테이블 스스로를 참조해 앵커 쿼리 결과나 이전 재귀 쿼리 결과로 다음 계층을 조회한다.

더 이상 매칭되는 하위 컬럼이 없을 때 까지 반복한다. (종료 조건)

 

앵커 쿼리가 START WITH 구문에 해당하고, 재귀 쿼리의 연결 조건이 CONNECT BY와 PRIOR 구문에 해당한다고 생각하면 된다.

 

쿼리 결과로 임시테이블에는 각 단계마다 데이터가 누적되고, 그 결과로 오라클의 CONNECT BY 를 사용할 때와 같은 결과를 얻을 수 있다. 

 

SELECT 
	 C_INST_NO AS SRVC_VIEW_CD
   , LIST_ID
   , C_INST_NO AS SRVC_LIST_ID
   , UP_LIST_ID AS UP_SRVC_VIEW_CD
   , LIST_CM
   , SE_CD AS PUB_SE
   , LIST_NM
   , CONCAT(LIST_NM,(SELECT CD_NM FROM TC_CM WHERE CD_ID = '000007' AND CD = LST.SE_CD)) AS LIST_PUB_NM
   , LIST_NM AS LIST_ENG_NM
   , ROWNUM AS LIST_SN
   , TBL_ID
   , TBL_NM
   , PUB_CNT
   , LEVEL AS LV
   , EXP
   , OPEN
FROM (
	SELECT 
  		 C_INST_NO
	   , NULL AS UP_LIST_ID
	   , '0' AS LIST_ID
	   , (SELECT CD_NM FROM TC_CM WHERE CD_ID = '000008' AND CD =  C_INST_NO) AS LIST_CM
	   , (SELECT CD_NM FROM TC_CM WHERE CD_ID = '000008' AND CD =  C_INST_NO) AS LIST_NM
	   , NULL AS TBL_ID
	   , NULL AS TBL_NM
	   , '12345' AS SE_CD
	   , 0 AS PUB_CNT
	   , 0 AS EXP
	   , '' AS OPEN
	   , 0 AS UP_LIST_SN
	   , 0 AS LIST_SN
    FROM TB_C_LIST
    
   UNION ALL
   
	SELECT 
		A.C_INST_NO
	   , CASE 
			WHEN A.UP_LIST_ID IS NULL THEN '0' 
			ELSE A.UP_LIST_ID 
		END AS UP_LIST_ID
	   , A.LIST_ID
	   , '' AS LIST_CM
	   , A.LIST_NM
	   , A.TBL_NM
	   , A.TBL_ID
	   , A.SE_CD
	   , CASE WHEN A.TBL_ID IS NOT NULL
		      THEN 0
		      ELSE (SELECT COUNT(*)
                      FROM STMS.TB_ATH_LIST
                     WHERE LIST_ID = a.LIST_ID
                       AND TBL_ID IS NOT null
                      )
		 END AS PUB_CNT
	   , 0 AS EXP
	   , 'N' AS OPEN
	   , NVL(UP_LIST_SN, 0) AS UP_LIST_SN
	   , NVL(LIST_SN, 0) AS LIST_SN
    FROM TB_A_LIST A
   WHERE TBL_ID IS NULL
  ) LST
START WITH LST.UP_LIST_ID IS NULL
CONNECT BY PRIOR LST.LIST_ID = LST.UP_LIST_ID
ORDER SIBLINGS BY LST.UP_LIST_SN, LST.LIST_SN

 

Oracle 에서 작성된 계층 쿼리를 

 

WITH RECURSIVE LST AS (
    SELECT 
           C_INST_NO,
           NULL AS UP_LIST_ID,
           '0' AS LIST_ID,
           (SELECT CD_NM FROM TC_CMDTLCD WHERE CD_ID = '000008' AND CD = C_INST_NO) AS LIST_CM,
           (SELECT CD_NM FROM TC_CMDTLCD WHERE CD_ID = '000008' AND CD = C_INST_NO) AS LIST_NM,
           NULL AS TBL_ID,
           NULL AS TBL_NM,
           '12345' AS SE_CD,
           0 AS PUB_CNT,
           0 AS EXP,
           '' AS OPEN,
           0 AS UP_LIST_SN,
           1 AS LIST_SN
    FROM TB_C_LIST
    
    UNION ALL
    
    SELECT A.C_INST_NO,
           CASE WHEN A.UP_LIST_ID IS NULL THEN '0' ELSE A.UP_LIST_ID END AS UP_LIST_ID,
           A.LIST_ID,
           '' AS LIST_CM,
           A.LIST_NM,
           A.TBL_NM,
           A.TBL_ID,
           A.SE_CD,
           CASE 
             WHEN A.TBL_ID IS NOT NULL THEN 0
             ELSE (
               SELECT COUNT(*) 
                 FROM KRICT_DEV.TB_ATH_LIST 
                WHERE list_ID = A.LIST_ID 
                  AND TBL_ID IS NOT null
             )
           END AS PUB_CNT,
           0 AS EXP,
           'N' AS OPEN,
           IFNULL(A.UP_LIST_SN, 0) AS UP_LIST_SN,
           IFNULL(A.LIST_SN, 0) AS LIST_SN
      FROM TB_A_LIST A
     WHERE A.TBL_ID IS NULL
),

CTE AS (
    SELECT
        LST.C_INST_NO AS SRVC_VIEW_CD,
        LST.LIST_ID,
        LST.C_INST_NO AS SRVC_LIST_ID,
        LST.UP_LIST_ID AS UP_SRVC_VIEW_CD,
        LST.LIST_CM,
        LST.SE_CD AS PUB_SE,
        LST.LIST_NM,
        CONCAT(LST.LIST_NM,(SELECT CD_NM FROM TC_CMDTLCD WHERE CD_ID = '000007' AND CD = LST.SE_CD)) AS LIST_PUB_NM,
        LST.LIST_NM AS LIST_ENG_NM,
        LST.TBL_ID,
        LST.TBL_NM,
        LST.PUB_CNT,
        LST.EXP,
        LST.OPEN,
        LST.UP_LIST_SN,
        LST.LIST_SN,
        1 AS LV,
        CAST( CONCAT(LPAD(LST.UP_LIST_SN, 5, '0'), '-', LPAD(LST.LIST_SN, 5, '0')) AS CHAR(9999)) AS SORT_PATH
     FROM LST
     WHERE LST.UP_LIST_ID IS NULL

     UNION ALL

     SELECT
        L.C_INST_NO AS SRVC_VIEW_CD,
        L.LIST_ID,
        L.C_INST_NO AS SRVC_LIST_ID,
        L.UP_LIST_ID AS UP_SRVC_VIEW_CD,
        L.LIST_CM,
        L.SE_CD AS PUB_SE,
        L.LIST_NM,
        CONCAT(L.LIST_NM,(SELECT CD_NM FROM TC_CMDTLCD WHERE CD_ID = '000007' AND CD = L.SE_CD)) AS LIST_PUB_NM,
        L.LIST_NM AS LIST_ENG_NM,
        L.TBL_ID,
        L.TBL_NM,
        L.PUB_CNT,
        L.EXP,
        L.OPEN,
        L.UP_LIST_SN,
        L.LIST_SN,
        C.LV + 1 AS LV,        
        CAST( CONCAT(C.SORT_PATH, '-', LPAD(L.UP_LIST_SN, 5, '0'), '-', LPAD(L.LIST_SN, 5, '0')) AS CHAR(9999)) AS SORT_PATH
      FROM LST L
      JOIN CTE C ON L.UP_LIST_ID = C.LIST_ID
)

SELECT
	SRVC_VIEW_CD
	, SORT_PATH
	, LIST_ID
	, SRVC_LIST_ID
	, UP_SRVC_VIEW_CD
	, LIST_CM
	, PUB_SE
	, LIST_NM
	, LIST_PUB_NM
	, LIST_ENG_NM
	, ROW_NUMBER() OVER(ORDER BY SORT_PATH) AS LIST_SN 
	, TBL_ID
	, TBL_NM
	, PUB_CNT
	, LV
	, EXP
	, OPEN
FROM CTE
ORDER BY SORT_PATH;

 

 

MySQL의 WITH RECURSIVE 기반 계층 쿼리로 바꿔보자. 

 

먼저 LST 임시테이블을 정의해 두 테이블을 합친 집합을 만들고 WITH RECURSIVE 로 만든 CTE 임시테이블으로 LST 임시테이블을 재귀적으로 참조한다. 

 

START WITH / CONNECT BY PRIOR 구문은 WITH RECURSIVE와 JOIN 조건을 대체되고, LEVEL 컬럼은 CTE 내부 LV로 대체해서 사용한다.

 

MySQL에서는 ROWNUM을 사용을 사용해서 각 행에 순차적 번호를 할당할 수 없으니 Oracle의 ORDER SIBLINGS BY 구문을 MySQL에서 구현하기 위해 SORT_PATH를 도입하고, 최종 SELECT 구문에서 ROW_NUMBER 윈도우 함수를 사용해 LIST_SN을 넘버링한다.

 

계층 쿼리에서 같은 레벨에 속한 형제 노드들을 특정 기준으로 정렬할 때 SORT_PATH를 도입한다.

각 단계의 정렬 기준을 누적해 전체 경로를 SORT_PATH에 저장하고 해당 컬럼으로 ORDER SIBLINGS BY 구문을 구현한다.