DB2 再帰 - 版の履歴

2020年2月16日 (日) 04:24にPirotoによる

2020-02-16T04:24:00Z

2020年2月15日 (土) 08:34にPirotoによる

2020-02-15T08:34:46Z

2020年2月15日 (土) 08:01にPirotoによる

2020-02-15T08:01:39Z

Piroto: ページの作成:「==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-…」

2020-02-15T07:31:22Z

ページの作成:「==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-…」

新規ページ

==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' で示されている部品を作成するにはどの部品が必要か」という質問に答える
*直接の副部品、副部品の副部品などが入る
<blockquote>ある部品が何回も使用される場合でも、その副部品は 1 回しかリストに示されません</blockquote>
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 を使用した結果です)

<blockquote>再帰的共通表式では、無限ループになる可能性を必ず考慮に入れてください</blockquote>
===要約正展開===
*部品 '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
<blockquote>副部品が '06' の行に注目してください。合計使用量の値 15 は、部品 '01' のための直接の数 3 と、部品 '02' のための数 (6) に部品 '01' の数 (2) を掛けたものとを加えた数</blockquote>

===深さの制御===
*レベル番号を結果に組み入れる
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

@@ 1行目: / 1行目: @@
 ==DB2 再帰==
-[[DB2]]{{category SQL}}
+[[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 再帰の例: 部品表からのメモ]

@@ 1行目: / 1行目: @@
-==DB2 再帰==
+==[[DB2 再帰]]==
-[[DB2]] | {{category SQL}}
+[[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 再帰の例: 部品表からのメモ]
@@ 6行目: / 6行目: @@
 ===前提===
 ====表====
-  CREATE TABLE PARTLIST
+  C[[R]]EATE TABLE PA[[R]]TLIST
-               (PART VARCHAR(8),
+               (PA[[R]]T VA[[R]]CHA[[R]](8),
-                SUBPART VARCHAR(8),
+                SUBPA[[R]]T VA[[R]]CHA[[R]](8),
-                QUANTITY INTEGER);
+                QU[[ANT]]ITY INTEGER);
 ====データ====
-  PART     SUBPART  QUANTITY
+  PART     SUBPART  QU[[ANT]]ITY
   -------- -------- -----------
        01                 5
@@ 35行目: / 35行目: @@
 *直接の副部品、副部品の副部品などが入る
 &lt;blockquote&gt;ある部品が何回も使用される場合でも、その副部品は 1 回しかリストに示されません&lt;/blockquote&gt;
-  WITH RPL (PART, SUBPART, QUANTITY) AS
+  WITH RPL (PART, SUBPART, QU[[ANT]]ITY) AS
-       (  SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
+       (  SELECT ROOT.PART, ROOT.SUBPART, ROOT.QU[[ANT]]ITY
-          FROM PARTLIST ROOT
+          F[[R]]OM PA[[R]]TLIST [[R]]OOT
-          WHERE ROOT.PART = '01'
+          WHE[[R]]E [[R]]OOT.PA[[R]]T = '01'
         UNION ALL
-          SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
+          SELECT CHILD.PART, CHILD.SUBPART, CHILD.QU[[ANT]]ITY
-          FROM RPL PARENT, PARTLIST CHILD
+          F[[R]]OM [[R]]PL PA[[R]]ENT, PA[[R]]TLIST CHILD
-          WHERE  PARENT.SUBPART = CHILD.PART
+          WHE[[R]]E  PA[[R]]ENT.SUBPA[[R]]T = CHILD.PA[[R]]T
+       )
-  SELECT DISTINCT PART, SUBPART, QUANTITY
+  SELECT DISTINCT PART, SUBPART, QU[[ANT]]ITY
-   FROM RPL
+   F[[R]]OM [[R]]PL
-    ORDER BY PART, SUBPART, QUANTITY;
+    ORDER BY PART, SUBPART, QU[[ANT]]ITY;
 =====共通表式=====
-*RPL という名前で指定されている
+*[[R]]PL という名前で指定されている
 *照会の再帰的な部分が表されている
 =====初期化全選択=====
 *UNION の第 1 オペランド (全選択)
 *部品 '01' の直接の子が求まります
-*この最初の全選択の結果が、 共通表式 RPL (再帰的 PARTLIST) の中に入れられる
+*この最初の全選択の結果が、 共通表式 [[R]]PL (再帰的 PA[[R]]TLIST) の中に入れられる
 *この例の場合、UNION は常に UNION ALL でなければならない
 =====副部品の副部品を計算=====
-*FROM 節で共通表式 RPL とソース表 (CHILD: 子) の部品を、 RPL (PARENT: 親) に入っている現行の結果の副部品に結び付ける
+*F[[R]]OM 節で共通表式 [[R]]PL とソース表 (CHILD: 子) の部品を、 [[R]]PL (PA[[R]]ENT: 親) に入っている現行の結果の副部品に結び付ける
-*結果は、再度 RPL に入れられる
+*結果は、再度 [[R]]PL に入れられる
 *UNION の第 2 オペランドは、子が存在しなくなるまで繰り返し使用される
 =====SELECT DISTINCT=====
@@ 63行目: / 63行目: @@
 =====照会結果=====
-  PART     SUBPART  QUANTITY
+  PART     SUBPART  QU[[ANT]]ITY
   -------- -------- -----------
        02                 2
@@ 89行目: / 89行目: @@
 *単一レベル正展開と異なる主な点は、数量を集計する必要があるということ
-  WITH RPL (PART, SUBPART, QUANTITY) AS
+  WITH RPL (PART, SUBPART, QU[[ANT]]ITY) AS
+     (
-        SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
+        SELECT ROOT.PART, ROOT.SUBPART, ROOT.QU[[ANT]]ITY
-         FROM PARTLIST ROOT
+         F[[R]]OM PA[[R]]TLIST [[R]]OOT
-         WHERE ROOT.PART = '01'
+         WHE[[R]]E [[R]]OOT.PA[[R]]T = '01'
       UNION ALL
-        SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
+        SELECT PARENT.PART, CHILD.SUBPART, PARENT.QU[[ANT]]ITY*CHILD.QU[[ANT]]ITY
-         FROM RPL PARENT, PARTLIST CHILD
+         F[[R]]OM [[R]]PL PA[[R]]ENT, PA[[R]]TLIST CHILD
-         WHERE PARENT.SUBPART = CHILD.PART
+         WHE[[R]]E PA[[R]]ENT.SUBPA[[R]]T = CHILD.PA[[R]]T
+     )
-  SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
+  SELECT PART, SUBPART, SUM(QU[[ANT]]ITY) AS "Total QTY Used"
-   FROM RPL
+   F[[R]]OM [[R]]PL
-    GROUP BY PART, SUBPART
+    G[[R]]OUP BY PA[[R]]T, SUBPA[[R]]T
-    ORDER BY PART, SUBPART;
+    O[[R]]DE[[R]] BY PA[[R]]T, SUBPA[[R]]T;
 =====数量の合計=====
-*RPL という名前で指定されている再帰的共通表式の中の UNION の第 2 オペランドの選択リストによって、数量の合計が示されている
+*[[R]]PL という名前で指定されている再帰的共通表式の中の UNION の第 2 オペランドの選択リストによって、数量の合計が示されている
 *副部品の使用量を求めるには、親の数量に、親 1 個当たりの子の数量を乗算
 =====最終的な集計=====
-*1 つの部品が異なる複数のロケーションで何回も使用される場合、共通表式 RPL をグループ化し、 主要全選択の選択リストの中で SUM 集約関数を使用することで、最終的な集計を行う
+*1 つの部品が異なる複数のロケーションで何回も使用される場合、共通表式 [[R]]PL をグループ化し、 主要全選択の選択リストの中で SUM 集約関数を使用することで、最終的な集計を行う
 =====照会結果=====
-  PART     SUBPART  Total Qty Used
+  PA[[R]]T     SUBPA[[R]]T  Total Qty Used
   -------- -------- --------------
        02                    2
@@ 129行目: / 129行目: @@
 ===深さの制御===
 *レベル番号を結果に組み入れる
-  WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS
+  WITH RPL (LEVEL, PART, SUBPART, QU[[ANT]]ITY) AS
+        (
-           SELECT 1,               ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
+           SELECT 1,               ROOT.PART, ROOT.SUBPART, ROOT.QU[[ANT]]ITY
-            FROM PARTLIST ROOT
+            F[[R]]OM PA[[R]]TLIST [[R]]OOT
-            WHERE ROOT.PART = '01'
+            WHE[[R]]E [[R]]OOT.PA[[R]]T = '01'
          UNION ALL
-           SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
+           SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QU[[ANT]]ITY
-            FROM RPL PARENT, PARTLIST CHILD
+            F[[R]]OM [[R]]PL PA[[R]]ENT, PA[[R]]TLIST CHILD
-            WHERE PARENT.SUBPART = CHILD.PART
+            WHE[[R]]E PA[[R]]ENT.SUBPA[[R]]T = CHILD.PA[[R]]T
-              AND PARENT.LEVEL &lt; 2
+              AND PA[[R]]ENT.LEVEL &lt; 2
+        )
-   SELECT PART, LEVEL, SUBPART, QUANTITY
+   SELECT PART, LEVEL, SUBPART, QU[[ANT]]ITY
-     FROM RPL;
+     F[[R]]OM [[R]]PL;
 =====LEVELの初期化=====
 *初期化全選択では、LEVEL 列の値を 1 に初期化
@@ 147行目: / 147行目: @@
 *結果のレベル数を制御するため、2 番目の全選択に、 親のレベルが 2 未満でなければならないという条件
 =====照会結果=====
-  PART     LEVEL       SUBPART  QUANTITY
+  PART     LEVEL       SUBPART  QU[[ANT]]ITY
   -------- ----------- -------- -----------
                  1 02                 2

@@ 1行目: / 1行目: @@
 ==DB2 再帰==
-[DB2]{{category SQL}}
+[[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 再帰の例: 部品表からのメモ]
@@ 34行目: / 34行目: @@
 *「'01' で示されている部品を作成するにはどの部品が必要か」という質問に答える
 *直接の副部品、副部品の副部品などが入る
-<blockquote>ある部品が何回も使用される場合でも、その副部品は 1 回しかリストに示されません</blockquote>
+&lt;blockquote&gt;ある部品が何回も使用される場合でも、その副部品は 1 回しかリストに示されません&lt;/blockquote&gt;
   WITH RPL (PART, SUBPART, QUANTITY) AS
        (  SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
@@ 84行目: / 84行目: @@
 *サブコンポーネントが 1 回しかリストに現れないようになっています (これは SELECT DISTINCT を使用した結果です)
-<blockquote>再帰的共通表式では、 無限ループ になる可能性を必ず考慮に入れてください</blockquote>
+&lt;blockquote&gt;再帰的共通表式では、 無限ループ になる可能性を必ず考慮に入れてください&lt;/blockquote&gt;
 ===要約正展開===
 *部品 '01' の作成には各部品が合計どれくらい必要か
@@ 125行目: / 125行目: @@
        13                  150
        14                  144
-<blockquote>副部品が '06' の行に注目してください。 合計使用量の値 15 は、部品 '01' のための直接の数 3 と、 部品 '02' のための数 (6) に部品 '01' の数 (2) を掛けたものとを加えた数</blockquote>
+&lt;blockquote&gt;副部品が '06' の行に注目してください。 合計使用量の値 15 は、部品 '01' のための直接の数 3 と、 部品 '02' のための数 (6) に部品 '01' の数 (2) を掛けたものとを加えた数&lt;/blockquote&gt;
 ===深さの制御===
@@ 138行目: / 138行目: @@
             FROM RPL PARENT, PARTLIST CHILD
             WHERE PARENT.SUBPART = CHILD.PART
-              AND PARENT.LEVEL < 2
+              AND PARENT.LEVEL &lt; 2
+        )
    SELECT PART, LEVEL, SUBPART, QUANTITY