Расширения SQL Server для группировки

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

ROLLUP

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

1 2 3

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

Как видно из скриншота, в конце таблицы была добавлена дополнительная строка, которая суммирует значение столбцов.

Альтернативный синтаксис запроса, который можно использовать, начиная с версии MS SQL Server 2008:

1 2 3

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

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

1 2 3

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

При сортировке с помощью ORDER BY следует учитывать, что она применяется уже после добавления суммирующей строки.

CUBE

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

1 2 3

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

GROUPING SETS

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

1 2 3

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

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

1 2 3 4

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

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

1 2 3 4

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

OVER

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

1 2 3 4

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

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

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