Агрегатные функции
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, хотя в принципе устанавливать псевдоним необязательно.
На этапе выборки можно применять фильтрацию. Например, найдем среднюю цену для товаров определенного производителя:
SELECT AVG(Price) FROM Products WHERE Manufacturer='Apple'
Также можно находить среднее значение для более сложных выражений. Например, найдем среднюю сумму всех товаров, учитывая их количество:
SELECT AVG(Price * ProductCount) FROM Products
Count
Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:
SELECT COUNT(*) FROM Products
Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:
SELECT COUNT(Manufacturer) FROM Products
Min и Max
Функции Min и Max вычисляют минимальное и максимальное значение по столбцу соответственно. Например, найдем минимальную цену среди товаров:
SELECT MIN(Price), MAX(Price) FROM Products
Данные функции также игнорируют значения 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. Изменение данных |