今更ながらPostgreSQLで「現在の行の値」と「現在の行から前にある行の値」を簡単に比較したかった～window関数便利～

はじめに

定期的に取得している情報に、抜けの情報があるかを監視することをSQLで簡単にできないかと思い、いろいろ調べた結果、window関数というものを初めて知りまして、使ってみたら便利で驚いたので自分用メモで残したいと思い記事を書きました。

結論

最初に結論からお見せすると、以下のようにLAGを使うと実現できます。

テーブル構成

抽出してくる情報を格納しているテーブルは以下のような例にしています。

sample_id  | object_id |     getinfo_dt
-----------+-----------+---------------------
sampletest | obj01     | 2018-01-29 15:00:00
sampletest | obj01     | 2018-01-29 15:10:00
sampletest | obj01     | 2018-01-29 15:20:00
sampletest | obj01     | 2018-01-29 15:30:00
sampletest | obj01     | 2018-01-29 15:40:00
sampletest | obj01     | 2018-01-29 17:20:00
sampletest | obj01     | 2018-02-09 12:30:00
sampletest | obj01     | 2018-02-09 14:50:00
sampletest | obj01     | 2018-02-13 10:20:00
sampletest | obj01     | 2018-03-12 11:50:00
sampletest | obj01     | 2018-03-12 13:30:00
sampletest | obj01     | 2018-07-10 14:50:00
sampletest | obj01     | 2018-07-10 15:20:00
sampletest | obj01     | 2018-09-04 16:10:00
sampletest | obj01     | 2018-09-04 16:10:00
sampletest | obj02     | 2018-03-12 11:30:00
sampletest | obj02     | 2018-03-12 16:30:00

・
・
・

SQL構文

※date_truncで時間を一時間ごとにまとめています。

SELECT
  sample_id, object_id, hour,
LAG (hour, 1, hour)
OVER (order by sample_id, object_id, hour) - hour AS diff
FROM
  t_object,
date_trunc('hour', getinfo_dt) AS hour
WHERE
  sample_id = 'sampletest'
ORDER BY
  object_id;

すると

sample_id  | object_id |        hour         |        diff
-----------+-----------+---------------------+---------------------
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 17:00:00 | -02:00:00
sampletest | obj01     | 2018-02-09 12:00:00 | -10 days -19:00:00
sampletest | obj01     | 2018-02-09 14:00:00 | -02:00:00
sampletest | obj01     | 2018-02-13 10:00:00 | -3 days -20:00:00
sampletest | obj01     | 2018-03-12 11:00:00 | -27 days -01:00:00
sampletest | obj01     | 2018-03-12 13:00:00 | -02:00:00
sampletest | obj01     | 2018-07-10 14:00:00 | -120 days -01:00:00
sampletest | obj01     | 2018-07-10 15:00:00 | -01:00:00
sampletest | obj01     | 2018-09-04 16:00:00 | -56 days -01:00:00
sampletest | obj01     | 2018-09-04 16:00:00 | 00:00:00
sampletest | obj02     | 2018-03-12 11:00:00 | 176 days 05:00:00
sampletest | obj02     | 2018-03-12 16:00:00 | -05:00:00

のようになり、この時間からこれだけ情報取得できてないというのがわかるようになります。
非常に便利。。。

具体的説明

そもそもwindow関数って？

@HiromuMasuda0228さんの記事がわかりやすかったので、こちらの方を参照ください。
(https://qiita.com/HiromuMasuda0228/items/0b20d461f1a80bd30cfc)

ではでは、LAGがどのように機能してるのか、ざっくり説明します。
※Microsoftの記事を参考にしています。(https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2014/hh231256(v=sql.120))

構文

構文は以下のようになっています。

LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )

引数（LAG）

• scalar_expression 指定されたオフセットに基づいて返される値。つまり、どのカラムを対象としているかを表しています。
• offset 値を取得する現在の行から戻る行の数。つまり、何個分前の行と比較するかを表しています。
• default offsetのscalar_expressionがNULLの場合に返される値。つまり、一番初めに返される行の初期値が設定しない場合には、NULLを返すよということです。結論で記述したソースコードでは、getinfo_dtを指定しているので一番初めの行の値を初期値として入っている状態になっています。

引数（OVER）

• partition_by_clause FROM句で生成された結果セットをパーティションに分割します。指定しない場合、関数ではクエリ結果セットのすべての行を 1 つのグループとして扱います。つまり、どのカラムを対象にグループ単位で見ていくかということです。わかりづらいと思うので、実際に以下のソースを見てください。

partition_by_clauseに、object_idを設定します。

SELECT
  sample_id, object_id, hour,
LAG (hour, 1, hour)
OVER (partition by object_id order by sample_id, object_id, hour) - hour AS diff
FROM
  t_object,
date_trunc('hour', getinfo_dt) AS hour
WHERE
  sample_id = 'sampletest'
ORDER BY
  object_id DESC;

すると、object_idのobject_idが異なったタイミングで、diffの値が初期化されていることがわかると思います。

