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
재귀적 CTE는 무한 루프에 빠질수 있다. 이런 경우를 피하기 위해서 시스템 파라미터 cte_max_recursions 를 원하는 임계치로 설정해야 한다. 이 파라미터의 기본값은 2,000번 재귀 반복이며, 최대값은 1,000,000 최소값은 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.
Warning
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.
DML과 CREATE에서 CTE의 사용¶
SELECT 문에 대한 사용 외에도 CTE는 다른 문장에도 사용될 수 있다. CTE가 CREATE TABLE table_name AS SELECT 에 사용될 수있다:
CREATE TABLE inc AS
WITH RECURSIVE cte (n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM cte
WHERE n < 3)
SELECT n FROM cte;
SELECT * FROM inc;
n
=============
1
2
3
또한, INSERT/REPLACE INTO table_name SELECT 도 CTE 사용이 가능하다:
INSERT INTO inc
WITH RECURSIVE cte (n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM cte
WHERE n < 3)
SELECT * FROM cte;
REPLACE INTO inc
WITH cte AS (SELECT * FROM inc)
SELECT * FROM cte;
또한 UPDATE 의 부질의에서도 사용가능하다:
CREATE TABLE green_products (producer_id INTEGER, sales_n INTEGER, product VARCHAR, product_type INTEGER, price INTEGER);
INSERT INTO green_products VALUES (1, 99, 'bicycle', 1, 99);
INSERT INTO green_products VALUES (2, 337, 'bicycle', 1, 129);
INSERT INTO green_products VALUES (3, 5012, 'bicycle', 1, 199);
INSERT INTO green_products VALUES (1, 989, 'scooter', 2, 899);
INSERT INTO green_products VALUES (3, 3211, 'scooter', 2, 599);
INSERT INTO green_products VALUES (4, 2312, 'scooter', 2, 1009);
WITH price_increase_th AS (
SELECT SUM (sales_n) * 7 / 10 AS threshold, product_type
FROM green_products
GROUP BY product_type
)
UPDATE green_products gp JOIN price_increase_th th ON gp.product_type = th.product_type
SET price = price + (price / 10)
WHERE sales_n >= threshold;
또한, DELETE 의 부질의에서도 사용가능하다:
WITH product_removal_th AS (
SELECT SUM (sales_n) / 20 AS threshold, product_type
FROM green_products
GROUP BY product_type
)
DELETE
FROM green_products gp
WHERE sales_n < (select threshold from product_removal_th WHERE product_type = gp.product_type);