Предложение GROUP BY (как и предложение HAVING) необходимо только в тех запросах, в которых используются агрегатные функции.

Предложение GROUP BY применяется для вывода агрегатного значения по одной или нескольким строкам, которые возвращает инструкция SELECT, на основе одного или нескольких неагрегатных столбцов, которые называются столбцами группировки (grouping columns). Например, ниже приводится запрос, в котором определяется, сколько людей мы нанимали каждый год в период с 1999 по 2004 год.

SELECT hire_year, COUNT(emp_id) AS "nbr_emps" FROM employee WHERE status="ACTIVE" AND hire_year BETWEEN 1999 AND 2004 GROUP BY hire.year;

Результаты будут следующие:

hire_year nbr_emps

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

Возвращается среднее всех непустых значений в указанном столбце (столбцах).

AVG DISTINCT

Возвращается среднее всех уникальных непустых значений в указанном столбце (столбцах).

Возвращается количество всех непустых значений в указанном столбце (столбцах).

COUNT DISTINCT

Возвращается количество всех уникальных непустых значений в указанном столбце (столбцах).

COUNT (*)

Вычисляется общее количество записей в таблице.

Возвращается наибольшее из непустых значений в указанном столбце (столбцах).

Возвращается наименьшее из непустых значений в указанном столбце (столбцах).

Сумма всех непустых значений в указанном столбце (столбцах).

SUM DISTINCT

Сумма всех уникальных непустых значений в указанном столбце (столбцах).

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

Например:

SELECT AVG(price) FROM titles // Результат: 14. 77

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

• Помещайте предложение GROUP BY правильно относительно других предложений - после предложения WHERE и перед предложением ORDER BY.
• В предложении GROUP BY все столбцы должны быть неагрегатными.
• Не используйте в предложении GROUP BY псевдонимы столбцов, хотя использование псевдонимов таблиц является допустимым.

Предположим, например, что вам нужно узнать общую сумму по нескольким покупкам по таблице OrderJDetails, которая выглядит следующим образом.

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

Кроме того, предложение GROUP В Y поддерживает несколько очень важных дополнительных предложений.

GROUP BY[{ROLLUP CUBE}] ([столбец_для_группировки [, …]]) [, список_наборов_для_группировки]

