(25.05.13) SQL 재귀 WITH RECURSIVE

 

SQLD 자격증을 공부하면서 습득했던 구문들과 문법들을 나름 다 활용하고 있다고 생각했는데,

Java 기반의 알고리즘 프로그래밍에서나 볼 수 있었던 "재귀" 에대해서 SQL구문을 활용을 하는 예제를 맞닥드렸다.

만약, 재귀법 없이 하나하나 구해야한다면 무한의 조건문을 만들어야 하기 때문에,

반드시 알아둬야하는 CTE 구문을 간단하게 정리하고 이를 활용하는 예제도 정리했다.

 

단! CTE (Common Table Expressions) 기능은 MySQL 8.0 버전부터 지원하기 때문에 

( MySQL 8.0 이상, PostgreSQL, SQL Server 2005 이상, Oracle 11g 이상 )

비교적 최신 구문으로, SQLD 자격 부분에 대해서는 쉽사리 다루지 않았던 것 같다.

이전 버전의 SQL 서버에서는 동작할 수 없기 때문에, 임시방편으로 서브쿼리 또는 임시 데이터테이블을 DB 자체에 만들곤 그걸 활용해야하나,복잡하고 중첩해서 쿼리를 반복해서 사용해야한다.

 

이 부분에 유의를 두고 성능관리 역시 신경을 쓰고 CTE를 활용할 수 있도록 해야한다.

---

 

SQL의 CTE, Common Table Expression

• SQL 문 안에서 효과적인 조회를 위해서 활용할 수 있는 임시의 가상 테이블을 정의 후, 활용하는 방법
• 반복이 아닌 딱 한번만 최초로 실행

WITH

• 해당 WITH 키워드를 통해 정의
• Sub Query 같이 하나의 쿼리문안에 종속된 형태가 아니기 때문에 재사용 가능

example)

WITH filtered_employees AS (
    SELECT * FROM employees WHERE salary > 5000
)

SELECT name FROM filtered_employees;

• employees 테이블에서 바로 조건문으로 사용할 수 있지만, filtered_employees 가상 테이블로, 필터링 된 데이터들을 연속적인 쿼리에서 지속적으로 재사용 가능

재귀 CTE

• WITH CTE 의 WITH RECURSIVE 로 자기자신을 재귀할 수 있도록 제공해 계층 구조(Tree 구조 등)를 형성하는데도 활용이 가능
  • WITH RECURSIVE 로 만들어지는 테이블을 자기 참조해서 활용
• 어떤 테이블의 ROW 테이블을 반복하는 것이 아닌, 기존의 테이블을 자기 자신과 참고해서 가상 테이블을 업데이트하는 방식

기본 문법

WITH RECURSIVE cte_name (column1, column2, ...) AS ( -- 1. CTE 구문
    -- 2. Anchor member (기초 데이터)
    SELECT ...
    FROM base_table
    WHERE ...

    UNION ALL -- 3. 집합 연산
    
    -- 4. Recursive member (자기 자신을 참조)
    SELECT ...
    FROM base_table
    JOIN cte_name
    ON ...
)
-- 5. CTE를 사용하는 본문
SELECT *
FROM cte_name;

1. CTE 구문
   • WITH RECURSIVE 가상테이블이름 AS ( 재귀 쿼리 ) 로 시작
2. Anchor member (기초 데이터)
   • 재귀할 때 사용하는 최초값을 적용
3. 집합 연산 UNION ALL
   • 일단 필터한 그대로의 테이블을 만들기 위해 중복은 그대로 유지하는게 일반적 (UNION 대신 UNION ALL)
   • UNION 보다 좀더 나은 성능
4. Recursive member (자기 자신을 참조)
   • 실제 DB 에서 조회해오는 테이블과 재귀용 테이블을 JOIN(INNER JOIN) 해서 새롭게 계속 재귀용 테이블에 갱신
     • 자식 데이터를 갱신하는 것
5. 본문 활용

본인이 아닌 여러 재귀를 진행

WITH
    cte1 AS (
        SELECT ...
    ),
    cte2 AS (
        SELECT ...
    ),
    cte3 AS (
        SELECT ...
    )
SELECT ...
FROM cte1
JOIN cte2 ON ...
JOIN cte3 ON ...;

• 이때는 일반적인 CTE를 활용하는 게 제일 가성비 있음

종료조건

• JOIN 시 ON에 있는 조건문에 대하여 어떤것도 조회가 되지 않는 null 일경우에 자동으로 종료하는 세팅
  • 물론 특정 조건에 따라서 종료시킬 수도 있음 (target 값에 도달하는 등)

