Sql запросы group. Команда SELECT Раздел GROUP BY. Чистая группировка с помощью group by
Мы с Вами рассмотрели много материала по 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). В конце группы сортируются по количеству товаров по убыванию.
Транслирует запрос SELECT во внутренний план исполнения («query plan»), который может различаться даже для синтаксически одинаковых запросов и от конкретной СУБД.
Оператор SELECT состоит из нескольких предложений (разделов):
- SELECT определяет список возвращаемых столбцов (как существующих, так и вычисляемых), их имена, ограничения на уникальность строк в возвращаемом наборе, ограничения на количество строк в возвращаемом наборе;
- FROM задаёт табличное выражение, которое определяет базовый набор данных для применения операций, определяемых в других предложениях оператора;
- WHERE задает ограничение на строки табличного выражения из предложения FROM;
- GROUP BY объединяет ряды, имеющие одинаковое свойство с применением агрегатных функций
- HAVING выбирает среди групп, определённых параметром GROUP BY
- ORDER BY задает критерии сортировки строк; отсортированные строки передаются в точку вызова.
Структура оператора
Оператор SELECT имеет следующую структуру:
SELECT [ DISTINCT | DISTINCTROW | ALL ] select_expression ,... FROM table_references [ WHERE where_definition ] [ GROUP BY { unsigned_integer | col_name | formula } ] [ HAVING where_definition ] [ ORDER BY { unsigned_integer | col_name | formula } [ ASC | DESC ], ...]
Параметры оператора
ORDER BY
ORDER BY - необязательный (опциональный) параметр операторов SELECT и UNION , который означает что операторы SELECT , UNION возвращают набор строк, отсортированных по значениям одного или более столбцов. Его можно применять как к числовым столбцам, так и к строковым. В последнем случае, сортировка будет происходить по алфавиту .
Использование предложения ORDER BY является единственным способом отсортировать результирующий набор строк. Без этого предложения СУБД может вернуть строки в любом порядке. Если упорядочение необходимо, ORDER BY должен присутствовать в SELECT , UNION .
Сортировка может производиться как по возрастанию, так и по убыванию значений.
- Параметр ASC (по умолчанию) устанавливает порядок сортировки по возрастанию, от меньших значений к большим.
- Параметр DESC устанавливает порядок сортировки по убыванию, от больших значений к меньшим.
Примеры
SELECT * FROM T ;
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
SELECT C1 FROM T ;
| C1 |
|---|
| 1 |
| 2 |
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
| C1 | C2 |
|---|---|
| 1 | a |
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
| C1 | C2 |
|---|---|
| 2 | b |
| 1 | a |
Для таблицы T запрос
SELECT * FROM T ;
вернёт все столбцы всех строк данной таблицы. Для той же таблицы запрос
SELECT C1 FROM T ;
вернёт значения столбца C1 всех строк таблицы- в терминах реляционной алгебры проекция . Для той же таблицы запрос
вернёт значения всех столбцов всех строк таблицы, у которых значение поля C1 равно "1"- в терминах реляционной алгебры можно сказать, что была выполнена выборка , так как присутствует ключевое слово WHERE. Последний запрос
SELECT * FROM T ORDER BY C1 DESC ;
вернёт те же строки, что и первый, однако результат будет отсортирован в обратном порядке (Z-A) из-за использования ключевого слова ORDER BY с полем C1 в качестве поля сортировки. Этот запрос не содержит ключевого слова WHERE, поэтому он вернёт всё, что есть в таблице. Несколько элементов ORDER BY могут быть указаны разделённые запятыми [напр. ORDER BY C1 ASC, C2 DESC] для более точной сортировки.
Отбирает все строки, где поле column_name равно одному из перечисленных значений value1,value2,…
Возвращает список идентификаторов отделов, продажи которых превысили 1000 долларов за 1 января 2000 года, вместе с суммами продаж за этот день:
Ограничение возвращаемых строк
Согласно ISO SQL:2003 возвращаемый набор данных может быть ограничен с помощью:
- введением оконных функций в оператор SELECT
Оконная функция ROW_NUMBER()
Существуют различные оконные функции . ROW_NUMBER() OVER может быть использована для простого ограничения числа возвращаемых строк. Например, для возврата не более десяти строк:
ROW_NUMBER может быть недетерминированным: если key не уникален, каждый раз при выполнении запроса возможно присвоение разных номеров строкам, у которых key совпадает. Когда key уникален, каждая строка будет всегда получать уникальный номер строки.
Оконная функция RANK()
Функция RANK() OVER работает почти так же, как ROW_NUMBER, но может вернуть более чем n строк при определённых условиях. Например, для получения top-10 самых молодых людей:
Данный код может вернуть более чем 10 строк. Например, если есть два человека с одинаковым возрастом, он вернёт 11 строк.
Нестандартный синтаксис
Не все СУБД поддерживают вышеуказанные оконные функции. При этом многие имеют нестандартный синтаксис для решения тех же задач. Ниже представлены варианты простого ограничения выборки для различных СУБД:
| Производитель/СУБД | Синтаксис ограничения |
|---|---|
| DB2 | (Поддерживает стандарт, начиная с DB2 Version 6) |
Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT , MIN , MAX, AVG и SUM ). Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT , должны быть включены в агрегатные функции, и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT , не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY . В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После чего к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, то есть при группировке по полю, содержащему NULL -значения, все такие строки попадут в одну группу.
Если при наличии предложения GROUP BY , в предложении SELECT отсутствуют агрегатные функции, то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT , можно использовать для исключения дубликатов строк в результирующем наборе.
Рассмотрим простой пример:
ВыполнитьSELECT model, COUNT (model) AS Qty_model,
AVG (price) AS Avg_price
FROM PC
GROUP BY model;
SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model;
В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средняя цена для каждой группы. Результатом выполнения запроса будет следующая таблица
|
Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений {модель, дата}.
Существует несколько определенных правил выполнения агрегатных функций.
- Если в результате выполнения запроса не получено ни одной строки (или ни одной строки для данной группы), то исходные данные для вычисления любой из агрегатных функций отсутствуют. В этом случае результатом выполнения функций COUNT
будет нуль, а результатом всех других функций - NULL
.
Данное свойство может дать не всегда очевидный результат. Рассмотрим, например, такой запрос:
ВыполнитьEXISTS (SELECT MAX (price)
FROM PC
Подзапрос в предикате EXISTS возвращает одну строку с NULL в качестве значения столбца. Поэтому, несмотря на то, что ПК с отрицательными ценами нет в базе данных, запрос в примере вернет 1. - Аргумент агрегатной функции не может сам содержать агрегатные функции (функция от функции). То есть в простом запросе (без подзапросов) нельзя, скажем, получить максимум средних значений .
- Результат выполнения функции COUNT есть целое число (INTEGER). Другие агрегатные функции наследуют типы данных обрабатываемых значений.
- Если при выполнении функции SUM будет получен результат, превышающий максимально возможное значение для используемого типа данных, возникает ошибка.
Итак, агрегатные функции, включенные в предложение SELECT запроса, не содержащего предложения GROUP BY , исполняются над всеми результирующими строками этого запроса. Если же запрос содержит предложение GROUP BY , каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY , составляют группу, и агрегатные функции выполняются для каждой группы отдельно.
