Сводить данные с помощью предложения GROUP BY.

  • 3 мин

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

CustomerID

OrderCount

1 234

3

1005

1

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

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

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

CustomerID

OrderCount

1005

1

1 234

3

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

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  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, необходимо понимать причины возникновения следующего типа сообщения об ошибке:

Сообщение 8120, уровень 16, состояние 1, строка 2. Столбец <имя_столбца> является недопустимым в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении 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;

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