PostgreSQL

WITH RECURSIVE counter(n) AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 100
)

SELECT * FROM counter
LIMIT 10;

• PostgreSQL 기준, LIMIT 을 통해 제한 가능

MySQL

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION AL
  SELECT n + 1
  FROM counter
  WHERE n < 10
)

SELECT * FROM counter;

• LIMIT 없이 WHERE 문으로 직접적으로 조절을 해야함

재귀를 활용한 SQL 문 작성하기

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

Column name	Type	Nullable
ID	INTEGER	FALSE
PARENT_ID	INTEGER	TRUE
SIZE_OF_COLONY	INTEGER	FALSE
DIFFERENTIATION_DATE	DATE	FALSE
GENOTYPE	INTEGER	FALSE
최초의 대장균 개체의 PARENT_ID 는 NULL 값입니다.

각 세대별 자식이 없는 개체의 수(COUNT)와 세대(GENERATION)를 출력하는 SQL문을 작성해주세요. 이때 결과는 세대에 대해 오름차순 정렬해주세요. 단, 모든 세대에는 자식이 없는 개체가 적어도 1개체는 존재합니다.

 

WITH RECURSIVE GENERATION AS (
    -- 1세대
    SELECT
        ID,
        1 AS GENERATION
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL

    -- 다음세대
    SELECT
        E.ID,
        G.GENERATION +1
    FROM ECOLI_DATA E
    JOIN GENERATION G
    ON E.PARENT_ID = G.ID 
)

SELECT
    COUNT(*) AS COUNT,
    G.GENERATION
FROM  ECOLI_DATA E
LEFT JOIN ECOLI_DATA C
ON E.ID = C.PARENT_ID
JOIN GENERATION G
ON E.ID = G.ID
WHERE C.ID IS NULL
GROUP BY G.GENERATION
ORDER BY G.GENERATION ASC
;

• 다음 세대로 넘어가는 것을 기록하는 GENERATION 테이블을 만들어서 JOIN 해 자식이 없는 값들을 찾아야함
  • 이 때, 자식이 몇 번째 GENERATION인지 본문 SQL에서 정적인 코드로는 바로 구성하기 어려움이 있음
  • → 부모-자식-자식-자식 꼬리를 물기 때문에 CASE WHEN THEN 구문을 활용해서 시간순서대로 순서대로 세대를 분류할 수 없음 : TREE 구조
• CTE 재귀 구문을 활용해서 가상의 GENERATION 테이블을 만들고, 이를 활용하는 전략
• → 본 ECOLI_DATA 테이블에서 자식이 없는 샘플의 ID = GENERATION 테이블의 샘플의 ID 를 JOIN 해서 쉽게 구할 수 있음

---

SQL 집합 연산자 Set Operators 참고

1. UNION

• 두 쿼리 결과를 합침 (중복 제거를 위한 작업이 추가로 필요)

2. UNION ALL

• 중복 제거 없이 합침 → 성능 더 좋음

3. INTERSECT

• 두 쿼리 결과의 공통된 행만 출력

4. EXCEPT 또는 MINUS

• 앞 쿼리에서 뒤 쿼리의 결과를 빼고 남은 결과만 출력
  • EXCEPT: 대부분의 RDBMS (MySQL, PostgreSQL 등)
  • MINUS: Oracle
    • 중복 데이터 를 DISTINCT 한 후 결과가 출력되는 형태
    • MySQL 경우 NOT EXISTS/EXISTS {서브쿼리} 로 대체해서 사용 (DISTINCT 미적용)

-- PostgreSQL, SQL ServerSELECT name FROM table1
EXCEPT
SELECT name FROM table2;

-- OracleSELECT name FROM table1
MINUS
SELECT name FROM table2;

 

---

참고자료

https://dev.mysql.com/doc/refman/8.4/en/with.html

MySQL :: MySQL 8.4 Reference Manual :: 15.2.20 WITH (Common Table Expressions)

 

---

특이한 재귀 구문을 SQL에서 활용한 경험이 없기 때문에, Applicaiton  서버 에서도 DB SQL 버전이 맞다면, Batis를 활용해서 재귀를 DB내에서 반복해서 활용할 수 있을 것이다.

 

재귀로 임시테이블을 만들고 활용하는 것이 어쩌면 JOIN 보다 더 효율적으로 작동될 수 있기 때문이라고 생각되고, 

특히, 루프를 돌게 해서 여러번 조회하는 것도 역시 성능적으로 우수할 것이라고 생각되기떄문에

기억하고 활용할 겨우 충분히 활용할 수 있도록 해야할 것이다.