MySQL - recursive SQL :: 잡학문구

MySQL - recursive SQL

필유아사 2023. 6. 29. 10:28

2023. 6. 29. 10:28

MySQL 버전 : 8.0.32

MySQL recursive SQL 에 대해 설명

재귀 공통 테이블 식은 자체 이름을 참조하는 하위 쿼리가 있는 식입니다. 예를 들어 아래와 같은 형식입니다.

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

재귀 CTE(Common Table Expressions)의 구조는 다음과 같습니다.

WITH 절의 CTE가 자신을 참조하는 경우 WITH 절은 WITH RECURSIVE로 시작해야 합니다.
재귀 CTE 하위 쿼리에는 UNION ALL 또는 UNION [DISTINCT]로 구분되는 두 부분이 있습니다.

SELECT ...      -- return initial row set
UNION ALL
SELECT ...      -- return additional row sets

첫 번째 SELECT는 CTE에 대한 초기 행을 생성하며 CTE 이름을 참조하지 않습니다. 두 번째 SELECT는 추가 행을 생성하고 FROM 절에서 CTE 이름을 참조하여 재귀합니다. 두 번째 SELECT가 새 행을 생성하지 않으면 재귀가 종료됩니다. Recursive CTE는 비재귀적 SELECT 부분과 재귀적 SELECT 부분으로 구성됩니다.

CTE의 컬럼타입은 비재귀적 SELECT 부분의 컬럼타입이며 모두 null을 허용합니다.
비재귀 부분과 재귀 부분이 UNION [DISTINCT]로 구분되면 중복 행이 제거됩니다. 이는 무한 루프를 피하기 위해 전이적 폐쇄를 수행하는 쿼리에 유용합니다.
재귀 부분의 각 반복은 이전 반복에서 생성된 행에 대해서만 작동합니다.

다음 쿼리는 재귀적 CTE 하위 쿼리의 초기 행 집합을 생성하는 비재귀적 부분입니다.

SELECT 1

CTE 하위의 아래 쿼리는 재귀 부분입니다.

SELECT n + 1 FROM cte WHERE n < 5

해당 SELECT는 이전 행 집합의 n 값보다 하나 더 큰 새 값을 가진 행을 생성합니다. 첫 번째 반복은 초기 행 세트(1)에 대해서 작동하고 1+1=2를 생성합니다. 두 번째 반복은 첫 번째 반복의 행 집합(2)에서 작동하고 2+1=3을 생성합니다. n이 5보다 작지 않을 때까지 계속됩니다.

CTE의 재귀 부분이 비재귀 부분보다 열에 대해 더 길은 값을 생성하는 경우 데이터 잘림을 방지하기 위해 비재귀 부분의 열을 확장해야 할 수 있습니다.

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

nonstrict SQL 모드에서는 위의 SQL문은 다음을 출력합니다.

비재귀적 SELECT가 열의 길이를 결정하기 때문에 str 열 값은 모두 'abc'입니다. 결과적으로 재귀 SELECT에 의해 생성된 더 길은 str 값이 잘립니다.

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

strict SQL모드에서는 이 명령문은 오류를 발생합니다.

ERROR 1406 (22001): Data too long for column 'str' at row 1

이 문제를 해결하려면 명령문이 잘림이나 오류를 생성하지 않도록 비재귀 SELECT에서 CAST()를 사용하여 str 열의 길이를 크게 만들어야 합니다.

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

이제 명령문은 잘림 없이 다음 결과를 생성합니다.

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

일부 구문 제약 조건은 재귀 CTE 하위 쿼리 내에서 적용됩니다.

재귀 SELECT 부분은 다음 구문을 포함하지 않아야 합니다.

Aggregate functions such as SUM()
Window functions
GROUP BY
ORDER BY
DISTINCT

이러한 제약 조건은 재귀 CTE의 비재귀 SELECT 부분에는 적용되지 않습니다.

재귀 SELECT 부분은 sub 쿼리가 아닌 FROM 절에서만 한 번만 CTE를 참조해야 합니다. CTE 이외의 테이블을 참조하고 CTE와 조인할 수 있습니다.

