sqlで最新の日付のレコードとその前のレコードとの差分を取りたい

やりたいこと

株価の日足データのように、1日1アイテムあたり1レコード存在するようなデータで、前日比を出したい。
※動作確認はPostgreSQL 8.4.20でのみ実施

対象テーブル

daily_stocks
(
  code character varying(10),
  date date,
  name character varying(255),
  end_value real,
  volume bigint,
  CONSTRAINT daily_stocks_code_key UNIQUE (code, date)
)

こんな感じで銘柄ごとの終値が入ってる

方法

とりあえず1日前との差分をとればいい？

前日との比較なので、self joinで、一日前のレコードを持って来ればいい

select *,a.end_value-b.end_value as diff_end_value,a.volume-b.volume as diff_volume from daily_stocks as a join daily_stocks as b on a.code=b.code and a.date=b.date+1;

火曜～金曜までならこれで良いが、月曜の前日は日曜なのでレコードがない。

ちゃんと考える

最新と2番目のレコードをとってくればいいので、まずは各銘柄について最新の日付を持つレコードを抽出する。
最新の日付を持つ銘柄のレコードは、そのレコードよりも古いレコードを 含まない レコードなので、
テーブルaのレコードに対して

select * from daily_stocks as b where a.code=b.code and a.date<b.date;

を含まないようにすればいい。つまり

select * from daily_stocks as a where ( select * from daily_stocks as b where a.code=b.code and a.date<b.date );

となる。

これで最新の日付を持つテーブルが取得できたので、これをもとのテーブルとjoinして差分をとればいい。

select *,cd.end_value-pd.end_value as diff_end_value from daily_stocks as pd join (select * from daily_stocks as a where not exists( select * from daily_stocks as b where a.code=b.code and a.date<b.date)) as cd on pd.code=cd.code and pd.date<>cd.date;

(長い…)

これで、最新以外の日付と最新の日付の終値の差分が取れた。

もともとは各日付のレコードに対して前日比のデータをくっつけたかったので、本来ほしいデータではないような気がしてきた。

ということで続く

それWindow関数でできるよ！

lag関数を使うことで、特定のグルーピング条件による一つ前のレコードを取得できるので、それを使ってやると

select *,end_value,end_value - lag(end_value) over (partition by code order by date) as lag_end_value from daily_stocks;

となって、非常にすっきり書けた。ありがとうWindow関数！

目的のBIツールでの表示もいい感じにできたので満足