|
|
|
Агрегатные функции Агрегатными функциями называется особый класс функций, применяемых сразу к нескольким записям набора данных, но возвращающим одно значение. Обычно агрегатные функции используются в запросах с группировкой по критерию, заданному в секции GROUP BY, но также встречается их применение в запросах, у которых целевой список состоит только из агрегатных функций. В этом случае агрегатная функция обрабатывает все записи итогового набора. В табл. 5.16 перечислены агрегатные функции, поддерживаемые в PostgreSQL. Полный список агрегатных функций выводится в psql командой \da. Таблица 5.16. Агрегатные функции
Ниже приведены более подробные описания всех агрегатных функций с конкретной информацией об особенностях использования, примерами и допустимыми типами входных данных. В дальнейших описаниях термин выражение означает любой идентификатор в итоговом наборе или любое действительное выражение, выполняющее операцию с этим идентификатором. Агрегатные выражения При вызове агрегатной функции передается агрегатное выражение, применяемое к записям, созданным командой 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) |
|
|