棚, 部品, 在庫
------------------
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.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を組みなおしてみます。
たいへん参考なりました。
もう少し考えてみます。
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 棚;
うまくいくといいのですが・・
お探しの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で指定したカラム以外...
-
【SQL】他テーブルに含まれる値...
-
SELECT INTOで一度に複数の変数...
-
CSVファイルを読み込んでテーブ...
-
既存データをINSERT文にして出...
-
テーブル名が可変の場合のクエ...
-
pandasでsqlite3にテーブル作成...
-
フラグをたてるってどういうこ...
-
認知心理学で言うスクリプトとは?
-
複数レコードの複数フィールド...
-
ACCESSにおいてスキーマとは
-
Accessで今日から5日後
-
sqlに記述できない文字
-
SQL Server:UNIONで結合した結...
-
SELECTした結果に行番号を求めたい
-
副問合せを使わずにUNIONと同様...
-
timestampのデータはどのように...
-
エラーを起こす方法
-
VBAでの行数を揃える方法
-
ACCESSのVBAにてExcelに行...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
group byで指定したカラム以外...
-
postgreSQLのselect文でデータ...
-
PostgresSQLで自動計算
-
PostgresSQLでの動的な計算
-
ストアドプロシージャによる動...
-
1週間後の日付を求めたい
-
POSTGRESQLでt時間差が30分以上...
-
複数の条件での絞り込み検索の仕方
-
件数をカウントして日付でソー...
-
フラグをたてるってどういうこ...
-
SELECT INTOで一度に複数の変数...
-
【SQL】他テーブルに含まれる値...
-
sqlに記述できない文字
-
UPDATEで既存のレコードに文字...
-
テーブル名が可変の場合のクエ...
-
エラーを起こす方法
-
オラクルのUPDATEで複数テーブル
-
truncate tableを使って複数の...
-
結合したテーブルをSUMしたい
-
Accessで今日から5日後
おすすめ情報