Лекция 14: Извлечение данных при помощи Transact-SQL
Предложение GROUP BY
Предложение GROUP BY применяется после предложения WHERE и означает, что строки набора результатов должны быть сгруппированы в соответствии с данными в колонке группировки. Если в предложении SELECT используется агрегатная функция, то для каждой группы вычисляется и отображается в выводе итоговое агрегатное значение. Агрегатная функция выполняет вычисления и возвращает значение. (Про агрегатные функции см. раздел "Агрегатные функции" далее.)
Примечание. В предложении GROUP BY в качестве колонок группировки должны быть заданы все колонки из списка выборки (кроме колонок, применяемых для агрегатных функций), в противном случае SQL Server выдаст сообщение об ошибке. Если бы это правило не соблюдалось, результаты нельзя было бы выдать в разумном виде, поскольку колонка, заданная в GROUP BY, должна группировать каждую колонку в списке выборки.Предложение GROUP BY особенно полезно, когда в предложении SELECT имеется агрегатная функция. Давайте рассмотрим пример оператора SELECT, применяющего предложение GROUP BY для получения сведений об общем количестве проданных книг для каждого из названий книг:
SELECT title_id, SUM(qty) FROM sales GROUP BY title_id GO
Будет выдан набор результатов, содержащий 16 строк:
title_id ---------------------- BU1032 15 BU1111 25 BU2075 35 BU7832 15 MC2222 10 MC3021 40 PC1035 30 PC8888 50 PS1372 20 PS2091 108 PS2106 25 PS3333 15 PS7777 25 TC3218 40 TC4203 20 TC7777 20
Этот запрос не содержит предложения WHERE – оно не нужно. Набор результатов состоит из колонки title_id (идентификатор названия книги) и итоговой колонки, не имеющей заголовка. Для каждого отдельного названия книги будет подсчитано общее количество экземпляров этой книги, это число будет показано в итоговой колонке. Например, пусть значение BU1032 колонки title_id встретится в таблице sales (продажи) два раза, первый раз оно будет обозначать продажу 5 экземпляров книги (колонка qty будет иметь значение 5), а во второй раз будет обозначать продажу книг по другому заказу, на этот раз будет продано 10 экземпляров книги. Агрегатная функция SUM произведет суммирование этих двух продаж, отсюда и получится, что общее количество проданных экземпляров равно 15, что и будет показано в итоговой колонке. Если вы хотите, чтобы итоговая колонка имела заголовок, воспользуйтесь ключевым словом AS, вот так:
SELECT title_id, SUM(qty) AS "Колич прод" FROM sales GROUP BY title_id GO
Теперь набор результатов станет показывать заголовок для итоговой колонки (в наборе результатов содержится 16 строк):
itle_id Колич прод ---------------------------- BU1032 15 BU1111 25 BU2075 35 BU7832 15 MC2222 10 MC3021 40 PC1035 30 PC8888 50 PS1372 20 PS2091 108 PS2106 25 PS3333 15 PS7777 25 TC3218 40 TC4203 20 TC7777 20
Возможна вложенная ("гнездовая") группировка, при которой в предложении GROUP BY задается более одной колонки. При вложенной группировке набор результатов будет группироваться по каждой из колонок, участвующих в группировке, в том порядке, в котором были заданы колонки. Например, чтобы узнать средние цены книг, сгруппированных сначала по типу, а затем по издательству, можно выполнить такой запрос:
SELECT type, pub_id, AVG(price) AS "Средняя цена" FROM titles GROUP BY type, pub_id GO В набор результатов попадут 8 строк: type pub_id Средняя цена -------------------------------------------------- business 0736 2.99 psychology 0736 11.48 UNDECIDED 0877 NULL mod_cook 0877 11.49 psychology 0877 21.59 trad_cook 0877 15.96 business 1389 17.31 popular_comp 1389 21.48
Обратите внимание, что книги, имеющие тип psychology и business, попали в набор результатов более одного раза, потому что они сгруппированы для разных идентификаторов издательств. Значение NULL, показанное в качестве средней цены книг типа UNDECIDED (нераспределенные), отражает тот факт, что для книг этого типа в таблицу не были введены их цены, поэтому невозможно вычислить среднюю цену.
Предложение GROUP BY можно применять с необязательным ключевым словом ALL, означающим, что в набор результатов должны быть включены все группы, даже не соответствующие условию поиска. Группы, не имеющие строк, соответствующих условию поиска, будут содержать в итоговой колонке значение NULL, поэтому их будет сразу видно. Например, чтобы узнать среднюю цену для книг, имеющих авторские отчисления 12%, а также показать в наборе результатов строки для книг, имеющих авторские отчисления не 12% (у них в итоговой колонке будет значение NULL), группируя книги сначала по типам, а затем по идентификатору издательства, выполните такой запрос:
SELECT type, pub_id, AVG(price) AS "Средняя цена" FROM titles WHERE royalty = 12 GROUP BY ALL type, pub_id GO
В набор результатов попадут 8 строк:
type pub_id Средняя цена ------------------------------------------------- business 0736 NULL psychology 0736 10.95 UNDECIDED 0877 NULL mod_cook 0877 19.99 psychology 0877 NULL trad_cook 0877 NULL business 1389 NULL popular_comp 1389 NULL
Будут выведены строки для всех типов книг, но для типов книг, у которых не имеется книг с 12-процентными авторскими отчислениями, появится NULL.
Если мы уберем ключевое слово ALL, то набор результатов будет содержать информацию только для тех типов книг, у которых имеются книги с 12-процентными авторскими отчислениями. Набор результатов будет содержать 2 строки и будет таким:
type pub_id Средняя цена --------------------------------------------------- psychology 0736 10.95 mod_cook 0877 19.99
Предложение GROUP BY часто применяется в сочетании с предложением HAVING, про которое мы сейчас вам расскажем.