Последнее обновление: 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). В конце группы сортируются по количеству товаров по убыванию.

В рамках данной статьи я расскажу вам о том, как осуществляется группировка данных, как правильно применять group by и having внутри SQL-запросов на примере нескольких запросов.

Большинство информации в базах данных хранятся в детализированном виде. Однако, частенько возникает необходимость получить сводки. Например, узнать общее число комментариев пользователей или быть может количество товара на складах. Подобных задач масса. Поэтому в языке SQL специально для таких случаев предусмотрены конструкции group by и having, позволяющие, соответственно, группировать и отфильтровывать полученные группы данных.

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

В рамках примера я буду рассматривать всего лишь одну таблицу. Причина проста, эти операторы применяются уже к полученной выборке данных (после объединения строк таблиц и их фильтрации). Так что от добавления операторов where и join суть не поменяется.

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

user_name - имя пользователя

forum_group - имя группы

mess_count - количество сообщений

is_have_social_profile - указан ли в профиле форуме ссылка на страничку в социальной сети

Как видите, таблица простая и многие вещи можно самому посчитать на калькуляторе. Однако, это лишь пример и тут всего 10 записей. В реальных базах записи могут измеряться тысячами. Поэтому приступим к запросам.

Чистая группировка с помощью group by

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

Вначале, небольшое словесное описание, чтобы легче было понимать SQL-запрос. Итак, вам нужно найти вычисляемые значения по группам форума. Соответственно, вам нужно поделить все эти десять строк на три разные группы: admin, moder, user. Чтобы это сделать, нужно в конце запроса добавить группировку по значениям поля forum_group. А так же добавить в select вычисляемые выражения с использованием так называемых агрегатных функций.

Указываем поля и вычисляемые столбцы select forum_group, avg(raiting) as avg_raiting, sum(mess_count) as total_mess_count -- Указываем таблицу from userstat -- Указываем группировку по полю group by forum_group

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

Так же я воспользовался двумя агрегатными функциями. AVG - вычисляет среднее значение. И SUM - вычисляет сумму.

1. Вначале все строки исходной таблицы были разбиты на три группы по значениям поля forum_group. Например, внутри группы admin было три пользователя. Внутри moder так же 3 строки. А внутри группы user было 4 строки (четыре пользователя).

2. Затем для каждой группы применялись агрегатные функции. Например, для группы admin средний рейтинг вычислялся так (2 + 5 + 5)/3 = 4. Количество сообщений вычислялось так (10 + 15 + 25) = 50.

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

Группировка с помощью group by и фильтрацией групп с having

Теперь, рассмотрим более сложный пример группировки данных. Допустим нам нужно оценить эффективность действий по привлечению пользователей к социальной деятельности. Если по простому, то узнать сколько пользователей в группах оставило ссылки на свои профили, а сколько проигнорировало рассылки и прочее. Однако, в реальной жизни таких групп может быть много, поэтому нам нужно отфильтровать те группы, которыми можно пренебречь (к примеру, слишком мало людей не оставило ссылку; зачем захламлять полный отчет). В моем примере это группы, где всего один пользователь.

Вначале, словесно опишем что необходимо сделать в SQL-запросе. Нам нужно все строки исходной таблицы userstat разделить по следующим признакам: имя группы и наличие социального профиля. Соответственно, необходимо группировать данные таблицы по полям forum_group и is_have_social_profile. Однако, нас не интересуют те группы, где всего один человек. Следовательно такие группы нужно отфильтровать.

Примечание : Стоит знать, что эту задачу можно было бы решить и с помощью группировки только по одному полю. Если использовать конструкцию case. Однако, в рамках данного примера показываются возможности именно группировки.

Так же хотел бы сразу уточнить один важный момент. Фильтровать с помощью having можно только при применении агрегатных функций, а не по отдельным полям. Другими словами, это не конструкция where, это фильтр именно групп строк, а не отдельных записей. Хотя условия внутри задаются аналогичным образом с помощью "or" и "and".

Вот как будет выглядеть SQL-запрос

Указываем поля и вычисляемые столбцы select forum_group, is_have_social_profile, count(*) as total -- Указываем таблицу from userstat -- Указываем группировку по полям group by forum_group, is_have_social_profile -- Указываем фильтр групп having count(*) > 1

Обратите внимание, что поля после конструкции group by указываются через запятую. Указание полей в select происходит аналогичным образом, как и в предыдущем примере. Так же я воспользовался агрегатной функцией count, которая вычисляет количество строк в группах.

Вот какой получился результат:

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

1. Вначале было получено 6 групп. Каждая из групп по forum_group была разбита на две подгруппы по значениям поля is_have_social_profile. Другими словами группы: , , , , , .

Примечание : Кстати, групп не обязательно должно было бы получится 6. Так, к примеру, если бы все администраторы заполнили профиль, то групп было бы 5, так как поле is_have_social_profile имело бы только одно значение у пользователей группы admin.

2. Затем для каждой группы было применено условие фильтрации в having. Поэтому были исключены следующие группы: , , . Так как внутри каждой такой группы присутствовала всего одна строка исходной таблицы.

3. После этого были вычислены необходимые данные и был получен результат.

Как видите, ничего сложно в использовании нет.

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

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

Теперь Вы можете создавать предикаты в терминах связей специально определенных 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