Оператор GROUP BY в MySQL

Оператор GROUP BY группирует результирующий набор по одному или нескольким столбцам.

К сгруппированным столбцам можно применять такие функции, как COUNT, SUM, AVG и т. д.

Оператор GROUP BY — важный инструмент в SQL-запросах для обобщения и анализа данных. Особенно при работе с большими объёмами данных он может предоставить полезную сводную информацию.

Синтаксис GROUP BY

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;
  • column1: указывает столбец (столбцы) для группировки.
  • aggregate_function(column2): агрегатная функция (функции), выполняемая (выполняемые) для каждой группы после группировки.
  • table_name: имя таблицы для запроса.
  • condition: необязательное условие, используемое для фильтрации результатов.

Предположим, есть таблица с именем orders, содержащая следующие столбцы:order_id, customer_id, order_date и order_amount。

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

Пример

SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

В приведённом выше примере мы используем GROUP BY customer_id, чтобы сгруппировать результаты по столбцу customer_id, затем используем SUM(order_amount), чтобы вычислить сумму столбца order_amount в каждой группе.

AS total_amount используется для присвоения псевдонима результату вычисления, что делает результат запроса более удобным для чтения.

Примечания:

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

Пример

SELECT column1, column2, aggregate_function(column3)
FROM TABLE_NAME
WHERE условие
GROUP BY column1, column2;

Демонстрация примера

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

Пример

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Структура таблицы для `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` INT(11) NOT NULL,
  `name` CHAR(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Количество входов',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Записи `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', 'Xiao Ming', '2016-04-22 15:25:33', '1'), ('2', 'Xiao Wang', '2016-04-20 15:25:47', '3'), ('3', 'Xiao Li', '2016-04-19 15:26:02', '2'), ('4', 'Xiao Wang', '2016-04-07 15:26:14', '4'), ('5', 'Xiao Ming', '2016-04-11 15:26:40', '4'), ('6', 'Xiao Ming', '2016-04-04 15:26:54', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

После успешного импорта выполните следующую SQL-инструкцию:

mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | signin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

Далее мы используем оператор GROUP BY, чтобы сгруппировать таблицу данных по имени и подсчитать, сколько записей имеет каждый человек:

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

Использование WITH ROLLUP

WITH ROLLUP позволяет выполнять ту же статистику (SUM, AVG, COUNT…) на основе сгруппированной статистики.

Например, мы группируем приведённую выше таблицу данных по имени, а затем подсчитываем количество входов для каждого человека:

mysql> SELECT name, SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | signin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

Запись с NULL представляет общее количество входов для всех людей.

Мы можем использовать coalesce, чтобы задать имя, которое может заменить NULL. Синтаксис coalesce:

select coalesce(a,b,c);

Описание параметров: если a==null, выбрать b; если b==null, выбрать c; если a!=null, выбрать a; если a, b, c все null, то вернуть null (не имеет смысла).

В следующем примере, если имя пустое, мы вместо этого используем итог:

mysql> SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | signin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)
Другие расширения