Использование группировок записей (GROUP BY)

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

GROUP BY столбец [,столбец1...]

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

ПРИМЕР

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

SELECT FundCode, COUNT (BookCode) AS count, SUM(Cost) AS sum

FROM BookInventaryNumbers

GROUP BY FundCode

ORDER BY FundCode

Нет необходимости включать имена столбцов BookCode и Cost в список фразы GROUP BY, поскольку в списке предложения SELECT они используются только в обобщающих функциях. В то же время, столбец FundCode в списке предложения SELECT не связан ни с какой обобщающей функцией и по этой причине обязательно должен быть указан в фразе GROUP BY.

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

Таблица 11.7

FundCode	Count	Sum
		84.89
		66.88

Концептуально, при обработке этого запроса выполняются следующие действия.

1. Строки таблицы BookInventaryNumbers распределяются в группы в соответствии со значениями в столбце номера кода фонда (FundCode). В пределах каждой из групп оказываются данные о кодах книг одного из фондов. В нашем примере будут созданы две группы.

FundCode	BookCode	Cost			СOUNT(BookCode)	SUM(Cost)
		15.56		84.89
		22.33
		34.01
		12.99
		56.78			66.88
		10.10

2. Для каждой из групп вычисляется общее количество строк, равное количеству кодов книг, а также сумма значений в столбце Cost, которая и является интересующей нас суммой стоимости книг каждого фонда. Затем генерируется единственная сводная строка для всей группы исходных строк.

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

11.4. Предложение HAVING – наложение ограничений на группировку записей.

Если нужно в результирующем наборе данных выдавать агрегацию не по всем группам, а только по тем из них, которые отвечают некоторому условию, то после предложения GROUP BY указывают предложение

HAVING < условия_поиска >

где условия поиска указываются по тем же правилам, что и условия поиска для предложения WHERE, за важным исключением: в условии поиска предложения HAVING можно указывать агрегатные функции, чего нельзя делать в условии поиска для WHERE.

Стандарт ISO требует, чтобы имена столбцов, используемые во фразе HAVING, обязательно присутствовали в списке фразы GROUP BY или применялись в обобщающих функциях. На практике условия поиска во фразе HAVING всегда включают, по меньшей мере, одну обобщающую функцию, в противном случае эти условия поиска должны быть помещены в предложение WHERE и применяться для отбора отдельных строк.

ПРИМЕР

Для каждого книжного фонда, где хранится более двух книг, определите количество книг, а также их суммарную стоимость.

SELECT FundCode, COUNT (BookCode) AS Count, SUM(Cost) AS Sum

FROM BookInventaryNumbers

GROUP BY FundCode

HAVING COUNT (BookCode) > 2

ORDER BY FundCode

Этот пример аналогичен предыдущему, но здесь используются дополнительные ограничения, указывающие на то, что нас интересуют сведения только о тех фондах, в которых хранится больше двух книг. Подобное требование налагается на группы, поэтому в запросе следует использовать фразу HAVING.

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

Таблица 11.8

FundCode	Count	Sum
		84.89

ЗАМЕЧАНИЕ. Следует всегда помнить, чем условие в HAVING отличается от условия в WHERE:

· HAVING исключает из результирующего набора данных группы с результатами агрегированных значений;

· WHERE исключает из расчета агрегатных значений по группировкам записи, не удовлетворяющие условию;

· в условии поиска WHERE нельзя указывать агрегатные функции.

Дата добавления: 2014-01-07