Агрегатные значения результирующего набора группируются в соответствии с одним или несколькими столбцами для группировки. (Предложение GROUP BY (столбец_для_группировки [, …]) без предложений ROLLUP и CUBE является наиболее простой и часто употребляемой формой предложения GROUP BY.

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

Для всех столбцов группировки создаются промежуточные и перекрестные итоговые значения. По сути, предложение CUBE позволяет быстро получать многомерные результирующие наборы из стандартных реляционных таблиц без особых усилий по программированию. Предложение CUBE особенно полезно при работе с большими объемами данных. Как и предложение ROLLUP, предложение CUBE создает промежуточные итоги по столбцам группировки, но сюда также входят строки промежуточных итогов для всех возможных комбинаций столбцов группировки, указанных в запросе.

GROUP BY GROUPING SETS [(ROLLUP CUBE}} ([столбец_для_группировки [, …}]) [, список_наборов_для группировки}

Позволяет использовать агрегатные группы по нескольким разным наборам столбцов группировки в одном запросе. Это свойство особенно полезно, если вы хотите получить только часть агрегатного результирующего набора. Предложение GROUPING SETS также позволяет выбрать сравниваемые столбцы группировки, в то время как предложение CUBE возвращает все столбцы группировки, a ROLLUP -иерархическую часть столбцов группировки. Как показывает синтаксис, стандарт ANSI также позволяет объединять предложение GROUPING SETS с предложениями ROLLUP и CUBE.

Каждый тип предложения GROUP BY возвращает свой набор агрегатных значений, а также, в случае ROLLUP и CUBE, итоги и промежуточные итоги.

Принцип предложений ROLLUP CUBE и GROUPING SETS будет гораздо понятнее, если объяснить его на примере. В следующем примере мы запрашиваем данные, представляющие собой количество заказов (sa!es_orders) по годам (order_date) и кварталам (orderquarter).

Добавление столбцов группировки в запрос дает более подробный результирующий набор (с большим количеством промежуточных итогов). Мы изменим приведенный выше пример, добавив в запрос регион (region) (однако, поскольку количество столбцов возрастает, мы рассмотрим только первый и второй квартал).

SELECT order_year AS year, order_quarter AS quarter, region, COUNT (*) AS orders FROM order_details WHERE order_year IN (2003, 2004) AND order_quarter IN (1.2) AND region IN ("USA", "CANADA") GROUP BY ROLLUP (order_year, order_quarter) ORDER BY order_year, order_quarter;

Результат будет следующий.

year quarter region orders

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

В следующем примере мы запрашиваем суммарные данные по количеству заказов (salesorders) по годам (orderdate) и кварталам (orderquarter).

SELECT order_year AS year, order_quarter AS quarter, COUNT () AS скаегЕ FROM order.details WHERE order.year IN (2003, 2004) GROUP BY id (order_year, order_quarter) ORDER BY order_year, order_quarter

Предложение GROUP BY GROUPING SETS позволяет проводить агрегацию по нескольким группам в одном запросе. Для каждого набора групп запрос возвращает промежуточные итоги, где столбец группировки обозначается как NULL. Если предложения CUBE и ROLLUP помещают в результирующий набор заранее заданные промежуточные итоги, предложение GROUPING SETS позволяет вам управлять тем, какие промежуточные итоги будут включаться в запрос. Предложение GROUPING SETS не возвращает общего итога.

Используя пример, сходный с приведенными выше примерами для предложений ROLLUP и CUBE, мы выберем на этот раз промежуточный итог по годам и кварталам, а также отдельно - по годам.

Еще предложение GROUPING SETS можно представить как предложение UNION ALL, примененное к нескольким запросам GROUP BY, ссылающимся на разные части одних и тех же данных. Вы можете заставить систему добавить промежуточные итоги в GROUPING SET, просто добавив предложение ROLLUP или CUBE, в соответствии с которым вы хотите вычислить промежуточные итоги.

Предложения GROUPING SETS можно объединять, чтобы в краткой инструкции с генерировать большое число группировок. Объединенные предложения GROUPING SETS дают продукт перекрестной группировки всех группировок всех наборов, указанных в списке GROUPING SET. Объединенные предложения GROUPING SETS совместимы с предложениями ROLLUP и CUBE. Объединенные предложения GROUPING SETS, являясь продуктом перекрестной группировки, создадут очень большое количество конечных группировок даже из небольшого числа объединенных. Например, если мы расширим 3.3, включив в нее ссылки на объединенные наборы группировок, мы получим 3.4.

В примере, приведенном в 3.4, объединенные предложения GROUPING SETS создают большое количество окончательных группировок. Можно представить, каким большим будет результирующий набор, если объединенные предложения GROUPING SETS содержат большое количество группировок! Тем не менее полученная информация может быть очень ценной и ее сложно получить как-то еще.

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

SELECT ПР, SUM(К_во) FROM Поставки GROUP BY ПР;

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

Рис. 2.3. Иллюстрации к фразе GROUP BY

Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. В рассматриваемом примере строки таблицы Поставки группируются так, что в одной группе содержатся все строки для продукта с ПР = 1, в другой – для продукта с ПР = 2 и т.д. (см. рис. 2.3.б). Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т.е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме).

Отметим, что фраза GROUP BY не предполагает ORDER BY. Чтобы гарантировать упорядочение по ПР результата рассматриваемого примера (рис. 2.3,в) следует дать запрос

SELECT ПР, SUM(К_во) FROM Поставки GROUP BY ПР ORDER BY ПР;

Наконец, отметим, что строки таблицы можно группировать по любой комбинации ее столбцов. Так, по запросу

SELECT Т, БЛ, COUNT(БЛ) FROM Заказ GROUP BY Т, БЛ;

