集約関数は、複数の値の集合に対して計算を行い、単一の結果を返す関数です。

count、count_if、max_by、min_by、approx_distinctを除くすべての集約関数は、null値を無視し、入力行がない場合やすべての値がnullの場合にはnullを返します。たとえば、sumは0ではなくnullを返し、avgはnull値をカウントに含めません。nullを0に変換するためには、coalesce関数を使用できます。

集約中の順序付け

array_aggなどの一部の集約関数は、入力値の順序に応じて異なる結果を生成します。この順序付けは、集約関数内にorder-by句を記述することで指定できます。

array_agg(x ORDER BY y DESC)
array_agg(x ORDER BY x, y, z)

集約中のフィルタリング

FILTERキーワードを使用して、WHERE句を使用して表される条件に基づいて、集約処理から行を削除することができます。これは、各行が集約に使用される前に評価され、すべての集約関数でサポートされています。

aggregate_function(...) FILTER (WHERE <condition>)

array_aggを使用する際に、FILTERを使用してnullを考慮から除外するという一般的で非常に便利な例があります。

SELECT array_agg(name) FILTER (WHERE name IS NOT NULL)
FROM region;

別の例として、アヤメの花の数に条件を追加したい場合を考えてみましょう。次のクエリを変更します。

SELECT species,
       count(*) AS count
FROM iris
GROUP BY species;

species    | count
-----------+-------
setosa     |   50
virginica  |   50
versicolor |   50

通常のWHERE文を使用すると情報が失われます。

SELECT species,
    count(*) AS count
FROM iris
WHERE petal_length_cm > 4
GROUP BY species;

species    | count
-----------+-------
virginica  |   50
versicolor |   34

フィルターを使用すると、すべての情報が保持されます。

SELECT species,
       count(*) FILTER (where petal_length_cm > 4) AS count
FROM iris
GROUP BY species;

species    | count
-----------+-------
virginica  |   50
setosa     |    0
versicolor |   34

一般的な集約関数