Limiting Common Table Expression Recursion

재귀 CTE의 경우 재귀 SELECT 부분에 재귀를 종료하는 조건이 포함되어야 합니다. 런어웨이 재귀 CTE를 방지하기 위한 개발 기법으로 실행 시간을 제한하여 강제 종료할 수 있습니다.

cte_max_recursion_depth 시스템 변수는 CTE의 재귀 수준에 대한 제한을 적용합니다. 이 변수 값보다 더 많은 수준을 반복하는 모든 CTE의 실행을 종료합니다.
max_execution_time 시스템 변수는 현재 세션 내에서 실행되는 SELECT 문에 대한 실행 제한 시간을 적용합니다.
MAX_EXECUTION_TIME 옵티마이저 힌트는 나타나는 SELECT 문에 대해 쿼리당 실행 제한 시간을 적용합니다.

재귀 CTE가 재귀 실행 종료 조건 없이 실수로 작성되었다고 가정합니다.

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;

-- cte_max_recursion_depth=1000 설정 시 오류 발생 -- 
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. 
Try increasing @@cte_max_recursion_depth to a larger value.

기본적으로 cte_max_recursion_depth 값은 1000이므로 CTE가 1000 수준을 초과하여 재귀하면 종료됩니다.

MySQL 8.0.19부터 재귀 쿼리 내에서 LIMIT를 사용하여 가장 바깥쪽 SELECT에 반환할 최대 행 수를 지정할 수도 있습니다. 예를 들면 다음과 같습니다.

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 900
)
SELECT * FROM cte;

Recursive Common Table Expression Examples

Hierarchical Data Traversal

재귀 공통 테이블 표현식은 계층 구조에 유용합니다. 아래의 예시를 살펴봅니다. 최상위 직원(CEO)의 관리자 ID는 NULL(관리자 없음)입니다.

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);

INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

각 직원의 관리 체인(즉, CEO에서 직원으로의 경로)이 포함된 조직도를 생성하려면 재귀 CTE를 사용합니다.

아래 예제는 사번ID의 체인경로를 조회한다. 이름으로 하고 싶은 경우에는 id 대신 name 을 사용하면 된다.

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, '->', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

+------+---------+--------------------+
| id   | name    | path               |
+------+---------+--------------------+
|  333 | Yasmina | 333                |
|  198 | John    | 333->198           |
|   29 | Pedro   | 333->198->29       |
| 4610 | Sarah   | 333->198->29->4610 |
|   72 | Pierre  | 333->198->29->72   |
|  692 | Tarek   | 333->692           |
|  123 | Adil    | 333->692->123      |
+------+---------+--------------------+

MySQL recursive SQL로 데이터 생성하기

아래와 같은 WITH RECURSIVE SQL로 임시 데이터를 생성할 수 있습니다.

임시 데이터를 입력할 테이블을 생성합니다.

CREATE TABLE cte_populated
(
  id INT NOT NULL PRIMARY KEY,
  val varchar(20)
) ENGINE = InnoDB;

INSERT 문장과 WITH RECURSIVE 문장으로 임시 데이터를 입력합니다. 아래 예제는 1부터 2000000까지 2000000건의 데이터를 입력하는 문장입니다. 이를 응용하여 다양하게 데이터를 생성할 수 있습니다.

INSERT cte_populated(id, val)
WITH  RECURSIVE int_seq AS (
SELECT 1 AS id, cast('test1' as char(200)) as pass
UNION ALL
SELECT id + 1, concat('test',id)
FROM int_seq
WHERE id  < 2000000)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 3M) */ id,  pass as id FROM int_seq;

/*+ SET_VAR(cte_max_recursion_depth = 3M) */ 힌트는 서버에 cte_max_recursion_depth 파라미터의 기본값이 1000으로 설정되어 있어 depth가 1000 이상인 경우 오류가 발생합니다. 이럴 경우 1000 건 이상 입력 시 힌트를 사용하여 원하는 만큼의 데이터를 생성할 수 있습니다. 3M에서 'M'은 백만입니다.

저작자표시 비영리 변경금지

잡학문구