Раздел GROUP BY

Раздел GROUP BYпозволяет выполнять группировку строк таблиц по определенным критериям. Для каждой группы можно выполнить специальные функции агрегирования, которые применяются ко всем строкам в группе. Одним из примеров использования раздела GROUP BYявляется суммирование однотипных значений.

Синтаксис раздела GROUP BYследующий:

GROUP BY [ALL]< условие_ группировки> [,..., n]

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

Аргумент < условие_ группировки) определяет условие группировки. Обычно в качестве условия группировки указывается имя столбца, однако в общем случае разрешается использование и выражений, включающих ссылки на столбцы.

Функции агрегирования позволяют выполнять статистическую обработку данных, подсчитывая количество, сумму, среднее значение и другие величины для всего набора данных. Во многих функциях агрегирования допускается использование ключевых слов ALL и DISTINCT. Ключевое слово АLL выполняет агрегирование всех строк исходного набора данных. При указании ключевого слова DISTINCT будет выполняться агрегирование только уникальных строк. Все повторяющиеся строки будут проигнорированы. По умолчанию выполняется агрегирование всех строк, то есть используется ключевое слово ALL. Далее приведены описания некоторых функций агрегирования.

AVGO

Эта функция вычисляет среднее значение для указанного столбца Функция имеет следующий синтаксис:

AVG ([ALL / DISTINCT] < выражение>)

При выполнении группировки (GROUP BY) вычисляет среднее значение для каждой группы. Если группировка не используется, то вычисляет среднее по всему столбцу. Например;

SELECT AVG (Количество_ часов) FROM Учебный_план

Результат запроса:

_ _ _ _ _ _ _ _ _ _

(1 row(s) affected)

Теперь рассмотрим пример использования функции AVG совместно с разделом GROUP BY при выполнении группировки по столбцу Семестр:

SELECT Семестр, AVG (количество_ часов)

FROM Учебный_ план

GROUP BY Семестр

Результат:

Семестр

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

1 50

2 54

3 46

4 39

5 37

6 27

7 34

8 44

9 32

(9 row(s) affected)

COUNT()

Функция подсчитывает количество строк в группе (при выполнении группировки) или количество строк результата запроса. Синтаксис функции COUNT следующий:

COUNT({[ALL / DISTINCT] < выражение> ] / ^*})

Параметр < выражение> в простейшем случае представляет собой имя столбца. Если обрабатываемая строка в соответствующем столбце содержит значение не NULL, то счетчик будет увеличен на единицу. Указание символа (*) предписывает считать общее количество строк независимо от того, содержат они значения NULI, или нет.

Пример использования функции COUNT

SELECT COUNT(*) AS 'Всего сотрудников',

СOUНТ(Телефон) AS 'С домашним телефоном'

FROM Кадровый_ состав

Этот запрос подсчитывает общее количество строк в таблицей также количество ненулевых значений в столбце Телефон.

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

Всего сотрудников С домашним телефоном

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

(l row (s) affected

Warning: Null value eliminated from aggregate

Пример использования функции COUNT() при выполнении группировки:

SELECT Должность, COUNT(*)

FROM Кадровый_ состав

GROUP BY Должность

Данный запрос возвращает количество строк в каждой группе столбца Должность:

Должность

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Ассистент 3

Доцент 4

Зав.каф. 2

Проф. 3

Ст.преп. 2

(5 row(s) affected)

МАХ ()

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

MAX ([ALL / DISTINCT] < выражение>)

Пример использования функции:

SELECT МАХ (Количество часов),

МАХ (количество_ часов/2)

FROM Учебный_ план

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

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

140 70

(1 row(s) affected)

MIN ()

Функция возвращает минимальное значение в указанном диапазоне. Синтаксис функции следующий:

MIN([ALL I DISTINCT] < выражение>)

Пример использования функции:

SELECT МIN (Количество_ часов)

FROM Учебный_ план

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

_ _ _ _ _ _ _ _ _

12 (l row (s) affected

SUM ()

Функция выполняет обычное суммирование значений в указанном диапазоне. В качестве такого диапазона может рассматриваться группа или весь набор строк (без использования раздела GROUP BY).

Синтаксис функции следующий:

SUM([ALL / DISTINCT] < выражение>)

В качестве примера просто суммируем значения в столбце Количество_ часов:

SELECT SUM (Количество_ часов), COUNT(^*),

SUM (Количество_ часов) /COUNT (*), AVG (Количество_ часов)

FROM Учебный_ план

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

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

694 89 41 41

(1 row(s) affected)

Теперь вновь обратимся к разделу SELECT и приведем пример группировки значений таблицы «Учебный план». Произведем группировку строк по семестрам (столбец Семестр) и подсчитаем общую нагрузку в часах за каждый семестр:

SELECT Семестр, SUM (количество_ часов) АЯ 'Нагрузка'

FROM (Учебный_ план]

GROUP BY Семестр

В результате выполнения запроса получен результат, показанный на рис. 7.24.

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

Рассмотрим теперь запрос, подсчитывающий количество экзаменов в каждом семестре:

SELECT Семестр, COUNT(^*.) AS Экзамены

FROM [Учебный_ план]

WHERE отчетность = Э

GROUP BY Семестр

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

Предложение группировки может содержать ключевое слово ALL. Назначение этого слова следующее. Нередко при выполнении группировки используется раздел WHERE, то есть группировка должна выполняться не над всеми строками, а лишь над определений частью строк. Результатом такого подхода может явиться то, что одна или более групп не будет содержать ни одной строки. Если группа не содержит ни одной строки, то по умолчанию эта группа не включается в результат выборки. Однако в некоторых ситуациях все же требуется, чтобы были выведены все группы, в том числе и не содержащие ни одной строки. Для этого и необходимо указывать в разделе GROUP BYключевое слово АLL. В этом случае будет выводиться список всех групп, но для групп, не содержащих строк, не будут выполняться функции агрегирования.

Рассмотрим это на примере. Для начала выполним группировку без использования ключевого слова ALL, но с вертикальной фильтрацией (с помощью раздела WHERE) — в таблице «Учебный_план» посчитаем для каждого семестра количество дисциплин с нагрузкой более 60 часов:

SELECT Семестр, COUNT(*) AS 'Количество часов > 60'

FROM [Учебный_ план]

WHERE Количество_ часов > 60

GROUP BY Семестр

Результат запроса показан на рис. 7.26.

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

Добавим в раздел GROUP BY ключевое слово ALL:

SELECT Семестр, COUNT(*) AS 'Количество часов > 60'

FROM [Учебный_ план]

WHERE Количеств_ часов > 60

GROUP BY ALL Семестр

Будет получен результат, показанный на рис. 7.27.