SQLで理想原価を求めたい

質問

部品, 購入数パターン, 原価
-------------------------
A　　　　100　　　　　　　100
A　　　　200　　　　　　　98
A　　　　300　　　　　　　90
B　　　　100　　　　　　　200
B　　　　200　　　　　　　180

上記の様な部品の購入数パターン毎の原価を管理している
テーブルに対し、理想原価を抽出したいのですが、
SQLでどのように実現したらよいか悩んでおります。
使用しているDBはpostgres 8.4です。

具体的な例を挙げますと、
部品Aの購入数が、
(1)100未満の場合、購入数パターン100とみなし原価100円とする
(2)200の場合、購入数パターン200とみなし原価98円とする
(3)210の場合、購入数パターン300とみなし原価90円とする
(4)300超過の場合、購入数パターン300とみなし原価90円とする

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

回答No.1

yamada_g · ベストアンサー

>理想原価を抽出したい
は、部品と予定購入数みたいな数値を渡すと上記テーブルから記載の条件で原価を取得する、
という意味で合ってますか？
とりあえず、そう考えて書いてみました。

select 部品,原価
from (
　select t.*
　　　　　--部品ごとに購入数で並べた時、前レコード前が存在するときはその購入数・存在しない時は0
　　　　　,lag(購入数,1,0) over(partition by 部品 order by 購入数) + 1 下範囲
　　　　　--部品ごとに購入数で並べた時、後レコードが存在するときは0・存在しない時は99999999
　　　　　,lead(0,1,99999999/* ※1 */) over(partition by 部品 order by 購入数) 上範囲
　from t)
where 部品 = 'A'/* ※2 */
and (310 /* ※3 */ between 下範囲 and 購入数 or 310 /* ※3 */ between 購入数 + 1 and 上範囲)
;

※1にはこれ以上の購入数はありえない、という値を設定してください
※2・※3は検索条件にあたる部品・購入数を設定してください。

ただ、PostgreSQLの環境がないのでOracle10gで試したものですので上手くいかないかもしれません。
PostgreSQL8.4から分析関数が利用できるようなので大丈夫かなと思いますが・・

http://www.postgresql.jp/document/8.4/html/functions-window.html

お礼

>>理想原価を抽出したい
>は、部品と予定購入数みたいな数値を渡すと上記テーブルから記載の条件で原価を取得する、
>という意味で合ってますか？

仰せの通り、その認識で間違いありません。

ご教授頂いたSQLで検証した結果、思惑通りの抽出結果となりました。
本当に助かりました、ありがとうございます。