CTE(共通テーブル式)の再帰問い合わせ
BOM(部品表)について調べてたら、CTE(共通テーブル式)の再帰問い合わせというSQLが出てきたので勉強がてらまとめてみます。
[目次]
1. CTE再帰問い合わせ
CTE(共通テーブル式)は副問い合わせの代わりに使ったりすると、SQLの可読性が上がるためよく使ってます。 CTE再帰問い合わせは、データベースのテーブルの木構造の親子関係をもったレコードを再帰的に取得できるそうです。
下記のテーブルでCTE再帰問い合わせを試してみます。 PARTは親、SUBPARTは子、QUANTITY は子の数量です。
PARTLISTテーブル
PART | SUBPART | QUANTITY | |
---|---|---|---|
1 | ProductA | Parts1 | 1 |
2 | ProductA | Parts2 | 1 |
3 | ProductA | Parts3 | 3 |
4 | ProductA | Parts4 | 2 |
5 | Parts1 | Parts5 | 1 |
6 | Parts1 | Parts6 | 2 |
7 | Parts2 | Parts7 | 1 |
8 | Parts2 | Parts8 | 2 |
9 | Parts4 | Parts9 | 10 |
10 | Parts8 | Parts6 | 4 |
親子関係を図で書くと次のような感じ。
1.1. 実際にSQLを書いてみる
PARTLISTテーブルに対して、CTE再帰問い合わせするSQLを書いてみます。このとき、子は「総数量=親.QUANTITY * 子.QUANTITY」の計算式で総数量を求めます。
WITH RoopPartsList (PART, SUBPART, QUANTITY) AS ( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = 'ProductA' UNION ALL SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY * PARENT.QUANTITY FROM RoopPartsList PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART ) SELECT PART, SUBPART, QUANTITY FROM RoopPartsList ;
初めにWITH句内の一つ目のSELECT文で親(図だと一番上のProductA)を取得してRoopPartsList に格納します。次に、2つ目のSELECT文でRoopPartsList と PARTLIST を結合し、子の情報を取得してRoopPartsList に格納します。以降、2つ目のSELECT文を繰り返し、子が見つからなくなるまで実行します。
WITH句の外のSELECT文で、最終的なRoopPartsList の結果を取得しています。
SQLの実行結果は下記です。
PART | SUBPART | QUANTITY | |
---|---|---|---|
1 | ProductA | Parts1 | 1 |
2 | ProductA | Parts2 | 1 |
3 | ProductA | Parts3 | 3 |
4 | ProductA | Parts4 | 2 |
5 | Parts4 | Parts9 | 20 |
6 | Parts2 | Parts7 | 1 |
7 | Parts2 | Parts8 | 2 |
8 | Parts8 | Parts6 | 8 |
9 | Parts1 | Parts5 | 1 |
10 | Parts1 | Parts6 | 2 |
親⇒子と辿って、総数量も求められていることがわかります。
1.2. 取得する階層数を指定する
取得したい階層の深さを指定するSQLは下記のものです。LEVELという項目を追加し、トップレベルの親は「1」を指定し、子は「親のLEVEL + 1」とすることで、階層を取得します。下記のSQLでは2階層目までの結果を取得しています。
WITH RoopPartsList (LEVEL, PART, SUBPART, QUANTITY) AS ( SELECT 1, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = 'ProductA' UNION ALL SELECT PARENT.LEVEL + 1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY * PARENT.QUANTITY FROM RoopPartsList PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART AND PARENT.LEVEL < 2 ) SELECT LEVEL, PART, SUBPART, QUANTITY FROM RoopPartsList ;
SQLの実行結果は下記です。
LEVEL | PART | SUBPART | QUANTITY | |
---|---|---|---|---|
1 | 1 | ProductA | Parts1 | 1 |
2 | 1 | ProductA | Parts2 | 1 |
3 | 1 | ProductA | Parts3 | 3 |
4 | 1 | ProductA | Parts4 | 2 |
5 | 2 | Parts4 | Parts9 | 20 |
6 | 2 | Parts2 | Parts7 | 1 |
7 | 2 | Parts2 | Parts8 | 2 |
8 | 2 | Parts1 | Parts5 | 1 |
9 | 2 | Parts1 | Parts6 | 2 |
3階層目のParts6(親はParts8)が取得されていないことがわかります。
2. まとめ
CTE(共通テーブル式)の再帰問い合わせを実際にSQLを作って試してみました。
参考
- IBM Knowledge Center「再帰の例: 部品表」: https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_10.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059242.html