| ページ一覧 | ブログ | twitter |　 書式 | 書式(表) |

MyMemoWiki

DB2 再帰

提供: MyMemoWiki
2020年2月15日 (土) 08:34時点におけるPiroto (トーク | 投稿記録)による版
ナビゲーションに移動 検索に移動

DB2 再帰

DB2 | テンプレート:Category SQL

前提

表

  1. CREATE TABLE PARTLIST
  2.              (PART VARCHAR(8),
  3.               SUBPART VARCHAR(8),
  4.               QUANTITY INTEGER);

データ

  1. PART     SUBPART  QUANTITY
  2. -------- -------- -----------
  3. 00       01                 5
  4. 00       05                 3
  5. 01       02                 2
  6. 01       03                 3
  7. 01       04                 4
  8. 01       06                 3
  9. 02       05                 7
  10. 02       06                 6
  11. 03       07                 6
  12. 04       08                10
  13. 04       09                11
  14. 05       10                10
  15. 05       11                10
  16. 06       12                10
  17. 06       13                10
  18. 07       14                 8
  19. 07       12                 8

単一レベルの展開

  • 「'01' で示されている部品を作成するにはどの部品が必要か」という質問に答える
  • 直接の副部品、副部品の副部品などが入る

<blockquote>ある部品が何回も使用される場合でも、その副部品は 1 回しかリストに示されません</blockquote> 

  1. WITH RPL (PART, SUBPART, QUANTITY) AS
  2.      (  SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
  3.         FROM PARTLIST ROOT
  4.         WHERE ROOT.PART = '01'
  5.       UNION ALL
  6.         SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
  7.         FROM RPL PARENT, PARTLIST CHILD
  8.         WHERE  PARENT.SUBPART = CHILD.PART
  9.      )
  10. SELECT DISTINCT PART, SUBPART, QUANTITY
  11.  FROM RPL
  12.   ORDER BY PART, SUBPART, QUANTITY;
共通表式
  • RPL という名前で指定されている
  • 照会の再帰的な部分が表されている
初期化全選択
  • UNION の第 1 オペランド (全選択)
  • 部品 '01' の直接の子が求まります
  • この最初の全選択の結果が、 共通表式 RPL (再帰的 PARTLIST) の中に入れられる
  • この例の場合、UNION は常に UNION ALL でなければならない
副部品の副部品を計算
  • FROM 節で共通表式 RPL とソース表 (CHILD: 子) の部品を、 RPL (PARENT: 親) に入っている現行の結果の副部品に結び付ける
  • 結果は、再度 RPL に入れられる
  • UNION の第 2 オペランドは、子が存在しなくなるまで繰り返し使用される
SELECT DISTINCT
  • 同じ部品/副部品が 2 回以上リストに現れることがないように
照会結果
  1. PART     SUBPART  QUANTITY
  2. -------- -------- -----------
  3. 01       02                 2
  4. 01       03                 3
  5. 01       04                 4
  6. 01       06                 3
  7. 02       05                 7
  8. 02       06                 6
  9. 03       07                 6
  10. 04       08                10
  11. 04       09                11
  12. 05       10                10
  13. 05       11                10
  14. 06       12                10
  15. 06       13                10
  16. 07       12                 8
  17. 07       14                 8
  • 部品 '01' が '02' に、そしてさらに '06' へと進むようになっています
  • 部品 '06' へは、'01' から直接に 1 回、 '02' から 1 回の計 2 回達することに注意
  • サブコンポーネントが 1 回しかリストに現れないようになっています (これは SELECT DISTINCT を使用した結果です)

<blockquote>再帰的共通表式では、 無限ループ になる可能性を必ず考慮に入れてください</blockquote>

要約正展開

  • 部品 '01' の作成には各部品が合計どれくらい必要か
  • 単一レベル正展開と異なる主な点は、数量を集計する必要があるということ
  1. WITH RPL (PART, SUBPART, QUANTITY) AS
  2.    (
  3.       SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
  4.        FROM PARTLIST ROOT
  5.        WHERE ROOT.PART = '01'
  6.     UNION ALL
  7.       SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
  8.        FROM RPL PARENT, PARTLIST CHILD
  9.        WHERE PARENT.SUBPART = CHILD.PART
  10.    )
  11. SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
  12.  FROM RPL
  13.   GROUP BY PART, SUBPART
  14.   ORDER BY PART, SUBPART;
数量の合計
  • RPL という名前で指定されている再帰的共通表式の中の UNION の第 2 オペランドの選択リストによって、数量の合計が示されている
  • 副部品の使用量を求めるには、親の数量に、親 1 個当たりの子の数量を乗算
最終的な集計
  • 1 つの部品が異なる複数のロケーションで何回も使用される場合、共通表式 RPL をグループ化し、 主要全選択の選択リストの中で SUM 集約関数を使用することで、最終的な集計を行う
照会結果
  1. PART     SUBPART  Total Qty Used
  2. -------- -------- --------------
  3. 01       02                    2
  4. 01       03                    3
  5. 01       04                    4
  6. 01       05                   14
  7. 01       06                   15
  8. 01       07                   18
  9. 01       08                   40
  10. 01       09                   44
  11. 01       10                  140
  12. 01       11                  140
  13. 01       12                  294
  14. 01       13                  150
  15. 01       14                  144

<blockquote>副部品が '06' の行に注目してください。 合計使用量の値 15 は、部品 '01' のための直接の数 3 と、 部品 '02' のための数 (6) に部品 '01' の数 (2) を掛けたものとを加えた数</blockquote>

深さの制御

  • レベル番号を結果に組み入れる
  1. WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS
  2.       (
  3.          SELECT 1,               ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
  4.           FROM PARTLIST ROOT
  5.           WHERE ROOT.PART = '01'
  6.        UNION ALL
  7.          SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
  8.           FROM RPL PARENT, PARTLIST CHILD
  9.           WHERE PARENT.SUBPART = CHILD.PART
  10.             AND PARENT.LEVEL < 2
  11.       )
  12.  SELECT PART, LEVEL, SUBPART, QUANTITY
  13.    FROM RPL;
LEVELの初期化
  • 初期化全選択では、LEVEL 列の値を 1 に初期化
  • それ以降の全選択では、親のレベルに 1 ずつ加算
  • 結果のレベル数を制御するため、2 番目の全選択に、 親のレベルが 2 未満でなければならないという条件
照会結果
  1. PART     LEVEL       SUBPART  QUANTITY
  2. -------- ----------- -------- -----------
  3. 01                 1 02                 2
  4. 01                 1 03                 3
  5. 01                 1 04                 4
  6. 01                 1 06                 3
  7. 02                 2 05                 7
  8. 02                 2 06                 6
  9. 03                 2 07                 6
  10. 04                 2 08                10
  11. 04                 2 09                11
  12. 06                 2 12                10
  13. 06                 2 13                10
「https://www.typea.info/tips_/index.php?title=DB2_再帰&oldid=4802」から取得

© 2006 矢木浩人