можно узнать коды и количество порций блюд, заказанных отдыхающими пансионата (32 человека) на каждую из трапез следующего дня:

Если в запросе используются фразы WHERE и GROUP BY, то строки, не удовлетворяющие фразе WHERE, исключаются до выполнения группирования.

Например, выдать для каждого продукта его код и общий объем возможных поставок, учитывая временную недееспособность поставщика с ПС=2:

SELECT ПР, SUM(К_во) FROM Поставки WHERE ПС 2 GROUP BY ПР;

Результат, приведенный на рис. 2.3,г, отличается от результата (рис. 2.3,а) аналогичного запроса для всех поставщиков объемом поставок продуктов с кодами 15, 5 и 6.

Последнее обновление: 19.07.2017

Для группировки данных в T-SQL применяются операторы GROUP BY и HAVING , для использования которых применяется следующий формальный синтаксис:

SELECT столбцы FROM таблица

GROUP BY

Оператор GROUP BY определяет, как строки будут группироваться.

Например, сгруппируем товары по производителю

SELECT Manufacturer, COUNT(*) AS ModelsCount FROM Products GROUP BY Manufacturer

Первый столбец в выражении SELECT - Manufacturer представляет название группы, а второй столбец - ModelsCount представляет результат функции Count, которая вычисляет количество строк в группе.

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

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

SELECT Manufacturer, COUNT(*) AS ModelsCount FROM Products

Другой пример, добавим группировку по количеству товаров:

SELECT Manufacturer, ProductCount, COUNT(*) AS ModelsCount FROM Products GROUP BY Manufacturer, ProductCount

Оператор GROUP BY может выполнять группировку по множеству столбцов.

Если столбец, по которому производится группировка, содержит значение NULL, то строки со значением NULL составят отдельную группу.

Следует учитывать, что выражение GROUP BY должно идти после выражения WHERE , но до выражения ORDER BY:

SELECT Manufacturer, COUNT(*) AS ModelsCount FROM Products WHERE Price > 30000 GROUP BY Manufacturer ORDER BY ModelsCount DESC

Фильтрация групп. HAVING

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

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

Например, найдем все группы товаров по производителям, для которых определено более 1 модели:

SELECT Manufacturer, COUNT(*) AS ModelsCount FROM Products GROUP BY Manufacturer HAVING COUNT(*) > 1

При этом в одной команде мы можем использовать выражения WHERE и HAVING:

SELECT Manufacturer, COUNT(*) AS ModelsCount FROM Products WHERE Price * ProductCount > 80000 GROUP BY Manufacturer HAVING COUNT(*) > 1

То есть в данном случае сначала фильтруются строки: выбираются те товары, общая стоимость которых больше 80000. Затем выбранные товары группируются по производителям. И далее фильтруются сами группы - выбираются те группы, которые содержат больше 1 модели.

Если при этом необходимо провести сортировку, то выражение ORDER BY идет после выражения HAVING:

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units FROM Products WHERE Price * ProductCount > 80000 GROUP BY Manufacturer HAVING SUM(ProductCount) > 2 ORDER BY Units DESC

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

Теперь Вы можете создавать предикаты в терминах связей специально определенных SQL. Вы можете искать значения в определенном диапазоне (BETWEEN) или в числовом наборе (IN), или Вы можете искать символьные значения, которые соответствуют тексту внутри параметров (LIKE).

4.4. GROUP BY и агрегатные функции SQL

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

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

Аргументу всех функций, кроме COUNT(*), может предшествовать ключевое слово DISTINCT (различный), указывающее, что дублирующие значения столбца

должны быть исключены перед тем, как будет применяться функция. Специальная же функция COUNT(*) служит для подсчета всех без исключения строк в таблице (включая дубликаты).

Агрегатные функции используются подобно именам полей в предложении запроса SELECT, но с одним исключением: они берут имена поля как аргументы. Только числовые поля могут использоваться с SUM и AVG.

