Начиная с MySQL 4.1.1, конструкцию GROUP BY предусматривает модификатор WITH ROLLUP, который добавляет
несколько дополнительных строк к итоговому выводу. Эти строки представляют итоговые операции высшего уровня
(суперагрегатные). ROLLUP, таким образом, позволяет ответить на вопросы на многих уровнях анализа в пределах одного
запроса. Это может быть использовано, например, для представления поддержки OLAP-операций (Online Analytical
Processing - онлайновой аналитической обработки).
Для примера предположим, что в таблице sales имеются столбцы year, country, product и profit для хранения информации
о рентабельности торговли:
CREATE TABLE sales
(
year INT NOT NULL, country VARCHAR(20) NOT NULL, product VARCHAR(32) NOT NULL, profit INT );
Итоговая информация по годам может быть получена простым GROUP BY следующим образом:
mysql> SELECT year, SUM(profit) FROM sales GROUP BY year; Здесь мы видим общую прибыль за каждый год, но если нужно просмотреть суммарную прибыль
за все года, придется просуммировать отдельные показатели вручную, либо выдать еще один запрос.
Вместо этого можно запустить ROLLUP, который представляет оба уровня анализа в пределах одного запроса. Добавление
модификатора WITH ROLLUP к конструкции GROUP BY заставляет запрос сгенерировать еще одну строку, которая показывает
общий итог по всем годам:
mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
Строка общего итога идентифицируется значением NULL столбца year. ROLLUP порождает более сложный эффект, когда в
конструкции group BY участвуют несколько столбцов. В этом случае каждый раз, когда появляется "разрыв" (изменение
в значении) в любом группируемом столбце кроме последнего, запрос генерирует дополнительную суперагрегатную строку.
Например, с использованием ROLLUP итоговая информация по таблице sales, сгруппированная по year, country и
product, выглядит так:
mysql> SELECT year, country, product, SUM(profit) FROM sales GROUP BY year, country, product;
Вывод показывает суммарные значения только на уровне анализа по году/стране/продукту. Когда добавляется ROLLUP,
запрос выдает несколько дополнительных строк:
mysql> SELECT year, country, product, SUM(profit) -> FROM sales -> GROUP BY year, country, product WITH ROLLUP;
Добавление rollup к этому запросу привело к включению суммарной информации на четырех уровнях анализа, а не
только одном. Вот как следует интерпретировать вывод ROLLUP:
После каждого множества строк о продукте для данного года и страны генерируется дополнительная итоговая
строка, представляющая итог по всем продуктам. Эти строки имеют в столбце product значения null.
После каждого множества строк по данному году добавляется дополнительная итоговая строка, представляющая итог
по всем странам и продуктам. В этих строках столбцы country и product имеют значения NULL.
И, наконец, после всех остальных добавляется строка, показывающая общий итогдля всех лет, стран и продуктов.
Значения столбцов year, country и product в этой строке равны NULL.
Другие соглашения относительно использования ROLLUP
Ниже описаны некоторые специфичные для MySQL аспекты реализации ROLLUP.
Когда вы применяете ROLLUP, вы не можете одновременно использовать конструкцию ORDER BY для сортировки
результатов. Другими словами, ROLLUP и ORDER BY являются взаимоисключающими. Однако у вас все же есть определенная
возможность повлиять на порядок сортировки. В MySQL конструкция GROUP BY сортирует результаты, и вы можете явно
указывать ключевые слова ASC и DESC для столбцов, перечисленных в конструкции GROUP BY, чтобы указать порядок
сортировки индивидуальных столбцов.
(Итоговые строки высшего уровня, добавленные ROLLUP, по-прежнему появятся после строк, по которым они вычисляются,
независимо от порядка сортировки.)
LIMIT может быть использовано для ограничения количества возвращаемых клиенту строк. LIMIT указывается после
ROLLUP, таким образом влияя и на дополнительные строки, сгенерированные ROLLUP, например:
mysql> SELECT year, country, product, SUM(profit) -> FROM sales -> GROUP BY year, country, product WITH ROLLUP -> LIMIT 5;
Применение LIMIT вместе с ROLLUP может дать результат, который труднее интерпретировать, поскольку теряется
контекст, необходимый для понимания суперагрегатных строк.
Индикатор NULL в каждой суперагрегатной строке генерируется, когда строка отправляется клиенту. Сервер смотрит
на крайние левые имена столбцов, перечисленные в конструкции GROUP BY, у которых изменяется значение. Для любого
столбца результирующего набора с именем, лексически соответствующим любому из этих имен, устанавливается значение
NULL. (Если вы указываете группируемые столбцы по номерам, сервер идентифицирует, какие столбцы устанавливать в
NULL, по их номерам.)
Так как значения NULL в суперагрегатных столбцах помещаются в результирующий набор на
поздней стадии обработки запроса, вы не можете проверять их на предмет значения NULL внутри самого запроса.
Например, нельзя добавить HAVING product IS NULL к запросу, чтобы исключить из результата все, кроме суперагрегатных строк.
С другой стороны, значения NULL появляются именно как NULL на клиентской стороне и могут быть протестированы с
использованием любого клиентского программного интерфейса MySQL.
