[PostgreSQL]データの前回比を取得

どうも、雪かきって大事なんだなと気付かされたざわです。

普段はWebシステムの構築を主に行なっておりますが、
AWSのRedshiftを使ってデータ分析基盤構築したり、チューニングしたりしてた時期もありました。
SQL楽しいですよね(なんの話や)
さて

やりたいこと

例えば次のようなテーブルがあります。

id hiduke point
1 2018-01-26 1
2 2018-01-29 3
3 2018-01-30 6
4 2018-01-31 5

そこから以下のデータを取得したい
* 日付の降順
* pointの前回比(歯抜けの日にち分は詰める)

さっそく答え

LAG()で1つ前のレコードの値を取得します。
LAG()を使用する場合はソートされている必要があるため、OVER()でhidukeの昇順にしています。

SELECT 
 id,  
 hiduke,
 point,
 point - LAG(point) OVER (ORDER BY hiduke) AS zenkaihi
FROM hoge; 

--結果
| id | hiduke     | point | zenkaihi |
-----+------------+-------+----------+
|  1 | 2018-01-26 |   1   |  [NULL]  |
|  2 | 2018-01-29 |   3   |     2    | 
|  3 | 2018-01-30 |   6   |     3    |
|  4 | 2018-01-31 |   5   |    -1    |

Window関数

LAG()はウィンドウ関数といわれるものですね。ウィンドウ関数は他にも色々あり、主に集計目的で使うのではないでしょうか

参考:ウィンドウ関数

関数 説明
row_number() 行番号取得
rank() ランキング。例)1位が2人いたら次は3位
dense_rank() ランキング。例)1位が2人いても次は2位
percent_rank() ランキング。パーセント。 (rank – 1) / (全行数 – 1)
cume_dist() パーセント。 (現在の行の位置) / (全行数)
ntile(N) ランキング (1..N に分割)
lag(value, offset, default) ソート状態での前の行の値
lead(value, offset, default) ソート状態での後の行の値
first_value(value) 最初の値
last_value(value) 最後の値
nth_value(value, N) N番目の値 (1はじまり)

おしまい

おしまい

サーバサイドエンジニア。オムライスが好物

シェアする

  • このエントリーをはてなブックマークに追加

フォローする