postgresqlで1分前の差を抽出し、差の5回分の平均値を出したい

Question

前提・実現したいこと

postgresqlのクエリで苦労しています。

現在を2019/10/10 11:27:00とした時
毎分の1分前との差を抽出し、それを現在から4回前までの5回分の平均値を出したいです。

・データ構造
テーブル

・実現したい結果

試したこと

次のSQL文を書いて1分前の差を出すことは出来ましたが、
5回分の平均が出せずに困っています。。
SELECT
日時,
値,
lag(t1.値, 1) OVER (ORDER BY t1.値) AS 1つ前の値,
(t1.値 - lag(値, 1) OVER (ORDER BY t1.日時)) AS 差,
FROM テーブル AS t1

何か良い方法はないでしょうか。

Answer 1

差の累計を求める事で可能です。
単なる累計ならWindow関数の**SUM()**を用いる事で、コストは下げられますが、５分分の累計という限定なので、相関副問合せを用います。

with t as (
    SELECT 日時, 値
         , lag(値) OVER (ORDER BY 日時) AS １つ前の差
         , 値 - lag(値) OVER (ORDER BY 日時) AS 差
    FROM (values
           ('2019/10/10 11:22:00'::timestamp, 4980)
          ,('2019/10/10 11:23:00'::timestamp, 5000)
          ,('2019/10/10 11:24:00'::timestamp, 5200)
          ,('2019/10/10 11:25:00'::timestamp, 4980)
          ,('2019/10/10 11:26:00'::timestamp, 5000)
          ,('2019/10/10 11:27:00'::timestamp, 5200)
          ,('2019/10/10 11:28:00'::timestamp, 5100)
         ) AS t1(日時, 値)
)
select *
     , case when lag(値, 5) OVER (ORDER BY 日時) is not null 
         then (select sum(差)/5 from t where 日時 between (t1.日時 - interval '4 minutes') and t1.日時)
       end AS ５回分平均
from t as t1

※回答したSQLは５件分＝５分分として相違が無いものとしています。
相違があるなら内容は変わります。

Comments

[sazi]

５回分の累計に限定しないと駄目ですね。
ちょっと見直しします。

[kmm232306]

saziさん

回答ありがとうございます。
ご回答の通り実行したところ、実現したいことが解決できました。
ウィンドウ関数の入れ子にしようかと苦労していましたが、
5件分で÷5にすれば良かったんですね。。気づきませんでした汗
大変勉強になりました。

ベストアンサーに選ばせていただきました。
ありがとうございました。