Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Довольно часто требуется узнать, сколько записей соответствует тому или иному запросу, какова сумма значений некоторого числового столбца, его максимальное, минимальное и среднее значение. Для этого служат так называемые итоговые (статистические, агрегатные) функции. Агрегатные функции – особый класс функций, применяемых сразу к нескольким записям набора данных, но возвращающим одно значение. Обычно агрегатные функции используются в запросах с группировкой, но также встречается их применение и в запросах без группирования. В этом случае агрегатная функция обрабатывает все записи итогового набора.
Далее перечислены агрегатные функции, поддерживаемые в PostgreSQL. Полный список агрегатных функций выводится в psql командой \da.
avg(выражение) Среднее арифметическое значений выражения для всех записей в группе
count(выражение) Количество записей в группе, для которых значение выражения отлично от NULL
max(выражение) Максимальное значение выражения в группе
min(выражение) Минимальное значение выражения в группе
stddev(выражение) Среднеквадратичное отклонение значений выражения в группе
sum(выражение) Сумма значений выражения в группе
variance(выражение) Дисперсия значений выражения в группе
Термин выражение означает любой столбец в итоговом наборе или любое выражение, выполняющее операцию с этим столбцом.
При использовании итоговых функций в списке столбцов в операторе SELECT заголовки соответствующих им столбцов в результатной таблице имеют вид Expr1001, Expr1002 и т.д. (или что-нибудь аналогичное, в зависимости от реализации SQL). Однако возможно задать заголовки для значений итоговых функций и других столбцов по своему усмотрению. Для этого достаточно после имени столбца в операторе SELECT указать выражение вида AS заголовок_столбца.
Count(параметр) – возвращает количество записей, указанных в параметре. Если требуется получить количество всех записей итогового набора, то в качестве параметра следует указать символ звездочки (*). Если в качестве параметра указать имя столбца, то функция вернет количество записей, в которых этот столбец имеет значения, отличные от NULL. Чтобы узнать, сколько различных значений содержит столбец, перед его именем следует указать ключевое слово DISTINCT.
Например:
SELECT count(location) AS set_locs,
count(ALL location) AS all_locs,
count(DISTINCT location) AS unique_locs,
count(*) AS all_rows
FROM subjects;
Результат запроса:
set_locs all_locs unique_locs all_rows
15 15 7 16
Примеры использования других агрегатных функций:
SELECT AVG(retail) AS средняя_цена
FROM stock;
SELECT MIN(reatail * 28.8) AS минимальная_цена_в_долларах
FROM stock;
SELECT SUM(retail) AS общая_стоимость_редких_книг
FROM stock
WHERE stock < 10;
Предложение GROUP BY в инструкции SELECT задает столбцы, используемые для формирования групп из выбранных строк. Строки каждой группы содержат одно и то же значение заданного столбца (столбцов). Выражение за ключевым словом GROUP BY может быть простым полем таблицы, оно также может представлять собой произвольную операцию с полем. При перечислении нескольких полей или выражений, разделенных запятыми, группировка записей производится по совпадению значений во всех перечисленных выражениях. Появление секции GROUP BY в запросе SQL приводит к тому, что все записи с одинаковым значением выражений, заданных в предложении GROUP BY, группируются в одну запись. Если предложение GROUP BY расположено после предложения WHERE, то создаются группы из строк, выбранных после применения WHERE.
Необходимо понимать, что все целевые поля, указанные в секции SELECT, участвующие в запросе с группировкой, но не указанные в секции GROUP BY, доступны лишь при выборке через агрегатную функцию. Другими словами, при включении предложения GROUP BY в инструкцию SELECT список выбора может состоять только из выражений, указанных в предложении GROUP BY или из агрегатных функций.
Выведем количество книг, хранящихся в базе данных booktown для каждого издательства:
SELECT name AS publisher,
count(isbn) AS number_of_books
FROM editions AS e INNER JOIN publishers AS p
ON (e.publisher_id = p.id)
GROUP BY name;
Секция GROUP BY указывает на то, что записи объединенного набора данных должны группироваться по имени издательства. Все записи с одинаковым названием издательства группируются, после чего функция count() подсчитывает в каждой группе количество непустых значений поля isbn и возвращает результат – количество записей, объединенных в каждую группу для одного издательства.
Получим количество книг, написанных авторами по каждой теме:
SELECT last_name, first_name, subject,
count(title) AS number_of_books
FROM books AS b INNER JOIN authors AS a
ON (b.author_id = a.id)
INNER JOIN subjects AS s
ON(b.subject_id = s.id)
GROUP BY last_name, first_name, subject;
Предложение HAVING, за которым следует условие отбора, определяет группы строк, которые включаются в результатную таблицу. Условие отбора будет применяться к каждой из групп, сформированных с помощью секции GROUP BY. Если некоторая группа не удовлетворяет условию отбора, то она не включается в результатную таблицу.
Разница между предложениями HAVING и WHERE заключается в том, что условие отбора, заданное в предложении WHERE, применяется к отдельным записям перед объединением их в группы, а условие отбора предложения HAVING применяется к группам строк. Секция WHERE не может содержать агрегатных функций. Условия же секции HAVING, наоборот, основаны на агрегатных функциях, а не на условиях для отдельных записей.
Выведем количество книг, хранящихся в базе данных booktown, для тех издательств, которые представлены двумя и более книгами:
SELECT name AS publisher,
count(isbn) AS number_of_books
FROM editions AS e INNER JOIN publishers AS p
ON (e.publisher_id = p.id)
GROUP BY name
HAVING count(isbn)>1;
Выполнить следующие запросы к базе данных booktown: