WITH 句

ClickHouse は、共通テーブル式（Common Table Expressions、CTE）、共通スカラ―式（Common Scalar Expressions）、および再帰クエリ（Recursive Queries）をサポートしています。

共通テーブル式

共通テーブル式 (CTE) は、名前付きサブクエリを表します。 SELECT クエリ内でテーブル式が許可されている任意の場所で、その名前を使って参照できます。名前付きサブクエリは、現在のクエリのスコープおよび子サブクエリのスコープ内で名前により参照できます。

SELECT クエリにおける共通テーブル式へのすべての参照は、CTE が明示的にマテリアライズドとして定義されていない場合、常にその定義にあるサブクエリに展開されます (マテリアライズド共通テーブル式を参照) 。再帰は、現在の 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 を 2 回参照しているため、そのたびに乱数が生成され、その結果として 280501, 392454, 261636, 196227 などのように毎回異なる乱数結果が表示されます。

マテリアライズド共通テーブル式

デフォルトでは、ClickHouse は CTE のサブクエリを参照箇所ごとにインライン展開し、その都度再実行します。 MATERIALIZED キーワードを追加すると、ClickHouse は CTE サブクエリを厳密に 1 回だけ実行し、結果を一時テーブルに保存して、以降のすべての参照をそのテーブルから処理します。これは、同じ CTE が 1 つのクエリ内で複数回参照される場合 (たとえば、自己結合や複数の IN サブクエリ内) に特に有用です。基になる計算は 1 回しか行われないためです。

注記

マテリアライズド CTE は実験的な機能です。使用するには、analyzer と設定 enable_materialized_cte を有効にする必要があります。

構文

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

使用するタイミング

マテリアライズドCTEが特に有効なのは、次のような場合です。

同じCTEが1つのクエリ内で複数回参照される場合。 MATERIALIZED がないと、参照のたびにサブクエリが個別に再実行されます。
CTEに generateRandom のような非決定的な関数が含まれている場合。マテリアライズすることで、すべての参照で同じデータが使われるようになります。
CTEに、繰り返し実行を避けたい高コストな計算 (集計、結合、大規模スキャン) が含まれている場合。

ヒント

マテリアライズドCTEが1回しか参照されない場合、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: 非決定的関数でも決定的な結果を得る

generateRandom を使用する通常の CTE は、参照するたびに異なる結果を生成します。 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 を有効にする必要があります。
analyzer が必要: マテリアライズド CTE は、analyzer が有効な場合にのみ動作します (enable_analyzer = 1)。
RECURSIVE ではサポートされません: MATERIALIZED キーワードと RECURSIVE キーワードを組み合わせることはできず、UNSUPPORTED_METHOD 例外が発生します。
相関 CTE は禁止されています: マテリアライズド CTE は、外側のクエリスコープのカラムを参照できません。

共通スカラ式

ClickHouse では、WITH 句で任意のスカラ式に対するエイリアスを宣言できます。共通スカラ式はクエリ内の任意の場所で参照できます。

注記

共通スカラ式が定数リテラル以外を参照する場合、その式は自由変数の発生を招く可能性があります。 ClickHouse は識別子を可能な限り最も近いスコープで解決するため、名前の衝突がある場合に自由変数が想定外のエンティティを参照したり、相関サブクエリを引き起こしたりする可能性があります。式中の識別子の解決挙動をより予測可能にするために、使用するすべての識別子をバインドするラムダ関数（analyzer を有効にしている場合のみ利用可能）として 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: 自由変数を伴う高階関数の使用

次のクエリ例は、束縛されていない識別子が最も近いスコープ内のエンティティに解決されることを示しています。ここでは、gen_name ラムダ関数本体内で extension は束縛されていません。 generated_names が定義および使用されるスコープでは、extension は共通スカラ式として '.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  │
   └───────────┘

例 4: sum(bytes) 式の結果を SELECT 句の列リストから削除する

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 test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;

再帰クエリ

オプションの 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 で導入されたクエリアナライザーに依存しています。バージョン 24.3+ を使用していて (UNKNOWN_TABLE) や (UNSUPPORTED_METHOD) という例外が発生する場合は、そのインスタンス、ロール、またはプロファイルでアナライザーが無効になっていることを示しています。アナライザーを有効にするには、設定 allow_experimental_analyzer を有効化するか、compatibility 設定をより新しいバージョンに更新してください。バージョン 24.8 以降では、アナライザーは本番環境での利用向けに正式採用されており、設定 allow_experimental_analyzer は enable_analyzer に名称変更されています。

再帰的な WITH クエリの一般的な形式は、常に非再帰項、続いて UNION ALL、その後に再帰項という順序になっており、クエリ自身の出力への参照を含めることができるのは再帰項のみです。再帰 CTE クエリは次のように実行されます。

非再帰項を評価します。非再帰項クエリの結果を一時作業テーブルに格納します。
作業テーブルが空でない限り、以下の手順を繰り返します。
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 │
└─────────────┘

共通テーブル式​

構文​

例​

マテリアライズド共通テーブル式​

構文​

使用するタイミング​

例​

制約事項​

共通スカラ式​

構文​

例​

再帰クエリ​

探索順序​

サイクル検出​

無限クエリ​