Разница между Rollup и Cube

Пересказ статьи Ben Richardson. The Difference Between Rollup and Cube

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

Операторы ROLLUP позволяют вам распространить функциональность предложения GROUP BY на вычисление подытогов и общих итогов для множества столбцов. Оператор CUBE похож по функциональности на оператор ROLLUP; однако оператор CUBE может вычислять подытоги и общие итоги для всех перестановок указанных в нем столбцов.

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

Создание демонстрационных данных

Давайте создадим некоторые данные, которые мы будем использовать для выполнения демонстрационных запросов. Создайте новую базу данных с именем «company», и выполните затем в ней следующий код для создания таблицы «employee».

USE company;
  CREATE TABLE employee
  (
      id INT PRIMARY KEY,
      name VARCHAR(50) NOT NULL,
      gender VARCHAR(50) NOT NULL,
      salary INT NOT NULL,
      department VARCHAR(50) NOT NULL
   )

Теперь наполним таблицу данными, с которыми мы будем работать.

INSERT INTO employee
  VALUES
  (1, 'David', 'Male', 5000, 'Sales'),
  (2, 'Jim', 'Female', 6000, 'HR'),
  (3, 'Kate', 'Female', 7500, 'IT'),
  (4, 'Will', 'Male', 6500, 'Marketing'),
  (5, 'Shane', 'Female', 5500, 'Finance'),
  (6, 'Shed', 'Male', 8000, 'Sales'),
  (7, 'Vik', 'Male', 7200, 'HR'),
  (8, 'Vince', 'Female', 6600, 'IT'),
  (9, 'Jane', 'Female', 5400, 'Marketing'),
  (10, 'Laura', 'Female', 6300, 'Finance'),
  (11, 'Mac', 'Male', 5700, 'Sales'),
  (12, 'Pat', 'Male', 7000, 'HR'),
  (13, 'Julie', 'Female', 7100, 'IT'),
  (14, 'Elice', 'Female', 6800,'Marketing'),
  (15, 'Wayne', 'Male', 5000, 'Finance')

Простой оператор GROUP BY

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

SELECT department, SUM(salary) AS Salary_Sum
  FROM employee
  GROUP BY department;

Результатом будет следующее:

Department	Salary_Sum
Finance		16800
HR		20200
IT		21200
Marketing	18700
Sales		18700

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

Оператор ROLLUP

Как упоминалось выше, оператор ROLLUP используется для вычисления подытогов и общих итогов для множества столбцов, упомянутых в предложении «GROUP BY ROLLUP».

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

SELECT COALESCE (department, 'All Departments') AS Department,
  SUM(salary) AS Salary_Sum
  FROM employee
  GROUP BY ROLLUP (department)

В этом коде мы использовали оператор ROLLUP для вычисления общих итогов по зарплате сотрудников со всех отделов. Однако в строке общих итогов ROLLUP будет возвращать NULL вместо имен отделов. Чтобы избежать этого, мы используем предложение Coalesce. Оно заменит NULL текстом «All Departments», не меняя имена отделов в этом столбце.

Department	Salary_Sum
Finance		16800
HR		20200
IT		21200
Marketing	18700
Sales		18700
All Departments	95600

Нахождение подытогов с помощью оператора ROLLUP

Оператор ROLLUP может также использоваться для вычисления подытогов для каждого столбца на основании группировки по этому столбцу.

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

SELECT
  COALESCE (department, 'All Departments') AS Department,
  COALESCE (gender,'All Genders') AS Gender,
  SUM(salary) AS Salary_Sum
  FROM employee
  GROUP BY ROLLUP (department, gender);

Запрос возвращает приведенную ниже таблицу. Как можно увидеть, он возвращает зарплаты сотрудников каждого отдела по трем категориям: мужчины, женщины и обоих полов. Подытогами являются строки со словом «All» в заголовках. Последняя строка представляет общие итоги, и поэтому содержит стово «All» в обоих столбцах.

Department	Gender	Salary_Sum
Finance	Female	11800
Finance	Male	5000
Finance	All Genders	16800
HR	Female	6000
HR	Male	14200
HR	All Genders	20200
IT	Female	21200
IT	All Genders	21200
Marketing	Female	12200
Marketing	Male	6500
Marketing	All Genders	18700
Sales	Male	18700
Sales	All Genders	18700
All Departments	All Genders	95600

