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);