他のほとんどのリレーショナルデータベース製品同様、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); WRONG
しかし、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, max(temp_lo)
FROM weather
GROUP BY city;
city | max ---------------+----- Hayward | 37 San Francisco | 46 (2 rows)
ここには都市毎に1行の出力があります。 それぞれの集約結果は都市に一致するテーブル行全体に対する演算結果です。 以下のように、HAVINGを使用すると、グループ化された行にフィルタを掛けることができます。
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;
city | max ---------+----- Hayward | 37 (1 row)
このコマンドは上と同じ計算を行うものですが、全てのtemp_loの値が40未満の都市のみを出力します。 最後になりますが、"S"から始まる名前の都市のみを対象にしたい場合は、以下を行います。
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%'(1)
GROUP BY city
HAVING max(temp_lo) < 40;
集約とSQLのWHEREとHAVING句の間の相互作用を理解することが重要です。 WHEREとHAVINGの基本的な違いを以下に記します。 WHEREは、グループや集約を演算する前に入力行を選択します(従って、これはどの行を使用して集約演算を行うかを制御します)。 一方、HAVINGは、グループと集約を演算した後に、グループ化された行を選択します。 従って、WHERE句は集約関数を持つことはできません。 集約を使用して、どの行をその集約の入力にするのかを決定することは意味を成しません。 一方で、HAVING句は常に集約関数を持ちます(厳密にいうと、集約を使用しないHAVING句を書くことができますが、これは無駄です。同じ条件はWHEREの段階でもっと効率良く使用できます)。
WHERE内に都市名制限を適用できることに注目して下さい。 集約を行う必要がないからです。 WHEREの検査で失敗する全ての行に対するグループ化や集約演算が行われませんので、HAVINGに制限を追加するよりもより効率的です。