親, 親在庫, 子, 子在庫, 員数
-----------------------------------------
製品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.2ベストアンサー
- 回答日時:
本来の質問は、
> 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で実現出来なかった時の最終手段として、
クライアント側での再帰処理でやるしかないと
思っておりましたので。。
No.3
- 回答日時:
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ステートメントを
取っ払ったら正常に通りました。
たいへん勉強になりました。
また、ご指摘ありがとうございました。
No.1
- 回答日時:
部品に複数段の階層があるようだと、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をカンマに変えたり色々試しているのですが、
エラーとなってしまいます。。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(ビジネススキル・経営ノウハウ) 在庫管理のこの問題が分かりません。どなたか解説お願いします 2 2022/04/18 18:35
- 数学 在庫管理のこの問題が分かりません。どなたか解説お願いします 4 2022/04/18 22:19
- 数学 在庫管理のこの問題が分かりません。どなたか解説お願いします 2 2022/04/18 22:21
- 経営情報システム 在庫管理のこの問題が分かりません。どなたか解説お願いします。 0 2022/04/18 16:02
- その他(データベース) c言語の問題です。これを踏まえてコーディングしたいのでおしえていただきたいです。 3 2023/08/03 09:27
- 数学 M種類の部品からN種類の部品を抽出する効率的なアルゴリズム 2 2022/04/22 16:51
- Excel(エクセル) Excelで在庫表(クエリ、ピボット) 2 2022/04/11 17:11
- 就職 光合成?交互性?早稲田卒のエリート新社員の発言について 1 2022/04/29 22:50
- その他(Microsoft Office) Excelの関数(FILTER関数)について教えてください 2 2023/07/31 16:11
- 統計学 確率統計:正規分布している実力のロットから部品を2つ抜き取って製品化する場合、製品の実力は良くなる? 5 2023/05/24 00:29
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
1週間後の日付を求めたい
-
IDとパスワードについて。
-
SELECT INTOで一度に複数の変数...
-
pandasでsqlite3にテーブル作成...
-
テーブル名が可変の場合のクエ...
-
SQLのテーブルにないデータの出力
-
結合したテーブルをSUMしたい
-
mysqlのindexとprimary keyにつ...
-
フラグをたてるってどういうこ...
-
2つのテーブルでの合計取得
-
オラクルのUPDATEで複数テーブル
-
参照数とはなんですか?
-
access vba での left join に...
-
既存データをINSERT文にして出...
-
truncate tableを使って複数の...
-
エクセルVBA 10分後にエクセル...
-
【エクセル/excel】if関数とフラグ
-
マクロでファイルを読み込み、...
-
集計でテストの各教科の最高得...
-
【VB】セルが空になるまで処理...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
group byで指定したカラム以外...
-
PostgresSQLでの動的な計算
-
1週間後の日付を求めたい
-
複数の条件での絞り込み検索の仕方
-
PHP+SQL在庫集計で在庫の累計が...
-
件数をカウントして日付でソー...
-
売り上げ集計SQLが作れません
-
UPDATE文で・・・・
-
時間内かどうかの抽出
-
Cは返して欲しくない
-
日ごとの集計
-
POSTGRESQLでt時間差が30分以上...
-
検索条件の指定方法がわかりま...
-
PostgresSQLで自動計算
-
日時の比較
-
○日以内のレコード取得について
-
【SQL】他テーブルに含まれる値...
-
SELECT INTOで一度に複数の変数...
-
フラグをたてるってどういうこ...
-
sqlに記述できない文字
おすすめ情報