Что такое агрегатные функции
Перейти к содержимому

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

  • автор:

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

SQL позволяет работать не только с отдельными строками, но и производить аналитику по всем строкам или по группе строк. Функции, возвращающие единственное значение для набора строк, называются агрегатными.

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

  • avg(выражение) — арифметическое среднее;
  • min(выражение) — минимальное значение выражения;
  • max(выражение) — маскимальное значение выражения;
  • sum(выражение) — сумма значений выражения;
  • count(*) — количество строк в результате запроса;
  • count(выражение) — количество значений выражения, не равных NULL .

Если в SQL запросе не используется GROUP BY , то значение агрегатной функции вычисляется по всем строкам, полученным в результате выполнения инструкций FROM и WHERE .

Чтобы вызвать агрегатную функцию необходимо указать ее в списке выборки (после SELECT ). Если в SELECT использована хотя бы одна агрегатная функция, то значениями других столбцов могут быть только вызовы агрегатных функций, либо константы. Попробуем воспользоваться агрегатными функциями на следующей таблице:

SELECT * FROM table1 
value
1
2
3
NULL
SELECT avg(value), min(value), max(value), sum(value), count(*) as count_total, count(value) as count_value FROM table1 
avg min max sum count_total count_value
2 1 3 6 4 3

При попытке вызова агрегатной функции вместе с выводом столбца таблицы в списке выборки возникнет ошибка:

SELECT product_id, max(count) FROM purchase_item 
column "purchase_item.product_id" must appear in the GROUP BY clause or be used in an aggregate function 

И это логично. Агрегатная функция должна вернуть одно значение на весь набор данных (в нашем запросе одну строку), а какое значение product_id взять из всех строк? Помимо агрегатных функций есть еще аналитические функции, которые не изменяют количество строк в результате выборки, но при этом позволяют вычислять значения по набору строк. С ними мы познакомимся позднее.

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

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

В табл. 5.16 перечислены агрегатные функции, поддерживаемые в PostgreSQL. Полный список агрегатных функций выводится в psql командой \da.

Таблица 5.16. Агрегатные функции

Функция Описание
avg( выражение’) Возвращает среднее арифметическое значений выражения для всех записей в группе
count ( выражение) Возвращает количество записей в группе, для которых значение выражения отлично от NULL
ma x ( выражение> Возвращает максимальное значение выражения в группе
mint выражение) Возвращает минимальное значение выражения в группе
stddev ( выражение) Возвращает среднеквадратичное отклонение значений выражения в группе
surrK выражение) Возвращает сумму значений выражения в группе
variance( выражение) Возвращает дисперсию значений выражения в группе

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

Агрегатные выражения

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

Если задано ключевое слово DISTINCT, функция обрабатывает только группы с уникальными значениями агрегатного выражения; группы с повторяющимися значениями игнорируются. Ключевое слово ALL, как и в команде SELECT, всего лишь явно указывает на тот факт, что выражение относится ко всем группам. В листинге 5.19 приведены примеры разных форм агрегатных выражений.

Листинг 5.19. Использование агрегатных выражений

booktown=# SELECT count(location) AS setjocations,

booktown-# count(ALL location) AS all_set_locations,

booktown-# count(DISTINCT location) AS unique_locations,

booktown-# count(*) AS all_rows booktown-# FROM subjects;

setjocations | all_set_locations | uniquejocations all_rows

У агрегатных выражений также имеется специальная форма, продемонстрированная в столбце al l_rows выходных данных листинга 5.19. Если при вызове агрегатной функции передается звездочка (*), это означает, что функция должна обрабатывать все поля, в том числе и обычно игнорируемые поля со значениями NULL. Поскольку таблица subjects содержит одну запись с полем location, равным NULL, результат подсчета записей по полю 1 ocati on отличается от результата подсчета по тому же нолю с использованием символа *.

