WITH 절

ClickHouse는 Common Table Expressions(CTE), Common Scalar Expressions, 그리고 재귀 쿼리(Recursive Queries)를 지원해요. WITH 절로 이름이 붙은 서브쿼리, 스칼라 표현식 별칭, 그리고 자기 자신의 출력을 참조하는 재귀 쿼리를 작성할 수 있어요.

출처: 문서

본문

Common Table Expressions (공통 테이블 표현식)

Common Table Expressions는 이름이 붙은 서브쿼리를 나타내요. 테이블 표현식이 허용되는 SELECT 쿼리 어디에서나 이름으로 참조할 수 있어요. 이름이 붙은 서브쿼리는 현재 쿼리의 스코프나 자식 서브쿼리의 스코프에서 이름으로 참조될 수 있어요. CTE가 명시적으로 materialized로 정의되지 않았다면, SELECT 쿼리에서 Common Table Expression에 대한 모든 참조는 항상 그 정의의 서브쿼리로 대체돼요 (Materialized Common Table Expressions 참고). 현재 CTE를 식별자 해석 과정에서 숨김으로써 재귀를 방지해요. CTE는 호출되는 모든 위치에서 동일한 결과를 보장하지는 않는다는 점에 유의해요. 각 사용처에서 쿼리가 다시 실행되기 때문이에요.

Syntax (구문)

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

Example (예제)

서브쿼리가 다시 실행되는 경우의 예시:

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 등 서로 다른 임의의 결과가 보이게 돼요.

Materialized Common Table Expressions (구체화된 공통 테이블 표현식)

기본적으로 ClickHouse는 CTE의 서브쿼리를 각 참조 지점에 인라인하여 매번 다시 실행해요. MATERIALIZED 키워드를 추가하면 ClickHouse가 CTE 서브쿼리를 정확히 한 번 실행하고 결과를 임시 테이블에 저장한 뒤, 모든 참조를 그 테이블에서 처리하게 해줘요. 이는 같은 CTE가 쿼리에서 여러 번 참조될 때(예: self-join이나 여러 IN 서브쿼리) 특히 유용한데, 기본 계산이 한 번만 수행되기 때문이에요. Materialized CTE는 실험적(experimental) 기능이에요. enable_materialized_cte 설정을 활성화해야 해요. 설정이 비활성화되어 있으면 MATERIALIZED 키워드는 무시되고, CTE는 일반 CTE처럼 각 참조 지점에 인라인되며 경고가 기록돼요.

Syntax (구문)

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

When to use (사용 시점)

Materialized CTE는 다음 경우에 가장 유용해요.

같은 CTE가 쿼리에서 두 번 이상 참조될 때. MATERIALIZED가 없으면 각 참조가 서브쿼리를 독립적으로 다시 실행해요.
CTE에 generateRandom 같은 비결정적(non-deterministic) 함수가 포함될 때. Materialize하면 모든 참조가 같은 데이터를 보게 돼요.
CTE가 비싼 계산(집계, 조인, 대규모 스캔)을 포함해서 반복하면 안 될 때.

Materialized CTE가 한 번만 참조되면, ClickHouse는 불필요한 오버헤드를 피하려고 이를 일반 서브쿼리로 자동 인라인해요.

Examples (예제)

Example 1 (예제 1): Materialized CTE에 대한 self-join

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 │
└─────────┘

Example 2 (예제 2): 비결정적 함수로 결정적 결과 얻기

일반 CTE는 generateRandom을 사용할 때 각 참조 지점에서 다른 결과를 만들어요. CTE를 materialize하면 일관성이 보장돼요.

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

두 참조가 같은 materialize된 데이터를 읽기 때문에 결과는 항상 1000000이에요.

Example 3 (예제 3): Materialized CTE 연결하기

Materialized CTE는 다른 materialized CTE를 참조할 수 있어요. ClickHouse는 의존성을 해결하고 올바른 순서로 materialize해요.

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 정의의 순서는 중요하지 않아요. 앞선 참조(forward reference)가 허용돼요.

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 │
└─────────┘

Restrictions (제한 사항)

실험적 설정 필요: enable_materialized_cte 설정이 활성화되어 있어야 해요. 비활성화되어 있으면 MATERIALIZED 키워드는 무시되고, CTE는 일반 CTE처럼 각 참조 지점에 인라인되며 경고가 기록돼요.
RECURSIVE와 함께 사용 불가: MATERIALIZED와 RECURSIVE 키워드를 조합하는 것은 허용되지 않으며 UNSUPPORTED_METHOD 예외가 발생해요.
상관 CTE 금지: materialized CTE는 바깥 쿼리 스코프의 열을 참조할 수 없어요.

Common Scalar Expressions (공통 스칼라 표현식)

ClickHouse는 WITH 절에서 임의의 스칼라 표현식에 별칭을 선언할 수 있게 해줘요. 공통 스칼라 표현식은 쿼리의 어떤 위치에서든 참조할 수 있어요. 공통 스칼라 표현식이 상수 리터럴이 아닌 다른 것을 참조하면, 그 표현식은 free variables를 만들 수 있어요. ClickHouse는 가능한 가장 가까운 스코프에서 식별자를 해석하므로, 이름 충돌이 있을 때 free variable이 예상치 못한 엔티티를 참조하거나 상관 서브쿼리(correlated subquery)를 만들 수 있어요. 더 예측 가능한 표현식 식별자 해석을 위해서는 사용된 모든 식별자를 바인딩하는 lambda function으로 CSE를 정의하는 것이 권장돼요.

