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

Как и большинство других реляционных баз данных, QHB поддерживает агрегатные функции. Агрегатная функция вычисляет единственное результирующее значение из множества входных строк. Например, есть агрегаты для вычисления количества (COUNT), суммы (SUM), среднего (AVG), максимального (MAX) и минимального (MIN) набора строк.

Например, мы можем найти максимум температуры из всех низких температур (столбец temp_lo):

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, max(temp_lo)
    FROM weather
    GROUP BY city;
city          | max
--------------+-----
Hayward       |  37
San Francisco |  46
(2 rows)

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

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;
city   | max
---------+-----
Hayward |  37
(1 row)

получив те же результаты только для тех городов, у которых значения temp_lo ниже 40.

Наконец, если нам нужны только города, названия которых начинаются с «S», можно написать:

SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            (1)
    GROUP BY city
    HAVING max(temp_lo) < 40;
city  | max
------+-----
(0 rows)

(1): Синтаксис оператора LIKE (выполняющий сравнение по шаблону) рассматривается в разделе Функции и операторы.

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

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