コマンド道場

$ man filter

FILTER

集約関数

集約関数ごとに数える範囲を変える。1 回の集計で全体と条件付きを並べられる。

呼び出しの形

集約関数(式) FILTER (WHERE 条件)
COUNT(*) FILTER (WHERE 条件)
SUM(式) FILTER (WHERE 条件)

つまずきやすいところ

  • 該当が 0 件のとき、COUNT は 0 ですが SUM と AVG は NULL です。 並べて表に出すなら COALESCE でそろえてください。

  • WHERE と混同しない。WHERE はその問い合わせの集約関数すべてに効きます。

  • FILTER の中に集約関数は書けません。集約した結果で絞るのは HAVING の仕事です。

  • 付けられるのは集約関数の呼び出しだけです。ふつうの列には付けられません。

実行例

結果は実際に流したものです。同じ SQL を 自由に打てる画面 で試すと、同じ結果になります。

全体と条件付きを 1 行に並べる
SELECT COUNT(*) AS zen, COUNT(*) FILTER (WHERE status = 'done') AS seiritsu, COUNT(*) FILTER (WHERE status = 'cancel') AS torikeshi FROM juchu
zenseiritsutorikeshi
1082

1 行

受注 10 件のうち、成立が 8 件、取消が 2 件です。

WHERE で絞ると、全体の件数まで変わってしまう
SELECT COUNT(*) AS zen FROM juchu WHERE status = 'done'
zen
8

1 行

全体のつもりの列が 10 ではなく 8 になります。WHERE は問い合わせ全体に効きます。

該当が無いとき、COUNT は 0 で SUM は NULL
SELECT k.kokyaku_name, COUNT(j.juchu_id) AS zen, COUNT(j.juchu_id) FILTER (WHERE j.status = 'done') AS seiritsu, SUM(1) FILTER (WHERE j.status = 'cancel') AS torikeshi FROM kokyaku k LEFT JOIN juchu j ON j.kokyaku_id = k.kokyaku_id GROUP BY k.kokyaku_name ORDER BY k.kokyaku_name
kokyaku_namezenseiritsutorikeshi
三田商事00NULL
白樺物産22NULL
赤坂商会211
青葉工業33NULL
黒松システム321

5 行

取消が 1 件も無い顧客の列は、0 ではなく NULL になります。

FILTER の中に集約関数は書けない
SELECT COUNT(*) FILTER (WHERE COUNT(*) > 1) AS r FROM juchu

実行するとこうなります

ERROR: aggregate functions are not allowed in FILTER

条件に書けるのは行を見る条件だけです。集約した結果で絞るなら HAVING を使います。

説明

FILTER は、その集約関数だけに効く WHERE です。

WHERE で絞ると、その問い合わせにある集約関数すべてが絞られた行だけを見ます。だから「全体の件数」と「成立ぶんの件数」を並べたいとき、WHERE では書けません。FILTER なら同じ 1 行の中に並べられます。

CASE を使った書き方（COUNT(CASE WHEN 条件 THEN 1 END)）と結果は同じです。FILTER のほうが短く、条件が集約に掛かっていることが見た目で分かります。

⚠️ 該当が 1 件も無いときの値が、関数によって違います。 COUNT(...) FILTER は 0 を返しますが、SUM(...) や AVG(...) は NULL です。同じ表に並べると片方だけ NULL になるので、そろえたいなら COALESCE で包んでください。

OVER と組み合わせて、ウィンドウ関数として使うこともできます。

ほかの製品でも通るか

この書き方が使えない製品があります そのまま使える: PostgreSQL / SQLite

  • MySQL では使えません（集約関数の FILTER (WHERE …)）
  • MariaDB では使えません（集約関数の FILTER (WHERE …)）
  • SQL Server では使えません（集約関数の FILTER (WHERE …)）

未確認: Oracle Database / IBM Db2

製品ごとの対応表（根拠つき）を見る

次に読む

使い方を順に読むなら

関係する関数

根拠にした一次情報