이전에 작성한 내용(MySQL 8.0 신기능 CTE 활용) 중에 재귀 쿼리에 대한 내용을 언급했었습니다. SQL은 일반적으로 재귀 쿼리 구조에 좋지 않지만 이제 MySQL에서 재귀 쿼리를 작성할 수 있습니다. MySQL 8.0 이전에는 stored routin을 생성해야만 재귀 쿼리가 가능했습니다.
재귀 CTE 쿼리란 무엇입니까?
재귀 CTE는 자체 이름을 참조하는 하위 쿼리가 있는 CTE입니다. 다음과 같은 경우에 특히 유용합니다.
- 시리즈 생성
- 계층적 또는 트리 구조의 데이터 순회
재귀적 CTE의 주요 구성 요소를 살펴보겠습니다. 다음은 이를 생성하는 구문입니다.
WITH RECURSIVE cte AS (
initial_query -- "seed" member
UNION ALL
recursive_query -- recusive member that references to the same CTE name
)
SELECT * FROM cte; -- main query먼저 RECURSIVE 절은 필수 항목이며 두 가지 필수 구성 요소가 있습니다. 시드 멤버는 첫번째 반복에서 실행 될 초기 쿼리입니다. 재귀 멤버는 동일한 CTE 이름에 대한 참조를 포함하는 쿼리입니다. 두 번째 구성 요소는 메인 쿼리의 모든 항목을 생성합니다.
반복이 행을 생성하지 않으면 프로세스가 중지됩니다. 메모리를 고갈시킬 수 있는 많은 반복을 생성하지 않도록 주의합니다.
재귀 CTE의 경우 재귀 멤버가 재귀를 종료하는 조건을 포함하는 것이 중요합니다. 개발 기술로 실행 시간을 제한하여 강제 종료할 수 있습니다.
- cte_max_recursion_depth 시스템 변수는 CTE를 위한 재귀 수준의 수에 제한을 적용합니다. 서버는 이 변수의 값보다 더 많은 수준을 반복하는 모든 CTE의 실행을 종료합니다. 기본값은 1000입니다.
- max_execution_time 시스템 변수는 현재 세션 내에서 SELECT 문에 대한 실행 시간 제한을 적용합니다.
- MAX_EXECUTION_TIME optimizer hint가 나타나는 SELECT 문에 대한 쿼리 실행 시간 제한을 적용합니다.
시리즈 생성
이제 시리즈를 생성하기 위해 재귀 CTE를 사용하는 몇 가지 간단한 사용법을 살펴보겠습니다.
1단계 시퀀스
먼저 1에서 10까지의 간단한 정수 시리즈를 만듭니다. N+1 값이 이전 N의 함수이기 때문에 이것은 1레벨 시퀀스입니다.
WITH RECURSIVE natural_sequence AS
( SELECT 1 AS n -- seed member: our sequence starts from 1
UNION ALL
SELECT n + 1 FROM natural_sequence -- recursive member: reference to itself
WHERE n < 10 -- stop condition
)
SELECT * FROM natural_sequence; -- main query
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
# let's see what happen if we miss the stop condition
mysql> WITH RECURSIVE natural_sequence AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM natural_sequence ) SELECT * FROM natural_sequence;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.또 다른 전형적인 예는 factorial 을 계산하는 것 입니다.
mysql> WITH RECURSIVE factorial(n, fact) AS (
SELECT 0, 1
UNION ALL
SELECT n + 1, fact * (n+1)
FROM factorial
WHERE n < 20 )
SELECT * from factorial;
+------+---------------------+
| n | fact |
+------+---------------------+
| 0 | 1 |
| 1 | 1 |
| 2 | 2 |
| 3 | 6 |
| 4 | 24 |
| 5 | 120 |
| 6 | 720 |
| 7 | 5040 |
| 8 | 40320 |
| 9 | 362880 |
| 10 | 3628800 |
| 11 | 39916800 |
| 12 | 479001600 |
| 13 | 6227020800 |
| 14 | 87178291200 |
| 15 | 1307674368000 |
| 16 | 20922789888000 |
| 17 | 355687428096000 |
| 18 | 6402373705728000 |
| 19 | 121645100408832000 |
| 20 | 2432902008176640000 |
+------+---------------------+2단계 시퀀스
N+2 값이 이전 두 값 N+1 및 N의 함수인 2단계 시퀀스를 만들고 싶습니다.
전형적인 예는 피보나치 수열입니다. 각 숫자는 0과 1부터 시작하여 앞의 두 숫자의 합입니다. 피보나치 수열의 처음 20개 항목을 계산해 보겠습니다.
mysql> WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
SELECT 1, 0, 1
UNION ALL
SELECT n + 1, next_fib_n, fib_n + next_fib_n
FROM fibonacci
WHERE n < 20 )
SELECT * FROM fibonacci;
+------+-------+------------+
| n | fib_n | next_fib_n |
+------+-------+------------+
| 1 | 0 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 2 |
| 4 | 2 | 3 |
| 5 | 3 | 5 |
| 6 | 5 | 8 |
| 7 | 8 | 13 |
| 8 | 13 | 21 |
| 9 | 21 | 34 |
| 10 | 34 | 55 |
| 11 | 55 | 89 |
| 12 | 89 | 144 |
| 13 | 144 | 233 |
| 14 | 233 | 377 |
| 15 | 377 | 610 |
| 16 | 610 | 987 |
| 17 | 987 | 1597 |
| 18 | 1597 | 2584 |
| 19 | 2584 | 4181 |
| 20 | 4181 | 6765 |
+------+-------+------------+날짜 순서
다음과 같이 상점의 매출을 포함하는 간단한 테이블이 있다고 가정해 보겠습니다.
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATE,
product VARCHAR(20),
price DECIMAL(10,2));
# populate the table
INSERT INTO sales(order_date, product, price)
VALUES('2020-02-01','DVD PLAYER',100.50),('2020-02-01','TV',399.99),('2020-02-02','LAPTOP',1249.00),
('2020-02-04','DISHWASHER',500.00),('2020-02-04','TV',699.00),('2020-02-06','LAPTOP',990.50),('2020-02-06','HAIRDRYER',29.90),
('2020-02-06','GAME CONSOLE',299.00),('2020-02-07','BOOK',9.00),('2020-02-07','REFRIGERATOR',600.00);
# let's run a query to generate the sales report by day
SELECT order_date, SUM(price) AS sales
FROM sales
GROUP BY order_date;
+------------+---------+
| order_date | sales |
+------------+---------+
| 2020-02-01 | 500.49 |
| 2020-02-02 | 1249.00 |
| 2020-02-04 | 1199.00 |
| 2020-02-06 | 1319.40 |
| 2020-02-07 | 609.00 |
+------------+---------+그러나 판매 보고서에 누락된 날짜(2월 3일 및 2월 5일)가 있습니다. 판매가 없는 날짜까지 포함하는 보고서를 생성하고 싶습니다.
WITH RECURSIVE dates(date) AS (
SELECT '2020-02-01'
UNION ALL
SELECT date + INTERVAL 1 DAY
FROM dates
WHERE date < '2020-02-07' )
SELECT dates.date, COALESCE(SUM(price), 0) sales
FROM dates LEFT JOIN sales ON dates.date = sales.order_date
GROUP BY dates.date;
+------------+---------+
| date | sales |
+------------+---------+
| 2020-02-01 | 500.49 |
| 2020-02-02 | 1249.00 |
| 2020-02-03 | 0.00 |
| 2020-02-04 | 1199.00 |
| 2020-02-05 | 0.00 |
| 2020-02-06 | 1319.40 |
| 2020-02-07 | 609.00 |
+------------+---------+
계층적 데이터 순회
이제 재귀적 CTE의 다른 사용 사례를 살펴보겠습니다. 다음 그림의 조직도에 대한 간단한 트리, 가족 계보에 대한 더 복잡한 트리 및 기차 경로에 대한 그래프입니다.
간단한 트리: 조직도
# create the table
CREATE TABLE orgchart(
id INT PRIMARY KEY,
name VARCHAR(20),
role VARCHAR(20),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES orgchart(id));
# insert the rows
INSERT INTO orgchart VALUES(1,'Matthew','CEO',NULL),
(2,'Caroline','CFO',1),(3,'Tom','CTO',1),
(4,'Sam','Treasurer',2),(5,'Ann','Controller',2),
(6,'Anthony','Dev Director',3),(7,'Lousie','Sys Admin',3),
(8,'Travis','Senior DBA',3),(9,'John','Developer',6),
(10,'Jennifer','Developer',6),(11,'Maria','Junior DBA',8);
# let's see the table, The CEO has no manager, so the manager_id is set to NULL
SELECT * FROM orgchat;
+----+----------+--------------+------------+
| id | name | role | manager_id |
+----+----------+--------------+------------+
| 1 | Matthew | CEO | NULL |
| 2 | Caroline | CFO | 1 |
| 3 | Tom | CTO | 1 |
| 4 | Sam | Treasurer | 2 |
| 5 | Ann | Controller | 2 |
| 6 | Anthony | Dev Director | 3 |
| 7 | Lousie | Sys Admin | 3 |
| 8 | Travis | Senior DBA | 3 |
| 9 | John | Developer | 6 |
| 10 | Jennifer | Developer | 6 |
| 11 | Maria | Junior DBA | 8 |
+----+----------+--------------+------------+재귀적 CTE를 사용하여 이러한 종류의 계층 쿼리를 실행해 보겠습니다.
# find the reporting chain for all the employees
mysql> WITH RECURSIVE reporting_chain(id, name, path) AS (
SELECT id, name, CAST(name AS CHAR(100))
FROM orgchart
WHERE manager_id IS NULL
UNION ALL
SELECT oc.id, oc.name, CONCAT(rc.path,' -> ',oc.name)
FROM reporting_chain rc JOIN orgchart oc ON rc.id=oc.manager_id)
SELECT * FROM reporting_chain;
+------+----------+---------------------------------------+
| id | name | path |
+------+----------+---------------------------------------+
| 1 | Matthew | Matthew |
| 2 | Caroline | Matthew -> Caroline |
| 3 | Tom | Matthew -> Tom |
| 4 | Sam | Matthew -> Caroline -> Sam |
| 5 | Ann | Matthew -> Caroline -> Ann |
| 6 | Anthony | Matthew -> Tom -> Anthony |
| 7 | Lousie | Matthew -> Tom -> Lousie |
| 8 | Travis | Matthew -> Tom -> Travis |
| 9 | John | Matthew -> Tom -> Anthony -> John |
| 10 | Jennifer | Matthew -> Tom -> Anthony -> Jennifer |
| 11 | Maria | Matthew -> Tom -> Travis -> Maria |
+------+----------+---------------------------------------+CTE의 "seed" 멤버에 대한 CAST 함수의 사용에 유의하십시오. CAST 기능을 사용하지 않는 경우 어떻게 되는지 살펴보겠습니다.
mysql> WITH RECURSIVE reporting_chain(id, name, path) AS (
SELECT id, name, name
FROM orgchart
WHERE manager_id IS NULL
UNION ALL
SELECT oc.id, oc.name, CONCAT(rc.path,' -> ',oc.name)
FROM reporting_chain rc JOIN orgchart oc ON rc.id=oc.manager_id)
SELECT * FROM reporting_chain;
ERROR 1406 (22001): Data too long for column 'path' at row 1쿼리는 이론상 정확하지만 문제는 path열의 유형이 비재귀적 SELECT에서만 결정되므로 CHAR(7)이라는 것입니다. CTE의 재귀 부분에서는 문자 잘림이 발생하므로 오류가 발생합니다.
트리를 탐색하고 조직도에서 직원의 수준을 계산하는 쿼리를 살펴보겠습니다.
mysql> WITH RECURSIVE reporting_chain(id, name, path, level) AS (
SELECT id, name, CAST(name AS CHAR(100)), 1
FROM orgchart
WHERE manager_id IS NULL
UNION ALL
SELECT oc.id, oc.name, CONCAT(rc.path,' -> ',oc.name), rc.level+1
FROM reporting_chain rc JOIN orgchart oc ON rc.id=oc.manager_id)
SELECT * FROM reporting_chain ORDER BY level;
+------+----------+---------------------------------------+-------+
| id | name | path | level |
+------+----------+---------------------------------------+-------+
| 1 | Matthew | Matthew | 1 |
| 2 | Caroline | Matthew -> Caroline | 2 |
| 3 | Tom | Matthew -> Tom | 2 |
| 4 | Sam | Matthew -> Caroline -> Sam | 3 |
| 5 | Ann | Matthew -> Caroline -> Ann | 3 |
| 6 | Anthony | Matthew -> Tom -> Anthony | 3 |
| 7 | Lousie | Matthew -> Tom -> Lousie | 3 |
| 8 | Travis | Matthew -> Tom -> Travis | 3 |
| 9 | John | Matthew -> Tom -> Anthony -> John | 4 |
| 10 | Jennifer | Matthew -> Tom -> Anthony -> Jennifer | 4 |
| 11 | Maria | Matthew -> Tom -> Travis -> Maria | 4 |
+------+----------+---------------------------------------+-------+복잡한 트리: 족보
조부모, 부모, 아들이 있는 다음 족보를 나타내는 테이블을 만듭니다.
CREATE TABLE genealogy(
id INT PRIMARY KEY,
name VARCHAR(20),
father_id INT,
mother_id INT,
FOREIGN KEY(father_id) REFERENCES genealogy(id),
FOREIGN KEY(mother_id) REFERENCES genealogy(id));
# populate the table
INSERT INTO genealogy VALUES(1,'Maria',NULL,NULL),
(2,'Tom',NULL,NULL),(3,'Robert',NULL,NULL),
(4,'Claire',NULL,NULL),(5,'John',2,1),
(6,'Jennifer',2,1),(7,'Sam',3,4),
(8,'James',7,6);
SELECT * FROM genealogy;
+----+----------+-----------+-----------+
| id | name | father_id | mother_id |
+----+----------+-----------+-----------+
| 1 | Maria | NULL | NULL |
| 2 | Tom | NULL | NULL |
| 3 | Robert | NULL | NULL |
| 4 | Claire | NULL | NULL |
| 5 | John | 2 | 1 |
| 6 | Jennifer | 2 | 1 |
| 7 | Sam | 3 | 4 |
| 8 | James | 7 | 6 |
+----+----------+-----------+-----------+James의 모든 조상과 관계를 찾아 보겠습니다.
mysql> WITH RECURSIVE ancestors AS (
SELECT *, CAST('son' AS CHAR(20)) AS relationship, 0 level
FROM genealogy
WHERE name='James'
UNION ALL
SELECT g.*, CASE WHEN g.id=a.father_id AND level=0 THEN 'father'
WHEN g.id=a.mother_id AND level=0 THEN 'mother'
WHEN g.id=a.father_id AND level=1 THEN 'grandfather'
WHEN g.id=a.mother_id AND level=1 THEN 'grandmother'
END,
level+1
FROM genealogy g, ancestors a
WHERE g.id=a.father_id OR g.id=a.mother_id)
SELECT * FROM ancestors;
+------+----------+-----------+-----------+--------------+-------+
| id | name | father_id | mother_id | relationship | level |
+------+----------+-----------+-----------+--------------+-------+
| 8 | James | 7 | 6 | son | 0 |
| 6 | Jennifer | 2 | 1 | mother | 1 |
| 7 | Sam | 3 | 4 | father | 1 |
| 1 | Maria | NULL | NULL | grandmother | 2 |
| 2 | Tom | NULL | NULL | grandfather | 2 |
| 3 | Robert | NULL | NULL | grandfather | 2 |
| 4 | Claire | NULL | NULL | grandmother | 2 |
+------+----------+-----------+-----------+--------------+-------+동일한 쿼리를 사용하지만 초기 조건을 변경하면 계층 구조에 있는 모든 사람(예: Jennifer)의 조상을 찾을 수 있습니다.
mysql> WITH RECURSIVE ancestors AS (
SELECT *, CAST('daughter' AS CHAR(20)) AS relationship, 0 level
FROM genealogy
WHERE name='Jennifer'
UNION ALL
SELECT g.*, CASE WHEN g.id=a.father_id AND level=0 THEN 'father'
WHEN g.id=a.mother_id AND level=0 THEN 'mother'
WHEN g.id=a.father_id AND level=1 THEN 'grandfather'
WHEN g.id=a.mother_id AND level=1 THEN 'grandmother'
END,
level+1
FROM genealogy g, ancestors a
WHERE g.id=a.father_id OR g.id=a.mother_id)
SELECT * FROM ancestors;
+------+----------+-----------+-----------+--------------+-------+
| id | name | father_id | mother_id | relationship | level |
+------+----------+-----------+-----------+--------------+-------+
| 6 | Jennifer | 2 | 1 | daughter | 0 |
| 1 | Maria | NULL | NULL | mother | 1 |
| 2 | Tom | NULL | NULL | father | 1 |
+------+----------+-----------+-----------+--------------+-------+그래프: 기차 노선
아래 이미지에서 더 중요한 도시에 대한 이탈리아의 기차 경로를 나타내는 그래프를 만들어 보겠습니다.
단방향 및 양방향 연결에 유의하세요. 각 연결에는 km 단위의 거리도 있습니다.
CREATE TABLE train_route(
id INT PRIMARY KEY,
origin VARCHAR(20),
destination VARCHAR(20),
distance INT);
# populate the table
INSERT INTO train_route VALUES(1,'MILAN','TURIN',150),
(2,'TURIN','MILAN',150),(3,'MILAN','VENICE',250),
(4,'VENICE','MILAN',250),(5,'MILAN','GENOA',200),
(6,'MILAN','ROME',600),(7,'ROME','MILAN',600),
(8,'MILAN','FLORENCE',380),(9,'TURIN','GENOA',160),
(10,'GENOA','TURIN',160),(11,'FLORENCE','VENICE',550),
(12,'FLORENCE','ROME',220),(13,'ROME','FLORENCE',220),
(14,'GENOA','ROME',500),(15,'ROME','NAPLES',210),
(16,'NAPLES','VENICE',800);
SELECT * FROM train_route;
+----+----------+-------------+----------+
| id | origin | destination | distance |
+----+----------+-------------+----------+
| 1 | MILAN | TURIN | 150 |
| 2 | TURIN | MILAN | 150 |
| 3 | MILAN | VENICE | 250 |
| 4 | VENICE | MILAN | 250 |
| 5 | MILAN | GENOA | 200 |
| 6 | MILAN | ROME | 600 |
| 7 | ROME | MILAN | 600 |
| 8 | MILAN | FLORENCE | 380 |
| 9 | TURIN | GENOA | 160 |
| 10 | GENOA | TURIN | 160 |
| 11 | FLORENCE | VENICE | 550 |
| 12 | FLORENCE | ROME | 220 |
| 13 | ROME | FLORENCE | 220 |
| 14 | GENOA | ROME | 500 |
| 15 | ROME | NAPLES | 210 |
| 16 | NAPLES | VENICE | 800 |
+----+----------+-------------+----------+밀라노를 출발지로 하는 모든 기차 목적지 반환:
mysql> WITH RECURSIVE train_destination AS (
SELECT origin AS dest
FROM train_route
WHERE origin='MILAN'
UNION
SELECT tr.destination
FROM train_route tr
JOIN train_destination td ON td.dest=tr.origin)
SELECT * from train_destination;
+----------+
| dest |
+----------+
| MILAN |
| TURIN |
| VENICE |
| GENOA |
| ROME |
| FLORENCE |
| NAPLES |
+----------+기본적으로 어느 도시에서나 이탈리아에서는 원하는 곳으로 갈 수 있지만 다른 경로가 있습니다. 따라서 쿼리를 실행하여 밀라노와 나폴리에서 시작하여 가능한 모든 경로와 각 경로의 총 길이를 알아보겠습니다.
mysql> WITH RECURSIVE paths (cur_path, cur_dest, tot_distance) AS (
SELECT CAST(origin AS CHAR(100)), CAST(origin AS CHAR(100)), 0
FROM train_route
WHERE origin='MILAN'
UNION
SELECT CONCAT(paths.cur_path, ' -> ', train_route.destination), train_route.destination, paths.tot_distance+train_route.distance
FROM paths, train_route
WHERE paths.cur_dest = train_route.origin
AND NOT FIND_IN_SET(train_route.destination, REPLACE(paths.cur_path,' -> ',',') ) )
SELECT * FROM paths;
+-------------------------------------------------------+----------+--------------+
| cur_path | cur_dest | tot_distance |
+-------------------------------------------------------+----------+--------------+
| MILAN | MILAN | 0 |
| MILAN -> TURIN | TURIN | 150 |
| MILAN -> VENICE | VENICE | 250 |
| MILAN -> GENOA | GENOA | 200 |
| MILAN -> ROME | ROME | 600 |
| MILAN -> FLORENCE | FLORENCE | 380 |
| MILAN -> TURIN -> GENOA | GENOA | 310 |
| MILAN -> GENOA -> TURIN | TURIN | 360 |
| MILAN -> GENOA -> ROME | ROME | 700 |
| MILAN -> ROME -> FLORENCE | FLORENCE | 820 |
| MILAN -> ROME -> NAPLES | NAPLES | 810 |
| MILAN -> FLORENCE -> VENICE | VENICE | 930 |
| MILAN -> FLORENCE -> ROME | ROME | 600 |
| MILAN -> TURIN -> GENOA -> ROME | ROME | 810 |
| MILAN -> GENOA -> ROME -> FLORENCE | FLORENCE | 920 |
| MILAN -> GENOA -> ROME -> NAPLES | NAPLES | 910 |
| MILAN -> ROME -> FLORENCE -> VENICE | VENICE | 1370 |
| MILAN -> ROME -> NAPLES -> VENICE | VENICE | 1610 |
| MILAN -> FLORENCE -> ROME -> NAPLES | NAPLES | 810 |
| MILAN -> TURIN -> GENOA -> ROME -> FLORENCE | FLORENCE | 1030 |
| MILAN -> TURIN -> GENOA -> ROME -> NAPLES | NAPLES | 1020 |
| MILAN -> GENOA -> ROME -> FLORENCE -> VENICE | VENICE | 1470 |
| MILAN -> GENOA -> ROME -> NAPLES -> VENICE | VENICE | 1710 |
| MILAN -> FLORENCE -> ROME -> NAPLES -> VENICE | VENICE | 1610 |
| MILAN -> TURIN -> GENOA -> ROME -> FLORENCE -> VENICE | VENICE | 1580 |
| MILAN -> TURIN -> GENOA -> ROME -> NAPLES -> VENICE | VENICE | 1820 |
+-------------------------------------------------------+----------+--------------+
mysql> WITH RECURSIVE paths (cur_path, cur_dest, tot_distance) AS (
SELECT CAST(origin AS CHAR(100)), CAST(origin AS CHAR(100)), 0
FROM train_route
WHERE origin='NAPLES'
UNION
SELECT CONCAT(paths.cur_path, ' -> ', train_route.destination), train_route.destination, paths.tot_distance+train_route.distance
FROM paths, train_route
WHERE paths.cur_dest = train_route.origin
AND NOT FIND_IN_SET(train_route.destination, REPLACE(paths.cur_path,' -> ',',') ) )
SELECT * FROM paths;
+-----------------------------------------------------------------+----------+--------------+
| cur_path | cur_dest | tot_distance |
+-----------------------------------------------------------------+----------+--------------+
| NAPLES | NAPLES | 0 |
| NAPLES -> VENICE | VENICE | 800 |
| NAPLES -> VENICE -> MILAN | MILAN | 1050 |
| NAPLES -> VENICE -> MILAN -> TURIN | TURIN | 1200 |
| NAPLES -> VENICE -> MILAN -> GENOA | GENOA | 1250 |
| NAPLES -> VENICE -> MILAN -> ROME | ROME | 1650 |
| NAPLES -> VENICE -> MILAN -> FLORENCE | FLORENCE | 1430 |
| NAPLES -> VENICE -> MILAN -> TURIN -> GENOA | GENOA | 1360 |
| NAPLES -> VENICE -> MILAN -> GENOA -> TURIN | TURIN | 1410 |
| NAPLES -> VENICE -> MILAN -> GENOA -> ROME | ROME | 1750 |
| NAPLES -> VENICE -> MILAN -> ROME -> FLORENCE | FLORENCE | 1870 |
| NAPLES -> VENICE -> MILAN -> FLORENCE -> ROME | ROME | 1650 |
| NAPLES -> VENICE -> MILAN -> TURIN -> GENOA -> ROME | ROME | 1860 |
| NAPLES -> VENICE -> MILAN -> GENOA -> ROME -> FLORENCE | FLORENCE | 1970 |
| NAPLES -> VENICE -> MILAN -> TURIN -> GENOA -> ROME -> FLORENCE | FLORENCE | 2080 |
+-----------------------------------------------------------------+----------+--------------+이제 한 출발지에서 최종 목적지까지의 최단 경로를 찾는 것이 매우 쉽습니다. 기본 쿼리를 필터링하고 정렬하기만 하면 됩니다. 여기 몇 가지 예가 있어요.
# shortest path from MILAN to NAPLES
mysql> WITH RECURSIVE paths (cur_path, cur_dest, tot_distance) AS (
SELECT CAST(origin AS CHAR(100)), CAST(origin AS CHAR(100)), 0 FROM train_route WHERE origin='MILAN'
UNION
SELECT CONCAT(paths.cur_path, ' -> ', train_route.destination), train_route.destination, paths.tot_distance+train_route.distance
FROM paths, train_route
WHERE paths.cur_dest = train_route.origin AND NOT FIND_IN_SET(train_route.destination, REPLACE(paths.cur_path,' -> ',',') ) )
SELECT * FROM paths
WHERE cur_dest='NAPLES'
ORDER BY tot_distance ASC LIMIT 1
+-------------------------+----------+--------------+
| cur_path | cur_dest | tot_distance |
+-------------------------+----------+--------------+
| MILAN -> ROME -> NAPLES | NAPLES | 810 |
+-------------------------+----------+--------------+
# shortest path from VENICE to GENOA
mysql> WITH RECURSIVE paths (cur_path, cur_dest, tot_distance) AS (
SELECT CAST(origin AS CHAR(100)), CAST(origin AS CHAR(100)), 0 FROM train_route WHERE origin='VENICE'
UNION
SELECT CONCAT(paths.cur_path, ' -> ', train_route.destination), train_route.destination, paths.tot_distance+train_route.distance
FROM paths, train_route
WHERE paths.cur_dest = train_route.origin AND NOT FIND_IN_SET(train_route.destination, REPLACE(paths.cur_path,' -> ',',') ) )
SELECT * FROM paths
WHERE cur_dest='GENOA'
ORDER BY tot_distance ASC LIMIT 1;
+--------------------------+----------+--------------+
| cur_path | cur_dest | tot_distance |
+--------------------------+----------+--------------+
| VENICE -> MILAN -> GENOA | GENOA | 450 |
+--------------------------+----------+--------------+
# shortest path from VENICE to NAPLES
mysql> WITH RECURSIVE paths (cur_path, cur_dest, tot_distance) AS (
SELECT CAST(origin AS CHAR(100)), CAST(origin AS CHAR(100)), 0 FROM train_route WHERE origin='VENICE'
UNION
SELECT CONCAT(paths.cur_path, ' -> ', train_route.destination), train_route.destination, paths.tot_distance+train_route.distance
FROM paths, train_route
WHERE paths.cur_dest = train_route.origin AND NOT FIND_IN_SET(train_route.destination, REPLACE(paths.cur_path,' -> ',',') ) )
SELECT * FROM paths
WHERE cur_dest='NAPLES'
ORDER BY tot_distance ASC LIMIT 1;
+-----------------------------------+----------+--------------+
| cur_path | cur_dest | tot_distance |
+-----------------------------------+----------+--------------+
| VENICE -> MILAN -> ROME -> NAPLES | NAPLES | 1060 |
+-----------------------------------+----------+--------------+
제한 사항
실행 시간과 반복 횟수를 제한하기 위해 이미 본 제한 사항 외에도 알고 있어야 하는 다른 기본 제공 제한 사항이 있습니다.
재귀적 SELECT에는 다음 구문이 포함되어서는 안 됩니다.
- SUM() 과 같은 집계 함수
- GROUP BY
- ORDER BY
- DISTINCT
- Window functions
이러한 제한은 비재귀적 CTE에는 유효하지 않습니다. 또한 재귀 SELECT 부분은 서브 쿼리가 아닌 FROM 절에서 CTE를 한 번만 참조해야 합니다.
결론
재귀 CTE 표현식은 MySQL 8.0을 사용하여 애플리케이션에 대한 쿼리를 구현하는 흥미로운 기능입니다. 재귀는 과거에 이미 stored routin을 생성하여 가능했지만 지금은 더 간단합니다. 또한 재귀 쿼리를 생성하기 위해 추가 권한이 필요하지 않습니다.
일반적으로 재귀적 CTE는 매우 간단하지만 비재귀적 CTE에 비해 조금 더 복잡합니다. 물론 구문의 문제가 아니라 재귀적으로 생각이 필요해서 발생한 문제일 뿐입니다.
'MySQL' 카테고리의 다른 글
| MySQL 테이블 단편화 (0) | 2021.09.02 |
|---|---|
| MySQL 8.0 신기능 CTE(Common Table Expression) 활용 (0) | 2021.08.26 |
| MySQL Shell Upgrade Checker Utility (0) | 2021.08.24 |
| MySQL 8 및 MySQL 5.7 메모리 소비 (0) | 2021.08.24 |
| pt-kill (0) | 2021.08.19 |
