PostgresのWindow関数を使う

時系列のデータを含んだテーブルがある場合、 前日比や前月比と言った以前の値との比較を出したくなります。 Pandasだとshiftメソッドを使えば可能ですが、PostgresだとWindow関数を使います。

こちらが元データ。

SELECT dt, x FROM data ORDER BY dt DESC LIMIT 3

dt	x
2018-08-01	50.8
2018-07-01	49
2018-06-01	49.2

こちらがWindow関数のうちlag関数を使った場合です。

SELECT dt, x, lag(x, 1) over (order by dt) as prev_x
FROM data
ORDER BY dt DESC
LIMIT 3

dt	x	prev_x
2018-08-01	50.8	49
2018-07-01	49	49.2
2018-06-01	49.2	50.1

prev_xのカラムにxの1行前の値が入っています。

lag(x, 1) over (order by dt)が肝です。

lag関数の第二引数を変えることで、例えば12とすると、行に毎月データがあれば、12ヶ月前を意味するので前年同月の値が入ります。

100日前の株価との比較をしてリターンを計算するといった操作はpandas側ではなく、postgres側でやってしまえば良さそうです。 viewを作って毎回クエリを書かないように済ませることもできますし。