kuniku’s diary

はてなダイアリーから移行(旧 d.hatena.ne.jp/kuniku/)、表示がおかしな箇所はコメントをお願いします。記載されている内容は日付およびバージョンに注意してください。直近1年以上前は古い情報の可能性が高くなります。

[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)

別の観点

中規模くらいのシステム、レコード数が 数千万〜、億を超えるくらい
    • View等を定義して、個々のロジックで異なることが少なくなるようにしてください
    • Viewを設計・メンテナンスするのは、DBA or DB回りの人が行ってください
    • チューニングが必要になった場合には、Viewのメンテナンスで対応できます(ヒント句を入れる、SQLを組み替える)
    • OraclePostgreSQLのマテリアライズド・ビューを利用する場合は、リフレッシュを考えてください、高速リフレッシュで対応できることが前提です。
      • 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 で比較的速くなります。

注意点は、
  • 価格情報のテーブルにトリガーを仕掛け、価格情報管理テーブルに反映するのは、どうしようも無い時の最終手段と考えてください。
  • トリガーは、見通しを非常に悪くします。再コンパイルが必要になり無停止リリースできない場合が多々あります。
  • アプリ側で共通ロジックを作成して対応することを推奨。