Сообщить об ошибке.

MySQL: Совместное использование GROUP BY и HAVING.

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

Содержание:


MySQL инструкция GROUP BY col [WITH ROLLUP].

MySQL инструкция GROUP BY col1[, col2, ...] позволяет группировать результаты по указанным колонкам col1, col2, ... при выборке столбцов из базы данных. К сгруппированным результатам (столбцам) можно применять агрегатные (групповые) функций БД MySQL.

Предположим, что в таблице продаж есть столбцы год year, страна country, продукт product и прибыль profit. Чтобы суммировать содержимое таблицы за год, нужно использовать простую группу GROUP BY следующим образом:

SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

Выходные данные показывают общую (совокупную) прибыль profit за каждый год. Чтобы также определить общую прибыль, суммированную за все годы, необходимо самостоятельно сложить отдельные значения или выполнить дополнительный запрос. Или можно использовать необязательный оператор ROLLUP, который обеспечивает оба уровня анализа с помощью одного запроса. Добавление модификатора WITH ROLLUP к предложению GROUP BY приводит к тому, что запрос создает еще одну (суперагрегированную) строку, которая показывает общую сумму значений за все годы:

SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

Значение NULL в столбце года определяет строку общего суперагрегата. Таким образом, ROLLUP позволяет отвечать на вопросы на нескольких уровнях анализа с помощью одного запроса. Например, ROLLUP можно использовать для обеспечения поддержки операций OLAP (онлайн-аналитическая обработка).

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

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

SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
+------+---------+------------+--------+

С добавлением ROLLUP запрос создает несколько дополнительных строк:

SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | India   | NULL       |   1350 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2000 | USA     | NULL       |   1575 |
| 2000 | NULL    | NULL       |   4525 |
| 2001 | Finland | Phone      |     10 |
| 2001 | Finland | NULL       |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
| 2001 | USA     | NULL       |   3000 |
| 2001 | NULL    | NULL       |   3010 |
| NULL | NULL    | NULL       |   7535 |
+------+---------+------------+--------+

Теперь вывод включает сводную информацию на четырех уровнях анализа, а не только на одном:

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

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

Поскольку значения NULL в строках суперагрегата помещаются в результирующий набор на таком позднем этапе обработки запроса, то их можно проверить на значение NULL только в списке выбора или предложении HAVING. Их нельзя проверить на значение NULL в условиях JOIN или предложении WHERE. Например, НЕЛЬЗЯ добавить в запрос WHERE product IS NULL, чтобы исключить из вывода все строки, кроме суперагрегированных.

Значения NULL отображаются как NULL на стороне клиента и могут быть проверены как таковые с помощью любого клиентского программного интерфейса MySQL. Однако на данный момент нельзя различить, представляет ли значение NULL обычное сгруппированное значение или суперагрегатное значение. Чтобы проверить различие, необходимо использовать функцию GROUPING(), описанную ниже.

Функция GROUPING(), проверка суперагрегированного значения.

Для запросов GROUP BY ... WITH ROLLUP, чтобы проверить, представляют ли значения NULL результат суперагрегированного значения, доступна функция GROUPING(). Например, GROUPING(year) возвращает 1, когда NULL в столбце year встречается в строке суперагрегата, и 0 в противном случае. Аналогично, GROUPING(country) и GROUPING(product) возвращают 1 для сверхагрегированных значений NULL в столбцах страны и продукта соответственно.

SELECT
         IF(GROUPING(year), 'All years', year) AS year,
         IF(GROUPING(country), 'All countries', country) AS country,
         IF(GROUPING(product), 'All products', product) AS product,
         SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+-----------+---------------+--------------+--------+
| year      | country       | product      | profit |
+-----------+---------------+--------------+--------+
| 2000      | Finland       | Computer     |   1500 |
| 2000      | Finland       | Phone        |    100 |
| 2000      | Finland       | All products |   1600 |
| 2000      | India         | Calculator   |    150 |
| 2000      | India         | Computer     |   1200 |
| 2000      | India         | All products |   1350 |
| 2000      | USA           | Calculator   |     75 |
| 2000      | USA           | Computer     |   1500 |
| 2000      | USA           | All products |   1575 |
| 2000      | All countries | All products |   4525 |
| 2001      | Finland       | Phone        |     10 |
| 2001      | Finland       | All products |     10 |
| 2001      | USA           | Calculator   |     50 |
| 2001      | USA           | Computer     |   2700 |
| 2001      | USA           | TV           |    250 |
| 2001      | USA           | All products |   3000 |
| 2001      | All countries | All products |   3010 |
| All years | All countries | All products |   7535 |
+-----------+---------------+--------------+--------+

С несколькими аргументами, функция GROUPING() возвращает результат, представляющий битовую маску, которая объединяет результаты для каждого выражения, причем младший бит соответствует результату для самого правого выражения. Результат работы GROUPING() с несколькими аргументами отличен от нуля, если любое из выражений представляет суперагрегат NULL, следовательно, используя ее в предложении HAVING можно вернуть только суперагрегированные строки и отфильтровать обычные сгруппированные строки следующим образом:

-- запрос возвращает только аналитические результаты
SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       HAVING GROUPING(year, country, product) <> 0;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2000 | Finland | NULL    |   1600 |
| 2000 | India   | NULL    |   1350 |
| 2000 | USA     | NULL    |   1575 |
| 2000 | NULL    | NULL    |   4525 |
| 2001 | Finland | NULL    |     10 |
| 2001 | USA     | NULL    |   3000 |
| 2001 | NULL    | NULL    |   3010 |
| NULL | NULL    | NULL    |   7535 |
+------+---------+---------+--------+

Условия отбора для групп HAVING в групповых операциях.

Предложение HAVING, как и предложение WHERE, определяет условия выбора. Предложение WHERE задает условия для столбцов в списке выбора, но не может ссылаться на агрегатные функции. Предложение HAVING определяет условия для групп, обычно формируемые предложением GROUP BY. Результат запроса включает только группы, удовлетворяющие условиям HAVING. (Если GROUP BY отсутствует, то все строки неявным образом образуют единую совокупную группу.)

Предложение HAVING применяется чуть ли не последним, непосредственно перед отправкой элементов клиенту без какой-либо оптимизации. ( после HAVING может применяться инструкция LIMIT.)

Стандарт SQL требует, чтобы HAVING ссылался только на столбцы в предложении GROUP BY или столбцы, используемые в агрегатных функциях. Однако MySQL поддерживает расширение этого поведения и разрешает HAVING ссылаться на столбцы в списке SELECT, а также на столбцы во внешних подзапросах.

Если предложение HAVING ссылается на неоднозначный столбец, то появляется предупреждение. В следующем операторе col2 неоднозначен, потому что он используется и как псевдоним, и как имя столбца:

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

Предпочтение отдается стандартному поведению SQL, поэтому, если имя столбца HAVING используется как в GROUP BY, так и в качестве столбца с псевдонимом в списке столбцов SELECT, то предпочтение отдается столбцу в столбце GROUP BY.

Не используйте HAVING для элементов, которые должны быть в предложении WHERE. Например, не пишите следующее:

-- НЕПРАВИЛЬНО
SELECT col_name FROM tbl_name HAVING col_name > 0;
-- ПРАВИЛЬНО
SELECT col_name FROM tbl_name WHERE col_name > 0;

Предложение HAVING может ссылаться на агрегатные функции, при этом предложение WHERE это делать не может:

SELECT user, MAX(salary) FROM users
  GROUP BY user HAVING MAX(salary) > 10;