Не удивляйтесь, что вы не видите итогов для мужчин в отделе IT. Это потому, что их там нет, как и в случае с женщинами в отделе продаж (sales).

Оператор CUBE

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

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

1. Зарплата сгруппирована по обоим столбцам — department и gender
2. Зарплата сгруппирована только по полу (gender)
3. Зарплата сгруппирована только по отделам (department)
4. Общие итоги по всем зарплатам.

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

SELECT
  COALESCE (department, 'All Departments') AS Department,
  COALESCE (gender,'All Genders') AS Gender,
  SUM(salary) AS Salary_Sum
  FROM employee
  GROUP BY CUBE (department, gender)

Результат выполнения запроса выглядит так:

Row No	Department	Gender	Salary_Sum
1	Finance	Female	11800
2	HR	Female	6000
3	IT	Female	21200
4	Marketing	Female	12200
5	All Departments	Female	51200
6	Finance	Male	5000
7	HR	Male	14200
8	Marketing	Male	6500
9	Sales	Male	18700
10	All Departments	Male	44400
11	All Departments	All Genders	95600
12	Finance	All Genders	16800
13	HR	All Genders	20200
14	IT	All Genders	21200
15	Marketing	All Genders	18700
16	Sales	All Genders	18700

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

1. В первых четырех строках и в строках с 6 по 9 зарплата сгруппирована и по отделам, и по полу.
2. В 5 и 10 строках зарплата сгруппирована только по полу, т.е. сотрудники мужчины и сотрудники женщины всех отделов.
3. В 11 строке мы видим общий итог, который является суммой зарплат сотрудников всех полов и всех отделов.
4. В последних 5 строках, т.е. строках с 12 по 16, зарплаты сгруппированы только по отделам.

Итак, мы видим в результатах все четыре комбинации, обсуждаемые выше.

Разница между ROLLUP и CUBE

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

Чтобы это понять, взгляните на результирующий набор оператора ROLLUP, когда сумма зарплат сотрудников группировалась по отделу и полу:

Row Number	Department	Gender	Salary_Sum
1	Finance	Female	11800
2	Finance	Male	5000
3	Finance	All Genders	16800
4	HR	Female	6000
5	HR	Male	14200
6	HR	All Genders	20200
7	IT	Female	21200
8	IT	All Genders	21200
9	Marketing	Female	12200
10	Marketing	Male	6500
11	Marketing	All Genders	18700
12	Sales	Male	18700
13	Sales	All Genders	18700
14	All Departments	All Genders	95600

Здесь данные агрегируются иерархическим образом. В строках 1,2,4,5,7,9,10 и 12 зарплаты группируются по отлелу и полу. В строках 3,6,8,11 и 13 зарплаты группируются только по отделу.

Наконец, в строке 14 мы имеем общую сумму всех зарплат всех сотрудников обоих полов и всех отделов. Здесь имеется три комбинации, котооые имеют иерархическую природу. Вот они:

1. Отдел и пол
2. Отдел
3. Общий итог

У нас нет только зарплаты, сгруппированной только по полу. Причина состоит в том, что пол является самым нижним уровнем иерархии.

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

1. Отдел и пол
2. Только отдел
3. Только пол
4. Общий итог

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

Какой оператор следует использовать?

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

Например, если вам требуется получить численность населения страны, штата и города, ROLLUP мог бы просуммировать численность на трех уровнях. Сначала он вернул численность населения уровня Страна-Штат-Город. Затем — Страна-Штат и, наконец, численность населения уровня страны. Также был бы обеспечен уровень общего итога.

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

1. Страна-Штат-Город
2. Штат-Город
3. Город
4. Страна-Штат
5. Штат
6. Страна-Город
7. Страна
8. Все

Итак, общее правило такое. Если вы имеете иерархические данные (например, страна->штат->город или отдел->менеджер->продавец и т.п.), то вам обычно требуются иерархические результаты, и вы используете ROLLUP для группировки данных.

Если вы имеете неиерархические данные (например, город-пол-национальность), то вам не нужны иерархические результаты, поэтому вы используете CUBE, который обеспечивает все возможные комбинации.

Добавить комментарий