17.05 - WITH修飾子の非再帰的な名前付きクエリー - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ操作言語

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
2021年1月
Content Type
プログラミング リファレンス
Publication ID
B035-1146-175K-JPN
Language
日本語 (日本)

次のテーブル定義があるとします。

     CREATE TABLE product (
       product_id INTEGER,
       on_hand    INTEGER);

    CREATE TABLE stocked (
      store_id   INTEGER,
      product_id INTEGER,
      quantity   INTEGER);

以下の文では、非再帰的WITH文修飾子を使用することによって、ASキーワードの後の選択式から生成される、orderable_itemsという名前付き一時結果セットを定義しています。

     WITH orderable_items (product_id, quantity) AS
     ( SELECT stocked.product_id, stocked.quantity
       FROM stocked, product
       WHERE stocked.product_id = product.product_id
       AND   product.on_hand > 5
    )
     
SELECT product_id, quantity
     FROM orderable_items
     WHERE quantity < 10;

派生テーブルを使用した次の文も、これと同じ結果になります。

     SELECT product_id, quantity
     FROM (SELECT stocked.product_id, stocked.quantity
           FROM stocked, product
           WHERE stocked.product_id = product.product_id
           AND   product.on_hand > 5) AS orderable_items
     WHERE quantity < 10;

WITH修飾子の再帰的な名前付きクエリー

再帰的な名前付きクエリーは、組織構造、部品テーブル、文書階層などの、データの階層を反復的に問合わせる方法です。

再帰的問合わせは、別の再帰的問合わせを直接的にも間接的にも参照できません。

再帰的な名前付きクエリーには、次の3つの実行フェーズがあります。

  1. 初期非再帰的またはシード結果セットを作成します。
  2. 一時的な名前の付いた結果セットに追加される行がなくなるまで、シードの結果セットに基づいて中間結果セットを再帰します。
  3. 一時的な名前の付いた結果セットについて最後の問合わせを実行し、最終の結果セットを返します。

次のemployeeテーブルについて考えてみましょう。

     CREATE TABLE employee (
       employee_number         INTEGER,
       manager_employee_number INTEGER,
       last_name               CHARACTER(20),
       first_name              VARCHAR(30));

テーブルは、employeeとmanagerの関係の組織的な構造を表わします。employeeテーブルは、この組織図に似ています。



以下の再帰的問合わせは、employee_number値が801の社員を直接的または間接的に上司とする社員すべての社員番号を取り出すためのものです。

     WITH RECURSIVE temp_table (employee_number) AS
       (SELECT root.employee_number
        FROM employee AS root
        WHERE root.manager_employee_number = 801
     UNION ALL
        SELECT indirect.employee_number
        FROM temp_table AS direct, employee AS indirect
        WHERE direct.employee_number = indirect.manager_employee_number
       )
     SELECT *
     FROM temp_table
     ORDER BY employee_number;

この例のtemp_tableは、再帰文のFROM句の中で参照可能な名前付き一時結果セットです。

非再帰文またはシード文によってtemp_tableの中に初期結果セットが生成され、employee_numberが801の社員を直接的に上司とする社員がそこに入れられます。

     SELECT root.employee_number
     FROM employee AS root
     WHERE root.manager_employee_number = 801

temp_tableに含まれる各社員と、temp_tableに含まれる社員を上司とする社員を結合することにより、再帰処理が実行されます。UNION ALLによりその結果がtemp_tableに追加されます。

     SELECT indirect.employee_number
     FROM temp_table AS direct, employee AS indirect
     WHERE direct.employee_number = indirect.manager_employee_number

新しい行がtemp_tableに追加されなくなった時点で再帰処理が停止します。

最終クエリーは再帰的WITHリクエスト修飾子の一部ではありませんが、そこでtemp_tableから社員情報を抽出しています。

     SELECT *
     FROM temp_table
     ORDER BY employee_number;

この再帰問合わせの結果は次のとおりです。

  employee_number
  ---------------
             1001
             1002
             1003
             1004
             1006
             1008
             1010
             1011
             1012
             1014
             1015
             1016
             1019

WITH修飾子

ルールと制限を以下に示します。
  • WITH修飾子内の再帰的な名前付きクエリーに表示できる唯一の集合演算子はUNION ALLです。
  • WITHまたはWITH RECURSIVE修飾子内で以下の要素を使用することはできません。
    • WITHまたはWITH RECURSIVE修飾子
    • TOP n演算子
    • ユーザー定義関数
  • WITH RECURSIVE文修飾子に含まれる再帰文の内部で以下の要素を使用することはできません。
    • 論理述部NOT INまたはNOT EXISTS
    • 集約関数
    • 順序付き分析関数
    • GROUP BY句
    • HAVING句
    • DISTINCT句
    • SUBPUERY
    • 派生テーブル
  • これらのデータベース オブジェクトのいずれかの定義においてWITH修飾子を指定することはできません。
    • トリガー
    • ストアド プロシージャ
  • 再帰文を持たない再帰的な名前付きクエリーは、非再帰的な名前付きクエリーと同じように動作します。

    以下のリクエストで生成される結果は、非再帰的な名前付きクエリーをWITH修飾子に指定するリクエストの場合と同じです。

         WITH RECURSIVE orderable_items (product_id, quantity) AS (
         SELECT stocked.product_id, stocked.quantity
         FROM stocked, product
         WHERE stocked.product_id = product.product_id
         AND   product.on_hand > 5)
         SELECT product_id, quantity
         FROM orderable_items
         WHERE quantity < 10;