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

Евгения Кузина

Обзор

Агрегатные функции вычисляют результат по набору входных значений. Например, можно вычислить количество, сумму, среднее, максимальное и минимальное значение по набору строк.

Пример:

SELECT MIN(public_year) FROM book;

Результат:

 min
------
 1869

Чтобы использовать агрегатную функцию в выражении WHERE, используйте подзапрос. Следующий пример возвращает название самой старой книги:

SELECT title FROM book WHERE public_year = (SELECT min(public_year) FROM book);

Результат:

     title
---------------
 War and Peace

Также можно комбинировать агрегатные функции с GROUP BY. Например, получим самую старую книгу каждого жанра:

SELECT genre, min(public_year) public_year FROM book GROUP BY genre;

Результат:

      genre      | public_year
-----------------+-------------
 science fiction |        1895
 fantasy         |        1937
 novel           |        1869

Чтобы фильтровать полученные значения, используйте HAVING:

SELECT genre, min(public_year) public_year
    FROM book GROUP BY genre HAVING min(public_year) < 1900;

Результат:

      genre      | min
-----------------+------
 science fiction | 1895
 novel           | 1869
ПРИМЕЧАНИЕ
Существует фундаментальное различие между выражениями WHERE и HAVING. WHERE выбирает входные строки перед группировкой и вычислением агрегатных функций. WHERE определяет, какие строки включаются в агрегатные вычисления. HAVING выбирает строки после группировки и расчета агрегатных функций. WHERE не может содержать агрегатные функции, а HAVING всегда их содержит.
Если вам нужно использовать дополнительные условия, добавьте их в WHERE, а не HAVING. Этот подход позволяет избежать агрегатных вычислений для строк, которые не соответствуют дополнительным условиям.

Полный список предопределенных агрегатных функций можно найти по ссылке: Aggregate functions.

Синтаксис агрегатных выражений

Агрегатное выражение может записываться одним из следующих способов:

  1. Первая форма агрегатного выражения вызывает агрегатную функцию один раз для каждой входящей строки в соответствии с указанным выражением:

    <название_агрегатной_функции> (<выражение> [ , ... ] [<order_by_выражение> ])
    [FILTER (WHERE <значение_filter>)]

    Где:

    • <название_агрегатной_функции> — название агрегатной функции;

    • <выражение> — выражение значения, которое не содержит какого-либо агрегатного выражения или вызова оконной функции;

    • <order_by> — необязательный параметр, который указывает порядок сортировки;

    • <значение_filter> — необязательный параметр, содержащий условие фильтра.

       

      Пример:

      SELECT MIN(public_year) FROM book;

  2. Вторая форма агрегатного выражения содержит модификатор ALL. Поскольку ALL является модификатором по умолчанию, вторая форма эквивалентна первой.

    <название_агрегатной_функции> (ALL <выражение> [ , ... ] [<order_by_выражение>])
    [FILTER (WHERE <значение_filter>)]

  3. Третья форма вызывает агрегатную функцию один раз для каждого уникального значения выражения или набора уникальных значений для нескольких выражений.

     <название_агрегатной_функции> (DISTINCT <выражение> [ , ... ] [<order_by_выражение>])
    [FILTER (WHERE <значение_filter>)]

    Следующее выражение возвращает количество уникальных названий книг из таблицы с заказами:

    SELECT COUNT (DISTINCT(book_title)) FROM orders;

  4. Четвертая форма вызывает агрегатную функцию один раз для каждой строки. Поскольку выражение, задающее входящие значения, отсутствует, эта форма применима только к функции COUNT(*).

    <название_агрегатной_функции> ( * ) [FILTER (WHERE <значение_filter>)]

  5. Пятая форма используется с агрегатными функциями, выполняемыми над упорядоченным набором значений (например, array_agg, string_agg, rank и percentile). Результат выполнения этих функций зависит от порядка входных строк. Для этих функций параметр order_by является обязательным.

    <название_агрегатной_функции> ([<выражение> [ , ... ] ])
    WITHIN GROUP(<order_by_выражение>)
    [FILTER (WHERE <значение_filter>)]

    Выражение order_by имеет тот же синтаксис, что и ORDER BY, используемый на уровне запроса, но не может содержать номера и имена выходных столбцов.

    Пример:

    SELECT array_agg(field1 ORDER BY field2 DESC) FROM table1;

    Если вы вызываете агрегатную функцию с несколькими аргументами, ORDER BY должен быть помещен после всех аргументов:

    SELECT string_agg(field1, ',' ORDER BY field1) FROM table1;

    Если DISTINCT указан в дополнение к ORDER BY, выражение ORDER BY должно соответствовать аргументам агрегатной функции. Сортировка по значениям, не включенным в список DISTINCT, невозможна.

    Для агрегатной функции с упорядоченным набором входных значений, вы можете поместить выражение order_by внутрь выражения WITHIN GROUP(…​). В этом случае выражения в order_by вычисляются один раз для каждой входящей строки как обычные агрегатные аргументы, сортируются в соответствии с требованиями order_by и передаются агрегатной функции в качестве входных аргументов. Если order_by не находится в WITHIN GROUP, оно не рассматривается как аргумент агрегатной функции. Выражения аргументов, предшествующие WITHIN GROUP, если таковые имеются, называются прямыми аргументами, чтобы отличить их от агрегированных аргументов, перечисленных в выражении order_by. Прямые аргументы обрабатываются один раз за вызов агрегатной функции. Они могут содержать переменные, только если эти переменные сгруппированы по GROUP BY. Прямые аргументы обычно используются для единственного значения на расчет агрегации (например, доли процентилей). Если список прямых аргументов пуст, напишите ().

    Пример:

    SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;

Для всех типов агрегатных выражений, перечисленных выше, значение_filter является необязательным параметром. Если указан FILTER, в агрегатную функцию передаются только те входящие строки, для которых значение_filter вычисляется как true.

Например, имеется следующая таблица:

 id |            book_title               | public_year
----+-------------------------------------+-------------
  1 | Mrs. Dalloway                       |        1925
  2 | To the Lighthouse                   |        1927
  3 | To Kill a Mockingbird               |        1960
  4 | The Great Gatsby                    |        1925
  5 | The Lord of the Rings               |        1955
  8 | War and Peace                       |        1869
  7 | The Hobbit, or There and Back Again |        1937
  9 | Hyperion                            |        1989
 10 | The Time Machine                    |        1895
  6 | 1984                                |        1949

Выполним запрос с вызовом агрегатной функции COUNT(*) и заданным фильтром:

SELECT
    COUNT(*) AS unfiltered,
    COUNT(*) FILTER (WHERE public_year < 1900) AS filtered
FROM book;

Результат:

 unfiltered | filtered
------------+----------
         10 |        2

Большинство агрегатных функций игнорируют входящее значение NULL, а строки, в которых одно или несколько значений содержат NULL, отбрасываются. Это верно для всех предопределенных агрегатных функций, если не указано иное. Например, COUNT(*) возвращает общее количество входных строк, COUNT(field1) возвращает количество входных строк, где field1 не равно нулю, а COUNT(DISTINCT field1) возвращает количество уникальных строк с ненулевыми значениями field1.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней