WITH

WITH 절에 대한 문서

ClickHouse는 공통 테이블 표현식(CTE), 공통 스칼라 표현식 및 재귀 쿼리를 지원합니다.

​

공통 테이블 표현식

공통 테이블 표현식은 이름이 있는 서브쿼리를 의미합니다. 테이블 표현식이 허용되는 SELECT 쿼리라면 어느 위치에서든 이름으로 참조할 수 있습니다. 이름이 있는 서브쿼리는 현재 쿼리의 범위 내에서, 또는 하위 서브쿼리의 범위 내에서 이름으로 참조할 수 있습니다.

SELECT 쿼리에서 공통 테이블 표현식에 대한 모든 참조는 CTE가 명시적으로 구체화된 것으로 정의되지 않은 한(Materialized Common Table Expressions 참조), 항상 해당 정의의 서브쿼리로 대체됩니다. 재귀는 현재 CTE를 식별자 해석 과정에서 숨겨 방지합니다.

CTE는 호출되는 모든 위치에서 동일한 결과를 보장하지 않는다는 점에 유의하십시오. 사용될 때마다 쿼리가 다시 실행되기 때문입니다.

​

구문

WITH <identifier> AS [MATERIALIZED] <subquery expression>

​

예시

서브쿼리가 다시 실행되는 경우의 예시는 다음과 같습니다:

WITH cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)

CTE가 단순한 코드 조각이 아니라 결과 자체를 그대로 전달한다면, 항상 1000000이 표시됩니다.

하지만 cte_numbers를 두 번 참조하기 때문에 매번 난수가 생성되고, 그 결과 280501, 392454, 261636, 196227 등 서로 다른 값이 표시됩니다…

​

구체화된 공통 테이블 표현식

기본적으로 ClickHouse는 CTE의 서브쿼리를 참조되는 각 지점에 인라인하므로, 참조될 때마다 다시 실행합니다. MATERIALIZED 키워드를 추가하면 ClickHouse는 CTE 서브쿼리를 정확히 한 번만 실행하고, 그 결과를 임시 테이블에 저장한 다음, 모든 참조가 해당 테이블에서 결과를 가져오도록 합니다. 따라서 동일한 CTE가 하나의 쿼리에서 여러 번 참조되는 경우(예: 셀프 조인 또는 여러 IN 서브쿼리) 특히 유용합니다. 기본 계산은 한 번만 수행되기 때문입니다.

구체화된 CTE는 실험적 기능입니다. 사용하려면 설정 enable_materialized_cte가 활성화되어 있어야 합니다. 이 설정이 비활성화되어 있으면 MATERIALIZED 키워드는 무시됩니다. 즉, CTE는 일반 CTE처럼 각 참조 지점에 인라인되며 경고가 기록됩니다.

​

구문

WITH <identifier> AS MATERIALIZED (<subquery>)
SELECT ...

​

사용하면 좋은 경우

구체화된 CTE는 다음과 같은 경우에 특히 유용합니다.

• 하나의 쿼리에서 동일한 CTE를 두 번 이상 참조하는 경우입니다. MATERIALIZED를 사용하지 않으면 각 참조마다 서브쿼리가 독립적으로 다시 실행됩니다.
• CTE에 generateRandom과 같은 비결정적 함수가 포함된 경우입니다. 구체화하면 모든 참조에서 동일한 데이터를 보게 됩니다.
• CTE에 비용이 큰 계산(집계, 조인, 대규모 스캔)이 포함되어 있어 반복 실행을 피해야 하는 경우입니다.

구체화된 CTE가 한 번만 참조되면 ClickHouse는 불필요한 오버헤드를 피하기 위해 이를 자동으로 일반 서브쿼리에 인라인합니다.

​

예시

예시 1: 구체화된 CTE에 대한 셀프 조인

MATERIALIZED가 없으면 조인의 양쪽에서 각각 서브쿼리를 독립적으로 실행합니다. MATERIALIZED를 사용하면 테이블을 한 번만 스캔하고, 조인의 양쪽이 동일한 임시 테이블에서 읽습니다.

SET enable_materialized_cte = 1;

CREATE TABLE users (uid Int16, name String, age Int16) ENGINE = Memory;
INSERT INTO users VALUES (1231, 'John', 33), (6666, 'Ksenia', 48), (8888, 'Alice', 50);

WITH
    a AS MATERIALIZED (SELECT * FROM users WHERE name = 'Alice')
SELECT count() FROM a AS l JOIN a AS r ON l.uid = r.uid;

