CASE WHENより使いやすい？PostgreSQLのFILTER句を使ってみます

こんにちは、さるまりんです。
きょうのトピックは、PostgreSQLでの「集計クエリの書き方」の便利な機能、FILTERについてです。

ふだんSQLを書くとき、こんなふうに CASE WHEN を多用していました。

SELECT
  COUNT(*) AS total,
  COUNT(CASE WHEN gender = 'male' THEN 1 END) AS male_count,
  COUNT(CASE WHEN gender = 'female' THEN 1 END) AS female_count
FROM users;

もちろんこれでも動くし間違いではないんですけど。。
PostgreSQLなら、もっと読みやすくてスッキリ書ける方法があります。

FILTER句とは？

FILTER句は、集計関数に対して個別のWHERE条件をつけられる便利な機能です。
PostgreSQL 9.4以降で使えるこの構文、こんなふうに書きます：

COUNT(*) FILTER (WHERE 条件)

先ほどのクエリも、FILTERを使うとこうなります：

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE gender = 'male') AS male_count,
  COUNT(*) FILTER (WHERE gender = 'female') AS female_count
FROM users;

ね、見やすいし、タイポも減りそうでしょ？

FILTERを使った実用例いろいろ

ステータスごとの件数を一発で！

SELECT
  COUNT(*) FILTER (WHERE status = 'todo') AS todo_count,
  COUNT(*) FILTER (WHERE status = 'in_progress') AS in_progress_count,
  COUNT(*) FILTER (WHERE status = 'done') AS done_count
FROM tasks;

CASE WHENを3回書くよりも、ずっとスマートでメンテもしやすいですね。

最近ログインしたユーザーの男女別集計

SELECT
  COUNT(*) AS total_logged_in,
  COUNT(*) FILTER (WHERE gender = 'male') AS male_logged_in,
  COUNT(*) FILTER (WHERE gender = 'female') AS female_logged_in
FROM users
WHERE last_login >= CURRENT_DATE - INTERVAL '7 days';

期間で絞った上で、さらに集計関数の中で性別ごとに条件を分ける、という使い方です。

金額合計をステータス別に分けて

SELECT
  SUM(amount) FILTER (WHERE status = 'paid') AS total_paid,
  SUM(amount) FILTER (WHERE status = 'pending') AS total_pending,
  SUM(amount) FILTER (WHERE status = 'cancelled') AS total_cancelled
FROM orders;

SUM(CASE WHEN ...) でも書けますが、FILTER句ならもっと読みやすくないですか？

FILTER句はSQL標準に含まれているけれど、実際に対応しているのはPostgreSQLなど一部のRDBMSだけなので注意が必要です。PostgreSQLでもv9.4以降で対応しています。OracleやSQL Server、MySQLでは対応していないのでCASE WHENで書く必要があります。

FILTERはCASE WHENより見やすくて(主観ですが)、COUNT、SUM、AVGなどとともに使えます。
PostgreSQLを使っているならうまく使うと良さそうですね。

読んでくださってありがとうございました。

それではまた！