Записи, у которых в агрегатное выражение входит NULL, не обрабатываются агрегатными функциями (исключением является функция count().

avg()

Функция avg() получает агрегатное выражение, результат которого относится к любому из числовых типов (numeric, bigint, smallint, real или double precision) или к типу interval.

Функция возвращает среднее арифметическое всех данных, описываемых выражением. Возвращаемое значение относится к типу numeric для выражения типа integer или к типу double precision для выражения типа real. В остальных случаях тип возвращаемого значения совпадает с типом выражения.

Примеры

booktown=# SELECT avg(cost) AS average_cost,

booktown-# avg(retail) AS average_price,

booktown-# avg(retail — cost) AS average_profit

booktown-# FROM stock:

average_cost | average_price | average_prof1t

24.8235294118 | 30.0088235294 5.1852941176

x>oktown=# SELECT avg(cost)

AS average_cost, p.name AS publisher

booktown-# FROM stock JOIN editions USING (isbn))

booktown-l JOIN publishers AS p (publisheMd)

booktown-# USING (publisheMd)

booktown-# GROUP BY p.name;

26.5000000000 | Ace Books

19.0000000000 | Books of Wonder

26.5000000000 I Doubleday

18.0000000000 | Henry Holt & Company. Inc.

23.0000000000 I Kids Can Press

23.0000000000 | Mojo Press

20.0000000000 I Penguin

23.0000000000 Random House

26.0000000000 | Watson-Guptill Publications

count()

Функция countO возвращает количество значений, для которых выражение отлично от NULL. Тип выражения не ограничивается. Следует помнить, что функция count () подсчитывает только значения, отличные от NULL, поэтому для получения осмысленных результатов используемое выражение не должно возвращать NULL для подсчитываемых записей.

Если при вызове count() передается символ *, функция просто подсчитывает все записи, в том числе и содержащие NULL.

Примеры

booktown=# SELECT countC*) FROM editions;

booktown=# SELECT count(isbn). p.name

booktown-# FROM editions JOIN publishers AS p (publisheMd)

booktown-# USING (publisheMd)

booktown-# GROUP BY p.name

booktown-# ORDER BY count DESC;

3 | Random House

1 | Books of Wonder

1 | Henry Holt & Company. Inc.

1 | Kids Can Press

1 | O’Reilly & Associates

1 | Watson-Gupti11 Publications

max()

Функция max() возвращает максимальное значение заданного выражения в группе. Результатом выражения может быть значение любого числового или строкового типа, а также типа даты или времени. Тип возвращаемого значения совпадает с типом выражения.

Примеры

booktown=# SELECT max(cost), max(retail) FROM stock;

booktown=# SELECT max(retail), p.name

booktown-# FROM (stock NATURAL JOIN editions)

booktown-# JOIN publishers AS p (publisher_id)

booktown-# USING (publisherjd)

booktown-# GROUP BY p.name

booktown-# ORDER BY max DESC;

45.95 i Ace Books 36.95 Doubleday 32.95

Random House 28.95 HarperCollins

28.95 I Watson-Guptill Publications

24.95 | Mojo Press

23.95 1 Henry Holt & Company. Inc. 23.95

Kids Can Press 21.95 Books of Wonder

min()

Функция min() возвращает минимальное значение заданного выражения в группе. Результатом выражения может быть значение любого числового или строкового типа, а также типа даты или времени. Тип возвращаемого значения совпадает с типом выражения.

Примеры

booktown=# SELECT min(cost). min(retail) FROM stock;

booktown=# SELECT min(retail), p.name

booktown-# FROM (stock NATURAL JOIN editions)

booktown-# JOIN publishers AS p (publisheMd)

booktown-# USING (publisheMd)

booktown-l GROUP BY p.name

booktown-# ORDER BY min ASC;

16.95 | Random House

21.95 I Books of Wonder

23.95 | Henry Holt & Company. Inc.

23.95 | Kids Can Press

24.95 | Mojo Press

28.95 I HarperCollins

28.95 | Watson-Guptill Publications

stddev()

Функция stddev О получает выражение, описывающее значения любого числового типа (numeri с, bigi nt, smal 1 i nt, real или doubl e preci si on), и возвращает среднеквадратичное отклонение для группы. Для вещественных выражений результат возвращается в виде значения типа double precision, а для остальных типов —в виде значения типа numeric.

booktown=# SELECT stddev(retail) FROM stock;

booktown=# SELECT stddev(retail), p.name

booktown-# FROM (stock NATURAL JOIN editions)

booktown-# JOIN publishers AS p ON (publisheMd = p.id)

booktown-# GROUP BY p.name

booktown-# ORDER BY stddev DESC

16.97 | Ace Books

16.97 | Roc 8.02 Random House

sum()

Функция sum() получает выражение, описывающее значения любого числового типа (numeric, bigint, smallint, real или double precision), и возвращает сумму значений в группе. Для выражений типа integer результат возвращается в виде значения типа numeric, а для выражений типа real — в виде значения типа double precision. В остальных случаях тип возвращаемого значения совпадает с типом выражения.

Примеры

booktown=# SELECT sum(stock) FROM stock;

booktown=# SELECT sum(stock). s.subject

booktown-# FROM ((stock NATURAL JOIN editions)

booktown(# JOIN books ON (books.id = bookjd))

booktown-f JOIN subjects AS s

booktown-# ON (books.subject_id = s.id)

booktown-# GROUP BY s.subject

booktown-# ORDER BY sum DESC;

166 I Science Fiction

91 | Children’s Books

variance()

Функция variance() получает выражение, описывающее значения любого числового типа (numeric, bigint, small int, real или double precision), и возвращает дисперсию для группы (stddevO в квадрате). Для вещественных выражений результат возвращается в виде значения типа double precision, а для остальных типов — в виде значения типа numeric.

Примеры

booktown=# SELECT variance(retaiT) FROM stock;

booktown=# SELECT varianceCretail), p.name

booktown-# FROM (stock NATURAL JOIN editions)

booktown-# JOIN publishers AS p

booktown-# ON (editions.pub!isher_id = p.id)

booktown-# GROUP BY p.name

booktown-# ORDER BY variance DESC

booktown-# LIMIT 4: variance name

288.00 | Ace Books 288.00 I Roc 64.33 !

Random House 32.00 | Doubleday

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

Агрегатные функции вычисляют некоторые скалярные значения в наборе строк. В MySQL есть следующие агрегатные функции:

  • AVG : вычисляет среднее значение
  • SUM : вычисляет сумму значений
  • MIN : вычисляет наименьшее значение
  • MAX : вычисляет наибольшее значение
  • COUNT : вычисляет количество строк в запросе

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

Выражения в функциях AVG и SUM должно представлять числовое значение (например, столбец, который хранит числовые значения). Выражение в функциях MIN , MAX и COUNT может представлять числовое или строковое значение или дату.

Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.

Avg

Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы.

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

CREATE TABLE Products ( Id INT AUTO_INCREMENT PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price DECIMAL NOT NULL ); INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price) VALUES ('iPhone X', 'Apple', 3, 76000), ('iPhone 8', 'Apple', 2, 51000), ('iPhone 7', 'Apple', 5, 32000), ('Galaxy S9', 'Samsung', 2, 56000), ('Galaxy S8', 'Samsung', 1, 46000), ('Honor 10', 'Huawei', 5, 28000), ('Nokia 8', 'HMD Global', 6, 38000)

Найдем среднюю цену товаров из базы данных:

SELECT AVG(Price) AS Average_Price FROM Products

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

Функция avg и поиск среднего значения в MySQL

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

SELECT AVG(Price) FROM Products WHERE Manufacturer='Apple'

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

SELECT AVG(Price * ProductCount) FROM Products

Count

Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:

SELECT COUNT(*) FROM Products

Функция count в MySQL и вычисление количества строк

Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:

SELECT COUNT(Manufacturer) FROM Products

Min и Max

Функции Min и Max вычисляют минимальное и максимальное значение по столбцу соответственно. Например, найдем минимальную цену среди товаров:

SELECT MIN(Price), MAX(Price) FROM Products

MIN и MAX в MySQL

Данные функции также игнорируют значения NULL и не учитывают их при подсчете.

Sum

Функция Sum вычисляет сумму значений столбца. Например, подсчитаем общее количество товаров:

SELECT SUM(ProductCount) FROM Products

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

SELECT SUM(ProductCount * Price) FROM Products

All и Distinct

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

SELECT COUNT(DISTINCT Manufacturer) FROM Products

По умолчанию вместо DISTINCT применяется оператор ALL , который выбирает все строки:

SELECT COUNT(ALL Manufacturer) FROM Products

В данном случае мы видим, что производители могут повторяться в таблице, так как некоторые товары могут иметь одних и тех же производителей. Поэтому чтобы подсчитать количество уникальных производителей, необходимо использовать оператор DISTINCT.

Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.

Комбинирование функций

Объединим применение нескольких функций:

SELECT COUNT(*) AS ProdCount, SUM(ProductCount) AS TotalCount, MIN(Price) AS MinPrice, MAX(Price) AS MaxPrice, AVG(Price) AS AvgPrice FROM Products

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

Как большинство других серверов реляционных баз данных, PostgreSQL поддерживает агрегатные функции. Агрегатная функция вычисляет единственное значение, обрабатывая множество строк. Например, есть агрегатные функции, вычисляющие: 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); НЕВЕРНО

но это не будет работать, так как агрегатную функцию 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;

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

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

Пред. Наверх След.
2.6. Соединения таблиц Начало 2.8. Изменение данных

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *