Суммирование данных с помощью GROUP BY

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

Использование предложения GROUP BY

Как вы узнали, в процессе обработки оператора SELECT, после вычисления предложений FROM и WHERE, создается виртуальная таблица. Содержимое виртуальной таблицы теперь доступно для дальнейшей обработки. Предложение GROUP BY можно использовать для подразделения содержимого этой виртуальной таблицы на группы строк.

Чтобы сгруппировать строки, укажите один или несколько элементов в предложении GROUP BY:

GROUP BY <value1> [, <value2>, …]

GROUP BY создает группы и помещает строки в каждую группу в соответствии с элементами, указанными в предложении.

Например, следующий запрос приведет к набору группированных строк, одной строке на customerID в таблице Sales.SalesOrderHeader . Другой способ просмотра процесса GROUP BY заключается в том, что все строки с одинаковым значением CustomerID будут сгруппированы и возвращены в одной строке результатов.

SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Приведенный выше запрос эквивалентен следующему запросу:

SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader

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

Итак, в чем разница между написанием запроса с помощью GROUP BY или DISTINCT? Если все, что вы хотите знать, это различные значения для CustomerID, нет разницы. Но с помощью GROUP BY мы можем добавить другие элементы в список SELECT, которые затем агрегируются для каждой группы.

Простейшая агрегатная функция — COUNT(*). Следующий запрос берёт исходные 830 строк из CustomerID и группирует их в 89 групп на основе значений CustomerID. Каждое отдельное значение CustomerID создает одну строку выходных данных в запросе GROUP BY

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Для каждого значения CustomerID запрос агрегирует и подсчитывает строки, поэтому мы получаем, сколько строк в таблице SalesOrderHeader принадлежит каждому клиенту.

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

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;

На этот раз результаты возвращаются в указанном порядке:

Предложения в инструкции SELECT применяются в следующем порядке:

1. ОТ
2. ГДЕ
3. ГРУППИРОВКА ПО
4. ОБЛАДАНИЕ
5. ВЫБЕРИТЕ
6. ORDER BY (СОРТИРОВАТЬ ПО)

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

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY Customer
ORDER BY Customer;

Однако следующий запрос будет выполнен успешно, группирование и сортировка результатов по идентификатору клиента.

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;

Устранение неполадок с ошибками, связанными с GROUP BY

Распространенное препятствие для удобства использования GROUP BY в инструкциях SELECT заключается в том, почему происходит следующее сообщение об ошибке:

Msg 8120, Level 16, State 1, Line 2 Column <column_name> является недопустимым в списке выбора, так как он не содержится ни в агрегатной функции, либо в предложении GROUP BY.

Например, следующий запрос разрешен, так как каждый столбец в списке SELECT является столбцом в предложении GROUP BY или агрегатной функцией, работающей в каждой группе:

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Следующий запрос возвращает ошибку, так как PurchaseOrderNumber не является частью GROUP BY, и он не используется с агрегатной функцией.

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Этот запрос возвращает ошибку:

Msg 8120, Level 16, State 1, Line 1
Column 'Sales.SalesOrderHeader.PurchaseOrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Вот еще один способ подумать об этом. Этот запрос возвращает одну строку для каждого значения CustomerID . Но строки для одного и того же CustomerID могут иметь разные значения PurchaseOrderNumber , поэтому какие из значений являются возвращаемыми?

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

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, PurchaseOrderNumber;

Этот запрос вернет одну строку для каждого клиента и каждой комбинации заказа на покупку, а также количество заказов для этого сочетания.