В материале рассматривается особенности применения групповых операций GROUP BY и условия отбора HAVING для выбранных групп, а так же использование необязательного оператора ROLLUP в инструкции GROUP BY, который позволяет отвечать на вопросы на нескольких уровнях анализа с помощью одного запроса.
GROUP BY col [WITH ROLLUP];GROUPING(), проверка суперагрегированного значения OLAP;HAVING в групповых операциях.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;