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

MyMemoWiki

「DB2 再帰」の版間の差分

提供: MyMemoWiki
ナビゲーションに移動 検索に移動
 
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 再帰の例: 部品表からのメモ]
 
*[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
 
  -------- -------- -----------
 
  -------- -------- -----------
 
  00      01                5
 
  00      01                5
35行目: 35行目:
 
*直接の副部品、副部品の副部品などが入る
 
*直接の副部品、副部品の副部品などが入る
 
<blockquote>ある部品が何回も使用される場合でも、その副部品は 1 回しかリストに示されません</blockquote>
 
<blockquote>ある部品が何回も使用される場合でも、その副部品は 1 回しかリストに示されません</blockquote>
−
  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
 
       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 オペランド (全選択)  
 
*UNION の第 1 オペランド (全選択)  
 
*部品 '01' の直接の子が求まります
 
*部品 '01' の直接の子が求まります
−
*この最初の全選択の結果が、 共通表式 RPL (再帰的 PARTLIST) の中に入れられる
+
*この最初の全選択の結果が、 共通表式 [[R]]PL (再帰的 PA[[R]]TLIST) の中に入れられる
 
*この例の場合、UNION は常に UNION ALL でなければならない
 
*この例の場合、UNION は常に UNION ALL でなければならない
 
=====副部品の副部品を計算=====
 
=====副部品の副部品を計算=====
−
*FROM 節で共通表式 RPL とソース表 (CHILD: 子) の部品を、 RPL (PARENT: 親) に入っている現行の結果の副部品に結び付ける
+
*F[[R]]OM 節で共通表式 [[R]]PL とソース表 (CHILD: 子) の部品を、 [[R]]PL (PA[[R]]ENT: 親) に入っている現行の結果の副部品に結び付ける
−
*結果は、再度 RPL に入れられる
+
*結果は、再度 [[R]]PL に入れられる
 
*UNION の第 2 オペランドは、子が存在しなくなるまで繰り返し使用される
 
*UNION の第 2 オペランドは、子が存在しなくなるまで繰り返し使用される
 
=====SELECT DISTINCT=====
 
=====SELECT DISTINCT=====
63行目: 63行目:
  
 
=====照会結果=====
 
=====照会結果=====
−
  PART    SUBPART  QUANTITY
+
  PART    SUBPART  QU[[ANT]]ITY
 
  -------- -------- -----------
 
  -------- -------- -----------
 
  01      02                2
 
  01      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
 
     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 個当たりの子の数量を乗算
 
=====最終的な集計=====
 
=====最終的な集計=====
−
*1 つの部品が異なる複数のロケーションで何回も使用される場合、共通表式 RPL をグループ化し、 主要全選択の選択リストの中で SUM 集約関数を使用することで、最終的な集計を行う
+
*1 つの部品が異なる複数のロケーションで何回も使用される場合、共通表式 [[R]]PL をグループ化し、 主要全選択の選択リストの中で SUM 集約関数を使用することで、最終的な集計を行う
 
=====照会結果=====
 
=====照会結果=====
−
  PART     SUBPART Total Qty Used
+
  PA[[R]]T     SUBPA[[R]]T Total Qty Used
 
  -------- -------- --------------
 
  -------- -------- --------------
 
  01      02                    2
 
  01      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
 
         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 < 2
+
             AND PA[[R]]ENT.LEVEL < 2
 
       )
 
       )
−
   SELECT PART, LEVEL, SUBPART, QUANTITY
+
   SELECT PART, LEVEL, SUBPART, QU[[ANT]]ITY
−
     FROM RPL;
+
     F[[R]]OM [[R]]PL;
 
=====LEVELの初期化=====
 
=====LEVELの初期化=====
 
*初期化全選択では、LEVEL 列の値を 1 に初期化
 
*初期化全選択では、LEVEL 列の値を 1 に初期化
147行目: 147行目:
 
*結果のレベル数を制御するため、2 番目の全選択に、 親のレベルが 2 未満でなければならないという条件
 
*結果のレベル数を制御するため、2 番目の全選択に、 親のレベルが 2 未満でなければならないという条件
 
=====照会結果=====
 
=====照会結果=====
−
  PART    LEVEL      SUBPART  QUANTITY
+
  PART    LEVEL      SUBPART  QU[[ANT]]ITY
 
  -------- ----------- -------- -----------
 
  -------- ----------- -------- -----------
 
  01                1 02                2
 
  01                1 02                2

2020年2月16日 (日) 04:24時点における最新版

DB2 再帰

DB2 |

前提

表

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
「https://www.typea.info/tips_/index.php?title=DB2_再帰&oldid=5888」から取得

© 2006 矢木浩人