質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

pandas

Pandasは、PythonでRにおけるデータフレームに似た型を持たせることができるライブラリです。 行列計算の負担が大幅に軽減されるため、Rで行っていた集計作業をPythonでも比較的簡単に行えます。 データ構造を変更したりデータ分析したりするときにも便利です。

データマイニング

データマイニングは、購買履歴やクレジットカードの利用履歴、電話の通話履歴など企業にある大量のデータを解析して、その中に隠れたパターンやルールを探し出す技術です。DMと略されることもあります。

Q&A

解決済

2回答

3382閲覧

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

marururu

総合スコア23

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

pandas

Pandasは、PythonでRにおけるデータフレームに似た型を持たせることができるライブラリです。 行列計算の負担が大幅に軽減されるため、Rで行っていた集計作業をPythonでも比較的簡単に行えます。 データ構造を変更したりデータ分析したりするときにも便利です。

データマイニング

データマイニングは、購買履歴やクレジットカードの利用履歴、電話の通話履歴など企業にある大量のデータを解析して、その中に隠れたパターンやルールを探し出す技術です。DMと略されることもあります。

1グッド

1クリップ

投稿2020/03/12 03:06

前提・実現したいこと

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

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

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

Postgres

1select 2 idx, 3 val 4from 5 test_table 6where 7 idx % 10 = 0

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

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

Postgres

1select 2 idx_date, 3 val 4from 5 test_table 6where 7 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行を上のように抽出するか…?と考えてます。
が、これもあまり良いコードに思えません…。

Postgres

1select * from 2 (select 3 raw_number() over () as index 4 date, 5 values 6 from 7 test_table 8 ) t 9where 10 index % 10 = 0

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

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

s.k👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

marururu

2020/03/12 05:58 編集

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

回答2

0

ベストアンサー

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

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

SQL

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

投稿2020/03/12 08:00

編集2020/03/12 09:58
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

marururu

2020/03/17 00:37

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

2020/03/17 05:07 編集

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

0

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

postgresql

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

投稿2020/03/12 04:32

yureighost

総合スコア2183

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

marururu

2020/03/12 05:47

なるほど確かに、秒単位を切り捨てたdateでgroupbyすればよかったんですね。気づいてませんでした。 10秒単位ではなく、日・時・分・秒などの単位なら、`date_trunc('minute',idx_date)`などを使うと、より早くなりそうだと思いました。 ただ、これだと切りのイイケースでないとで高速化できません。データ件数がより増えた場合(数万件~数十万件)でも、対応できそうなケースは考えられるようなものなのでしょうか。 また、(集計自体の方法がわかればそれでいいといえばいいのですが)一応データ抽出方法のお作法についての言及も、もう少し募らせていただきたく思います。 ご回答ありがとうございます。 以上、よろしくお願いいたします。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問