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

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

В 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, в котором вид •соединения указывается с помощью операторов сравнения или логических опера­торов.

Соединение равенства

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

Синтаксис оператора выборки для этого способа соединения таблиц следующий:

SELECT Таблица1.поле1. Таблица2.поле2 {,… ТаблицаN. полеN}

FROM поле1, поле2 { полеN}

WHERE Таблица1.о6щее_поле1 = Таблица2.общее_поле1

{AND Таблица1.общее_поле2 = Таблица2.общее поле2}

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

Рассмотрим пример выборки из двух таблиц посредством соединения равенства. Выберем из таблицы Клиенты поля, содержащие сведения об именах клиентов, а из таблицы Продажи — поля, в которых содержатся сведения о покупках, сделанных клиентами. Для связывания таблиц воспользуемся общим для обеих таблиц по­лем Код клиента:

SELECT Клиенты.Фамилия. Клиенты.Имя,

Клиенты.Отчество. Продажи.Продано

FROM Клиенты, Продажи

WHERE Клиенты.[Код клиента]=Продажи.[Код клиента]

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

Рис. 29. Использование соединения равенства для выборки из двух таблиц

При связывании таблиц можно использовать предложение группировки. Изме­ним рассмотренный запрос (см. рис. 11.29) таким образом, чтобы результаты были сгруппированы по полям Фамилия, Имя, Отчество, и для каждого клиента выводи­лось суммарное количество покупок:

SELECT Клиенты.Фамилия. Клиенты.Имя. Клиенты.Отчество,

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

FROM Клиенты, Продажи, Товары

WHERE Клиенты.[Код клиента]=Продажи.[Код клиента]

GROUP BY Клиенты.Фамилия. Клиенты.Имя. Клиенты.Отчество

Результаты, возвращаемые этим запросом, приведены на рис. 30.

Рис.30. Пример группировки результатов выборки из двух таблиц

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

SELECT Клиенты.Фамилия. Клиенты.Имя. Клиенты.Отчество,

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

Товары.Наименование

FROM Клиенты, Продажи, Товары

WHERE Клиенты.[Код клиента]=Продажи.[Код клиента] AND

GROUP BY Клиенты.Фамилия. Клиенты.Имя. Клиенты.Отчество

Товары.Наименование

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

Рис. 31. Пример выборки из трех таблиц