С COUNT, MAX, и MIN могут использоваться и числовые или символьные поля. Когда они используются с символьными полями, MAX и MIN будут транслировать их в эквивалент ASCII кода, который должен сообщать, что MIN будет означать первое, а MAX - последнее значение в алфавитном порядке.

Чтобы найти SUM всех окладов в таблице ОТДЕЛ_СОТРУДНИК (рис. 2.3) надо ввести следующий запрос:

FROM Отдел_ Сотрудники;

И на экране увидим результат: 46800 (в таблице будет один столбец с именем СУММА).

Подсчет среднего значения по окладам также прост:

SELECT AVG ((Оклад))

FROM Отдел_ Сотрудники;

Функция COUNT несколько отличается от всех. Она считает число значений в данном столбце или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT (различных) чтобы производить счет чисел уникальных значений в данном поле.

В таблице восемь строк, в которых находятся различные значения окладов.

Отметим, что в последних трех примерах учитывается информация и об уволенных сотрудниках.

Следующее предложение позволяет определить число подразделений на

DISTINCT, сопровождаемый именем поля, с которым он применяется, помещенный в круглые скобки, с COUNT применяется к индивидуальным столбцам.

SELECT COUNT (*)

FROM Отдел_ Сотрудники;

COUNT (*) подсчитывает все без исключения строки таблицы.

DISTINCT не применим c COUNT (*).

Предположим, что таблица ВЕДОМОСТЬ_ОПЛАТЫ (рис. 2.4) имеет еще один столбец, который хранит сумму произведенных вычетов (поле Вычет) для каждой строки ведомости. Тогда если Вас интересует вся сумма, то содержимое столбца Сумма и Вычет надо сложить.

Если же Вас интересует максимальная сумма с учетом вычетов, содержащаяся в ведомости, то это можно сделать с помощью следующего предложения:

SELECT MAX (Сумма + Вычет)

FROM Ведомость_ оплаты;

Для каждой строки таблицы этот запрос будет складывать значение поля Сумма со значением поля Вычет и выбирать самое большое значение, которое он найдет.

ПРЕДЛОЖЕНИЕ GROUP BY (перекомпоновка, порядок)

Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT.

Например, предположим, что Вы хотите определить, сколько сотрудников находятся в каждом отделе (ответ приведен на рис. 4.21):

SELECT Ид_Отд, COUNT (DISTINCT Ид_Отд) AS Кол-во_сотрудников

В результате выполнения предложения GROUP BY остаются только уникальные значения столбцов, по умолчанию отсортированные по возрастанию . В этом аспекте предложение GROUP BY отличается от предложения ORDER BY тем, что последнее хотя и сортирует записи по возрастанию, но не удаляет повторяющиеся значения. В приведенном примере запрос группирует строки таблицы по значениям столбца Ид_Отд (по номерам отделов). Строки с одинаковыми номерами отделов сначала объединяются в группы, но при этом для каждой группы отображается только одна строка. Во втором столбце выводится количество строк в каждой группе, т.е. число сотрудников в каждом отделе.

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

Пусть, например, таблица ВЕДОМОСТЬ_ОПЛАТЫ имеет вид рис. 4.22 и мы хотим определить максимальную сумму, выплаченную по ведомости каждому сотруднику.

В результате получим.

Рис. 4.23. Агрегатная функция с AS

Группировка может быть осуществлена и по нескольким атрибутам:

FROM Ведомость1

GROUP BY Ид_сотр, Дата;

Результат:

Рис. 4.24. Группировка по нескольким атрибутам

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

4.5. Использование фразы HAVING

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

лишь при наличии фразы GROUP BY, а выражение в HAVING должно принимать единственное значение для группы.

Например, пусть надо выдать количественный состав всех отделов (рис. 2.3), исключая отдел с номером 3.

SELECT Ид_Отд, COUNT (DISTINCT Ид_Отд) AS Кол-во _сотрудников

Последним элементом при вычислении табличного выражения используется раздел HAVING (если он присутствует). Синтаксис этого раздела следующий:

::=

HAVING