[SQL]SQLで最新の日付のデータや同一グループの中で最新のレコードを取得する
履歴テーブルというか、どんどん情報を格納しておいて、そこから最新を取得するSQL
例えば、
価格情報のテーブル
商品ID
日付
価格
といった構造において 商品IDごとに、最新日付の価格を取得するといった場合
毎回、いろいろな手法があるなーと思うんです。
RDBごとに性能も変わるので、試してみるしかない。
Group byのmaxを使う
- 降順でソートして、Limit 1 や分析関数の row_number() を使う
- 最大値のカラムに対して、条件を指定する xxx = select max(yyy) / xxx In select max(yyy)
- existsを使う(not exists の場合は、 b.create_date > a.create_date のように、existsの場合は、 b.create_date < a.create_date)
以下、いろいろな手法が記載されているURLを集めました
http://www.risewill.co.jp/blog/archives/3483
http://labs.timedia.co.jp/2014/10/selecting-max-record-in-group-by.html
http://qa.atmarkit.co.jp/q/1847
http://qiita.com/nogitsune413/items/f413268d01b4ea2394b1
http://lightgauge.net/database/sqlserver/1672/
http://www.oracle.co.jp/forum/message.jspa?messageID=35028967
https://oshiete.goo.ne.jp/qa/1274959.html
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q127773347
別の観点
中規模くらいのシステム、レコード数が 数千万〜、億を超えるくらい
-
- View等を定義して、個々のロジックで異なることが少なくなるようにしてください
- Viewを設計・メンテナンスするのは、DBA or DB回りの人が行ってください
- チューニングが必要になった場合には、Viewのメンテナンスで対応できます(ヒント句を入れる、SQLを組み替える)
- Oracle、PostgreSQLのマテリアライズド・ビューを利用する場合は、リフレッシュを考えてください、高速リフレッシュで対応できることが前提です。
- 1日数回程度のリフレッシュすれば良いのであれば、完全リフレッシュでも良いかもしれません。
データ量が1億レコード超えてくるような場合だと、非常に遅くなります
- そのような場合の1つの打開策として、最新を管理するテーブルを別に用意します。
価格情報のテーブル
ID(シーケンスみたいな値、PK)
商品ID
日付
価格
価格情報管理テーブル
最新ID:価格情報のテーブル.ID
商品ID:1つ古い、もしくは最新と同じ非同期処理がある場合の最新IDはデータが確定・固定されるまでは、最新IDに反映しない方が良い。でないと、中途半端なデータが最新扱いになる可能性がある。
最新IDと商品IDが一致する場合と 最新IDが100, 商品IDが99のような場合もありえる。逆転して 最新ID99、商品ID 100 というケースは発生しないようにするか、どのケースはどちらを使うのか業務ロジックを決めておく。
として、価格情報管理テーブル.最新のIDから、価格情報のテーブル.ID をたどるようにします。
これだと、INNER JOIN で比較的速くなります。
注意点は、
- 価格情報のテーブルにトリガーを仕掛け、価格情報管理テーブルに反映するのは、どうしようも無い時の最終手段と考えてください。
- トリガーは、見通しを非常に悪くします。再コンパイルが必要になり無停止リリースできない場合が多々あります。
- アプリ側で共通ロジックを作成して対応することを推奨。