Syntax (구문)

WITH <expression> AS <identifier>

Examples (예제)

Example 1 (예제 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;

Example 2 (예제 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  │
   └───────────┘

Example 3 (예제 3): free variable과 함께 고차 함수 사용

다음 예시 쿼리들은 바인딩되지 않은 식별자가 가장 가까운 스코프의 엔티티로 해석된다는 것을 보여줘요. 여기서 extension은 gen_name 람다 함수 본문에 바인딩되어 있지 않아요. extension이 generated_names 정의와 사용 스코프에서 공통 스칼라 표현식으로 '.txt'로 정의되어 있음에도, generated_names 서브쿼리에서 사용 가능하기 때문에 테이블 extension_list의 열로 해석돼요.

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  │
   └───────────┘

Example 4 (예제 4): SELECT 절 열 목록에서 sum(bytes) 표현식 결과 추출

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

Example 5 (예제 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;

Example 6 (예제 6): 서브쿼리에서 lambda로 정의한 공통 스칼라 표현식 재사용

공통 스칼라 표현식을 lambda로 정의해서 인자를 명시적으로 바인딩해요.

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 Queries (재귀 쿼리)

선택적인 RECURSIVE 수정자는 WITH 쿼리가 자신의 출력을 참조할 수 있게 해줘요.

Example (예제): 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에서 도입된 query analyzer에 의존하는데, 해당 버전부터 기본값이며 26.9부터는 필수예요. analyzer가 아직 비활성화된 이전 버전(인스턴스, 역할 또는 프로필에 대해서)에서는 재귀 CTE가 (UNKNOWN_TABLE) 또는 (UNSUPPORTED_METHOD) 예외를 발생시켜요. 해당 환경에서는 enable_analyzer 설정을 활성화하거나 업그레이드해야 해요. 재귀 WITH 쿼리의 일반적인 형태는 항상 비재귀 항(term), UNION ALL, 재귀 항 순서이며, 여기서 재귀 항만 쿼리 자신의 출력을 참조할 수 있어요. 재귀 CTE 쿼리는 다음과 같이 실행돼요.

비재귀 항을 평가해요. 비재귀 항 쿼리의 결과를 임시 작업(working) 테이블에 넣어요.
작업 테이블이 비어 있지 않은 동안 다음 단계를 반복해요. 임시 작업 테이블의 현재 내용을 재귀 자기 참조로 대체하여 재귀 항을 평가해요. 재귀 항 쿼리의 결과를 임시 중간(intermediate) 테이블에 넣어요. 작업 테이블의 내용을 중간 테이블의 내용으로 교체한 뒤, 중간 테이블을 비워요.

재귀 쿼리는 보통 계층적 또는 트리 구조의 데이터를 다룰 때 사용돼요. 예를 들어 트리 순회(traversal)를 수행하는 쿼리를 작성할 수 있어요.

Example (예제): 트리 순회

먼저 트리 테이블을 만들어 볼게요.

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

이런 쿼리로 그 트리를 순회할 수 있어요.

Example (예제): 트리 순회

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 │
└────┴───────────┴───────────┘

Search order (검색 순서)

깊이 우선(depth-first) 순서를 만들려면 각 결과 행에 대해 이미 방문한 행들의 배열을 계산해요.

Example (예제): 트리 순회 깊이 우선 순서

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]   │
└────┴───────────┴───────────┴─────────┘

너비 우선(breadth-first) 순서를 만들려면 검색의 깊이를 추적하는 열을 추가하는 것이 표준적인 방법이에요.

Example (예제): 트리 순회 너비 우선 순서

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 │
└────┴──────┴───────────┴─────────┴───────┘

Cycle detection (사이클 감지)

먼저 그래프 테이블을 만들어 볼게요.

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

이런 쿼리로 그 그래프를 순회할 수 있어요.

Example (예제): 사이클 감지 없는 그래프 순회

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)

사이클을 처리하는 표준적인 방법은 이미 방문한 노드들의 배열을 계산하는 것이에요.

Example (예제): 사이클 감지가 있는 그래프 순회

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)] │
└──────┴────┴────────┴──────────┴───────────────────────────┘

Infinite queries (무한 쿼리)

바깥 쿼리에서 LIMIT를 사용하면 무한 재귀 CTE 쿼리도 가능해요.

Example (예제): 무한 재귀 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 │
└─────────────┘

Trailing Comma (끝의 쉼표)

WITH 절의 마지막 요소 뒤에는 쉼표가 허용돼요.

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

WITH 절

WITH 절

본문

Common Table Expressions (공통 테이블 표현식)

Syntax (구문)

Example (예제)

Materialized Common Table Expressions (구체화된 공통 테이블 표현식)

Syntax (구문)

When to use (사용 시점)

Examples (예제)

Restrictions (제한 사항)

Common Scalar Expressions (공통 스칼라 표현식)

Syntax (구문)

Examples (예제)

Recursive Queries (재귀 쿼리)

Search order (검색 순서)

Cycle detection (사이클 감지)

Infinite queries (무한 쿼리)

Trailing Comma (끝의 쉼표)

더 알아보기 (Learn more)