Агрегатные функции

примечание

Эта страница переведена при помощи нейросети GigaChat.

Как и большинство других продуктов реляционных баз данных, PostgreSQL поддерживает агрегатные функции. Агрегатная функция вычисляет один результат из нескольких входных строк. Например, существуют агрегатные функции для вычисления count, sum, avg (average), max (maximum) and min (minimum) для набора строк.

Например, можно найти самое высокое показание низкой температуры в любом месте с:

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, count(*), max(temp_lo)
    FROM weather
    GROUP BY city;

Результат:

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

Это дает одну выходную строку на город. Каждый суммарный результат вычисляется над строками таблицы, соответствующими данному городу. Можно отфильтровать эти сгруппированные строки с помощью 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)

Важно понимать взаимодействие между агрегатами и пунктами WHERE и HAVING в SQL. Фундаментальное различие между WHERE и HAVING заключается в следующем: WHERE выбирает входные строки до вычисления групп и агрегатов (таким образом, он контролирует, какие строки попадают в вычисления агрегатов), тогда как HAVING выбирает групповые строки после вычисления групп и агрегатов. Таким образом, предложение WHERE не должно содержать агрегатных функций; бессмысленно пытаться использовать агрегат для определения того, какие строки будут входить в агрегаты. С другой стороны, предложение HAVING всегда содержит агрегатные функции. (Строго говоря, можно написать предложение HAVING, в котором не используются агрегаты, но это редко бывает полезно. То же условие можно более эффективно использовать на этапе WHERE).

В предыдущем примере можно применить ограничение на название города в WHERE, поскольку оно не требует агрегации. Это более эффективно, чем добавление ограничения в HAVING, поскольку избегаем выполнения вычислений группировки и агрегации для всех строк, не прошедших проверку WHERE.

Другой способ выбрать строки, которые пойдут в агрегированные вычисления, – использовать 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)

FILTER во многом похож на WHERE, за исключением того, что он удаляет строки только со входа конкретной агрегатной функции, к которой он присоединен. Здесь агрегат count учитывает только строки с temp_lo ниже 45; но агрегат max по-прежнему применяется ко всем строкам, поэтому он все равно находит значение 46.