- PR -

直近の日付と連結するSQL文

1
投稿者投稿内容
Ishi
会議室デビュー日: 2005/08/09
投稿数: 4
投稿日時: 2005-09-12 14:12
環境を書き忘れていました。
SQLSERVER 8.0を使用しております。

結合を使った集計用SQLで悩んでいます。

Aテーブル（日毎・番号ごとデータ）
日付　datetime
番号　int
実績値　int
…

Bテーブル（機種状態）
変更日付 datetime
番号 int
機種ID int

機種の入替が発生するたびに、
その日一日の全ての機種の状態を保存する。

たとえば番号が5まであるとすると、
日付：5/10
　番号：1　機種：1
　番号：2　機種：1
　番号：3　機種：2
　番号：4　機種：3
　番号：5　機種：2

日付：5/20
　番号：1　機種：1
　番号：2　機種：2
　番号：3　機種：3
　番号：4　機種：1
　番号：5　機種：2


といった形の二つのテーブルがあり、
Aテーブルには日毎のデータが入っているので、これを元に
機種ごとの一ヶ月の実績値の集計などを出したいのです。
ただ、たとえば5/15の実績データについては、
5/10の機種状態から機種を判断します。

つまり、LEFT JOINする際に、Aの各レコードについて、
直近の日付のBデータを持ってきて機種を判断したいのですが、
この、直近の日付のレコードを結合するという際に良い方法はありますでしょうか。

自分で考えたのは、
SELECT 機種,SUM(実績値)
FROM A
LEFT JOIN B AS B1
ON A.番号 = B1.番号 AND A.日付 >= B1.日付
LEFT JOIN B AS B2
ON A.番号 = B2.番号 AND A.日付 >= B2.日付 AND B1.日付 < B2.日付
WHERE B2.日付 IS NULL
GROUP BY 機種

というものです。とりあえず実績より古い日付の機種状態を全て結合し、
その後もう一つ、機種状態を結合し、そのときに機種状態1の日付より新しい日付のみ結合します。
すると最新のものは機種状態2のレコードが一件も結合されないため、値がNULLとなるので、そのレコードのみ抽出できます。
ただこれですと件数が多くなった場合にかなりパフォーマンスが落ちてしまいます。
いい手はないでしょうか。


[ メッセージ編集済み 編集者: Ishi 編集日時 2005-09-12 14:20 ]
明智重蔵
大ベテラン
会議室デビュー日: 2005/09/05
投稿数: 127
投稿日時: 2005-09-12 14:44
凄く面白い外部結合の使い方ですね

Aの日付未満で最大の日付をサブクエリとして結合条件に使用すればよいかと思います

Oracleではありますが
以下のSQLを応用すればよいかと思います

OracleSQLパズル
http://oraclesqlpuzzle.hp.infoseek.co.jp/5-22.html
http://oraclesqlpuzzle.hp.infoseek.co.jp/5-30.html
Ishi
会議室デビュー日: 2005/08/09
投稿数: 4
投稿日時: 2005-09-12 16:37
後者のアドレスを参考にして書いてみました。
一応手元のサンプルデータでは同じ結果が出せた上にパフォーマンスもかなりあがりました。
サブクエリのWhere句で親クエリの項目と比較するという着眼点に気づけなかったのが失敗でした。
このクエリでもA,Bともにレコード数が増えると不安な点は残りますが、
そこはもう少し考えてみることにします。
ありがとうございました。

SELECT B1.機種,SUM(A.実績値)
FROM A
LEFT JOIN B AS B1
ON A.番号 = B1.番号 AND A.日付 >= B1.日付
WHERE B1.日付 >= (SELECT MAX(B2.日付) FROM B AS B2 WHERE A.日付 >= B2.日付)
GROUP BY B1.機種
1

スキルアップ／キャリアアップ（JOB@IT）