棚, 部品, 在庫
------------------
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で質問しましょう!
似たような質問が見つかりました
- その他(データベース) c言語の問題です。これを踏まえてコーディングしたいのでおしえていただきたいです。 3 2023/08/03 09:27
- Excel(エクセル) Excelで在庫表(クエリ、ピボット) 2 2022/04/11 17:11
- その他(ビジネススキル・経営ノウハウ) 在庫管理のこの問題が分かりません。どなたか解説お願いします 2 2022/04/18 18:35
- 数学 在庫管理のこの問題が分かりません。どなたか解説お願いします 4 2022/04/18 22:19
- 数学 在庫管理のこの問題が分かりません。どなたか解説お願いします 2 2022/04/18 22:21
- 経営情報システム 在庫管理のこの問題が分かりません。どなたか解説お願いします。 0 2022/04/18 16:02
- その他(Microsoft Office) Excelの関数(FILTER関数)について教えてください 2 2023/07/31 16:11
- Excel(エクセル) マクロで最終行から上に検索を逆にしたい 1 2022/05/17 18:27
- SOHO・在宅ワーク・内職 不良在庫と言うなんて 2 2023/04/14 22:05
- 求人情報・採用情報 派遣について これはあくまで仮定の話しにもなりますが 現在自動車部品メーカーの下請けの倉庫内のある場 4 2023/02/19 08:13
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
group byで指定したカラム以外...
-
SELECT INTOで一度に複数の変数...
-
今って秋田新幹線へ動いてますか?
-
フラグをたてるってどういうこ...
-
【SQL】他テーブルに含まれる値...
-
sqlに記述できない文字
-
テーブル名が可変の場合のクエ...
-
オラクルのUPDATEで複数テーブル
-
truncate tableを使って複数の...
-
PostgreSQLのtimestamp型で時間...
-
MySQLで数字の値のもののみを取...
-
既にテーブルが存在する場合の...
-
joinの場合のテーブル名の別名...
-
IDとパスワードについて。
-
SELECTした結果に行番号を求めたい
-
timestamp が空のデータを除い...
-
UPDATEで既存のレコードに文字...
-
ACCESSのVBAにてExcelに行...
-
Accessで最新のレコード...
-
T-SQLで任意の箇所で強制終了す...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
group byで指定したカラム以外...
-
PostgresSQLでの動的な計算
-
1週間後の日付を求めたい
-
複数の条件での絞り込み検索の仕方
-
PHP+SQL在庫集計で在庫の累計が...
-
件数をカウントして日付でソー...
-
売り上げ集計SQLが作れません
-
日ごとの集計
-
UPDATE文で・・・・
-
Cは返して欲しくない
-
時間内かどうかの抽出
-
PostgresSQLで自動計算
-
日時の比較
-
検索条件の指定方法がわかりま...
-
○日以内のレコード取得について
-
POSTGRESQLでt時間差が30分以上...
-
【SQL】他テーブルに含まれる値...
-
SELECT INTOで一度に複数の変数...
-
フラグをたてるってどういうこ...
-
sqlに記述できない文字
おすすめ情報