PostgresSQLでの動的な計算

親,　　親在庫,　　子,　　子在庫,　　員数
-----------------------------------------
製品A,　1,　　　部品A,　　　2,　　　　1
製品A,　1,　　　部品B,　　　1,　　　　1
部品A,　2,　　　部品a,　　　100,　　　10
部品A,　2,　　　部品b,　　　50,　　　 20

上記のような製品Aの構成部品と在庫を
管理しているテーブルから、各部品の所要量
を計算し、抽出するSQLで悩んでおります。

上記の場合、例えば製品Aを10台製作するための
各部品の所要量として、

親,　　親在庫,　　親所要量, 子,　　子在庫,　　員数,　　子所要量
------------------------------------------------------------------
製品A,　1,　　　　　10,　　　　部品A,　　2,　　　　1,　　　　9
製品A,　1,　　　　　10,　　　　部品B,　　1,　　　　1,　　　　9
部品A,　2,　　　　　9,　　　　部品a　　　100　　　　10,　　　70
部品A,　2,　　　　　9,　　　　部品b　　　50　　　　20,　　　140

という結果が抽出できるのが理想です。
子の所要量を計算するために、(親所要量 - 親在庫)を
動的に算出し、それを子の員数と乗算させる。
という処理を繰り返すイメージです。

なお、ここでいう員数とは、「親を1台製作するために必要な数」
という定義です。

相関サブクエリ、自己結合をつかって試しているのですが、
レコード毎に動的に上記の処理をさせることができずに
困っております。

ご教示の程、よろしくお願い致します。

No.1 回答者： gacky-79 回答日時： 2011/03/26 13:27

部品に複数段の階層があるようだと、PostgreSQL 8.4 以降で使える再帰クエリが必要になります。

CREATE TABLE 部品管理表
　(親 text, 親在庫 int, 子 text, 子在庫 int, 員数 int);
INSERT INTO 部品管理表 VALUES
　('製品A', 1, '部品A', 2, 1),
　('製品A', 1, '部品B', 1, 1),
　('部品A', 2, '部品a', 100, 10),
　('部品A', 2, '部品b', 50, 20);

WITH RECURSIVE r AS (
　SELECT
　　親,
　　親在庫,
　　10 AS 親所要量,
　　子,
　　子在庫,
　　員数,
　　(10 - 親在庫) * 員数 AS 子所要量
　FROM
　　部品管理表
　WHERE 親 = '製品A'
UNION ALL
　SELECT
　　t.親,
　　t.親在庫,
　　r.子所要量 AS 親所要量,
　　t.子,
　　t.子在庫,
　　t.員数,
　　(r.子所要量 - t.親在庫) * t.員数 AS 子所要量
　FROM
　　部品管理表 t, r
　WHERE t.親 = r.子
)
SELECT * FROM r;

あと、所要量の計算で引き算をしている個所には、マイナスになる場合 (在庫が十分な場合) の追加の計算が必要かもしれません。

参考URL：http://www.postgresql.jp/document/current/html/q …

この回答への補足

ご回答ありがとうございます。

再帰クエリですが、既に一連のSQLの中で使用しており、
ご教授してくださった方法で2回目の再帰クエリを使用したところ、
エラーとなってしまいました。

以下、適所コメントで割愛しておりますが、現在このような
形で再帰クエリを使用し、部品構成情報を取得しております。

////////////////////////////////////////////////////////
WITH RECURSIVE AS Middle
(
------(1)ここで部品構成情報を取得（在庫情報は含まれない）
)

,

SELECT xxxx FROM Middle
------(2)ここで上記(1)の部品構成情報と在庫テーブルを結合し、
質問にあるテーブル抽出。

WITH RECURSIVE r AS (
------(3)ここで上記(2)から所要量を計算したい。
)
////////////////////////////////////////////////////////

分かりずらくて申し訳ないのですが、再帰クエリは既に先頭の(1)で
使用しており、(3)でご教授してくださった内容で再帰クエリを使用した
次第です。

WITH句の中で、RECURSIVEを複数回使用できるのか実績がないので、
わかりませんが、(3)のWITHをカンマに変えたり色々試しているのですが、
エラーとなってしまいます。。

補足日時：2011/03/26 19:20

No.2 ベストアンサー 回答者： gacky-79 回答日時： 2011/03/26 19:56

本来の質問は、

> WITH句の中で、RECURSIVEを複数回使用できるのか
なのでは？

WITH RECURSIVE は、連続実行も入れ子にもできるため、後は書き方の問題です。
再帰クエリを2段重ねたいのであれば、入れ子にするのはいかがでしょう？

WITH RECURSIVE r AS (
　WITH RECURSIVE Middle AS (...) SELECT ... FROM Middle JOIN 在庫テーブル
UNION ALL
　SELECT * FROM r
)
SELECT ... FROM r;

この回答へのお礼

返事が遅くなり申し訳ありません。

>本来の質問は、
>> WITH句の中で、RECURSIVEを複数回使用できるのか
>なのでは？

質問内容をシンプルにしたつもりが、
重要なことを記載せず混乱を招いてしまい、
申し訳ありませんでした。

ご提示して下さった方法でなんとか実現することができました。
本当にありがとうございます。

ちなみになのですが、再帰クエリ以外で実現する方法は
あるのでしょうか？
SQLで実現出来なかった時の最終手段として、
クライアント側での再帰処理でやるしかないと
思っておりましたので。。

お礼日時：2011/03/28 20:44

No.3 回答者： chukenkenkou 回答日時： 2011/03/26 21:28

PostgreSQLのバージョンは、何ですか？

単に「エラーになる」といった提示でなく、具体的にどのようなSQLを書き、どのようなエラーになるのか、メッセージやコード類を提示するようにしてください。

情報を出さず、親切にSQLまで考えて回答してくださった方に、「既に再帰クエリを使ってまして・・・」なんて後出しするというのは、親身になってくださる方に無駄な労力を使わせていることになってしまいます。

この回答へのお礼

返事が遅くなり、申し訳ありません。

>PostgreSQLのバージョンは、何ですか？
バージョンは、8.4.6になります。

詳細な情報を提示せず、申し訳ありませんでした。
エラーの原因は、2度目の再帰クエリに
RECURSIVEステートメントをつけていたことが原因でした。

////////////////////////////////////////
WITH RECURSIVE Middle AS
(...
UNION ALL
...)

, SELECT ... FROM Middle ...

, RECURSIVE r AS
(
UNION ALL
)
////////////////////////////////////////

上記の、RECURSIVE r AS の部分で、
「syntax error at near "r"」
というエラーがでており、RECURSIVEステートメントを
取っ払ったら正常に通りました。

たいへん勉強になりました。
また、ご指摘ありがとうございました。

お礼日時：2011/03/28 20:55