WITH句
ClickHouseは共通テーブル式（CTE）をサポートしており、WITH句で定義されたコードは、SELECTクエリの残りのすべての使用箇所で置き換えられます。名前付きのサブクエリは、テーブルオブジェクトが許可されている場所で現在および子クエリのコンテキストに含めることができます。再帰は、現在のレベルのCTEをWITH式から隠すことで防がれます。
CTEが呼ばれるすべての場所で同じ結果を保証するわけではないことに注意してください。なぜなら、クエリは使用ケースごとに再実行されるからです。
そのような動作の例は以下の通りです。
CTEが結果そのものを渡すのではなく、コードの一部だけを渡すので、常に 1000000 が表示されるわけではありません。
ただし、cte_numbers を2回参照しているため、毎回ランダムな数が生成され、それに応じて異なるランダムな結果、280501, 392454, 261636, 196227 などが表示されることになります。
構文
または
例
例 1: 定数式を「変数」として使用
例 2: SELECT句のカラムリストから sum(bytes) 結果を削除
例 3: スカラサブクエリの結果を使用
例 4: サブクエリでの式の再利用
再帰クエリ
オプションの RECURSIVE 修飾子を使用すると、WITHクエリが自身の出力を参照できるようになります。例:
例: 1から100までの整数を合計
再帰CTEは、バージョン 24.3 で導入された 新しいクエリアナライザー に依存しています。バージョン 24.3+ を使用していて、(UNKNOWN_TABLE) または (UNSUPPORTED_METHOD) 例外に遭遇した場合、これは新しいアナライザーがインスタンス、ロール、またはプロファイルで無効になっていることを示しています。アナライザーを有効にするには、設定 allow_experimental_analyzer を有効にするか、compatibility 設定をより新しいバージョンに更新してください。
バージョン 24.8 からは新しいアナライザーが生産環境で完全に推奨され、設定 allow_experimental_analyzer は enable_analyzer に名前が変更されました。
再帰的な WITH クエリの一般的な形式は、常に非再帰的な項、次に UNION ALL、その後に再帰的な項を含みます。再帰的な項にのみ、クエリ自身の出力への参照を含めることができます。再帰CTEクエリは次のように実行されます：
- 非再帰的な項を評価します。非再帰的な項のクエリの結果を一時的な作業テーブルに置きます。
- 作業テーブルが空でない限り、これらのステップを繰り返します：
- 再帰的な項を評価し、作業テーブルの現在の内容を再帰的な自己参照に置き換えます。再帰的な項のクエリの結果を一時的な中間テーブルに置きます。
- 作業テーブルの内容を中間テーブルの内容で置き換え、中間テーブルを空にします。
再帰クエリは通常、階層データまたはツリー構造データを操作するために使用されます。たとえば、ツリーのトラバーサルを実行するクエリを書くことができます：
例: ツリーのトラバーサル
まず、ツリーテーブルを作成します：
そのツリーを以下のクエリでトラバースできます：
例: ツリーのトラバーサル
探索順序
深さ優先の順序を作成するために、訪れた行の配列を各結果行に対して計算します：
例: ツリーのトラバーサル深さ優先順序
幅優先の順序を作成するための標準的な方法は、探索の深さを追跡するカラムを追加することです：
例: ツリーのトラバーサル幅優先順序
サイクル検出
まず、グラフテーブルを作成しましょう：
次のクエリでグラフをトラバースできます：
例: サイクル検出なしでのグラフトラバース
しかし、そのグラフにサイクルを追加すると、前のクエリは Maximum recursive CTE evaluation depth エラーで失敗します：
サイクルを処理するための標準方法は、すでに訪れたノードの配列を計算することです：
例: サイクル検出を伴うグラフトラバース
無限クエリ
また、外側のクエリで LIMIT を使用する場合、無限再帰CTEクエリを使用することも可能です：
例: 無限再帰CTEクエリ