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クエリ