┌─count()─┐
│       1 │
└─────────┘

예시 2: 비결정적 함수로 결정적인 결과 얻기

일반 CTE에서 generateRandom을 사용하면 참조할 때마다 서로 다른 결과가 생성됩니다. CTE를 구체화하면 일관성이 보장됩니다:

SET enable_materialized_cte = 1;

WITH cte_numbers AS MATERIALIZED
(
    SELECT num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers);

두 참조 모두 동일한 구체화된 데이터에서 읽으므로 결과는 항상 1000000입니다.

예시 3: 구체화된 CTE 연결하기

구체화된 CTE는 다른 구체화된 CTE를 참조할 수 있습니다. ClickHouse는 종속성을 해결하고 올바른 순서로 구체화합니다.

SET enable_materialized_cte = 1;

WITH
    a AS MATERIALIZED (SELECT uid, name FROM users),
    b AS MATERIALIZED (SELECT uid FROM a)
SELECT count() FROM b AS l LEFT SEMI JOIN b AS r ON l.uid = r.uid;

┌─count()─┐
│       3 │
└─────────┘

CTE 정의 순서는 중요하지 않습니다 — 전방 참조가 허용됩니다:

SET enable_materialized_cte = 1;

WITH
    b AS MATERIALIZED (SELECT uid FROM a),
    a AS MATERIALIZED (SELECT uid FROM users)
SELECT count() FROM b AS l LEFT SEMI JOIN b AS r ON l.uid = r.uid;

┌─count()─┐
│       3 │
└─────────┘

​

제약 사항

• 실험적 설정 필요: enable_materialized_cte 설정을 활성화해야 합니다. 비활성화된 경우 MATERIALIZED 키워드는 무시됩니다. 즉, CTE는 일반 CTE처럼 각 참조 위치에 인라인되며, 경고가 로그에 기록됩니다.
• RECURSIVE와 함께 사용할 수 없음: MATERIALIZED와 RECURSIVE 키워드를 함께 사용하는 것은 허용되지 않으며, UNSUPPORTED_METHOD 예외가 발생합니다.
• 상관 CTE 사용 금지: 구체화된 CTE는 외부 쿼리 범위의 컬럼을 참조할 수 없습니다.

​

공통 스칼라 표현식

ClickHouse에서는 WITH 절에서 임의의 스칼라 표현식에 대한 별칭을 선언할 수 있습니다. 공통 스칼라 표현식은 쿼리의 어느 위치에서나 참조할 수 있습니다.

공통 스칼라 표현식이 상수 리터럴이 아닌 다른 대상을 참조하면 해당 표현식으로 인해 자유 변수가 생길 수 있습니다. ClickHouse는 식별자를 가능한 한 가장 가까운 범위에서 해석하므로, 이름 충돌이 발생하면 자유 변수가 예상치 못한 개체를 참조하거나 상관 서브쿼리로 이어질 수 있습니다. 표현식 식별자 해석이 더 예측 가능하게 동작하도록, 사용된 모든 식별자를 바인딩하는 람다 함수로 CSE를 정의하는 것이 좋습니다.

​

구문

WITH <expression> AS <identifier>

​

예시

예시 1: 상수 표현식을 “변수”처럼 사용하기

WITH '2019-08-01 15:23:00' AS ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound;

예시 2: 고차 함수를 사용해 식별자의 범위를 한정하기

WITH
    '.txt' as extension,
    (id, extension) -> concat(lower(id), extension) AS gen_name
SELECT gen_name('test', '.sql') as file_name;

   ┌─file_name─┐
1. │ test.sql  │
   └───────────┘

예시 3: 자유 변수를 사용하는 고차 함수

다음 예시 쿼리는 바인딩되지 않은 식별자가 가장 가까운 범위의 엔터티로 해석됨을 보여줍니다. 여기서 extension은 gen_name 람다 함수 본문에서 바인딩되어 있지 않습니다. extension은 generated_names의 정의 및 사용 범위에서 공통 스칼라 표현식으로 '.txt'로 정의되어 있지만, generated_names 서브쿼리(subquery)에서 사용할 수 있으므로 테이블 extension_list의 컬럼(column)으로 해석됩니다.

CREATE TABLE extension_list
(
    extension String
)
ORDER BY extension
AS SELECT '.sql';

WITH
    '.txt' as extension,
    generated_names as (
        WITH
            (id) -> concat(lower(id), extension) AS gen_name
        SELECT gen_name('test') as file_name FROM extension_list
    )
