ROLLUP, CUBE, GROUPING SETS, OVER - группировка данных

Дополнительно к стандартным операторам GROUP BY и HAVING SQL Server поддерживает еще четыре специальных расширения для группировки данных: ROLLUP, CUBE, GROUPING SETS и OVER.

Базы данных:

CREATE DATABASE IF NOT EXISTS productsdb;

USE productsdb;

CREATE TABLE Products
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(30) NOT NULL,
Manufacturer VARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price DECIMAL NOT NULL
);

Заполнение баз данных:

USE productsdb;

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES
('iPhone X', 'Apple', 3, 71000),
('iPhone 8', 'Apple', 3, 56000),
('Galaxy S9', 'Samsung', 6, 56000),
('Galaxy S8', 'Samsung', 2, 46000),
('Honor 10', 'Huawei', 3, 26000);

ROLLUP

SELECT `Manufacturer`, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM `Products`
GROUP BY `Manufacturer` WITH ROLLUP;

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

SELECT `Manufacturer`, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM `Products`
GROUP BY `Manufacturer`, `ProductCount` WITH ROLLUP;

CUBE

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

SELECT `Manufacturer`, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM `Products`
GROUP BY `Manufacturer`, `ProductCount` WITH CUBE;

GROUPING SETS

Оператор GROUPING SETS аналогично ROLLUP и CUBE добавляет суммирующую строку для групп. Но при этом он не включает сами группам:

SELECT `Manufacturer`, COUNT(*) AS Models, `ProductCount`
FROM `Products`
GROUP BY GROUPING SETS(`Manufacturer`, `ProductCount`);

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

SELECT `Manufacturer`, COUNT(*) AS Models,
`ProductCount`, SUM(ProductCount) AS Units
FROM `Products`
GROUP BY GROUPING SETS(ROLLUP(Manufacturer), `ProductCount`);

С помощью скобок можно определить более сложные сценарии группировки:

SELECT `Manufacturer`, COUNT(*) AS Models,
`ProductCount`, SUM(ProductCount) AS Units
FROM `Products`
GROUP BY GROUPING SETS((`Manufacturer`, `ProductCount`), `ProductCount`);

OVER

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

SELECT `ProductName`, `Manufacturer`, `ProductCount`,
COUNT(*) OVER (PARTITION BY `Manufacturer`) AS Models,
SUM(`ProductCount`) OVER (PARTITION BY `Manufacturer`) AS Units
FROM `Products`;

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