GROUPING
ROLLUP и CUBE являются модификаторами GROUP BY. Оба модификатора вычисляют промежуточные итоги. ROLLUP принимает упорядоченный список столбцов, например (day, month, year), и вычисляет промежуточные итоги на каждом уровне агрегации, а затем общий итог. CUBE вычисляет промежуточные итоги для всех возможных комбинаций указанных столбцов. GROUPING определяет, какие строки, возвращённые ROLLUP или CUBE, являются сверхагрегатами, а какие — строками, которые были бы возвращены немодифицированным GROUP BY.
Функция GROUPING принимает несколько столбцов в качестве аргумента и возвращает битовую маску.
1 указывает, что строка, возвращённая модификатором ROLLUP или CUBE к GROUP BY, является промежуточным итогом0 указывает, что строка, возвращённая ROLLUP или CUBE, не является промежуточным итогом
GROUPING SETS
По умолчанию модификатор 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 строк в наборе. Затрачено: 0.409 сек.
Простые запросы
Получите число серверов в каждом дата-центре в разбивке по дистрибутивам:
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 строки в наборе. Затрачено: 0.212 сек.
SELECT
datacenter,
SUM (quantity) qty
FROM
servers
GROUP BY
datacenter;
┌─datacenter──┬─qty─┐
│ Westport │ 215 │
│ Schenectady │ 230 │
└─────────────┴─────┘
Получено 2 строки. Прошло: 0.277 сек.
SELECT
distro,
SUM (quantity) qty
FROM
servers
GROUP BY
distro;
┌─distro─┬─qty─┐
│ Arch │ 155 │
│ RHEL │ 290 │
└────────┴─────┘
Получено 2 строки. Затрачено: 0,352 сек.
SELECT
SUM(quantity) qty
FROM
servers;
┌─qty─┐
│ 445 │
└─────┘
Получена 1 строка. Время выполнения: 0.244 сек.
Сравнение нескольких предложений GROUP BY и GROUPING SETS
Разбиение данных без использования CUBE, ROLLUP или GROUPING SETS:
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 строк. Затрачено: 0.527 сек.
Получение тех же данных с использованием GROUPING SETS:
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 строк в наборе. Затрачено: 0.427 сек.
Сравнение CUBE с GROUPING SETS
CUBE в следующем запросе CUBE(datacenter, distro, version) создает иерархию, которая логически не имеет смысла. Некорректно рассматривать version одновременно для двух дистрибутивов (так как Arch и RHEL имеют разные циклы релизов и стандарты именования версий). Пример с GROUPING SETS, приведённый далее, более уместен, так как он группирует 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 строк в наборе. Затрачено: 0.355 сек.
Примечание
Версия в приведённом выше примере может быть неинформативной, если она не привязана к дистрибутиву; если бы мы отслеживали версию ядра, это могло бы быть логично, поскольку версию ядра можно связать с любым из дистрибутивов. Использование GROUPING SETS, как в следующем примере, может быть более удачным выбором.
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 строк в наборе. Затрачено: 1.036 сек.