SELECT file_name FROM generated_names;

   ┌─file_name─┐
1. │ test.sql  │
   └───────────┘

예시 4: SELECT 절의 컬럼 목록에서 sum(bytes) 표현식 결과 제거하기

WITH sum(bytes) AS s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s;

예시 5: 스칼라 서브쿼리 결과 사용하기

/* this example would return TOP 10 of most huge tables */
WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

예시 6: 서브쿼리에서 람다로 정의된 공통 스칼라 표현식 재사용

인수가 명시적으로 바인딩되도록 공통 스칼라 표현식을 람다로 정의합니다:

WITH (value) -> value + 1 AS increment
SELECT increment(first_result) AS second_increment
FROM
(
    SELECT increment(number) AS first_result
    FROM numbers(3)
);

┌─second_increment─┐
│                2 │
│                3 │
│                4 │
└──────────────────┘

​

재귀 쿼리

선택적 RECURSIVE 수정자를 사용하면 WITH 쿼리가 자신의 출력 결과를 참조할 수 있습니다. 예시:

예시: 1부터 100까지 정수의 합

WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table WHERE number < 100
)
SELECT sum(number) FROM test_table;

┌─sum(number)─┐
│        5050 │
└─────────────┘

재귀 CTE는 버전 **24.3**에 도입된 쿼리 분석기에 의존하며, 해당 버전부터 기본으로 사용되고 **26.9**부터는 필수입니다. 인스턴스, role 또는 profile에서 분석기가 여전히 비활성화되어 있는 이전 버전에서는 재귀 CTE가 (UNKNOWN_TABLE) 또는 (UNSUPPORTED_METHOD) 예외를 발생시킵니다. 해당 환경에서 enable_analyzer 설정을 활성화하거나 업그레이드하십시오.

재귀 WITH 쿼리의 일반적인 형태는 항상 비재귀 항, 그다음 UNION ALL, 그다음 재귀 항으로 이루어집니다. 이때 쿼리 자신의 출력에 대한 참조를 포함할 수 있는 것은 재귀 항뿐입니다. 재귀 CTE 쿼리는 다음과 같이 실행됩니다:

1. 비재귀 항을 평가합니다. 비재귀 항 쿼리의 결과를 임시 작업 테이블에 저장합니다.
2. 작업 테이블이 비어 있지 않은 동안 다음 단계를 반복합니다:
   1. 재귀 항을 평가하면서, 재귀 자기 참조를 작업 테이블의 현재 내용으로 치환합니다. 재귀 항 쿼리의 결과를 임시 중간 테이블에 저장합니다.
   2. 작업 테이블의 내용을 중간 테이블의 내용으로 대체한 다음, 중간 테이블을 비웁니다.

재귀 쿼리는 일반적으로 계층형 데이터나 트리 구조 데이터를 다룰 때 사용됩니다. 예를 들어, 트리 순회를 수행하는 쿼리를 작성할 수 있습니다:

예시: 트리 순회

먼저 트리 테이블을 생성해 보겠습니다:

DROP TABLE IF EXISTS tree;
CREATE TABLE tree
(
    id UInt64,
    parent_id Nullable(UInt64),
    data String
) ENGINE = MergeTree ORDER BY id;

INSERT INTO tree VALUES (0, NULL, 'ROOT'), (1, 0, 'Child_1'), (2, 0, 'Child_2'), (3, 1, 'Child_1_1');

다음 쿼리로 해당 트리를 순회할 수 있습니다:

예시: 트리 순회

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree;

┌─id─┬─parent_id─┬─data──────┐
│  0 │      ᴺᵁᴸᴸ │ ROOT      │
│  1 │         0 │ Child_1   │
│  2 │         0 │ Child_2   │
│  3 │         1 │ Child_1_1 │
└────┴───────────┴───────────┘

​

탐색 순서

깊이 우선 순서를 만들기 위해 각 결과 행마다 지금까지 방문한 행의 배열을 계산합니다:

예시: 트리 순회의 깊이 우선 순서

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data, [t.id] AS path
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data, arrayConcat(path, [t.id])
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree ORDER BY path;

┌─id─┬─parent_id─┬─data──────┬─path────┐
│  0 │      ᴺᵁᴸᴸ │ ROOT      │ [0]     │
│  1 │         0 │ Child_1   │ [0,1]   │
│  3 │         1 │ Child_1_1 │ [0,1,3] │
│  2 │         0 │ Child_2   │ [0,2]   │
└────┴───────────┴───────────┴─────────┘

