PostgresSQLで自動計算

質問

棚,　部品, 　在庫
------------------
1-1　A　　　　300　
1-2　A　　　　200　
1-3　A　　　　100　
1-4　B　　　　100　
1-5　B　　　　50　　

上記のような、部品の保管場所、在庫を管理しているテーブルに対し、
在庫が少ない部品から優先して引当数を自動計算したいのですが、
SQLで実現可能でしょうか。
なお、使用しているDBはpostgres 8.4です。

例として、部品Aの引当総数が550個だとすると、
SQL のSELECT文で、

棚,　部品, 　在庫,　引当数
-----------------------------
1-1　A　　　　300　　　　　　250
1-2　A　　　　200　　　　　　200
1-3　A　　　　100　　　　　　100
1-4　B　　　　100　　　　　　0
1-5　B　　　　50　　　　　　　0

のような結果が抽出できれば理想的です。

引当条件としては、
(1)在庫が少ないものから、優先的に
(2)在庫を超える引当は不可
です。

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

回答No.2

yamada_g

URLを示しておいて自分がちゃんと読んでなかったですね・・
申し訳ありません。
9.0.3からはvalue PRECEDING、value FOLLOWING という書き方もできるようですね。

ということで、ご自身でも考えていらっしゃるようですが訂正版です。
select
　棚,部品,在庫,
　case
　--累計在庫が引当総数に満たない時は、当該棚から引当てる
　　when 550 /* 引当総数 */ - COALESCE(累計在庫,0) > 0 then
　　　case
　　　　--当該棚から全在庫を引当てると引当総数を越えてしまう場合は、引当総数-累計在庫を引当てる
　　　　when 550 - (COALESCE(累計在庫,0) + 在庫) < 0 then 550 - 累計在庫
　　　　else 在庫
　　　end
　else 0 end 引当
from (
　select
　　棚,部品,在庫
　　--在庫の昇順に並べ、1行前までの累計在庫を取得
　　/* 訂正
　　,sum(在庫) over(partition by 部品 order by 在庫
　　　rows between unbounded preceding and 1 preceding) 累計在庫
　　*/
　　,sum(在庫) over(partition by 部品 order by 在庫
　　　rows between unbounded preceding and current row) - 在庫 累計在庫
　from テーブル
　where 部品 = 'A' /* 対象部品 */)
order by 棚;

うまくいくといいのですが・・

回答No.1

yamada_g

こんな感じでどうでしょうか。

--全角でインデントしています
select
　棚,部品,在庫,
　case
　--累計在庫が引当総数に満たない時は、当該棚から引当てる
　　when 550 /* 引当総数 */ - COALESCE(累計在庫,0) > 0 then
　　　case
　　　　--当該棚から全在庫を引当てると引当総数を越えてしまう場合は、引当総数-累計在庫を引当てる
　　　　when 550 - (COALESCE(累計在庫,0) + 在庫) < 0 then 550 - 累計在庫
　　　　else 在庫
　　　end
　else 0 end 引当
from (
　select
　　棚,部品,在庫
　　--在庫の昇順に並べ、1行前までの累計在庫を取得
　　,sum(在庫) over(partition by 部品 order by 在庫
　　　rows between unbounded preceding and 1 preceding) 累計在庫
　from テーブル
　where 部品 = 'A' /* 対象部品 */)
order by 棚;

PostgreSQLの環境がないのでOracle10gで試したSQLですが、
PostgreSQL8.4からwindow関数が使えるので大丈夫かなと思います。
window関数については
http://www.postgresql.jp/document/8.4/html/tutorial-window.html
http://www.postgresql.jp/document/8.4/html/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
などを見てみてください。
引当と関係ない部品の情報まで取得しようとしたらもう一工夫が必要ですね。

お礼

ご教授ありがとうございます。

PostgreSQL8.4にて本SQLを実行したところ、分析関数「rows between unbounded preceding and 1 preceding」の'1'のところでエラーとなりました。

PostgreSQL8.4でサポートしているオプションは以下だけのようです。。
---------------------------------------
(1)RANGE UNBOUNDED PRECEDING
(2)RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
(3)RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
(4)ROWS UNBOUNDED PRECEDING
(5)ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
(6)ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
---------------------------------------
http://www.postgresql.jp/document/8.4/html/sql-expressions.html

1行前までの累計在庫を取得するために(5)を採用して、
（該当行までの累計在庫 - 該当行の在庫）を
計算してSQLを組みなおしてみます。

たいへん参考なりました。
もう少し考えてみます。