PostgreSQLでデータを数行ごとに間引いて選択

Question

前提・実現したいこと

PostgreSQLで、データを数行おきに間引いて取得したいです。

連番のインデックスがあるケース

以下のような方法を一番に考えたのですが、他に方法はありますでしょうか。

select
    idx,
    val
from
    test_table
where
    idx % 10 = 0

インデックスが時刻データのケース

元々やりたいことは、インデックスが連番ではなく、時系列データの場合であり、10秒おきにデータを抽出したいと考えてます。
この場合だと、以下のように時系列データから秒数部分を抜き出して、MODを計算してWHEREで抽出するんでしょうか…？

select
    idx_date,
    val
from
    test_table
where
    cast(DATE_part('seconds',idx_date) as integer)%10=0

時刻データが一定間隔ではないケース

上のケースでは、時刻データが一定間隔ごとに存在しているケースを想定しています。
しかし、実際に扱いたいデータは歯抜けがあり、2秒間隔とか3秒間隔のデータも含まれています。
そのため、上のコードの場合だと、データがごっそり抜け落ちてしまう可能性があります。
（秒数が11,13,15,17,19,21,22,24,26,28,31,...のように微妙にずれていると、データは0件…）

なので、一定間隔でなくてもとりあえず構わないので、10行ごとにデータを抽出したい場合も考えています。
この場合、一度連番を振ってから、連番のindex行を上のように抽出するか…？と考えてます。
が、これもあまり良いコードに思えません…。

select * from
    (select
        raw_number() over () as index
        date,
        values
    from
        test_table
    ) t
where
    index % 10 = 0

ご教示いただきたくお願いいたします。

※そもそものやりたいことは、pythonのpandasでいうところの、df.resample(rule='10s').mean()という作業です。
本件での作業の前に、移動平均をとる作業をした上で、本件のようにデータを間引くことを考えています。
pandasならたった1行で書ける作業ではあるのですが、ことSQLで書くとなるとどうすればいいのでしょうか…。
何卒よろしくお願いいたします。

Comments

[hoshi-takanori]

ウィンドウ関数で 10s ごとに分けて、その平均を取ればいいのでは。
https://www.postgresql.jp/document/11/html/tutorial-window.html
https://qiita.com/HiromuMasuda0228/items/0b20d461f1a80bd30cfc
https://postd.cc/window_functions_postgresql/

[marururu]

ウィンドウ関数（またはgroupby）をあまり詳しく勉強してないせいで、陥ってしまっているみたいですね…。
「部署ごとの平均」というケースや、「前○列、後○列を選択したデータの平均（移動平均）」のような使い方は、調べる中で見つけたのですが、ウィンドウ関数で”10sごとのグループを集計する"という処理は可能なのでしょうか？
回答例で示すような手順で10秒置きのデータを作って、ウィンドウ関数の中で`(PERTITION BY idx_date_10s)`のような形をとるのか、それともまた別の方法があるでしょうか…？

Answer 1

generate_series()を用いて、１０秒ごとの目盛を作ります。
その目盛とデータを突合させて、row_number()で目盛内の連番を作ります。

※withは分かり易くする事とテストデータ用に使用しているので、纏めたり取っ払ったりして下さい。

with param as ( -- パラメータ
    select '2020/03/12 09:00:00'::timestamp as measure_start -- 範囲開始
          , EXTRACT(EPOCH FROM (
                '2020/03/12 09:01:00'::timestamp -- 範囲終了
              - '2020/03/12 09:00:00'::timestamp -- 範囲開始
            ))::int measure_range -- 範囲の通算秒
), expn as (  -- 目盛の元
    select  measure_start, generate_series(0, measure_range, 10) as range_scale -- 10秒毎
    from param    
), measure as (-- 10秒毎の目盛
    select  tsrange(
              measure_start + (range_scale || ' seconds')::interval
            , measure_start + ((range_scale +10) || ' seconds')::interval
            ,'[)'
           )  as scale  -- timestampの範囲型
    from expn
) , test_table as (-- テストデータ
    select * from (values
       ('2020/03/12 09:00:00'::timestamp , 1)
      ,('2020/03/12 09:00:11'::timestamp , 2)
      ,('2020/03/12 09:00:13'::timestamp , 3)
      ,('2020/03/12 09:00:15'::timestamp , 4)
      ,('2020/03/12 09:00:17'::timestamp , 5)
      ,('2020/03/12 09:00:19'::timestamp , 6)
      ,('2020/03/12 09:00:21'::timestamp , 7)
      ,('2020/03/12 09:00:22'::timestamp , 8)
      ,('2020/03/12 09:00:24'::timestamp , 9)
      ,('2020/03/12 09:00:26'::timestamp , 10)
      ,('2020/03/12 09:00:28'::timestamp , 11)
      ,('2020/03/12 09:00:31'::timestamp , 12)
    ) as w(idx_date, val)
)
select *
from (
    select msr.scale, tst.*
         , row_number() over(partition by msr.scale order by tst.idx_date) as scale_seq
    from   measure msr
         left join test_table tst
         on msr.scale @> tst.idx_date
     ) step1
where scale_seq=1 --1件に絞る

Comments

[marururu]

generate_series()を用いる方法は、自分もあり得ると考えていましたが、SQL初心者には手が出せませんでした…。
本SQL文をみても全く理解できませんが、少しずつ勉強しようと思います。ご返信ありがとうございます。

[sazi]

generate_series()の結果はメモリーに展開され、それを条件に対象テーブルのデータを抽出するので、idx_dateにインデックスが設定されていれば、group byを使用する集計より高速になるはずです。
実行計画で確認されてみて下さい。

Answer 2

こんな感じですかね。
日付を文字列化し、秒1桁台を切り捨ててグループ化するやり方です。

select
    substr(to_char(idx_date, 'YYYY/MM/DD HH24:MI:SS'), 1, 18) || '0' as ten_seconds,
    avg(val) as avg_val
from
    test_table
group by substr(to_char(idx_date, 'YYYY/MM/DD HH24:MI:SS'), 1, 18) || '0'
order by substr(to_char(idx_date, 'YYYY/MM/DD HH24:MI:SS'), 1, 18) || '0'

Comments

[marururu]

なるほど確かに、秒単位を切り捨てたdateでgroupbyすればよかったんですね。気づいてませんでした。

10秒単位ではなく、日・時・分・秒などの単位なら、`date_trunc('minute',idx_date)`などを使うと、より早くなりそうだと思いました。
ただ、これだと切りのイイケースでないとで高速化できません。データ件数がより増えた場合（数万件～数十万件）でも、対応できそうなケースは考えられるようなものなのでしょうか。

また、（集計自体の方法がわかればそれでいいといえばいいのですが）一応データ抽出方法のお作法についての言及も、もう少し募らせていただきたく思います。
ご回答ありがとうございます。
以上、よろしくお願いいたします。