sample_id  | object_id |        hour         |        diff
-----------+-----------+---------------------+---------------------
sampletest | obj02     | 2018-03-12 11:00:00 | 00:00:00
sampletest | obj02     | 2018-03-12 16:00:00 | -05:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 17:00:00 | -02:00:00
sampletest | obj01     | 2018-02-09 12:00:00 | -10 days -19:00:00
sampletest | obj01     | 2018-02-09 14:00:00 | -02:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-03-12 11:00:00 | -27 days -01:00:00
sampletest | obj01     | 2018-03-12 13:00:00 | -02:00:00
sampletest | obj01     | 2018-07-10 14:00:00 | -120 days -01:00:00
sampletest | obj01     | 2018-07-10 15:00:00 | -01:00:00
sampletest | obj01     | 2018-09-04 16:00:00 | -56 days -01:00:00
sampletest | obj01     | 2018-09-04 16:00:00 | 00:00:00
sampletest | obj01     | 2018-02-13 10:00:00 | -3 days -20:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00

• order_by_clause 関数を適用する前にデータの順序を決定します。order_by_clause は必須です。つまり、SQL出力結果とは別に、何を基準に並び替えた結果の前の行を参照するかということです。これについてもわかりづらいと思うので、実際に以下のソースを見てください。

【object_idを基準にして値の誤差を評価した場合】

SELECT
  sample_id, object_id, hour,
LAG(hour, 1, hour)
OVER (partition by object_id order by object_id) - hour AS diff
FROM
  t_object,
date_trunc('hour', getinfo_dt) AS hour
WHERE
  sample_id = 'sampletest';

sample_id  | object_id |        hour         |       diff
-----------+-----------+---------------------+-------------------
sampletest | obj01     | 2018-09-04 16:00:00 | 00:00:00
sampletest | obj01     | 2018-09-04 16:00:00 | 00:00:00
sampletest | obj01     | 2018-07-10 15:00:00 | 56 days 01:00:00
sampletest | obj01     | 2018-07-10 14:00:00 | 01:00:00
sampletest | obj01     | 2018-03-12 13:00:00 | 120 days 01:00:00
sampletest | obj01     | 2018-03-12 11:00:00 | 02:00:00
sampletest | obj01     | 2018-02-13 10:00:00 | 27 days 01:00:00
sampletest | obj01     | 2018-02-09 14:00:00 | 3 days 20:00:00
sampletest | obj01     | 2018-02-09 12:00:00 | 02:00:00
sampletest | obj01     | 2018-01-29 17:00:00 | 10 days 19:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 02:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj02     | 2018-03-12 16:00:00 | 00:00:00
sampletest | obj02     | 2018-03-12 11:00:00 | 05:00:00

これでは、撮影が対象になっておらず、diffが期待する値になっていないことがわかります。

【object_idやsample_id、hourを基準にして値の誤差を評価した場合】

SELECT
  sample_id, object_id, hour,
LAG(hour, 1, hour)
OVER (partition by object_id order by sample_id, object_id, hour) - hour AS diff
FROM
  t_object,
date_trunc('hour', getinfo_dt) as hour
WHERE
  sample_id = 'sampletest';

sample_id  | object_id |        hour         |        diff
-----------+-----------+---------------------+---------------------
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 15:00:00 | 00:00:00
sampletest | obj01     | 2018-01-29 17:00:00 | -02:00:00
sampletest | obj01     | 2018-02-09 12:00:00 | -10 days -19:00:00
sampletest | obj01     | 2018-02-09 14:00:00 | -02:00:00
sampletest | obj01     | 2018-02-13 10:00:00 | -3 days -20:00:00
sampletest | obj01     | 2018-03-12 11:00:00 | -27 days -01:00:00
sampletest | obj01     | 2018-03-12 13:00:00 | -02:00:00
sampletest | obj01     | 2018-07-10 14:00:00 | -120 days -01:00:00
sampletest | obj01     | 2018-07-10 15:00:00 | -01:00:00
sampletest | obj01     | 2018-09-04 16:00:00 | -56 days -01:00:00
sampletest | obj01     | 2018-09-04 16:00:00 | 00:00:00
sampletest | obj02     | 2018-03-12 11:00:00 | 00:00:00
sampletest | obj02     | 2018-03-12 16:00:00 | -05:00:00

これだと、しっかり前回の時間からの情報をdiffで判断することができました。

最後に

window関数のLAGを用いると、簡単に行数比較ができることがわかりました。
今回驚きだったのが、partition_by_clauseが非常に便利で、返ってきたSQLに対してPHPとかで何とか初期化するという手間がなくなったのが感動でした。（これを知らなかったときは、PHPとかで何とか初期化してました。。）

参考

• window関数を使いこなす 〜分析のためのSQL〜 https://qiita.com/HiromuMasuda0228/items/0b20d461f1a80bd30cfc
• LAG (Transact-SQL) https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2014/hh231256(v=sql.120)
• PostgreSQLウィンドウ関数のTips https://postd.cc/window_functions_postgresql/#7