Агрегатные функции
Агрегатными функциями называется особый класс функций, применяемых сразу к нескольким записям набора данных, но возвращающим одно значение. Обычно агрегатные функции используются в запросах с группировкой по критерию, заданному в секции 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
15 15 | 7 16
(1 row)
У агрегатных выражений также имеется специальная форма, продемонстрированная в столбце al l_rows выходных данных листинга 5.19. Если при вызове агрегатной функции передается звездочка (*), это означает, что функция должна обрабатывать все поля, в том числе и обычно игнорируемые поля со значениями NULL. Поскольку таблица subjects содержит одну запись с полем location, равным NULL, результат подсчета записей по полю 1 ocati on отличается от результата подсчета по тому же нолю с использованием символа *.
ВНИМАНИЕ
Записи, у которых в агрегатное выражение входит NULL, не обрабатываются агрегатными функциями (исключением является функция count().
avg()
Синтаксис: 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
(1 row)
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;
average_cost | publisher
26.5000000000 | Ace Books
19.0000000000 | Books of Wonder
26.5000000000 I Doubleday
25.0000000000 | HarperCollins
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.5000000000 | Roc
26.0000000000 | Watson-Guptill Publications
(11 rows)
count()
Синтаксис:
count(выражение)
Функция countO возвращает количество значений, для которых выражение отлично от NULL. Тип выражения не ограничивается. Следует помнить, что функция count () подсчитывает только значения, отличные от NULL, поэтому для получения осмысленных результатов используемое выражение не должно возвращать NULL для подсчитываемых записей.
Если при вызове count() передается символ *, функция просто подсчитывает все записи, в том числе и содержащие NULL.
Примеры
booktown=# SELECT countC*) FROM editions;
count
17
(1 row)
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;
count | name
3 | Random House
2 | Ace Books
2 | Doubleday
2 Roc
1 | Books of Wonder
1 | HarperCollins
1 | Henry Holt & Company. Inc.
1 | Kids Can Press
1 | Mojo Press
1 | O’Reilly & Associates
1 | Penguin
1 | Watson-Gupti11 Publications
(12 rows)
max()
Синтаксис:
max(выражение)
Функция max() возвращает максимальное значение заданного выражения в группе. Результатом выражения может быть значение любого числового или строкового типа, а также типа даты или времени. Тип возвращаемого значения совпадает с типом выражения.
Примеры
booktown=# SELECT max(cost), max(retail) FROM stock;
max max
36.00 I 46.95
(1 row)
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;
max | name
46.95 | Roc
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
24.95 | Penguin
23.95 1 Henry Holt & Company. Inc. 23.95
Kids Can Press 21.95 Books of Wonder
(11 rows)
min()
Синтаксис:
min (выражение)
Функция min() возвращает минимальное значение заданного выражения в группе. Результатом выражения может быть значение любого числового или строкового типа, а также типа даты или времени. Тип возвращаемого значения совпадает с типом выражения.
Примеры
booktown=# SELECT min(cost). min(retail) FROM stock;
min | min
16.00 16.95
(1 row)
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;
miP 1 ..mme._
16.95 | Random House
21.95 Ace Books
21.95 I Books of Wonder
22.95 | Roc
23.95 | Henry Holt & Company. Inc.
23.95 | Kids Can Press
24.95 | Mojo Press
24.95 I Penguin
28.95 | Doubleday
28.95 I HarperCollins
28.95 | Watson-Guptill Publications
(11 rows)
stddev()
Синтаксис:
stddev(выражение)
Функция stddev О получает выражение, описывающее значения любого числового типа (numeri с, bigi nt, smal 1 i nt, real или doubl e preci si on), и возвращает среднеквадратичное отклонение для группы. Для вещественных выражений результат возвращается в виде значения типа double precision, а для остальных типов —в виде значения типа numeric.
Примеры
booktown=# SELECT stddev(retail) FROM stock;
stddev
8.46
(1 row)
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
booktown-# LIMIT 4:
stddev | name
16.97 | Ace Books
16.97 | Roc 8.02 Random House
5.66 | Doubleday
(4 rows)
sum()
Синтаксис:
sum (выражение)
Функция sum() получает выражение, описывающее значения любого числового типа (numeric, bigint, smallint, real или double precision), и возвращает сумму значений в группе. Для выражений типа integer результат возвращается в виде значения типа numeric, а для выражений типа real — в виде значения типа double precision. В остальных случаях тип возвращаемого значения совпадает с типом выражения.
Примеры
booktown=# SELECT sum(stock) FROM stock;
sum
«BOB»
(1 row)
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;
189 | Horror
166 I Science Fiction
91 | Children’s Books
28 | Drama
18 | Classics
16 | Arts
(6 rows)
variance()
Синтаксис: variance(выражение)
Функция variance() получает выражение, описывающее значения любого числового типа (numeric, bigint, small int, real или double precision), и возвращает дисперсию для группы (stddevO в квадрате). Для вещественных выражений результат возвращается в виде значения типа double precision, а для остальных типов — в виде значения типа numeric.
Примеры
booktown=# SELECT variance(retaiT) FROM stock;
variance
71.60
(1 row)
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
(4 rows)