begriffs open source - ai-pg/blob - full-docs/txt/tutorial-agg.txt

   1
   2 2.7. Aggregate Functions #
   3
   4    Like most other relational database products, PostgreSQL supports
   5    aggregate functions. An aggregate function computes a single result
   6    from multiple input rows. For example, there are aggregates to compute
   7    the count, sum, avg (average), max (maximum) and min (minimum) over a
   8    set of rows.
   9
  10    As an example, we can find the highest low-temperature reading anywhere
  11    with:
  12 SELECT max(temp_lo) FROM weather;
  13
  14  max
  15 -----
  16   46
  17 (1 row)
  18
  19    If we wanted to know what city (or cities) that reading occurred in, we
  20    might try:
  21 SELECT city FROM weather WHERE temp_lo = max(temp_lo);     -- WRONG
  22
  23    but this will not work since the aggregate max cannot be used in the
  24    WHERE clause. (This restriction exists because the WHERE clause
  25    determines which rows will be included in the aggregate calculation; so
  26    obviously it has to be evaluated before aggregate functions are
  27    computed.) However, as is often the case the query can be restated to
  28    accomplish the desired result, here by using a subquery:
  29 SELECT city FROM weather
  30     WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
  31
  32      city
  33 ---------------
  34  San Francisco
  35 (1 row)
  36
  37    This is OK because the subquery is an independent computation that
  38    computes its own aggregate separately from what is happening in the
  39    outer query.
  40
  41    Aggregates are also very useful in combination with GROUP BY clauses.
  42    For example, we can get the number of readings and the maximum low
  43    temperature observed in each city with:
  44 SELECT city, count(*), max(temp_lo)
  45     FROM weather
  46     GROUP BY city;
  47
  48      city      | count | max
  49 ---------------+-------+-----
  50  Hayward       |     1 |  37
  51  San Francisco |     2 |  46
  52 (2 rows)
  53
  54    which gives us one output row per city. Each aggregate result is
  55    computed over the table rows matching that city. We can filter these
  56    grouped rows using HAVING:
  57 SELECT city, count(*), max(temp_lo)
  58     FROM weather
  59     GROUP BY city
  60     HAVING max(temp_lo) < 40;
  61
  62   city   | count | max
  63 ---------+-------+-----
  64  Hayward |     1 |  37
  65 (1 row)
  66
  67    which gives us the same results for only the cities that have all
  68    temp_lo values below 40. Finally, if we only care about cities whose
  69    names begin with “S”, we might do:
  70 SELECT city, count(*), max(temp_lo)
  71     FROM weather
  72     WHERE city LIKE 'S%'            -- (1)
  73     GROUP BY city;
  74
  75      city      | count | max
  76 ---------------+-------+-----
  77  San Francisco |     2 |  46
  78 (1 row)
  79
  80    (1)
  81
  82    The LIKE operator does pattern matching and is explained in
  83    Section 9.7.
  84
  85    It is important to understand the interaction between aggregates and
  86    SQL's WHERE and HAVING clauses. The fundamental difference between
  87    WHERE and HAVING is this: WHERE selects input rows before groups and
  88    aggregates are computed (thus, it controls which rows go into the
  89    aggregate computation), whereas HAVING selects group rows after groups
  90    and aggregates are computed. Thus, the WHERE clause must not contain
  91    aggregate functions; it makes no sense to try to use an aggregate to
  92    determine which rows will be inputs to the aggregates. On the other
  93    hand, the HAVING clause always contains aggregate functions. (Strictly
  94    speaking, you are allowed to write a HAVING clause that doesn't use
  95    aggregates, but it's seldom useful. The same condition could be used
  96    more efficiently at the WHERE stage.)
  97
  98    In the previous example, we can apply the city name restriction in
  99    WHERE, since it needs no aggregate. This is more efficient than adding
 100    the restriction to HAVING, because we avoid doing the grouping and
 101    aggregate calculations for all rows that fail the WHERE check.
 102
 103    Another way to select the rows that go into an aggregate computation is
 104    to use FILTER, which is a per-aggregate option:
 105 SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
 106     FROM weather
 107     GROUP BY city;
 108
 109      city      | count | max
 110 ---------------+-------+-----
 111  Hayward       |     1 |  37
 112  San Francisco |     1 |  46
 113 (2 rows)
 114
 115    FILTER is much like WHERE, except that it removes rows only from the
 116    input of the particular aggregate function that it is attached to.
 117    Here, the count aggregate counts only rows with temp_lo below 45; but
 118    the max aggregate is still applied to all rows, so it still finds the
 119    reading of 46.