!!!DB2 再帰 [DB2]{{category SQL}} *[再帰の例: 部品表からのメモ|http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059242.html?cp=SSEPGG_10.1.0%2F2-9-6-3-0-0&lang=ja] !!前提 !表 CREATE TABLE PARTLIST (PART VARCHAR(8), SUBPART VARCHAR(8), QUANTITY INTEGER); !データ PART SUBPART QUANTITY -------- -------- ----------- 00 01 5 00 05 3 01 02 2 01 03 3 01 04 4 01 06 3 02 05 7 02 06 6 03 07 6 04 08 10 04 09 11 05 10 10 05 11 10 06 12 10 06 13 10 07 14 8 07 12 8 !!単一レベルの展開 *「'01' で示されている部品を作成するにはどの部品が必要か」という質問に答える *直接の副部品、副部品の副部品などが入る ""ある部品が何回も使用される場合でも、その副部品は 1 回しかリストに示されません WITH RPL (PART, SUBPART, QUANTITY) AS ( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = '01' UNION ALL SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY FROM RPL PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART ) SELECT DISTINCT PART, SUBPART, QUANTITY FROM RPL 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 回以上リストに現れることがないように ::照会結果 PART SUBPART QUANTITY -------- -------- ----------- 01 02 2 01 03 3 01 04 4 01 06 3 02 05 7 02 06 6 03 07 6 04 08 10 04 09 11 05 10 10 05 11 10 06 12 10 06 13 10 07 12 8 07 14 8 *部品 '01' が '02' に、そしてさらに '06' へと進むようになっています *部品 '06' へは、'01' から直接に 1 回、 '02' から 1 回の計 2 回達することに注意 *サブコンポーネントが 1 回しかリストに現れないようになっています (これは SELECT DISTINCT を使用した結果です) ""再帰的共通表式では、 無限ループ になる可能性を必ず考慮に入れてください !!要約正展開 *部品 '01' の作成には各部品が合計どれくらい必要か *単一レベル正展開と異なる主な点は、数量を集計する必要があるということ WITH RPL (PART, SUBPART, QUANTITY) AS ( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = '01' UNION ALL SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY FROM RPL PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART ) SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used" FROM RPL GROUP BY PART, SUBPART ORDER BY PART, SUBPART; ::数量の合計 *RPL という名前で指定されている再帰的共通表式の中の UNION の第 2 オペランドの選択リストによって、数量の合計が示されている *副部品の使用量を求めるには、親の数量に、親 1 個当たりの子の数量を乗算 ::最終的な集計 *1 つの部品が異なる複数のロケーションで何回も使用される場合、共通表式 RPL をグループ化し、 主要全選択の選択リストの中で SUM 集約関数を使用することで、最終的な集計を行う ::照会結果 PART SUBPART Total Qty Used -------- -------- -------------- 01 02 2 01 03 3 01 04 4 01 05 14 01 06 15 01 07 18 01 08 40 01 09 44 01 10 140 01 11 140 01 12 294 01 13 150 01 14 144 ""副部品が '06' の行に注目してください。 合計使用量の値 15 は、部品 '01' のための直接の数 3 と、 部品 '02' のための数 (6) に部品 '01' の数 (2) を掛けたものとを加えた数 !!深さの制御 *レベル番号を結果に組み入れる WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS ( SELECT 1, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = '01' UNION ALL SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY FROM RPL PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART AND PARENT.LEVEL < 2 ) SELECT PART, LEVEL, SUBPART, QUANTITY FROM RPL; ::LEVELの初期化 *初期化全選択では、LEVEL 列の値を 1 に初期化 *それ以降の全選択では、親のレベルに 1 ずつ加算 *結果のレベル数を制御するため、2 番目の全選択に、 親のレベルが 2 未満でなければならないという条件 ::照会結果 PART LEVEL SUBPART QUANTITY -------- ----------- -------- ----------- 01 1 02 2 01 1 03 3 01 1 04 4 01 1 06 3 02 2 05 7 02 2 06 6 03 2 07 6 04 2 08 10 04 2 09 11 06 2 12 10 06 2 13 10