Функции агрегирования

Функциями агрегирования называются функции, которые позволяют определить количество записей в таблице, количество значений в столбце таблицы, найти минимальное, максимальное и среднее значение для столбца таблицы, вычислить сумму данных для столбца. Таким образом, агрегирующие функции обеспечивают получение некоторой обобщенной информации.

В SQL определены следующие стандартные функции агрегирования:

· COUNT — выполняет подсчет записей в таблице пли подсчет ненулевых значе­ний в столбце таблицы;

· SUM — возвращает сумму содержащихся в столбце значений;

· MIN — возвращает минимальное значение в столбце;

· МАХ — возвращает максимальное значение в столбце;

· AVG — вычисляет среднее значение для содержащихся в столбце значений.

В качестве примера рассмотрим таблицу Продажи. Подсчитаем количество запи­сей в поле Продано, минимальное и максимальное количество проданных товаров, общую сумму проданных товаров и среднее значение проданных товаров. Для это­го нужен следующий запрос:

SELECT COUNT(продано) AS [Всего записей].

MIN(Продано) AS miп.

МАХ(Продано) AS max,

SUM(Продано) AS [Всего продано].

AVG(Продано) AS [Среднее количество продаж]

FROM Продажи

Результат выполнения этого запроса показан на рис. 22.

Со всеми функциями агрегирования можно использовать параметр DISTINCT. В этом случае выполняется обобщение информации только для различающихся строк.

Рис. 22. Пример использования функций агрегирования

Как правило, использовать параметр DISTINCT с агрегирующими функциями не име­ет смысла, поскольку при подсчете обобщенных данных обычно приходится учиты­вать все записи, а не только уникальные.

Группировка данных — это объединение записей в соответствии со значениями не­которого поля. Для группировки результатов выборки совместно с оператором SELECT используется предложение GROUP BY. Данное предложение должно следовать после предложения WHERE, но перед предложением ORDER BY. После ключевых слов GROUP BY указывается список полей, включенных в выборку с помощью оператора SELECT. Причем нужно обязательно указывать все отбираемые ноля (за исключением полей, относящихся к агрегирующим функциям), хотя порядок их перечисления после предложения GROUP BY может не соответствовать порядку списка после слова SELECT.

Синтаксис оператора SELECT с предложением GROUP BY следующий:

SELECT поле1. поле2 полеN

FROM Таблица1 {. Таблица2 ТаблицаN}

WHERE условие

GROUP BY поле1. поле2 полеN

ORDER BY поле1 {ASC | DESC}

Например, если выбрать из таблицы Товары два поля — Наименование и Категория, а затем сгруппировать их с помощью следующего запроса, то результат выборки будет упорядочен по значению первого поля, указанного в предложении GROUP BY (рис. 23):

SELECT Наименование. Категория

FROM Товары

GROUP BY Категория. Наименование

Рис. 23. Пример группировки данных

Если в запросе выбрать только одно поле и выполнить для него группировку, то результирующая выборка не будет содержать дублирующих друг друга записей. Например, если выполнить запрос, аналогичный предыдущему (см. рис. 23), но выбрать только поле Категория, как показано ниже, то выборка будет содержать только три записи (рис. 24):

SELECT Категория

FROM Товары

GROUP BY Категория

Рис. 24. Результат группировки одного поля

В этом случае группировка дает такой же результат, как применение оператора SELECTс параметром DISTINCTи предложением ORDER BY.

Поскольку применение одного предложения GROUP BY не дает никакого нового результата, то совместно с ним, как правило, используются функции агрегирова­ния. В этом случае они служат для вычисления итоговых значений по отдель­ным группам данных.

Рис. 26. Пример сортировки результатов выборки с помощью предложения GROUP BY

Например, чтобы подсчитать количество покупок товаров, сделанных каждым из клиентов, используется следующий запрос:

SELECT [Код клиента].

SUM(Продано) AS [Количество покупок]

FROM Продажи

GROUP BY [Код клиента]

Результат выполнения такого запроса приведен на рис. 25.

Рис.25. Использование агрегирующих функций при группировке данных

Результаты группировки можно упорядочить с помощью предложения ORDER BY, а в операторе SELECT,содержащем предложение группировки, использовать пред­ложение WHERE.Для иллюстрации этой возможности модифицируем предыду­щий запрос следующим образом: выберем только тех клиентов, которые сделали за один раз более 10 покупок, и упорядочим результаты выборки по возраста­нию.

SELECT [Код клиента].

SUM(Продано) AS [Количество покупок]

FROM Продажи

WHERE Продано>10

GROUP BY [Код клиента]

ORDER BY 2 DESC

Результат выполнения данного запроса изображен на рис. 26.

Для задания ограничений на создаваемые группы совместно с ключевым словом GROUP BY может использоваться предложение HAVING. Оно должно следовать пос­ле предложения GROUP BY, но до предложения ORDER BY (если оно присутствует ,в запросе).

В предыдущем примере в качестве условия было задано количество покупок за один раз. Если мы хотим установить ограничение на общее количество покупок, то нужно применить предложение HAVING:

SELECT [Код клиента].

SUМ(Продано) AS [Количество покупок]

FROM Продажи

WHERE Продано>10

GROUP BY [Код клиента]

HAVING SUM(Продано)>20

ORDER BY 2 DESC

Результат выполнения запроса приведен на рис. 27.

Рис. 27. Пример использования предложения HAVING

В предложении HAVING не обязательно использовать только те поля, которые заданы в списке оператора SELECT.

Модифицируем рассмотренный пример (см. рис. 27) таким образом, чтобы ограничение было наложено не на количество купленных товаров, а на их стои­мость:

SELECT [Код клиента].

SUM(Продано) AS [Количество покупок]

FROM Продажи

GROUP BY [Код клиента]

HAVING SUM(Продано*Цена)>25000

ORDER BY 2 DESC

Данный запрос учитывает клиентов, купивших товаров более чем на 25 000, и ото­бражает количество сделанных ими покупок (рис.28).

В предложении GROUP BY, в отличие от предложения ORDER BY, нельзя вместо имен выбранных полей использовать их порядковые номера в списке оператора SELECT.

Рис. 28. Пример запроса с предложением HAVING

Как правило, информация, хранящаяся в базе данных, содержится в нескольких связанных между собой таблицах. Язык SQL позволяет создавать запросы, извле­кающие данные из нескольких таблиц. При этом выполняется операция соедине­ния нескольких таблиц с целью поиска в них запрошенных данных.

Существует несколько способов соединения таблиц. Наиболее часто встречаются следующие:

· соединение равенства;

· соединение неравенства;

· внешние соединения.

Для задания вида соединения используется предложение WHERE, в котором вид •соединения указывается с помощью операторов сравнения или логических опера­торов.