他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

2.7. 集約関数

他のほとんどのリレーショナルデータベース製品同様、PostgreSQL集約関数をサポートします。 集約関数は複数の入力行から1つの結果を計算します。 例えば、行の集合に対して、count(総数)、sum(総和)、avg(平均)、max(最大)、min(最小)といった演算を行う集約があります。

例として、次のように全ての都市の最低気温から最も高い気温を求めることができます。

SELECT max(temp_lo) FROM weather;

 max
-----
  46
(1 row)

どの都市のデータなのかを知りたいとしたら、下記のような問い合わせを試行するかもしれません。


SELECT city FROM weather WHERE temp_lo = max(temp_lo);     間違い

しかし、max集約をWHERE句で使用することができませんので、このコマンドは動作しません (WHERE句はどの行を集約処理に渡すのかを決定するものです。したがって、集約関数の演算を行う前に評価されなければならないことは明らかです。 このためにこの制限があります)。 しかし、よくあることですが、問い合わせを書き直すことで、意図した結果が得られます。 これには以下のような副問い合わせを使用します。

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

     city
---------------
 San Francisco
(1 row)

副問い合わせは、外側の問い合わせで起こることとは別々に集約を計算する独立した演算ですので、この問い合わせは問題ありません。

また、GROUP BY句と組み合わせた集約も非常に役に立ちます。 例えば、以下のコマンドで都市ごとにデータの数と最低気温の最大値を求めることができます。

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     2 |  46
(2 rows)

ここには都市ごとに1行の出力があります。 それぞれの集約結果はその都市に一致するテーブル行全体に対する演算結果です。 以下のように、HAVINGを使ってグループ化した行にフィルタをかけることができます。

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

  city   | count | max
---------+-------+-----
 Hayward |     1 |  37
(1 row)

このコマンドは上と同じ計算を行うものですが、全てのtemp_loの値が40未満の都市のみを出力します。 最後になりますが、Sから始まる名前の都市のみを対象にしたい場合は、以下を行います。

SELECT city, count(*), max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 San Francisco |     2 |  46
(1 row)

(1)

LIKE演算子はパターンマッチを行います。これについては9.7で説明します。

集約とSQLWHEREHAVING句の間の相互作用を理解することが重要です。 WHEREHAVINGの基本的な違いは、WHEREが、グループや集約を演算する前に入力行を選択する(したがって、これはどの行を使用して集約演算を行うかを制御します)のに対し、HAVINGは、グループと集約を演算した後に、グループ化された行を選択する、ということです。 したがって、WHERE句は集約関数を持つことはできません。 集約を使用して、どの行をその集約の入力にするのかを決定することは意味をなしません。 一方で、HAVING句は常に集約関数を持ちます (厳密に言うと、集約を使用しないHAVING句を書くことはできますが、これが有用となることはほぼありません。 同じ条件はWHEREの段階でもっと効率良く使用できます)。

前の例ではWHERE内に都市名の制限を適用できます。 集約を行う必要がないからです。 これはHAVINGに制限を追加するよりも効率的です。 なぜならWHEREの検査で失敗する全ての行についてグループ化や集約演算が行われないからです。

集約計算に使用する行を選択するもう1つの方法は、集約ごとのオプションであるFILTERを使用することです。

SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     1 |  46
(2 rows)

FILTERWHEREによく似ていますが、結び付けられている特定の集約関数の入力からのみ行を削除する点が異なります。 ここでは、count集約はtemp_loが45未満の行のみを数えますが、max集約はすべての行に適用されるため、読み取り値46が検出されます。