Like most other relational database products,
<productname>PostgreSQL</productname> supports
<firstterm>aggregate functions</firstterm>.
An aggregate function computes a single result from multiple input rows.
For example, there are aggregates to compute the
<function>count</function>, <function>sum</function>,
<function>avg</function> (average), <function>max</function> (maximum) and
<function>min</function> (minimum) over a set of rows.
他のほとんどのリレーショナルデータベース製品同様、PostgreSQLは集約関数をサポートします。
集約関数は複数の入力行から1つの結果を計算します。
例えば、行の集合に対して、count
(総数)、sum
(総和)、avg
(平均)、max
(最大)、min
(最小)といった演算を行う集約があります。
As an example, we can find the highest low-temperature reading anywhere with: 例として、次のように全ての都市の最低気温から最も高い気温を求めることができます。
SELECT max(temp_lo) FROM weather;
max ----- 46 (1 row)
If we wanted to know what city (or cities) that reading occurred in, we might try: どの都市のデータなのかを知りたいとしたら、下記のような問い合わせを試行するかもしれません。
SELECT city FROM weather WHERE temp_lo = max(temp_lo); <lineannotation>WRONG</lineannotation> SELECT city FROM weather WHERE temp_lo = max(temp_lo); 間違い
but this will not work since the aggregate
<function>max</function> cannot be used in the
<literal>WHERE</literal> clause. (This restriction exists because
the <literal>WHERE</literal> clause determines which rows will be
included in the aggregate calculation; so obviously it has to be evaluated
before aggregate functions are computed.)
However, as is often the case
the query can be restated to accomplish the desired result, here
by using a <firstterm>subquery</firstterm>:
しかし、max
集約をWHERE
句で使用することができませんので、このコマンドは動作しません
(WHERE
句はどの行を集約処理に渡すのかを決定するものです。したがって、集約関数の演算を行う前に評価されなければならないことは明らかです。
このためにこの制限があります)。
しかし、よくあることですが、問い合わせを書き直すことで、意図した結果が得られます。
これには以下のような副問い合わせを使用します。
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city --------------- San Francisco (1 row)
This is OK because the subquery is an independent computation that computes its own aggregate separately from what is happening in the outer query. 副問い合わせは、外側の問い合わせで起こることとは別々に集約を計算する独立した演算ですので、この問い合わせは問題ありません。
Aggregates are also very useful in combination with <literal>GROUP
BY</literal> clauses. For example, we can get the number of readings
and the maximum low temperature observed in each city with:
また、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)
which gives us one output row per city. Each aggregate result is
computed over the table rows matching that city.
We can filter these grouped
rows using <literal>HAVING</literal>:
ここには都市ごとに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)
which gives us the same results for only the cities that have all
<structfield>temp_lo</structfield> values below 40. Finally, if we only care about
cities whose
names begin with <quote><literal>S</literal></quote>, we might do:
このコマンドは上と同じ計算を行うものですが、全ての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)
The <literal>LIKE</literal> operator does pattern matching and
is explained in <xref linkend="functions-matching"/>.
|
It is important to understand the interaction between aggregates and
<acronym>SQL</acronym>'s <literal>WHERE</literal> and <literal>HAVING</literal> clauses.
The fundamental difference between <literal>WHERE</literal> and
<literal>HAVING</literal> is this: <literal>WHERE</literal> selects
input rows before groups and aggregates are computed (thus, it controls
which rows go into the aggregate computation), whereas
<literal>HAVING</literal> selects group rows after groups and
aggregates are computed. Thus, the
<literal>WHERE</literal> clause must not contain aggregate functions;
it makes no sense to try to use an aggregate to determine which rows
will be inputs to the aggregates. On the other hand, the
<literal>HAVING</literal> clause always contains aggregate functions.
(Strictly speaking, you are allowed to write a <literal>HAVING</literal>
clause that doesn't use aggregates, but it's seldom useful. The same
condition could be used more efficiently at the <literal>WHERE</literal>
stage.)
集約とSQLのWHERE
とHAVING
句の間の相互作用を理解することが重要です。
WHERE
とHAVING
の基本的な違いは、WHERE
が、グループや集約を演算する前に入力行を選択する(したがって、これはどの行を使用して集約演算を行うかを制御します)のに対し、HAVING
は、グループと集約を演算した後に、グループ化された行を選択する、ということです。
したがって、WHERE
句は集約関数を持つことはできません。
集約を使用して、どの行をその集約の入力にするのかを決定することは意味をなしません。
一方で、HAVING
句は常に集約関数を持ちます
(厳密に言うと、集約を使用しないHAVING
句を書くことはできますが、これが有用となることはほぼありません。
同じ条件はWHERE
の段階でもっと効率良く使用できます)。
In the previous example, we can apply the city name restriction in
<literal>WHERE</literal>, since it needs no aggregate. This is
more efficient than adding the restriction to <literal>HAVING</literal>,
because we avoid doing the grouping and aggregate calculations
for all rows that fail the <literal>WHERE</literal> check.
前の例ではWHERE
内に都市名の制限を適用できます。
集約を行う必要がないからです。
これはHAVING
に制限を追加するよりも効率的です。
なぜならWHERE
の検査で失敗する全ての行についてグループ化や集約演算が行われないからです。
Another way to select the rows that go into an aggregate
computation is to use <literal>FILTER</literal>, which is a
per-aggregate option:
集約計算に使用する行を選択するもう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)
<literal>FILTER</literal> is much like <literal>WHERE</literal>,
except that it removes rows only from the input of the particular
aggregate function that it is attached to.
Here, the <literal>count</literal> aggregate counts only
rows with <literal>temp_lo</literal> below 45; but the
<literal>max</literal> aggregate is still applied to all rows,
so it still finds the reading of 46.
FILTER
はWHERE
によく似ていますが、結び付けられている特定の集約関数の入力からのみ行を削除する点が異なります。
ここでは、count
集約はtemp_lo
が45未満の行のみを数えますが、max
集約はすべての行に適用されるため、読み取り値46が検出されます。