Movatterモバイル変換

Like most other relational database products,PostgreSQL supportsaggregate functions. An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute thecount,sum,avg (average),max (maximum) andmin (minimum) over a set of rows.

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);WRONG

but this will not work since the aggregatemax cannot be used in theWHERE clause. (This restriction exists because theWHERE 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 asubquery:

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 withGROUP BY clauses. For example, we can get the number of readings and the maximum low temperature observed in each city with:

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 usingHAVING:

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 alltemp_lo values below 40. Finally, if we only care about cities whose names begin with“S”, we might do:

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)

It is important to understand the interaction between aggregates andSQL'sWHERE andHAVING clauses. The fundamental difference betweenWHERE andHAVING is this:WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereasHAVING selects group rows after groups and aggregates are computed. Thus, theWHERE 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, theHAVING clause always contains aggregate functions. (Strictly speaking, you are allowed to write aHAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at theWHERE stage.)

In the previous example, we can apply the city name restriction inWHERE, since it needs no aggregate. This is more efficient than adding the restriction toHAVING, because we avoid doing the grouping and aggregate calculations for all rows that fail theWHERE check.

Another way to select the rows that go into an aggregate computation is to useFILTER, which is a per-aggregate option:

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)

FILTER is much likeWHERE, except that it removes rows only from the input of the particular aggregate function that it is attached to. Here, thecount aggregate counts only rows withtemp_lo below 45; but themax aggregate is still applied to all rows, so it still finds the reading of 46.