Разница между операторами cube, rollup, groupBy в SQL

Быстрый ответ

Операторы CUBE, ROLLUP и GROUP BY различаются способами агрегирования данных:

1. GROUP BY: Группирует записи по указанным атрибутам, агрегируя информацию.

   SQL

   Скопировать код

   SELECT Страна, COUNT(Пользователь)
   FROM Пользователи GROUP BY Страна;

   Запустить код

2. ROLLUP: Позволяет создавать иерархические подитоги, двигаясь от детальных данных к сводной информации.

   SQL

   Скопировать код

   SELECT Страна, Город, COUNT(Пользователь)
   FROM Пользователи GROUP BY ROLLUP(Страна, Город);

   Запустить код

3. CUBE: Обеспечивает возможность агрегации данных по всем возможным комбинациям указанных столбцов, облегчая многомерный анализ.

   SQL

   Скопировать код

   SELECT Страна, Город, COUNT(Пользователь)
   FROM Пользователи GROUP BY CUBE(Страна, Город);

   Запустить код

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

Аналитический подход к группировке

Мультимодальное агрегирование с CUBE

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

Подробнее об этом расскажет наш спикер на видео

Иерархическое создание отчетов с применением ROLLUP

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

Простая группировка с использванием GROUP BY

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

Визуализация

В продуктовом магазине, способы группировки можно сравнить со способами покупки фруктов:

CUBE: 🍎🍐🍊 + 🍎🍐 + 🍎 + 🍊 + 🍐 + ()
# Возможность увидеть все комбинации выбора, включая пустую корзину.

ROLLUP: 🍎🍐🍊 ➡️ 🍎🍐 ➡️ 🍎 ➡️ ()
# Последовательное добавление фруктов в корзину с наращиванием количества.

GROUP BY: 🍎🍎, 🍐🍐, 🍊🍊
# Отдельные группы для каждого вида фруктов.

Выбор метода группировки определяется конкретной задачей, так же как и стратегия покупки фруктов.

Практические аспекты SQL операторов

CUBE и ROLLUP в арсенале инструментов СУБД

Для многомерного анализа в хранилищах данных наиболее подходят CUBE и ROLLUP. Эти операторы очень мощные, однако следует учитывать риск увеличения числа строк в результате работы оператора CUBE.

Различение подитогов с помощью функций GROUPING

С помощью функций GROUPING или GROUPING_ID можно различать подитоги, которые создаются с помощью CUBE и ROLLUP, от обычных результатов группировки.

Требования к ресурсам при использовании оператора CUBE

В связи с большим количеством комбинаций CUBE может быть ресурсоемким. По этой причине, ROLLUP обычно является более эффективным выбором.

Точная группировка с использованием GROUPING SETS

Команда GROUPING SETS будет полезна, когда встроенные возможности CUBE и ROLLUP не отвечают требованиям запроса. Она позволяет точно определить группировку.

Полезные материалы

1. GROUP BY (Transact-SQL) – SQL Server — официальная документация Microsoft.
2. PostgreSQL: Documentation: 16: 7.2. Table Expressions — подход к группировке в PostgreSQL.
3. Audit SQL Server Logins Using SQL Profiler — пример использования CUBE и ROLLUP при аудите в SQL Server.
4. The Power of the CUBE, ROLLUP and GROUPING SETS in SQL Server — полный разбор использования CUBE и ROLLUP на примере SQL Server.