CTE
CTE(Common Table Expressions)는 질의문과 관련된 임시 테이블(결과 목록)이다. 질의문 내에서 CTE를 여러 번 참조할 수 있으며 질의문 범위 내에서만 표시된다. CTE를 사용하면 질의문 로직을 보다 효과적으로 분리하여 수행 성능을 개선할 수 있으며 계층 질의문을 생성할 때 CONNECT BY 질의문 또는 복잡한 질의 대신 재귀적 CTE를 사용할 수 있다.
CTE는 WITH 절로 시작한다. 부질의 목록과 부질의를 사용하는 최종 질의가 있어야 한다. 각 부질의(테이블 표현식)는 이름과 질의 정의를 포함한다. 테이블 표현식은 이전에 동일한 질의문에 정의된 다른 테이블 표현식을 참조할 수 있다. 구문은 다음과 같다.
WITH
[RECURSIVE <recursive_cte_name> [ (<recursive_column_names>) ] AS <recursive_sub-query>]
<cte_name1> [ (<cte1_column_names>) ] AS <sub-query1>
<cte_name2> [ (<cte2_column_names>) ] AS <sub-query2>
...
<final_query>
recursive_cte_name, cte_name1, cte_name2 : 테이블 표현식(부질의)의 식별자
recursive_column_names, cte1_column_names, cte2_column_names : 각 테이블 표현식 결과 컬럼에 대한 식별자
sub-query1, sub-query2 : 각 테이블 표현식을 정의하는 부질의
final_query : 이전에 정의된 테이블 표현식을 사용하는 질의. 일반적으로 FROM 절은 CTE 식별자를 포함한다.
가장 단순한 사용법은 테이블 표현식의 결과 목록을 결합하는 것이다.
CREATE TABLE products (id INTEGER PRIMARY KEY, parent_id INTEGER, item VARCHAR(100), price INTEGER);
INSERT INTO products VALUES (1, -1, 'Drone', 2000);
INSERT INTO products VALUES (2, 1, 'Blade', 10);
INSERT INTO products VALUES (3, 1, 'Brushless motor', 20);
INSERT INTO products VALUES (4, 1, 'Frame', 50);
INSERT INTO products VALUES (5, -1, 'Car', 20000);
INSERT INTO products VALUES (6, 5, 'Wheel', 100);
INSERT INTO products VALUES (7, 5, 'Engine', 4000);
INSERT INTO products VALUES (8, 5, 'Frame', 4700);
WITH
of_drones AS (SELECT item, 'drones' FROM products WHERE parent_id = 1),
of_cars AS (SELECT item, 'cars' FROM products WHERE parent_id = 5)
SELECT * FROM of_drones UNION ALL SELECT * FROM of_cars ORDER BY 1;
item 'drones'
============================================
'Blade' 'drones'
'Brushless motor' 'drones'
'Car' 'cars'
'Drone' 'drones'
'Engine' 'cars'
'Frame' 'drones'
'Frame' 'cars'
'Wheel' 'cars'
한 CTE의 부질의가 다른 CTE의 부질의에 참조될 수 있다(참조되는 CTE가 미리 정의되어 있어야 함) :
WITH
of_drones AS (SELECT item FROM products WHERE parent_id = 1),
filter_common_with_cars AS (SELECT * FROM of_drones INTERSECT SELECT item FROM products WHERE parent_id = 5)
SELECT * FROM filter_common_with_cars ORDER BY 1;
item
======================
'Frame'
- 다음과 같은 경우 오류가 발생한다. :
둘 이상의 CTE에서 동일한 식별자명 사용.
중첩된 WITH 절 사용.
WITH
my_cte AS (SELECT item FROM products WHERE parent_id = 1),
my_cte AS (SELECT * FROM my_cte INTERSECT SELECT item FROM products WHERE parent_id = 5)
SELECT * FROM my_cte ORDER BY 1;
before '
SELECT * FROM my_cte ORDER BY 1;
'
CTE name ambiguity, there are more than one CTEs with the same name: 'my_cte'.
WITH
of_drones AS (SELECT item FROM products WHERE parent_id = 1),
of_cars1 AS (WITH
of_cars2 AS (SELECT item FROM products WHERE parent_id = 5)
SELECT * FROM of_cars2
)
SELECT * FROM of_drones, of_cars1 ORDER BY 1;
before '
SELECT * FROM of_drones, of_cars1 ORDER BY 1;
'
Nested WITH clauses are not supported.
CTE 컬럼명
각 CTE 결과의 컬럼명은 CTE 이름 다음에 지정할 수 있다. CTE 컬럼 목록의 요소 수는 CTE 부질의의 컬럼 수와 일치해야 한다.
WITH
of_drones (product_name, product_type, price) AS (SELECT item, 'drones', price FROM products WHERE parent_id = 1),
of_cars (product_name, product_type, price) AS (SELECT item, 'cars', price FROM products WHERE parent_id = 5)
SELECT * FROM of_drones UNION ALL SELECT * FROM of_cars ORDER BY product_type, price;
WITH
of_drones (product_name, product_type, price) AS (SELECT item, 'drones' as type, MAX(price) FROM products WHERE parent_id = 1 GROUP BY type),
of_cars (product_name, product_type, price) AS (SELECT item, 'cars' as type, MAX (price) FROM products WHERE parent_id = 5 GROUP BY type)
SELECT * FROM of_drones UNION ALL SELECT * FROM of_cars ORDER BY product_type, price;
product_name product_type price
=========================================================
'Wheel' 'cars' 100
'Engine' 'cars' 4000
'Frame' 'cars' 4700
'Blade' 'drones' 10
'Brushless motor' 'drones' 20
'Frame' 'drones' 50
product_name product_type price
========================================================
'Wheel' 'cars' 4700
'Blade' 'drones' 50
CTE에 컬럼명이 없으면 CTE의 첫 번째 내부 Select 문에서 컬럼명을 가져온다. 원본 구문에 따라 표현식 결과 컬럼명이 결정된다.
WITH
of_drones AS (SELECT item, 'drones', MAX(price) FROM products WHERE parent_id = 1 GROUP BY 2),
of_cars AS (SELECT item, 'cars', MAX (price) FROM products WHERE parent_id = 5 GROUP BY 2)
SELECT * FROM of_drones UNION ALL SELECT * FROM of_cars ORDER BY 1;
item 'drones' max(products.price)
================================================================
'Blade' 'drones' 50
'Wheel' 'cars' 4700
재귀절
RECURSIVE 키워드를 사용하여 반복되는 질의를 구성할 수 있다(테이블 표현식 부질의 정의 자체 이름 포함). 재귀 테이블 표현식은 비재귀적 부분과 재귀적 부분(CTE 이름으로 부질의 참조)으로 구성된다. UNION ALL 질의 연산자를 사용하여 재귀적 부분과 비재귀적 부분을 결합 해야 한다 . 무한 반복하지 않도록 재귀적 부분을 정의해야 한다. 또한 재귀적 부분에 집계 함수를 포함하는 경우 집계 함수가 항상 튜플을 반환하고 재귀 반복이 계속되므로 GROUP BY 절도 포함해야 한다. WHERE 절의 조건을 더 이상 만족하지 않고 현재 수행된 반복의 결과가 없을 경우 재귀 반복이 중단된다.
WITH
RECURSIVE cars (id, parent_id, item, price) AS (
SELECT id, parent_id, item, price
FROM products WHERE item LIKE 'Car%'
UNION ALL
SELECT p.id, p.parent_id, p.item, p.price
FROM products p
INNER JOIN cars rec_cars ON p.parent_id = rec_cars.id)
SELECT item, price FROM cars ORDER BY 1;
item price
===================================
'Car' 20000
'Engine' 4000
'Frame' 4700
'Wheel' 100
DML에서 CTE 사용(UPDATE 또는 DELETE 데이터) :
UPDATE products SET price =
(WITH
RECURSIVE cars (id, parent_id, item, price) AS (
SELECT id, parent_id, item, price
FROM products WHERE item LIKE 'Car%'
UNION ALL
SELECT p.id, p.parent_id, p.item, p.price
FROM products p
INNER JOIN cars rec_cars ON p.parent_id = rec_cars.id)
SELECT SUM(price) - MAX(price) FROM cars ORDER BY 1)
WHERE item='Car';
select item, price from products where item='Car';
item price
===================================
'Car' 8800
재귀적 CTE는 무한 루프에 빠질 수 있다. 이러한 경우를 방지하려면 시스템 파라미터 cte_max_recursions 를 원하는 임계값으로 설정한다. 기본값은 2000번 재귀 반복이고, 최대값은 1000000, 최소값은 2이다.
SET SYSTEM PARAMETERS 'cte_max_recursions=2';
WITH
RECURSIVE cars (id, parent_id, item, price) AS (
SELECT id, parent_id, item, price
FROM products WHERE item LIKE 'Car%'
UNION ALL
SELECT p.id, p.parent_id, p.item, p.price
FROM products p
INNER JOIN cars rec_cars ON p.parent_id = rec_cars.id)
SELECT item, price FROM cars ORDER BY 1;
In the command from line 9,
Maximum recursions 2 reached executing CTE.
경고
CTE 부질의의 복잡도에 따라 부질의에 대한 결과 셋이 매우 크게 증가하여 대용량의 데이터가 생성될 수 있으므로 디스크 공간 부족을 방지하기 위해 cte_max_recursions 설정값 조정을 고려해야 한다.
- 재귀적 CTE의 수행 알고리즘은 다음과 같이 요약할 수 있다.
CTE의 비재귀적 부분을 수행하고 결과를 최종 결과 셋에 추가
비재귀적 부분에서 얻은 결과 셋을 사용하여 재귀적 부분을 수행하고, 결과를 최종 결과 셋에 추가한 후, 결과 셋 내에서 현재 반복의 시작과 끝을 기억한다.
이전 반복의 결과 셋을 사용하여 비재귀적 부분의 수행을 반복하고 해당 결과를 최종 결과 셋에 추가
재귀 반복에서 결과가 생성되지 않으면 중지
설정된 최대 반복 횟수에 도달하는 경우에도 중지
재귀적 CTE를 FROM 절에서 바로 참조해야 한다. 부질의에서 참조하면 오류가 발생한다.
WITH
RECURSIVE cte1(x) AS SELECT c FROM t1 UNION ALL SELECT * FROM (SELECT cte1.x + 1 FROM cte1 WHERE cte1.x < 5)
SELECT * FROM cte1;
before '
SELECT * FROM cte1;
'
Recursive CTE 'cte1' must be referenced directly in its recursive query.