1分おきにレコードされたデータを10分おきに集計したい

Question

1分おきにレコードされた1日分のデータを10分おきにある列の平均や最大値を集計したいのですがやり方がわかりません。
日時の列はtimestamp型です。

テーブル定義

create table public.test (
  "日時" timestamp(6) without time zone not null
  , "外気温度" numeric
  , "外気湿度" numeric
  , primary key ("日時")
);

下記のコードを試してみましたがうまくいきませんでした。

TO_TIMESTAMP((TO_CHAR(日時,'YYYY-MM-DD HH24') || ':' || TO_CHAR(TRUNC(EXTRACT('minute' FROM 日時)/10)*10,'FM09')),'YYYY-MM-DD HH24:MI:00') AS colmun_koma_timestamp
FROM time_stamp 
GROUP BY colmun_koma_timestamp

わかる方教えていただけないでしょうか。

よろしくお願いいたします。

Comments

[m.ts10806]

テーブル定義をCREATE TABLE文で提示し、
サンプルデータをINSERT文で提示してください。

あとタグにPythonがありますが本件とはどう関係するのでしょうか。

[Lapis051130]

返信ありがとうございます。
テーブル定義を追記しました。
すみませんが、「サンプルデータをINSERT文で提示する」の意味がわからなかったのでその点は追記してません。
Pythonのタグはあまり関係がないと思ったので消しました。

よろしくお願いいたします。

[m.ts10806]

こちらの手元で確認、検証するには定義だけではなくデータも必要です。
「勝手に作った都合のいいデータ」ではアドバイスになりません。
わざわざ自分でINSERT文を組むのは非常に手間ですし、おそらくそこまでしてアドバイスしたい人は稀でしょう。
ですから、自身で持ってるデータをそのまま投入できるINSERT文の形でサンプルを提示してもらいたいのです。

Answer 1

集計用の目盛はGENERATE_SERIES()を用いて作成する事ができます。

select 
       '2020/12/09'::date + (time_range || 'minute')::interval as scale_time_from
    ,  '2020/12/09'::date + (time_range + 10 || 'minute')::interval as scale_time_to
from GENERATE_SERIES(0, 1439, 10) as time_range(time_range) 
     -- 0始まりで10毎に1439までで一日分を生成

後は上記目盛を軸にデータを結合して集計。

select scale.time_from, max(test.外気温度), avg(test.外気温度)
from (
      select 
           '2020/12/09'::date + (time_range || 'minute')::interval as time_from
        ,  '2020/12/09'::date + (time_range + 10 || 'minute')::interval as time_to
      from GENERATE_SERIES(0, 1439, 10) as time_range(time_range)
    ) scale 
    left join test
    on   test.日時 >= scale.time_from and test.日時 < scale.time_to
     and test.日時 >= '2020/12/09 00:00:00' and test.日時 < '2020/12/10 00:00:00'
group by scale.time_from

Comments

[Lapis051130]

ご回答ありがとうございます。
上記のコードを試してみたら、10分のデータに集計することができました。
saziさん、ありがとうございました。

[sazi]

create文やデータのinsert文があると回答しやすくなるのは事実ですので、次回がある場合はよしなにお願いします.

[Lapis051130]

次からは気をつけます。

追加で質問なのですが、10分間の平均や最大値は集計できましたが、ある列の10分目,20分目,30分目...の瞬間の値も一緒に集計したいのですがなにか方法はありますか？
よろしくお願いいたします。

[sazi]

目盛と同じ時点の値を出したいという事なら、
max(case when scale.time_from=test.日時 then [対象の項目] end )
という項目をselectに加えれば良いと思います。
※目盛と一致しない箇所はNullになります。

[Lapis051130]

対象の値というところには何をいれればいいのでしょうか？

[sazi]

値ではなくて項目でした。「ある列」と言っている項目です。

[Lapis051130]

瞬間の値も集計できました。
ご親切にどうもありがとうございました。

[Lapis051130]

追加で質問なのですが、瞬間の値がNullだった場合は前回値を取得したいのですがどうすればいいでしょうか？
よろしくお願いいたします。