PostgreSQL の FILTER 句を活用した効率的な集計
PostgreSQL でデータを集計する際、COUNT(*) や SUM() などの集約関数を使うことが多いですが、特定の条件でフィルタした件数を取得する場合、FILTER 句を使うとクエリがシンプルで可読性が高くなります。FILTER 句は PostgreSQL 9.4 から導入された拡張機能で、標準 SQL にはない PostgreSQL 特有の機能ですが、その有用性から他の DBMS でも徐々に採用されつつあります。
FILTER 句とは？
通常、集約関数で条件付きの集計を行う場合、CASE WHEN ... THEN ... ELSE ... END を使うこともあると思いますが、FILTER 句を使うとより直感的に記述できます。
FILTER 句の構文は以下のような形式です。
集約関数(...) FILTER (WHERE 条件式)
FILTER句は以下のような一般的な集計関数と組み合わせて使用できます。
- COUNT, SUM, AVG - カウントや数値の集計
- MIN, MAX - 最小値や最大値の取得
- ARRAY_AGG - 値の配列への集約
- STRING_AGG - 文字列の連結
- JSONB_AGG - JSON配列への集約
- 統計関数 (STDDEV, VARIANCE, CORR など)
FILTER 句 / CASE WHEN を使って、1分単位で無効・有効なデータを集計する
例えば、以下のような device_logs テーブルがあるとします。
このテーブルには、IoT デバイスから送信された位置情報のログが格納されています。
CREATE TABLE device_logs (
id UUID PRIMARY KEY,
device_id VARCHAR(255) NOT NULL, -- デバイスの ID
event_time TIMESTAMPTZ NOT NULL, -- データの発生時間
location GEOGRAPHY(Point,4326) NULL -- IoT デバイスの位置情報を格納するカラム
);
たとえば、以下の条件で 1 分単位のデータ件数を集計したいとします。
-
locationがNULLまたは(0,0)のデータを「無効」とする - それ以外の
locationを「有効」とする -
device_idごとに集計する
CASE WHEN を使った場合
通常、CASE WHEN を使うと以下のようになります。
SELECT
date_trunc('minute', event_time) AS minute_time,
device_id,
SUM(
CASE
WHEN location IS NULL
OR location = ST_GeomFromEWKT('SRID=4326;POINT(0 0)') THEN 1
ELSE 0
END
) AS invalid_count,
SUM(
CASE
WHEN location IS NOT NULL
AND location <> ST_GeomFromEWKT('SRID=4326;POINT(0 0)') THEN 1
ELSE 0
END
) AS valid_count
FROM
device_logs
GROUP BY
minute_time,
device_id
ORDER BY
minute_time,
device_id;
結果は以下のようになります。
minute_time | device_id | invalid_count | valid_count
------------------------+-----------+---------------+-------------
2025-03-01 12:33:00+00 | device_1 | 0 | 11
2025-03-01 12:33:00+00 | device_2 | 11 | 0
2025-03-01 12:37:00+00 | device_1 | 0 | 18
2025-03-01 12:37:00+00 | device_2 | 18 | 0
2025-03-01 12:38:00+00 | device_1 | 0 | 4
2025-03-01 12:38:00+00 | device_2 | 4 | 0
2025-03-01 12:53:00+00 | device_1 | 0 | 12
2025-03-01 12:53:00+00 | device_2 | 12 | 0
2025-03-01 13:09:00+00 | device_1 | 0 | 23
2025-03-01 13:09:00+00 | device_2 | 23 | 0
2025-03-01 13:10:00+00 | device_1 | 0 | 1
2025-03-01 13:10:00+00 | device_2 | 1 | 0
2025-03-01 13:17:00+00 | device_1 | 0 | 45
2025-03-01 13:17:00+00 | device_2 | 45 | 0
2025-03-01 13:18:00+00 | device_1 | 0 | 32
2025-03-01 13:18:00+00 | device_2 | 32 | 0
(16 rows)
この書き方でも問題ありませんが、SUM(CASE WHEN ... THEN 1 ELSE 0 END) の部分が冗長になりがちです。同じ処理を FILTER 句を使うと、より分かりやすく記述できます。
FILTER 句を使った場合
SELECT
date_trunc('minute', event_time) AS minute_time,
device_id,
COUNT(*) FILTER (
WHERE
location IS NULL
OR location = ST_GeomFromEWKT('SRID=4326;POINT(0 0)')
) AS invalid_count,
COUNT(*) FILTER (
WHERE
location IS NOT NULL
AND location <> ST_GeomFromEWKT('SRID=4326;POINT(0 0)')
) AS valid_count
FROM
device_logs
GROUP BY
minute_time,
device_id
ORDER BY
minute_time,
device_id;
結果は以下のようになります。同じ結果が得られています。
minute_time | device_id | invalid_count | valid_count
------------------------+-----------+---------------+-------------
2025-03-01 12:33:00+00 | device_1 | 0 | 11
2025-03-01 12:33:00+00 | device_2 | 11 | 0
2025-03-01 12:37:00+00 | device_1 | 0 | 18
2025-03-01 12:37:00+00 | device_2 | 18 | 0
2025-03-01 12:38:00+00 | device_1 | 0 | 4
2025-03-01 12:38:00+00 | device_2 | 4 | 0
2025-03-01 12:53:00+00 | device_1 | 0 | 12
2025-03-01 12:53:00+00 | device_2 | 12 | 0
2025-03-01 13:09:00+00 | device_1 | 0 | 23
2025-03-01 13:09:00+00 | device_2 | 23 | 0
2025-03-01 13:10:00+00 | device_1 | 0 | 1
2025-03-01 13:10:00+00 | device_2 | 1 | 0
2025-03-01 13:17:00+00 | device_1 | 0 | 45
2025-03-01 13:17:00+00 | device_2 | 45 | 0
2025-03-01 13:18:00+00 | device_1 | 0 | 32
2025-03-01 13:18:00+00 | device_2 | 32 | 0
(16 rows)
パフォーマンスは異なるのか？
FILTER 句を使うと、集約関数の前にフィルタリングが適用されるため、無駄な計算が減ると考えられます。ただし、FILTER 句は集約関数ごとに適用されるため、集約関数の数が多いとオーバーヘッドになることがあるようです。
一方、CASE WHEN では 1回のスキャンで複数の条件を評価するため、場合によってはより効率的になるようです。
また、Postgreのバージョンによっては、クエリプランナーの最適化が異なるため、FILTER 句の方が遅いと言及している記事もありました。
実際に検証してみた(Postgres 17)
実際に1,000,000行のデータを投入後に、両方のクエリの実行計画を測定してみました。
EXPLAIN ANALYZEを実行した結果、どちらも同じ実行計画になりました。クエリオプティマイザが内部的に同様の処理に変換しているためと考えられます。
実行計画
Finalize GroupAggregate (cost=25853.69..25861.13 rows=28 width=33) (actual time=466.924..469.205 rows=17 loops=1)
Group Key: (date_trunc('minute'::text, event_time)), device_id
-> Gather Merge (cost=25853.69..25860.22 rows=56 width=33) (actual time=466.919..469.194 rows=19 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=24853.67..24853.74 rows=28 width=33) (actual time=456.617..456.618 rows=6 loops=3)
Sort Key: (date_trunc('minute'::text, event_time)), device_id
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=24852.64..24852.99 rows=28 width=33) (actual time=456.587..456.588 rows=6 loops=3)
Group Key: date_trunc('minute'::text, event_time), device_id
Batches: 1 Memory Usage: 24kB
Worker 0: Batches: 1 Memory Usage: 24kB
Worker 1: Batches: 1 Memory Usage: 24kB
-> Parallel Seq Scan on device_logs (cost=0.00..17558.85 rows=416788 width=49) (actual time=0.031..98.971 rows=333431 loops=3)
Planning Time: 0.112 ms
Execution Time: 469.242 ms
クエリの実行時間は以下の通りです。
-
FILTER句を使ったクエリ: 平均実行時間 471ms -
CASE WHENを使ったクエリ: 平均実行時間 483ms
同じ実行計画を生成しているため、クエリの実行時間はほぼ同じでした。
パフォーマンスに大きな差がない場合、FILTER 句を使用する方が可読性が高く、冗長なコードを避けられるため、FILTER 句を使用する方が望ましいと考えられます。
まとめ
繰り返しになりますが、PostgreSQL の FILTER 句を使うことで、条件付きの集約クエリをよりシンプルに記述できます。
✅ CASE WHEN よりも可読性が高く、冗長なコードを避けられる
✅ SUM() や AVG() などの他の集約関数とも組み合わせ可能
✅ クエリがシンプルになり、メンテナンスしやすい
パフォーマンスを比較しつつ、適切な場面で活用してみてください！
参考
Discussion