
棚, 部品, 在庫
------------------
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)在庫を超える引当は不可
です。
ご教授の程、よろしくお願い致します。
A 回答 (2件)
- 最新から表示
- 回答順に表示
No.2
- 回答日時:
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
- 回答日時:
こんな感じでどうでしょうか。
--全角でインデントしています
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/tutor …
http://www.postgresql.jp/document/8.4/html/sql-e …
などを見てみてください。
引当と関係ない部品の情報まで取得しようとしたらもう一工夫が必要ですね。
ご教授ありがとうございます。
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-e …
1行前までの累計在庫を取得するために(5)を採用して、
(該当行までの累計在庫 - 該当行の在庫)を
計算してSQLを組みなおしてみます。
たいへん参考なりました。
もう少し考えてみます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
group byで指定したカラム以外...
-
【SQL】他テーブルに含まれる値...
-
SELECT INTOで一度に複数の変数...
-
フラグをたてるってどういうこ...
-
sqlに記述できない文字
-
PostgreSQLのtimestamp型で時間...
-
既にテーブルが存在する場合の...
-
副問合せを使わずにUNIONと同様...
-
JDBCを使ってdate型へのINSERT...
-
chr(13) と char(13) の違いっ...
-
オラクルのUPDATEで複数テーブル
-
ADO+ODBCでテーブルに接続する...
-
UPDATEで既存のレコードに文字...
-
既存データをINSERT文にして出...
-
テーブル名が可変の場合のクエ...
-
SQLSERVER 連番更新について
-
csvデータ不要列の削除をbatフ...
-
truncate tableを使って複数の...
-
SQLサーバに対するSQL文で抽出...
-
timestampのデータはどのように...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
PostgresSQLで自動計算
-
group byで指定したカラム以外...
-
ストアドプロシージャによる動...
-
複数の条件での絞り込み検索の仕方
-
PostgresSQLでの動的な計算
-
【SQL】他テーブルに含まれる値...
-
SELECT INTOで一度に複数の変数...
-
フラグをたてるってどういうこ...
-
sqlに記述できない文字
-
truncate tableを使って複数の...
-
UPDATEで既存のレコードに文字...
-
PostgreSQLのtimestamp型で時間...
-
オラクルのUPDATEで複数テーブル
-
既存データをINSERT文にして出...
-
エラーを起こす方法
-
SQLで、Join句で結合したテ...
-
Accessの構成をコピーしたい
-
SQLサーバに対するSQL文で抽出...
-
UPDATEの更新前の値を取得したい
-
JDBCを使ってdate型へのINSERT...
おすすめ情報