Разница между Cube и Rollup
Пересказ статьи Ben Richardson. The Difference Between Rollup and Cube
Предложение GROUP BY используется для группировки результатов агрегатных функций по заданному столбцу. Однако предложение GROUP BY не выполняет операции агрегации на множестве уровней иерархии. Например, вы можете вычислить суммарную зарплату сотрудников для каждого отдела компании (один уровень иерархии), но не можете вычислить общую зарплату всех сотрудников, независимо от отделов, в которых они работают (два уровня иерархии).
Операторы ROLLUP позволяют вам распространить функциональность предложения GROUP BY на вычисление подытогов и общих итогов для множества столбцов. Оператор CUBE похож по функциональности на оператор ROLLUP; однако оператор CUBE может вычислять подытоги и общие итоги для всех перестановок указанных в нем столбцов.
В этой статье мы рассмотрим оба оператора - ROLLUP и CUBE - на простых примерах. Это позволит нам увидеть практическую разницу между ними, и когда следует использовать тот или иной из них.
Давайте создадим некоторые данные, которые мы будем использовать для выполнения демонстрационных запросов. Создайте новую базу данных с именем "company", и выполните затем в ней следующий код для создания таблицы "employee".
Теперь наполним таблицу данными, с которыми мы будем работать.
Давайте начнем с простого предложения GROUP BY, чтобы вычислить сумму зарплат всех сотрудников, сгруппированных по отделам.
Результатом будет следующее:
Здесь вы можете увидеть сумму зарплат сотрудников, сгруппированных по отделам. Однако мы не можем получить общую сумму, которая является суммой зарплат всех сотрудников из всех отделов компании. В этом может помочь оператор ROLLUP.
Как упоминалось выше, оператор ROLLUP используется для вычисления подытогов и общих итогов для множества столбцов, упомянутых в предложении "GROUP BY ROLLUP".
Посмотрим, как предложение ROLLUP помогает вычислить суммарную зарплату сотрудников, сгруппированных по отделам и общие итоги по зарплате всех сотрудников компании. Для этого мы поработаем со следующим простым запросом.
В этом коде мы использовали оператор ROLLUP для вычисления общих итогов по зарплате сотрудников со всех отделов. Однако в строке общих итогов ROLLUP будет возвращать NULL вместо имен отделов. Чтобы избежать этого, мы используем предложение Coalesce. Оно заменит NULL текстом "All Departments", не меняя имена отделов в этом столбце.
Оператор ROLLUP может также использоваться для вычисления подытогов для каждого столбца на основании группировки по этому столбцу.
Давайте рассмотрим пример, в котором мы хотим просуммировать зарплаты сотрудников по отделам и полу наряду с подытогами и общими итогами по всем зарплатами мужчин и женщин во всех отделах.
Запрос возвращает приведенную ниже таблицу. Как можно увидеть, он возвращает зарплаты сотрудников каждого отдела по трем категориям: мужчины, женщины и обоих полов. Подытогами являются строки со словом "All" в заголовках. Последняя строка представляет общие итоги, и поэтому содержит стово "All" в обоих столбцах.
Не удивляйтесь, что вы не видите итогов для мужчин в отделе IT. Это потому, что их там нет, как и в случае с женщинами в отделе продаж (sales).
Оператор CUBE также используется в комбинации с предложением GROUP BY, однако оператор CUBE производит результаты посредством генерации всех комбинаций столбцов, указанных в предложении GROUP BY CUBE.
Давайте использовать его для нахождения зарплаты, сгруппированной по отделам и полу. При внимательном рассмотрении видно, что имеется 4 возможных комбинации, по которым мы можем сгруппировать зарплату по отделам и полу. Вот они:
1. Зарплата сгруппирована по обоим столбцам - department и gender
2. Зарплата сгруппирована только по полу (gender)
3. Зарплата сгруппирована только по отделам (department)
4. Общие итоги по всем зарплатам.
Выполните следующий скрипт, чтобы увидеть эти четыре комбинации в результирующем наборе.
Результат выполнения запроса выглядит так:
Давайте найдем эти четыре комбинации, по которым сгруппирована зарплата в результатах выше.
1. В первых четырех строках и в строках с 6 по 9 зарплата сгруппирована и по отделам, и по полу.
2. В 5 и 10 строках зарплата сгруппирована только по полу, т.е. сотрудники мужчины и сотрудники женщины всех отделов.
3. В 11 строке мы видим общий итог, который является суммой зарплат сотрудников всех полов и всех отделов.
4. В последних 5 строках, т.е. строках с 12 по 16, зарплаты сгруппированы только по отделам.
Итак, мы видим в результатах все четыре комбинации, обсуждаемые выше.
Имеется только одно главное отличие в функциональности между операторами ROLLUP и CUBE. ROLLUP генерирует агрегатные результаты для выбранных столбцов иерархическим образом. CUBE, напротив, генерирует агрегатные результаты, которые содержат все возможные комбинации для выбранных столбцов.
Чтобы это понять, взгляните на результирующий набор оператора ROLLUP, когда сумма зарплат сотрудников группировалась по отделу и полу:
Здесь данные агрегируются иерархическим образом. В строках 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, который обеспечивает все возможные комбинации.
В этой статье мы рассмотрим оба оператора - 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, который обеспечивает все возможные комбинации.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой