В рамках данной статьи я расскажу вам о том, как осуществляется группировка данных, как правильно применять 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 - вычисляет сумму.
| forum_group | avg_raiting | total_mess_count |
|---|---|---|
| admin | 4 | 50 |
| moder | 3 | 50 |
| user | 3 | 150 |
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, которая вычисляет количество строк в группах.
Вот какой получился результат:
| forum_group | is_have_social_profile | total |
|---|---|---|
| admin | 1 | 2 |
| moder | 1 | 2 |
| user | 0 | 3 |
Давайте разберем по шагам, как получился данный результат.
1. Вначале было получено 6 групп. Каждая из групп по forum_group была разбита на две подгруппы по значениям поля is_have_social_profile. Другими словами группы: , , , , , .
Примечание : Кстати, групп не обязательно должно было бы получится 6. Так, к примеру, если бы все администраторы заполнили профиль, то групп было бы 5, так как поле is_have_social_profile имело бы только одно значение у пользователей группы admin.
2. Затем для каждой группы было применено условие фильтрации в having. Поэтому были исключены следующие группы: , , . Так как внутри каждой такой группы присутствовала всего одна строка исходной таблицы.
3. После этого были вычислены необходимые данные и был получен результат.
Как видите, ничего сложно в использовании нет.
Стоит знать, что в зависимости от базы данных, возможности этих конструкций могут отличаться. К примеру, агрегатных функций может быть больше или же можно указывать в качестве группировки не отдельные поля, а вычисляемые столбцы. Эту информацию уже необходимо смотреть в спецификации.
Теперь, вы знаете как применять группировку данных с group by, а так же как фильтровать группы с помощью having.
Одной из важных команд в SQL является GROUP BY . Данная конструкция создана для выборки отдельных групп строк из таблицы, к каждой из которых применяются функции, указанные в SELECT (например, COUNT() , MIN() и так далее). Давайте разберём на конкретных примерах.
Допустим, у нас есть таблица супермаркетов:
- id - уникальный идентификатор.
- shop_id - уникальный идентификатор супермаркета.
- price - цена на молоко.
Нам необходимо узнать среднюю цену на молоко у каждого супермаркета. Обратите внимание, что shop_id может повторяться (ведь есть сети супермакетов). Поэтому нам необходимо сделать группу по shop_id , и для каждой строки в этой группе вычислить среднюю цену.
Исходная таблица выглядит следующим образом:
Таким образом, мы узнали среднюю цену в конкретной сети супермаркетов (или в одиночном магазине).
Ещё одним очень частым применением является выборка уникальных записей из таблиц. В предыдущем примере Вы заметили, что в результирующей выборке нет повторяющихся shop_id , тогда как в исходной таблице они были.
Допустим, у нас есть таблица с пользователями:
- id - уникальный идентификатор.
- email - e-mail пользователя.
- hash - уникальный хэш пользователя.
И перед нами встала задача выбрать уникальных пользователей , причём именно уникальных людей, а не уникальных учётных записей. Ведь у одного человека может быть и 100 аккаунтов с разными e-mail и, разумеется, id . А hash - это некая строка, характеризующая его как уникального человека.
Таким образом, нам надо выбрать все записи с уникальным hash . Для этого опять же используется GROUP BY :
SELECT * FROM `table` GROUP BY `hash`
В результате, будут извлечены только уникальные hash , то есть 2 одинаковых hash в результирующей выборке Вы не увидите.
Вот таких два практических примера использования GROUP BY в SQL мы разобрали.
Мы с Вами рассмотрели много материала по SQL , в частности Transact-SQL, но мы не затрагивали такую, на самом деле простую тему как группировка данных GROUP BY . Поэтому сегодня мы научимся использовать оператор group by для группировки данных.
Многие начинающие программисты, когда сталкиваются с SQL, не знают о такой возможности как группировка данных с помощью оператора GROUP BY, хотя эта возможность требуется достаточно часто на практике, в связи с этим наш сегодняшний урок, как обычно с примерами, посвящен именно тому, чтобы Вам было проще и легче научиться использовать данный оператор, так как Вы с этим обязательно столкнетесь. Если Вам интересна тема SQL, то мы, как я уже сказал ранее, не раз затрагивали ее, например, в статьях Язык SQL – объединение JOIN или Объединение Union и union all , поэтому можете ознакомиться и с этим материалом.
И для вступления небольшая теория.
Что такое оператор GROUP BY
GROUP BY – это оператор (или конструкция, кому как удобней ) SQL для группировки данных по полю, при использовании в запросе агрегатных функций, таких как sum, max, min, count и других.
Как Вы знаете, агрегатные функции работают с набором значений, например sum суммирует все значения. А вот допустим, Вам необходимо просуммировать по какому-то условию или сразу по нескольким условиям, именно для этого нам нужен оператор group by, чтобы сгруппировать все данные по полям с выводом результатов агрегатных функций.
Как мне кажется, наглядней будет это все разобрать на примерах, поэтому давайте перейдем к примерам.
Примечание! Все примеры будем писать в Management Studio SQL сервера 2008.
Примеры использования оператора GROUP BY
И для начала давайте создадим и заполним тестовую таблицу с данными, которой мы будет посылать наши запросы select с использованием группировки group by. Таблица и данные конечно выдуманные, чисто для примера.
Создаем таблицу
CREATE TABLE .( NULL, (50) NULL, NULL, NULL) ON GOЯ ее заполнил следующими данными:
- Id –идентификатор записи;
- Name – фамилия сотрудника;
- Summa- денежные средства;
- Priz – признак денежных средств (допустим 1- Оклад; 2-Премия).
Группируем данные с помощью запроса group by
И в самом начале давайте разберем синтаксис group by , т.е. где писать данную конструкцию:
Синтаксис :
Select агрегатные функции
From источник
Where Условия отбора
Group by поля группировки
Having Условия по агрегатным функциям
Order by поля сортировки
Теперь если нам необходимо просуммировать все денежные средства того или иного сотрудника без использования группировки мы пошлем вот такой запрос:
SELECT SUM(summa)as summa FROM test_table WHERE name="Иванов"
А если нужно просуммировать другого сотрудника, то мы просто меняем условие. Согласитесь, если таких сотрудников много, зачем суммировать каждого, да и это как-то не наглядно, поэтому нам на помощь приходит оператор group by. Пишем запрос:
SELECT SUM(summa)as summa, name FROM test_table GROUP BY name
Как Вы заметили, мы не пишем никаких условий, и у нас отображаются сразу все сотрудники с просуммированным количеством денежных средств, что более наглядно.
Примечание! Сразу отмечу то, что, сколько полей мы пишем в запросе (т.е. поля группировки), помимо агрегатных функций, столько же полей мы пишем в конструкции group by. В нашем примере мы выводим одно поле, поэтому в group by мы указали только одно поле (name), если бы мы выводили несколько полей, то их все пришлось бы указывать в конструкции group by (в последующих примерах Вы это увидите).
Также можно использовать и другие функции, например, подсчитать сколько раз поступали денежные средства тому или иному сотруднику с общей суммой поступивших средств. Для этого мы кроме функции sum будем еще использовать функцию count.
SELECT SUM(summa)as [Всего денежных средств], COUNT(*) as [Количество поступлений], Name [Сотрудник] FROM test_table GROUP BY name
Но допустим для начальства этого недостаточно, они еще просят, просуммировать также, но еще с группировкой по признаку, т.е. что это за денежные средства (оклад или премия), для этого мы просто добавляем в группировку еще одно поле, и для лучшего восприятия добавим сортировку по сотруднику, и получится следующее:
SELECT SUM(summa)as [Всего денежных средств], COUNT(*) as [Количество поступлений], Name [Сотрудник] , Priz [Источник] FROM test_table GROUP BY name, priz ORDER BY name
Теперь у нас все отображается, т.е. сколько денег поступило сотруднику, сколько раз, а также из какого источника.
А сейчас для закрепления давайте напишем еще более сложный запрос с группировкой, но еще добавим названия этого источника, так как согласитесь по идентификаторам признака не понятно из какого источника поступили средства. Для этого мы используем конструкцию case .
SELECT SUM(summa) AS [Всего денежных средств], COUNT(*) AS [Количество поступлений], Name [Сотрудник], CASE WHEN priz = 1 then "Оклад" WHEN priz = 2 then "Премия" ELSE "Без источника" END AS [Источник] FROM test_table GROUP BY name, priz ORDER BY name
Вот теперь все достаточно наглядно и не так уж сложно, даже для начинающих.
Также давайте затронем условия по итоговым результатам агрегатных функций (having ). Другими словами, мы добавляем условие не по отбору самих строк, а уже на итоговое значение функций, в нашем случае это sum или count. Например, нам нужно вывести все то же самое, но только тех, у которых «всего денежных средств» больше 200. Для этого добавим условие having:
SELECT SUM(summa)as [Всего денежных средств], COUNT(*) as [Количество поступлений], Name [Сотрудник], CASE WHEN priz = 1 then "Оклад" WHEN priz = 2 then "Премия" ELSE "Без источника" END AS [Источник] FROM test_table GROUP BY name, priz --группируем HAVING SUM(summa) > 200 --отбираем ORDER BY name -- сортируем
Теперь у нас вывелись все значения sum(summa), которые больше 200, все просто.
Надеюсь, после сегодняшнего урока Вам стало понятно, как и зачем использовать конструкцию group by . Удачи! А SQL мы продолжим изучать в следующих статьях.
Последнее обновление: 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 оператор GROUP BY с синтаксисом и примерами.
Описание
SQL оператор GROUP BY можно использовать в операторе SELECT для сбора данных по нескольким записям и группировки результатов по одному или нескольким столбцам.
Синтаксис
Синтаксис оператора GROUP BY в SQL:
Параметры или аргументы
expression1 , expression2 , … expression_n Выражения, которые не инкапсулированы в агрегатную функцию и должны быть включены в GROUP BY в конце SQL-запроса. aggregate_function Это агрегатная функция, такая как SUM, COUNT, MIN, MAX или AVG. aggregate_expression Это столбец или выражение, для которого будет использоваться aggregate_function . tables Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть указана хотя бы одна таблица. WHERE conditions Необязательный. Это условия, которые должны быть выполнены для выбора записей. ORDER BY expression Необязательный. Выражение, используемое для сортировки записей в наборе результатов. Если указано более одного выражения, значения должны быть разделены запятыми. ASC Необязательный. ASC сортирует результирующий набор в порядке возрастания по expression . Это поведение по умолчанию, если модификатор не указан. DESC Необязательный. DESC сортирует результирующий набор в порядке убывания по expression .Пример — использование GROUP BY с функцией SUM
Давайте посмотрим, как использовать GROUP BY с функцией SUM в SQL.
В этом примере у нас есть таблица employees
со следующими данными:
| dept_id | total_salaries |
|---|---|
| 500 | 119500 |
| 501 | 113000 |
В этом примере мы использовали функцию SUM, чтобы сложить все зарплаты для каждого dept_id , и мы результатам SUM(salary) указали псевдоним «total_salaries». Поскольку dept_id не инкапсулирован в функцию SUM, он должен быть указан в предложении GROUP BY.
Пример — использование GROUP BY с функцией COUNT
Давайте посмотрим, как использовать предложение GROUP BY с функцией COUNT в SQL.
В этом примере у нас есть таблица products со следующими данными:
В этом примере мы использовали функцию COUNT для вычисления количества total_products для каждого category_id , и мы указали псевдоним «total_products» как результаты функции COUNT. Мы исключили все значения category_id , которые имеют значение NULL, отфильтровав их в предложении WHERE. Поскольку category_id не инкапсулирован в функции COUNT, он должен быть указан в предложении GROUP BY.
Пример — использование GROUP BY с функцией MIN
Давайте теперь посмотрим, как использовать предложение GROUP BY с функцией MIN в SQL.
В этом примере мы снова будем использовать таблицу employees со следующими данными:
Будет выбрано 2 записи. Вот результаты, которые вы получите:
| dept_id | lowest_salary |
|---|---|
| 500 | 57500 |
| 501 | 42000 |
В этом примере мы использовали функцию MIN, чтобы вернуть самое минимальное значение salary для каждого dept_id , и мы присвоили результатам функции MIN псевдоним «lowest_salary». Поскольку dept_id не инкапсулирован в функцию MIN, он должен быть указан в предложении GROUP BY.
Пример — использование GROUP BY с функцией MAX
Наконец, давайте посмотрим, как использовать предложение GROUP BY с функцией MAX.
Давайте снова воспользуемся таблицей employees , но на этот раз найдем самую максимальную зарплату для каждого dept_id :
| employee_number | first_name | last_name | salary | dept_id |
|---|---|---|---|---|
| 1001 | Justin | Bieber | 62000 | 500 |
| 1002 | Selena | Gomez | 57500 | 500 |
| 1003 | Mila | Kunis | 71000 | 501 |
| 1004 | Tom | Cruise | 42000 | 501 |
Введите следующий SQL оператор.
