Самоучитель по SQL-сервер в Linux



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

Агрегатными функциями называется особый класс функций, применяемых сразу к нескольким записям набора данных, но возвращающим одно значение. Обычно агрегатные функции используются в запросах с группировкой по критерию, заданному в секции 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)




Книжный магазин