日付により変動する「単価」を取得するSQLを教えてください

質問

PostgreSQL + PHP による Web アプリケーションを開発しています。

ある日付時点での各商品単価が取得できるSQLを考えていますが良い考えが浮かびません。
たとえば、以下のように2009年11月18日時点での価格一覧が取得できないでしょうか？

牛丼（小）300円
牛丼（並）350円
牛丼（大）470円
サラダＡ　200円
サラダＢ　205円
サラダ盛合せ　498円

テーブル設計に問題があるのかもしれませんが...

テーブル設計は以下のとおりです。
ブラウザによっては崩れて読みにくいかもしれません、ご容赦ください。

■商品マスタ
┏━━━━┳━━━━━━━┳━━━━━━━┓
┃商品ID　　┃　商品詳細ID　　┃　　　商品名　　　┃
┃　　　　　 1┃　　　　　　　　 200┃牛丼（小）　　　　┃
┃　　　　　 1┃　　　　　　　　 300┃牛丼（並）　　　　┃
┃　　　　　 1┃　　　　　　　　 500┃牛丼（大）　　　　┃
┃　　　　　 2┃　　　　　　　　　　 1┃サラダＡ　　　┃
┃　　　　　 2┃　　　　　　　　　　 2┃サラダＢ　　　┃
┃　　　　　 2┃　　　　　　　　　　 3┃サラダ盛合せ　　┃
┗━━━━┻━━━━━━━┻━━━━━━━┛

■商品価格
┏━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━┓
┃商品ID　　┃　商品詳細ID　　┃　　　単価適用日　　　┃　　　単価　　┃
┃　　　　　 1┃　　　　　　　　 200┃　　　　　　2009-10-01┃　　　　　　 300┃
┃　　　　　 1┃　　　　　　　　 200┃　　　　　　2009-11-30┃　　　　　　 280┃
┃　　　　　 1┃　　　　　　　　 200┃　　　　　　2010-02-05┃　　　　　　 320┃
┃　　　　　 1┃　　　　　　　　 300┃　　　　　　2009-10-01┃　　　　　　 350┃
┃　　　　　 1┃　　　　　　　　 300┃　　　　　　2009-11-20┃　　　　　　 370┃
┃　　　　　 1┃　　　　　　　　 300┃　　　　　　2010-03-01┃　　　　　　 380┃
┃　　　　　 1┃　　　　　　　　 500┃　　　　　　2009-09-30┃　　　　　　 500┃
┃　　　　　 1┃　　　　　　　　 500┃　　　　　　2009-10-15┃　　　　　　 470┃
┃　　　　　 2┃　　　　　　　　　　 1┃　　　　　　2009-10-01┃　　　　　　 180┃
┃　　　　　 2┃　　　　　　　　　　 1┃　　　　　　2009-10-18┃　　　　　　 200┃
┃　　　　　 2┃　　　　　　　　　　 2┃　　　　　　2009-09-05┃　　　　　　 210┃
┃　　　　　 2┃　　　　　　　　　　 2┃　　　　　　2009-09-20┃　　　　　　 205┃
┃　　　　　 2┃　　　　　　　　　　 2┃　　　　　　2010-02-02┃　　　　　　 170┃
┃　　　　　 2┃　　　　　　　　　　 3┃　　　　　　2009-09-01┃　　　　　　 480┃
┃　　　　　 2┃　　　　　　　　　　 3┃　　　　　　2009-09-15┃　　　　　　 500┃
┃　　　　　 2┃　　　　　　　　　　 3┃　　　　　　2009-11-02┃　　　　　　 498┃
┃　　　　　 2┃　　　　　　　　　　 3┃　　　　　　2010-01-20┃　　　　　　 470┃
┗━━━━┻━━━━━━━┻━━━━━━━━━┻━━━━━━┛

長文すみません、よろしくお願いいたします。

回答No.3

nda23 · ベストアンサー

単価適用日のカラクリを考えると、指定日以前で最大の日付と一致する
データが対象となるようですね。
SELECT A.商品名,B.単価 FROM
商品マスタ A LEFT JOIN
(SELECT X.* FROM 商品価格 X LEFT JOIN
(SELECT 商品ID,商品詳細ID,MAX(単価適用日) 適用日 FROM 商品価格
WHERE 単価適用日<='2009-11-18' GROUP BY 商品ID,商品詳細ID) Y
ON X.商品ID=Y.商品ID AND X.商品詳細ID=Y.商品詳細ID
AND X.単価適用日=Y.適用日) B
ON A.商品ID=B.商品ID AND A.商品詳細ID=B.商品詳細ID

お礼

nda23 さま、再三のご回答ありがとうございます。

サンプルのSQL、、ズバリこれです！
わざわざ書いていただき申し訳ないです。

先に seimurakam さまが回答されたパフォーマンスのことも考えると
テーブル設計を考え直そうかなと思います。

しかし、とても興味深いSQLを書いていただき、大変参考になりました。

回答No.2

seimurakam

自分もこれと同等の形式のテーブルを持つシステムに関わったことがあります。

ある日の単価のレコードを引くSQLはこんな感じです。
select
*
from
商品価格
where
商品ID = [商品ID]
and 商品詳細ID = [商品詳細ID]
and 単価適用日 = (
select
max(単価適用開始日)
from
商品価格
where
商品ID = [商品ID]
and 商品詳細ID = [商品詳細ID]
and 単価適用日 <= [対象日付]
)

※以下ご参考まで
自分の関わった環境は、商品マスタ=10万行、商品価格=100万行ほどだったのですが、
パフォーマンス上の問題から結局、商品価格テーブルに適用終了日を追加して、
[対象日付] between 適用開始日 and 適用終了日
で検索する形式に変更しました。

お礼

早速の回答ありがとうございます。
SQL参考になりました。

>パフォーマンス上の問題
なるほど、私がいま開発中のものはそこまで商品点数を想定していませんが、今後のことを考えるとテーブル設計を見直す必要があるかもしれませんね。

回答No.1

nda23

商品マスタと商品価格を結合するのは？
SELECT A.商品名,B.単価 FROM
商品マスタ A LEFT JOIN
(SELECT * FROM 商品価格 WHERE 単価適用日='2009-11-18') B
ON A.商品ID=B.商品ID AND A.商品詳細ID=B.商品詳細ID

補足

早速の回答ありがとうございます。

申し訳ございません、私の主観のみで書いておりましたので、記載漏れがありました。

"商品価格" テーブルの "単価適用日" とは、その日付以降に適用される単価です。あらかじめ、未来日付の単価を登録することを想定としています。