Расширения 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 UnitsFROM ProductsGROUP BY Manufacturer WITH ROLLUP |
Как видно из скриншота, в конце таблицы была добавлена дополнительная строка, которая суммирует значение столбцов.
Альтернативный синтаксис запроса, который можно использовать, начиная с версии MS SQL Server 2008:
1 2 3 | SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS UnitsFROM ProductsGROUP BY ROLLUP(Manufacturer) |
При группировке по нескольким критериям ROLLUP будет создавать суммирующую строку для каждой из подгрупп:
1 2 3 | SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS UnitsFROM ProductsGROUP BY Manufacturer, ProductCount WITH ROLLUP |
При сортировке с помощью ORDER BY следует учитывать, что она применяется уже после добавления суммирующей строки.
CUBE
CUBE похож на ROLLUP за тем исключением, что CUBE добавляет суммирующие строки для каждой комбинации групп.
1 2 3 | SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS UnitsFROM ProductsGROUP BY Manufacturer, ProductCount WITH CUBE |
GROUPING SETS
Оператор GROUPING SETS аналогично ROLLUP и CUBE добавляет суммирующую строку для групп. Но при этом он не включает сами группам:
1 2 3 | SELECT Manufacturer, COUNT(*) AS Models, ProductCountFROM ProductsGROUP BY GROUPING SETS(Manufacturer, ProductCount) |
При этом его можно комбинировать с ROLLUP или CUBE. Например, кроме суммирующих строк по каждой из групп добавим суммирующую строку для всех групп:
1 2 3 4 | SELECT Manufacturer, COUNT(*) AS Models, ProductCount, SUM(ProductCount) AS UnitsFROM ProductsGROUP BY GROUPING SETS(ROLLUP(Manufacturer), ProductCount) |
С помощью скобок можно определить более сложные сценарии группировки:
1 2 3 4 | SELECT Manufacturer, COUNT(*) AS Models, ProductCount, SUM(ProductCount) AS UnitsFROM ProductsGROUP 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 UnitsFROM Products |
Выражение OVER ставится после агрегатной функции, затем в скобках идет выражение PARTITION BY и столбец, по которому выполняется группировка.
То есть в данном случае мы выбираем название модели, производителя, количество единиц модели и добавляем к этому количество моделей для данного производителя и общее количество единиц всех моделей производителя:
