ГРУППИРОВКА
ROLLUP и CUBE - это модификаторы для GROUP BY. Оба из них рассчитывают промежуточные итоги. ROLLUP принимает упорядоченный список колонок, например (day, month, year), и вычисляет промежуточные итоги на каждом уровне агрегации, а затем общий итог. CUBE вычисляет промежуточные итоги для всех возможных комбинаций указанных колонок. GROUPING определяет, какие строки, возвращаемые ROLLUP или CUBE, являются суперагрегатами, а какие - это строки, которые были бы возвращены без модификации GROUP BY.
Функция GROUPING принимает несколько колонок в качестве аргумента и возвращает битовую маску.
1 указывает на то, что строка, возвращенная модификатором ROLLUP или CUBE к GROUP BY, является промежуточным итогом0 указывает на то, что строка, возвращенная ROLLUP или CUBE, является строкой, которая не является промежуточным итогом
НОЖИ ГРУППИРОВКИ
По умолчанию модификатор CUBE вычисляет промежуточные итоги для всех возможных комбинаций колонок, переданных в CUBE. GROUPING SETS позволяет вам указать конкретные комбинации для расчета.
Анализ иерархических данных является хорошим примером использования модификаторов ROLLUP, CUBE и GROUPING SETS. Примером здесь является таблица, содержащая данные о том, какая версия дистрибутива Linux установлена в двух дата-центрах. Может быть полезно рассмотреть данные по дистрибутиву, версии и местоположению.
Загрузка образца данных
CREATE TABLE servers ( datacenter VARCHAR(255),
distro VARCHAR(255) NOT NULL,
version VARCHAR(50) NOT NULL,
quantity INT
)
ORDER BY (datacenter, distro, version)
INSERT INTO servers(datacenter, distro, version, quantity)
VALUES ('Schenectady', 'Arch','2022.08.05',50),
('Westport', 'Arch','2022.08.05',40),
('Schenectady','Arch','2021.09.01',30),
('Westport', 'Arch','2021.09.01',20),
('Schenectady','Arch','2020.05.01',10),
('Westport', 'Arch','2020.05.01',5),
('Schenectady','RHEL','9',60),
('Westport','RHEL','9',70),
('Westport','RHEL','7',80),
('Schenectady','RHEL','7',80)
┌─datacenter──┬─distro─┬─version────┬─quantity─┐
│ Schenectady │ Arch │ 2020.05.01 │ 10 │
│ Schenectady │ Arch │ 2021.09.01 │ 30 │
│ Schenectady │ Arch │ 2022.08.05 │ 50 │
│ Schenectady │ RHEL │ 7 │ 80 │
│ Schenectady │ RHEL │ 9 │ 60 │
│ Westport │ Arch │ 2020.05.01 │ 5 │
│ Westport │ Arch │ 2021.09.01 │ 20 │
│ Westport │ Arch │ 2022.08.05 │ 40 │
│ Westport │ RHEL │ 7 │ 80 │
│ Westport │ RHEL │ 9 │ 70 │
└─────────────┴────────┴────────────┴──────────┘
10 rows in set. Elapsed: 0.409 sec.
Простые запросы
Получите количество серверов в каждом дата-центре по дистрибутиву:
SELECT
datacenter,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter,
distro;
┌─datacenter──┬─distro─┬─qty─┐
│ Schenectady │ RHEL │ 140 │
│ Westport │ Arch │ 65 │
│ Schenectady │ Arch │ 90 │
│ Westport │ RHEL │ 150 │
└─────────────┴────────┴─────┘
4 rows in set. Elapsed: 0.212 sec.
SELECT
datacenter,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter;
┌─datacenter──┬─qty─┐
│ Westport │ 215 │
│ Schenectady │ 230 │
└─────────────┴─────┘
2 rows in set. Elapsed: 0.277 sec.
SELECT
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
distro;
┌─distro─┬─qty─┐
│ Arch │ 155 │
│ RHEL │ 290 │
└────────┴─────┘
2 rows in set. Elapsed: 0.352 sec.
SELECT
SUM(quantity) qty
FROM
servers;
┌─qty─┐
│ 445 │
└─────┘
1 row in set. Elapsed: 0.244 sec.
Сравнение нескольких заявлений GROUP BY с НОЖАМИ ГРУППИРОВКИ
Разделение данных без CUBE, ROLLUP или НОЖЕЙ ГРУППИРОВКИ:
SELECT
datacenter,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter,
distro
UNION ALL
SELECT
datacenter,
null,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter
UNION ALL
SELECT
null,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
distro
UNION ALL
SELECT
null,
null,
SUM(quantity) qty
FROM
servers;
┌─datacenter─┬─distro─┬─qty─┐
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 445 │
└────────────┴────────┴─────┘
┌─datacenter──┬─distro─┬─qty─┐
│ Westport │ ᴺᵁᴸᴸ │ 215 │
│ Schenectady │ ᴺᵁᴸᴸ │ 230 │
└─────────────┴────────┴─────┘
┌─datacenter──┬─distro─┬─qty─┐
│ Schenectady │ RHEL │ 140 │
│ Westport │ Arch │ 65 │
│ Schenectady │ Arch │ 90 │
│ Westport │ RHEL │ 150 │
└─────────────┴────────┴─────┘
┌─datacenter─┬─distro─┬─qty─┐
│ ᴺᵁᴸᴸ │ Arch │ 155 │
│ ᴺᵁᴸᴸ │ RHEL │ 290 │
└────────────┴────────┴─────┘
9 rows in set. Elapsed: 0.527 sec.
Получение той же информации с использованием НОЖЕЙ ГРУППИРОВКИ:
SELECT
datacenter,
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
GROUPING SETS(
(datacenter,distro),
(datacenter),
(distro),
()
)
┌─datacenter──┬─distro─┬─qty─┐
│ Schenectady │ RHEL │ 140 │
│ Westport │ Arch │ 65 │
│ Schenectady │ Arch │ 90 │
│ Westport │ RHEL │ 150 │
└─────────────┴────────┴─────┘
┌─datacenter──┬─distro─┬─qty─┐
│ Westport │ │ 215 │
│ Schenectady │ │ 230 │
└─────────────┴────────┴─────┘
┌─datacenter─┬─distro─┬─qty─┐
│ │ │ 445 │
└────────────┴────────┴─────┘
┌─datacenter─┬─distro─┬─qty─┐
│ │ Arch │ 155 │
│ │ RHEL │ 290 │
└────────────┴────────┴─────┘
9 rows in set. Elapsed: 0.427 sec.
Сравнение CUBE с НОЖАМИ ГРУППИРОВКИ
CUBE в следующем запросе, CUBE(datacenter,distro,version), предоставляет иерархию, которая может быть неуместной. Не имеет смысла рассматривать Версию для двух дистрибутивов (так как Arch и RHEL не имеют одинакового цикла выпуска или стандартов именования версий). Пример с НОЖАМИ ГРУППИРОВКИ, следующий за этим, более уместен, поскольку он группирует distro и version в одном наборе.
SELECT
datacenter,
distro,
version,
SUM(quantity)
FROM
servers
GROUP BY
CUBE(datacenter,distro,version)
ORDER BY
datacenter,
distro;
┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐
│ │ │ 7 │ 160 │
│ │ │ 2020.05.01 │ 15 │
│ │ │ 2021.09.01 │ 50 │
│ │ │ 2022.08.05 │ 90 │
│ │ │ 9 │ 130 │
│ │ │ │ 445 │
│ │ Arch │ 2021.09.01 │ 50 │
│ │ Arch │ 2022.08.05 │ 90 │
│ │ Arch │ 2020.05.01 │ 15 │
│ │ Arch │ │ 155 │
│ │ RHEL │ 9 │ 130 │
│ │ RHEL │ 7 │ 160 │
│ │ RHEL │ │ 290 │
│ Schenectady │ │ 9 │ 60 │
│ Schenectady │ │ 2021.09.01 │ 30 │
│ Schenectady │ │ 7 │ 80 │
│ Schenectady │ │ 2022.08.05 │ 50 │
│ Schenectady │ │ 2020.05.01 │ 10 │
│ Schenectady │ │ │ 230 │
│ Schenectady │ Arch │ 2022.08.05 │ 50 │
│ Schenectady │ Arch │ 2021.09.01 │ 30 │
│ Schenectady │ Arch │ 2020.05.01 │ 10 │
│ Schenectady │ Arch │ │ 90 │
│ Schenectady │ RHEL │ 7 │ 80 │
│ Schenectady │ RHEL │ 9 │ 60 │
│ Schenectady │ RHEL │ │ 140 │
│ Westport │ │ 9 │ 70 │
│ Westport │ │ 2020.05.01 │ 5 │
│ Westport │ │ 2022.08.05 │ 40 │
│ Westport │ │ 7 │ 80 │
│ Westport │ │ 2021.09.01 │ 20 │
│ Westport │ │ │ 215 │
│ Westport │ Arch │ 2020.05.01 │ 5 │
│ Westport │ Arch │ 2021.09.01 │ 20 │
│ Westport │ Arch │ 2022.08.05 │ 40 │
│ Westport │ Arch │ │ 65 │
│ Westport │ RHEL │ 9 │ 70 │
│ Westport │ RHEL │ 7 │ 80 │
│ Westport │ RHEL │ │ 150 │
└─────────────┴────────┴────────────┴───────────────┘
39 rows in set. Elapsed: 0.355 sec.
примечание
Версия в приведенном выше примере может быть неуместной, если она не ассоциирована с дистрибутивом. Если бы мы отслеживали версию ядра, это могло бы иметь смысл, потому что версия ядра может быть ассоциирована с любым дистрибутивом. Использование НОЖЕЙ ГРУППИРОВКИ, как в следующем примере, может быть более подходящим выбором.
SELECT
datacenter,
distro,
version,
SUM(quantity)
FROM servers
GROUP BY
GROUPING SETS (
(datacenter, distro, version),
(datacenter, distro))
┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐
│ Westport │ RHEL │ 9 │ 70 │
│ Schenectady │ Arch │ 2022.08.05 │ 50 │
│ Schenectady │ Arch │ 2021.09.01 │ 30 │
│ Schenectady │ RHEL │ 7 │ 80 │
│ Westport │ Arch │ 2020.05.01 │ 5 │
│ Westport │ RHEL │ 7 │ 80 │
│ Westport │ Arch │ 2021.09.01 │ 20 │
│ Westport │ Arch │ 2022.08.05 │ 40 │
│ Schenectady │ RHEL │ 9 │ 60 │
│ Schenectady │ Arch │ 2020.05.01 │ 10 │
└─────────────┴────────┴────────────┴───────────────┘
┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐
│ Schenectady │ RHEL │ │ 140 │
│ Westport │ Arch │ │ 65 │
│ Schenectady │ Arch │ │ 90 │
│ Westport │ RHEL │ │ 150 │
└─────────────┴────────┴─────────┴───────────────┘
14 rows in set. Elapsed: 1.036 sec.
