SQL-Урок 8. Группировка данных (GROUP BY)
Группировка данных позволяет разделить все данные на логические наборы, благодаря чему становится возможным выполнение статистических вычислений по отдельности в каждой группе.
1. Создание групп (GROUP BY)
Группы создаются с помощью инструкции GROUP BY оператора SELECT. Рассмотрим на примере.
Run SQLSELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product
Try it Yourself
Данным запросом мы извлекли информацию о количестве реализуемой продукции в каждом месяце. Оператор SELECT приказывает вывести два столбца Product – название продукта и Product_num – расчетное поле, которое мы создали для отображения количества реализуемой продукции (формула поля SUM(Quantity)). Предложение GROUP BY указывает СУБД сгруппировать данные по столбцу Product.
Следует также отметить, что GROUP BY должно идти после предложения WHERE и перед ORDER BY.
2. Фильтрующие группы (HAVING)
Так же, как мы фильтровали строки в таблице, мы можем производить фильтрацию по сгруппированным данным. Для этого в SQL существует оператор HAVING. Возьмем предыдущий пример и добавим фильтрацию по группам.
Run SQLSELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity) > 4000
Try it Yourself
Видим, что после того, как было сочтено количество реализованного товара в разрезе каждого продукта, СУБД «отрубила» те продукты, которых было реализовано менее 4000 шт.
Как видим, оператор HAVING очень подобен оператору WHERE, однако между собой они имеют существенное отличие: WHERE фильтрует данные до того, как они будут сгруппированы, а HAVING — осуществляет фильтрацию после группировки. Таким образом, строки, которые были удалены предложением WHERE, не будут включены в группу. Итак, операторы WHERE и HAVING могут использоваться в одном предложении. Рассмотрим пример:
Run SQLSELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct WHERE Product <> 'Skis Long' GROUP BY Product HAVING SUM(Quantity) > 4000
Try it Yourself
Мы к предыдущему примеру добавили оператор WHERE, где указали товар «Skis Long», что в свою очередь повлияло на группировку оператором HAVING. Как результат мы видим, что товар «Skis Long» не попал в перечень групп с количеством реализованной продукции более 4000 шт.
3. Группировка и сортировка
Как и при обычной выборке данных, мы можем отсортировать группы после группировки оператором HAVING. Для этого мы можем использовать уже знакомый нам оператор ORDER BY. В данной ситуации его применение аналогично предыдущим примерам. К примеру:
Run SQLSELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity) > 3000 ORDER BY SUM(Quantity)
Try it Yourself
или просто укажем номер поля по порядку, по которому хотим посортировать:
Run SQLSELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity) > 3000 ORDER BY 2
Try it Yourself
Видим, что для сортировки сводных результатов нам нужно просто прописать предложение из ORDER BY после оператора HAVING.
MS Access не поддерживает сортировку групп по псевдонимам колонок, то есть в нашем примере, чтобы посортировать значения, мы не сможем в конце запроса прописать ORDER BY Product_num.
- Изменение регистра букв в тексте
- Сумма прописью на украинском языке
- Поиск латиницы в кириллице и наоборот
- Транслитерация с украинского на английский
GROUP BY по нескольким выражениям
В GROUP BY можно добавлять несколько выражений через запятую. В таком случае в результат попадут все уникальные комбинации этих выражений.
Рассмотрим заказы в разрезе магазинов и категорий товаров:
SELECT p.store_id, pr.category_id, pi.product_id, pi.count, pi.price FROM purchase p JOIN purchase_item pi ON pi.purchase_id = p.purchase_id JOIN product pr ON pr.product_id = pi.product_id ORDER BY p.store_id, pr.category_id | store_id | category_id | product_id | count | price |
|---|---|---|---|---|
| 100 | 6 | 3 | 1 | 26100.00 |
| 100 | 6 | 3 | 1 | 26100.00 |
| 201 | 5 | 12 | 1 | 100000.00 |
| 201 | 6 | 4 | 1 | 21500.00 |
| 301 | 10 | 7 | 1 | 4900.00 |
| 302 | 8 | 8 | 3 | 38300.00 |
| 302 | 8 | 8 | 1 | 38300.00 |
| 302 | 8 | 9 | 1 | 44500.00 |
Теперь по каждой категории товаров вычислим сумму всех заказов по магазинам:
SELECT p.store_id, pr.category_id, sum(pi.count * pi.price) as total_price FROM purchase p JOIN purchase_item pi ON pi.purchase_id = p.purchase_id JOIN product pr ON pr.product_id = pi.product_id GROUP BY p.store_id, pr.category_id ORDER BY p.store_id, pr.category_id | store_id | category_id | total_price |
|---|---|---|
| 100 | 6 | 52200.00 |
| 201 | 5 | 100000.00 |
| 201 | 6 | 21500.00 |
| 301 | 10 | 4900.00 |
| 302 | 8 | 197700.00 |
Обрати внимание, что для магазина 201 две записи — для категорий 5 и 6.
SQL GROUP BY
Заявление GROUP BY группирует строки с одинаковыми значениями в сводные строки, например «найти количество клиентов в каждой стране».
Заявление GROUP BY часто используется с агрегатными функциями (COUNT, MAX, MIN, SUM, AVG) для группировки результирующего набора по одному или нескольким столбцам.
Синтаксис GROUP BY
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Демо база данных
Ниже приведен выбор из таблицы «Customers» в образце базы данных Northwind:
Примеры SQL GROUP BY
В следующей инструкции SQL указано количество клиентов в каждой стране:
Пример
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
В следующей инструкции SQL перечислено количество клиентов в каждой стране, отсортированных от высокого к низкому:
Пример
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
Демо база данных
Ниже приведен выбор из таблицы «Orders» в образце базы данных Northwind:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10248 | 90 | 5 | 1996-07-04 | 3 |
| 10249 | 81 | 6 | 1996-07-05 | 1 |
| 10250 | 34 | 4 | 1996-07-08 | 2 |
И выбор из таблицы «Shippers»:
| ShipperID | ShipperName |
|---|---|
| 1 | Speedy Express |
| 2 | United Package |
| 3 | Federal Shipping |
Пример GROUP BY с JOIN
В следующей инструкции SQL указано количество заказов, отправленных каждым грузоотправителем:
Пример
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
Мы только что запустили
SchoolsW3 видео
курс сегодня!
Сообщить об ошибке
Если вы хотите сообщить об ошибке или внести предложение, не стесняйтесь отправлять на электронное письмо:
Ваше предложение:
Спасибо Вам за то, что помогаете!
Ваше сообщение было отправлено в SchoolsW3.
Schoolsw3 оптимизирован для бесплатного обучения, проверки и подготовки знаний. Примеры в редакторе упрощают и улучшают чтение и базовое понимание. Учебники, ссылки, примеры постоянно пересматриваются, чтобы избежать ошибок, но не возможно гарантировать полную правильность всего содержания. Некоторые страницы сайта могут быть не переведены на РУССКИЙ язык, можно отправить страницу как ошибку, так же можете самостоятельно заняться переводом. Используя данный сайт, вы соглашаетесь прочитать и принять Условия к использованию, Cookies и политика конфиденциальности.
GROUP BY оператор
SQL оператор GROUP BY можно использовать в SELECT для сбора данных по нескольким записям и группировки результатов одного или нескольких столбцов.
Синтаксис
SELECT expression1, expression2, . expression_n,
aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, . expression_n;
Параметры или аргументы
expression1 , expression2 , . expression_n — выражения, которые не входят в aggregate_function и должны быть включены в предложение GROUP BY в конце оператора SQL.
aggregate_function — это функция, такая как SUM, COUNT, функции MIN, MAX или AVG.
aggregate_expression – это столбец или выражение, которое используется в aggregate_function .
tables – таблицы из которых вы хотите выгрузить данные. После оператора FROM должна быть указана хотя бы одна таблица.
WHERE conditions . Необязательный. Условия, которые должны быть выполнены для записей, которые будут выбраны.
Пример – использования функции SUM
Давайте посмотрим на примере SQL запроса GROUP BY, который использует SQL функцию SUM.
В этом примере GROUP BY использует функцию SUM, чтобы получить total sales (общий объем продаж) по department (наименование отдела) таблицы order_details .