너비 우선 순서를 만들기 위한 표준적인 방법은 검색 깊이를 추적하는 컬럼을 추가하는 것입니다:

예시: 트리 순회 너비 우선 순서

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data, [t.id] AS path, toUInt64(0) AS depth
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data, arrayConcat(path, [t.id]), depth + 1
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree ORDER BY depth;

┌─id─┬─link─┬─data──────┬─path────┬─depth─┐
│  0 │ ᴺᵁᴸᴸ │ ROOT      │ [0]     │     0 │
│  1 │    0 │ Child_1   │ [0,1]   │     1 │
│  2 │    0 │ Child_2   │ [0,2]   │     1 │
│  3 │    1 │ Child_1_1 │ [0,1,3] │     2 │
└────┴──────┴───────────┴─────────┴───────┘

​

사이클 감지

먼저 그래프 테이블을 만들어 보겠습니다:

DROP TABLE IF EXISTS graph;
CREATE TABLE graph
(
    from UInt64,
    to UInt64,
    label String
) ENGINE = MergeTree ORDER BY (from, to);

INSERT INTO graph VALUES (1, 2, '1 -> 2'), (1, 3, '1 -> 3'), (2, 3, '2 -> 3'), (1, 4, '1 -> 4'), (4, 5, '4 -> 5');

다음 쿼리로 해당 그래프를 순회할 수 있습니다:

예시: 사이클 감지 없이 그래프 순회

WITH RECURSIVE search_graph AS (
    SELECT from, to, label FROM graph g
    UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;

┌─from─┬─to─┬─label──┐
│    1 │  4 │ 1 -> 4 │
│    1 │  2 │ 1 -> 2 │
│    1 │  3 │ 1 -> 3 │
│    2 │  3 │ 2 -> 3 │
│    4 │  5 │ 4 -> 5 │
└──────┴────┴────────┘

하지만 해당 그래프에 사이클이 추가되면 앞선 쿼리는 Maximum recursive CTE evaluation depth 오류로 실패합니다:

INSERT INTO graph VALUES (5, 1, '5 -> 1');

WITH RECURSIVE search_graph AS (
    SELECT from, to, label FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;

Code: 306. DB::Exception: Received from localhost:9000. DB::Exception: Maximum recursive CTE evaluation depth (1000) exceeded, during evaluation of search_graph AS (SELECT from, to, label FROM graph AS g UNION ALL SELECT g.from, g.to, g.label FROM graph AS g, search_graph AS sg WHERE g.from = sg.to). Consider raising max_recursive_cte_evaluation_depth setting.: While executing RecursiveCTESource. (TOO_DEEP_RECURSION)

사이클을 처리하는 일반적인 방법은 이미 방문한 노드들의 배열을 구하는 것입니다:

예시: 사이클 감지를 포함한 그래프 순회

WITH RECURSIVE search_graph AS (
    SELECT from, to, label, false AS is_cycle, [tuple(g.from, g.to)] AS path FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label, has(path, tuple(g.from, g.to)), arrayConcat(sg.path, [tuple(g.from, g.to)])
    FROM graph g, search_graph sg
    WHERE g.from = sg.to AND NOT is_cycle
)
SELECT * FROM search_graph WHERE is_cycle ORDER BY from;

┌─from─┬─to─┬─label──┬─is_cycle─┬─path──────────────────────┐
│    1 │  4 │ 1 -> 4 │ true     │ [(1,4),(4,5),(5,1),(1,4)] │
│    4 │  5 │ 4 -> 5 │ true     │ [(4,5),(5,1),(1,4),(4,5)] │
│    5 │  1 │ 5 -> 1 │ true     │ [(5,1),(1,4),(4,5),(5,1)] │
└──────┴────┴────────┴──────────┴───────────────────────────┘

​

무한 쿼리

외부 쿼리에서 LIMIT를 사용하면 무한 재귀 CTE 쿼리도 사용할 수 있습니다:

예시: 무한 재귀 CTE 쿼리

WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table
)
SELECT sum(number) FROM (SELECT number FROM test_table LIMIT 100);

┌─sum(number)─┐
│        5050 │
└─────────────┘

​

후행 쉼표

WITH 절의 마지막 요소 뒤에도 쉼표를 사용할 수 있습니다:

WITH
    (SELECT sum(number) FROM numbers(10)) AS total,
    total * 2 AS doubled,
SELECT total, doubled;