2.7. Функции агрегации#
2.7. Функции агрегации
Как и большинство других продуктов реляционных баз данных, Tantor BEподдерживает функции агрегации. Агрегатная функция вычисляет единственный результат из нескольких входных строк. Например, существуют агрегаты для вычисления функций count, sum,
avg (среднее значение), max (максимум) и min (минимум) для набора строк.
В качестве примера, мы можем найти самое высокое значение низкой температуры где угодно с помощью:
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.