Sql group by rollup: MS SQL Server и T-SQL

MS SQL Server и T-SQL

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

Последнее обновление: 19.07.2017

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

ROLLUP

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

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

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

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

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

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

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

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

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 и столбец,
по которому выполняется группировка.

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

Понимание различий между CUBE и ROLLUP

Мое задание состояло в том, чтобы выяснить «how many invoices are written for each date?»

Я немного застрял и попросил моего профессора о помощи. Она отправила мне по электронной почте запрос, который отвечал бы на вопрос: «сколько печей каждого типа и версии было построено?
Для вызова, но без дополнительных очков, включите общее количество печей.»

Это был вопрос, который она мне послала:

SELECT STOVE.Type + STOVE.Version AS 'Type+Version'
, COUNT(*) AS 'The Count'
FROM STOVE
GROUP BY STOVE.Type + STOVE.Version WITH ROLLUP;

Поэтому я подправил этот запрос, пока он не удовлетворил мои потребности. Вот что я придумал:

SELECT InvoiceDt
, COUNT(InvoiceNbr) AS 'Number of Invoices' 
FROM INVOICE 
GROUP BY InvoiceDt WITH ROLLUP 
ORDER BY InvoiceDt ASC;

И он вернул следующие результаты, которые я хотел.

Как бы то ни было, я решил почитать статью ROLLUP и начал со статьи из Microsoft . В нем говорилось, что предложение ROLLUP аналогично предложению CUBE, но отличается от предложения CUBE следующим образом:

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

Поэтому я решил заменить ROLLUP в своем запросе на CUBE, чтобы посмотреть, что произойдет. Они дали те же результаты. Я предполагаю, что это где я путаюсь.

Похоже, что если вы используете тот тип запроса, который я здесь использую, то между этими двумя предложениями нет никакой практической разницы. Это правда? Или я чего-то не понимаю? Когда я закончил читать статью Microsoft, я подумал, что мои результаты должны были быть другими, используя предложение CUBE.

sql

sql-server

sql-server-2008

tsql

Поделиться

Источник

krebshack    

13 августа 2011 в 21:41

4 ответа

• Как объединить CUBE и ROLLUP?

  У меня есть запрос с важной агрегацией GROUP BY . Вот упрощенная схема ( SQL Fiddle ): CREATE TABLE [data] ( [year] [int] NOT NULL , [month] [int] NOT NULL , [country] [varchar] (32) NOT NULL , [state] [varchar] (32), [city] [varchar] (32) NOT NULL , [federation] [varchar] (32) NOT NULL , [id]…

• Когда использовать группирующие наборы, CUBE и ROLLUP

  Недавно я узнал о группирующих наборах, CUBE и ROLLUP для определения нескольких группирующих наборов в sql server. Я спрашиваю, при каких обстоятельствах мы используем эти функции ? Каковы преимущества и преимущества их использования? SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS…

158

Вы не увидите никакой разницы, так как вы сворачиваете только одну колонку. Рассмотрим пример, где мы это делаем

ROLLUP (YEAR, MONTH, DAY)

С ROLLUP , он будет иметь следующие результаты:

YEAR, MONTH, DAY
YEAR, MONTH
YEAR
()

С CUBE он будет иметь следующее:

YEAR, MONTH, DAY
YEAR, MONTH
YEAR, DAY
YEAR
MONTH, DAY
MONTH
DAY
()

CUBE по существу содержит все возможные сценарии свертки для каждого узла, в то время как ROLLUP будет поддерживать иерархию в такте (поэтому он не пропустит MONTH и не покажет YEAR/DAY,, в то время как CUBE будет)

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

Надеюсь, это поможет.

Поделиться

Derek Kromm    

13 августа 2011 в 23:25

77

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

SELECT * INTO #TEMP
FROM
(
    SELECT 'Quarter 1' PERIOD,'Amar' NAME ,97 MARKS
    UNION ALL
    SELECT 'Quarter 1','Ram',88 
    UNION ALL
    SELECT 'Quarter 1','Simi',76 
    UNION ALL
    SELECT 'Quarter 2','Amar',94 
    UNION ALL
    SELECT 'Quarter 2','Ram',82 
    UNION ALL
    SELECT 'Quarter 2','Simi',71 
    UNION ALL
    SELECT 'Quarter 3' ,'Amar',95 
    UNION ALL
    SELECT 'Quarter 3','Ram',83 
    UNION ALL
    SELECT 'Quarter 3','Simi',77
    UNION ALL
    SELECT 'Quarter 4' ,'Amar',91 
    UNION ALL
    SELECT 'Quarter 4','Ram',84 
    UNION ALL
    SELECT 'Quarter 4','Simi',79
)TAB

1. ROLLUP (можно найти итог для соответствующего одного столбца)

(а) получить общий балл каждого студента во всех четвертях.

SELECT * FROM #TEMP
UNION ALL
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY NAME,PERIOD 
WITH ROLLUP
HAVING PERIOD IS NULL AND NAME IS NOT NULL 
// Having is used inorder to emit a row that is the total of all totals of each student

Ниже приведен результат (а)

(Б) Если вам нужно получить общий балл за каждый квартал

SELECT * FROM #TEMP
UNION ALL
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY PERIOD,NAME 
WITH ROLLUP
HAVING PERIOD IS NOT NULL AND NAME IS NULL

Ниже приведен результат (b)

2. CUBE (найти общее количество за квартал, а также студентов в одном кадре)

SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY NAME,PERIOD 
WITH CUBE
HAVING PERIOD IS NOT NULL OR NAME IS NOT NULL

Ниже приведен результат CUBE

Теперь вы можете задаться вопросом об использовании ROLLUP и CUBE в реальном времени. Иногда нам нужен отчет, в котором мы должны видеть итог каждой четверти и итог каждого студента в одном кадре. Вот пример

Я немного изменяю вышеприведенный запрос CUBE, так как нам нужен итог для обоих итогов.

SELECT CASE WHEN PERIOD IS NULL THEN 'TOTAL' ELSE PERIOD END PERIOD,
CASE WHEN NAME IS NULL THEN 'TOTAL' ELSE NAME END NAME,
SUM(MARKS) MARKS
INTO #TEMP2
FROM #TEMP
GROUP BY NAME,PERIOD 
WITH CUBE

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + PERIOD + ']', 
               '[' + PERIOD + ']')
               FROM    (SELECT DISTINCT PERIOD FROM #TEMP2) PV  
               ORDER BY PERIOD    


DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 SELECT * FROM #TEMP2
             ) x
             PIVOT 
             (
                 SUM(MARKS)
                 FOR [PERIOD] IN (' + @cols + ')
            ) p;' 

EXEC SP_EXECUTESQL @query

Теперь вы получите следующий результат

Поделиться

Sarath Avanavu    

01 декабря 2014 в 06:11

6

Это происходит потому, что у вас есть только один столбец, по которому вы группируетесь.

Добавьте Group by InvoiceDt, InvoiceCountry (или любое другое поле, которое даст вам больше данных.

С помощью куба вы получите сумму для каждого InvoiceDt, и вы получите сумму для каждого InvoiceCountry.

Поделиться

JeffO    

13 августа 2011 в 23:25

1

Вы можете найти более подробную информацию о группировке SET, CUBE, ROLL UP. TL;DR они просто расширяют группу на + UNION все в некотором роде, чтобы получить агрегацию 🙂

https://technet.microsoft.com/en-us/ library/bb510427(v=sql.105).aspx

Поделиться

o0omycomputero0o    

04 января 2017 в 07:22

Похожие вопросы:

PostgreSQL:How для использования группирующих наборов, CUBE и ROLLUP для сводных итогов

У меня есть следующий фрагмент кода, который я написал в MySQL 5.6: INSERT INTO Totals SELECT Zone, State, COUNT(Sponsored), COUNT(Enrolled), COUNT(PickedUp) FROM MasterData GROUP BY Zone, StateName…

В чем разница между операторами cube, rollup и groupBy?

Вопрос в значительной степени заключен в названии. Я не могу найти никакой подробной документации относительно различий. Я действительно замечаю разницу, потому что при обмене вызовами функций cube…

Понимание ROLLUP в SQL

Я понял, что CUBE просто генерирует все перестановки, но у меня возникли проблемы с ROLLUP. Кажется, нет никаких хороших ресурсов в интернете или в книге, которую я читаю, для объяснения SQL для…

Как объединить CUBE и ROLLUP?

У меня есть запрос с важной агрегацией GROUP BY . Вот упрощенная схема ( SQL Fiddle ): CREATE TABLE [data] ( [year] [int] NOT NULL , [month] [int] NOT NULL , [country] [varchar] (32) NOT NULL ,…

Когда использовать группирующие наборы, CUBE и ROLLUP

Недавно я узнал о группирующих наборах, CUBE и ROLLUP для определения нескольких группирующих наборов в sql server. Я спрашиваю, при каких обстоятельствах мы используем эти функции ? Каковы…

Есть ли способ сделать ROLLUP или CUBE в R, как в sql?

Есть ли способ суммировать данные в R, используя такие функции, как ROLLUP и CUBE в sql? Если да, то может ли кто-нибудь указать мне направление примера?

Как Postgres реализует операторы CUBE -, ROLLUP-и группирующих множеств

Меня интересует, как Postgres реализует операторы CUBE -, ROLLUP-и группирующих множеств?

GROUP BY с несколькими наборами группировок, CUBE и ROLLUP предложениями

Сегодня я прочитал совет об использовании нескольких группирующих наборов, предложений CUBE и ROLLUP в предложении GROUP BY. Вы можете указать несколько наборов группировок, предложения CUBE и…

Нет ROLLUP и CUBE в PostgreSQL 9.4?

У меня есть несколько запросов, содержащих операторы rollup и cube. Когда я запускаю их на компьютере с PostgreSQL 9.4 и PgAdmin III, он говорит, что таких функций не существует. Те же самые запросы…

Разница между CUBE и с кубом

Я обнаружил, что в MSSQL существует и то, и другое SELECT <list of columns>, sum(measure) as measure FROM fact-table GROUP BY <list of columns> WITH CUBE и SELECT <list of…

MySQL 8.0: функция GROUPING — статьи sqlinfo.ru

Дата: 5.05.2017

Данная статья является переводом статьи Chaithra Gopalareddy.

Начиная с MySQL 8.0.1, сервер поддерживает SQL-функцию GROUPING. Эта функция используется для того, чтобы при использовании модификатора ROLLUP отличать NULL в строках с итоговыми/подытоговыми значениями от NULL в обычных строках.

Ведение

Сервер MySQL поддерживает модификатор ROLLUP при выполнении группирующих запросов. Ниже пример его использования:

mysql> create table t1 (a integer, b integer, c integer);
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into t1 values (111,11,11);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into t1 values (222,22,22);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t1 values (111,12,12);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t1 values (222,23,23);
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from t1;
+——+——+——+
| a    | b    | c    |
+——+——+——+
|  111 |   11 |   11 |
|  222 |   22 |   22 |
|  111 |   12 |   12 |
|  222 |   23 |   23 |
+——+——+——+
4 rows in set (0.00 sec)
 
mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;
+——+——+——+
| a    | b    | SUM  |
+——+——+——+
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  111 | NULL |   23 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
|  222 | NULL |   45 |
| NULL | NULL |   68 |
+——+——+——+
7 rows in set (0.00 sec)
 

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

Давайте добавим значения NULL в исходные данные таблицы:

mysql> INSERT INTO t1 values (1111,NULL,112);
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO t1 values (NULL,112,NULL);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t1;
+——+——+——+
| a    | b    | c    |
+——+——+——+
|  111 |   11 |   11 |
|  222 |   22 |   22 |
|  111 |   12 |   12 |
|  222 |   23 |   23 |
| 1111 | NULL |  112 |
| NULL |  112 | NULL |
+——+——+——+
6 rows in set (0.00 sec)
 

Теперь при использовании модификатора ROLLUP становится сложно понять — какие значения NULL относятся к обычным сгруппированным значениям, а какие указывают на подытоги и заключительное итоговое значение:

mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;
+——+——+——+
| a    | b    | SUM  |
+——+——+——+
| NULL |  112 | NULL |
| NULL | NULL | NULL |
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  111 | NULL |   23 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
|  222 | NULL |   45 |
| 1111 | NULL |  112 |
| 1111 | NULL |  112 |
| NULL | NULL |  180 |
+——+——+——+
11 rows in set (0.01 sec)
 

Что нового в MySQL-8.0.1

В приведенном выше примере можно использовать функцию GROUPING, чтобы отличать NULL, добавляемые ROLLUP, от NULL, относящихся к сгруппированным значениям. Функция GROUPING, использующая в качестве аргумента имя поля, возвращает 1, если значение NULL в этом поле является результатом действия ROLLUP. В противном случае возвращается 0.

mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a), GROUPING(b) FROM t1 GROUP BY a,b WITH ROLLUP;
+——+——+——+————-+————-+
| a    | b    | SUM  | GROUPING(a) | GROUPING(b) |
+——+——+——+————-+————-+
| NULL |  112 | NULL |           0 |           0 |
| NULL | NULL | NULL |           0 |           1 |
|  111 |   11 |   11 |           0 |           0 |
|  111 |   12 |   12 |           0 |           0 |
|  111 | NULL |   23 |           0 |           1 |
|  222 |   22 |   22 |           0 |           0 |
|  222 |   23 |   23 |           0 |           0 |
|  222 | NULL |   45 |           0 |           1 |
| 1111 | NULL |  112 |           0 |           0 |
| 1111 | NULL |  112 |           0 |           1 |
| NULL | NULL |  180 |           1 |           1 |
+——+——+——+————-+————-+
11 rows in set (0.01 sec)
 

Как видно из примера, GROUPING(b) возвращает 1 только для тех строк, у которых в поле b стоит NULL, являющийся результатом действия ROLLUP (иными словами, для строк с подытогом).

Другой способ использования GROUPING — указать в качестве аргумента несколько столбцов. Тогда GROUPING вернет целое число, полученное из битовой маски, имеющей 1 у тех аргументов, для которых GROUPING(argument) равен 1. Например:

mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a,b) FROM t1 GROUP BY a,b WITH ROLLUP;
+——+——+——+—————+
| a    | b    | SUM  | GROUPING(a,b) |
+——+——+——+—————+
| NULL |  112 | NULL |             0 |
| NULL | NULL | NULL |             1 |
|  111 |   11 |   11 |             0 |
|  111 |   12 |   12 |             0 |
|  111 | NULL |   23 |             1 |
|  222 |   22 |   22 |             0 |
|  222 |   23 |   23 |             0 |
|  222 | NULL |   45 |             1 |
| 1111 | NULL |  112 |             0 |
| 1111 | NULL |  112 |             1 |
| NULL | NULL |  180 |             3 |
+——+——+——+—————+
11 rows in set (0.00 sec)
 

В данном примере GROUPING (a,b), возвращающая 3, означает, что значение NULL в столбце a и значение NULL в столбце b являются результатом действия ROLLUP. Для строк, у которых GROUPING(a,b) равно 1, значение NULL только в столбце b являются результатом действия ROLLUP.

Другие способы использования функции GROUPING

Функцию GROUPING можно использовать в части перечисления полей после ключевого слова SELECT или в части HAVING (условия отбора после группировки). Например, во втором случае можно получить отображение только тех строк, которые содержат итоговые/подытоговые значения, как показано в примере ниже:

mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP HAVING GROUPING(a) = 1 or GROUPING(b) = 1;
+——+——+——+
| a    | b    | SUM  |
+——+——+——+
| NULL | NULL | NULL |
|  111 | NULL |   23 |
|  222 | NULL |   45 |
| 1111 | NULL |  112 |
| NULL | NULL |  180 |
+——+——+——+
5 rows in set (0.00 sec)
 

Кроме того, с помощью функции GROUPING можно придать выборке удобный для восприятия вид:

mysql> SELECT IF(GROUPING(a)=1,’All Departments’, a) as Department, IF(GROUPING(b)=1, ‘All Employees’, b) as Employees, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;
+——————+—————+——+
| Department      | Employees     | SUM  |
+——————+—————+——+
| NULL            | 112           | NULL |
| NULL            | All Employees | NULL |
| 111             | 11            |   11 |
| 111             | 12            |   12 |
| 111             | All Employees |   23 |
| 222             | 22            |   22 |
| 222             | 23            |   23 |
| 222             | All Employees |   45 |
| 1111            | NULL          |  112 |
| 1111            | All Employees |  112 |
| All Departments | All Employees |  180 |
+——————+—————+——+
11 rows in set (0.00 sec)
 

Это были некоторые из возможностей использования новой функции GROUPING.

Заключение

Мы хотели бы поблагодарить Zhe Dong за его вклад, который был использован в качестве основы при добавлении этой функциональности. Пожалуйста, протестируйте GROUPING и сообщите нам о своих пожеланиях.

Дата публикации: 5.05.2017

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Разница между сверткой и кубом — SQLServerCentral

Предложение GROUP BY используется для группировки результатов агрегатных функций в соответствии с указанным столбцом. Однако предложение GROUP BY не выполняет агрегированных операций на нескольких уровнях иерархии. Например, вы можете рассчитать общую заработную плату всех сотрудников для каждого отдела в компании (один уровень иерархии), но вы не можете рассчитать общую заработную плату всех сотрудников, независимо от того, в каком отделе они работают (два уровня иерархии).

Операторы

ROLLUP позволяют расширить функциональные возможности предложений GROUP BY, вычисляя промежуточные и общие итоги для набора столбцов. Оператор CUBE аналогичен по функциональности оператору ROLLUP; однако оператор CUBE может вычислять промежуточные и общие итоги для всех перестановок столбцов, указанных в нем.

В этой статье мы рассмотрим операторы ROLLUP и CUBE на простом примере. Это позволит нам увидеть практические различия между ними и понять, когда следует использовать каждый из них.

Создание фиктивных данных

Давайте создадим фиктивные данные, по которым мы сможем выполнять наши примеры запросов. Создайте новую базу данных под названием «компания», а затем запустите приведенный ниже код, чтобы создать таблицу «сотрудников».

 ЕГЭ компании;
  СОЗДАТЬ ТАБЛИЦУ сотрудника
  (
      id INT ПЕРВИЧНЫЙ КЛЮЧ,
      name VARCHAR (50) NOT NULL,
      пол VARCHAR (50) NOT NULL,
      зарплата INT NOT NULL,
      отдел VARCHAR (50) NOT NULL
   )
 

Теперь, когда у нас настроены наша база данных и таблица, нам нужно заполнить ее фиктивными данными для работы.

 INSERT INTO сотрудник
  ЗНАЧЕНИЯ
  (1, Дэвид, Мужчина, 5000, Продажи),
  (2, «Джим», «Женский», 6000, «HR»),
  (3, «Катя», «Женщина», 7500, «ИТ»),
  (4, «Воля», «Мужской», 6500, «Маркетинг»),
  (5, «Шейн», «Женский», 5500, «Финансы»),
  (6, Сарай, Мужской, 8000, Продажи),
  (7, Вик, Мужской, 7200, HR),
  (8, «Винс», «Женщина», 6600, «ИТ»),
  (9, «Джейн», «Женщина», 5400, «Маркетинг»),
  (10, «Лаура», «Женский», 6300, «Финансы»),
  (11, Mac, Мужской, 5700, Продажи),
  (12, «Пат», «Мужской», 7000, «HR»),
  (13, «Джули», «Женщина», 7100, «ИТ»),
  (14, «Элис», «Женщина», 6800, «Маркетинг»),
  (15, Уэйн, Мужчина, 5000, Финансы)
 

Simple GROUP BY Пункт

Давайте начнем с простого предложения GROUP BY, чтобы вычислить сумму зарплат всех сотрудников, сгруппированных по их отделам.

 ВЫБЕРИТЕ отдел, сумма (зарплата) как Salary_Sum
  ОТ сотрудника
  ГРУППА ПО отделам
 

Это вернет следующее:

Здесь вы можете увидеть сумму зарплат всех сотрудников, сгруппированных по их отделам.Однако мы не можем увидеть общую сумму, которая представляет собой сумму зарплат всех сотрудников, принадлежащих ко всем отделам компании.

Другой способ взглянуть на это — сказать, что предложение GROUP BY не извлекает общую сумму зарплат всех сотрудников компании. Здесь пригодится оператор ROLLUP.

Оператор ROLLUP

Как упоминалось ранее, оператор ROLLUP используется для вычисления промежуточных и общих итогов для набора столбцов, переданных в предложение «GROUP BY ROLLUP».

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

 SELECT coalesce (отдел, "Все отделы") AS Отдел,
  сумма (зарплата) как Salary_Sum
  ОТ сотрудника
  ГРУППА ПО РОЛИКЕ (отдел)
 

В этом коде мы использовали оператор ROLLUP для вычисления итоговой суммы зарплат сотрудников всех отделов.Однако для общего итога ROLLUP вернет NULL для отдела. Чтобы избежать этого, мы использовали предложение «Coalesce». Это заменит NULL текстом «Все отделы» и отобразит название каждого отдела в столбце «Отдел». Подробнее об использовании «Coalesce» см. В этой статье.

Поиск промежуточных итогов с помощью оператора ROLLUP

Оператор ROLLUP также может использоваться для вычисления промежуточных итогов для каждого столбца на основе группировок в этом столбце.

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

 ВЫБРАТЬ
  coalesce (отдел, "Все отделы") Отдел AS,
  coalesce (пол, "Все полы") КАК Пол,
  сумма (зарплата) как Salary_Sum
  ОТ сотрудника
  ГРУППА ПО РОЛИКЕ (отдел, пол)
 

Этот запрос возвращает приведенную ниже таблицу.Как видите, он возвращает сумму зарплат сотрудников каждого отдела, разделенную на три категории: мужские, женские и все пола. Промежуточные итоги — это строки со словом «Все» в них. Последняя строка — это общая сумма, поэтому в обоих столбцах есть «Все».

NB: Для целей этой статьи я выделил полужирным шрифтом «Все» в таблице ниже, чтобы выделить их, это не то, как они выглядят на самом деле.

Не беспокойтесь, что вы не видите общую зарплату сотрудников-мужчин в ИТ-отделе.Это потому, что их нет, как в случае с женщинами-сотрудниками в отделе продаж.

Оператор КУБА

Оператор CUBE также используется в сочетании с предложением GROUP BY, однако оператор CUBE дает результаты, генерируя все комбинации столбцов, указанные в предложении GROUP BY CUBE.

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

1. Заработная плата с разбивкой по отделам и полу
2. Заработная плата только по полу
3. Заработная плата, сгруппированная только по отделам
4. Итого по всем зарплатам

Выполните следующий сценарий, чтобы увидеть эти четыре комбинации в наборе результатов.

 ВЫБРАТЬ
  coalesce (отдел, "Все отделы") Отдел AS,
  coalesce (пол, "Все полы") КАК Пол,
  сумма (зарплата) как Salary_Sum
  ОТ сотрудника
  ГРУППА ПО КУБУ (отдел, пол)
 

Результат вышеупомянутого скрипта выглядит следующим образом:

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

NB: Я добавил номера строк, чтобы очистить ссылки на записи в наборе результатов. Их фактически нет в наборе результатов.

1. В первых четырех строках и в строках с 6 по 9 зарплаты сгруппированы по отделам и полу.
2. В строке 5 ^-й и 10 ^-й заработная плата сгруппирована только по полу, т.е. женщины и мужчины, работающие во всех отделах.
3. В строке 11 ^-й мы можем увидеть общую сумму заработных плат сотрудников всех полов и всех отделов.
4. В последних пяти строках, то есть строках с 12 по 16, зарплаты сгруппированы только по отделам.

Итак, мы можем видеть все четыре комбинации, которые мы обсуждали ранее в выходных данных выше.

Разница между ROLLUP и CUBE

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

Чтобы понять это, посмотрите на набор результатов для оператора ROLLUP, где сумма зарплат сотрудников сгруппирована по отделам и полу:

Здесь данные агрегированы иерархически.В строках 1, 2, 4, 5, 7, 9, 10 и 12 зарплаты сгруппированы по отделам и полу. В строках 3, 6, 8, 11 и 13 зарплаты сгруппированы только по департаментам.

Наконец, в строке 14 у нас есть общая сумма зарплат всех сотрудников всех полов из всех отделов. Здесь мы имеем три комбинации, которые имеют иерархический характер. Это следующие:

1. Отдел и пол
2. Отдел
3. Всего

У нас нет заработной платы только по полу.Это потому, что пол находится на самом низком уровне иерархии.

С другой стороны, если вы посмотрите на агрегированный результат оператора CUBE, где сумма зарплат сотрудников была сгруппирована по отделам и полу, у нас были все четыре возможных комбинации:

1. 1- Отдел и пол
2. 2- Только отдел
3. 3- только пол
4. 4- Всего.

или

Примечание: Здесь важно отметить, что результаты операторов ROLLUP и CUBE будут одинаковыми, если ваши данные сгруппированы только по одному столбцу.

Какой мне использовать?

ROLLUP и CUBE — это инструменты для повышения производительности. Вам следует использовать ROLLUP, если вы хотите, чтобы ваши данные были иерархическими, и CUBE, если вам нужны все возможные комбинации.

Например, если вы хотите получить общую численность населения страны, штата и города. ROLLUP суммирует население на трех уровнях. Сначала он вернет сумму населения на уровне страны-государства-города. Затем он суммирует население на уровне страны-государства и, наконец, суммирует население на уровне страны.Это также обеспечило бы общий общий уровень.

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

1. Страна-государство-город
2. Стейт-Сити
3. Город
4. Страна-штат
5. Государство
6. Загородный город
7. Страна
8. Все

Все зависит от того, что вам нужно, что бы вы выбрали. Простое практическое правило: если у вас есть иерархические данные (например, страна-> штат-> город или отдел-> менеджер-продавец и т. Д.), вам обычно нужны иерархические результаты, и вы используете ROLLUP для группировки данных.

Если у вас есть неиерархические данные (например, город-пол-национальность), вам не нужны иерархические результаты, поэтому вы используете CUBE, поскольку он предоставит все возможные комбинации.

вопросов о наборах CUBE, ROLLUP и GROUPING, которые вы стеснялись задать

1. А? Что такое GROUPING SET, CUBE и ROLLUP в SQL?
2. Почему мне могут быть полезны ROLLUP или CUBE?
3. Это стандартный SQL или только Microsoft?
4. Могу ли я исключить один или несколько столбцов из ROLLUP?
5. Что же тогда такое НАБОРЫ ГРУППИРОВКИ? Я должен знать о них?
6. Зачем нам нужно объединять столбцы в агрегации?
7. Есть ли в ГРУППИРОВАНИИ НАБОРЫ нечто большее, чем способ создания КУБОВ ‘à la carte’?
8. Почему предусмотрены функции Grouping () и Grouping_ID ()?

1.А? Что такое GROUPING SET, CUBE и ROLLUP в SQL?

CUBE, ROLLUP и GROUPING SET — необязательные операторы предложения GROUP BY оператора SELECT для создания отчетов с большим объемом информации. Они позволяют выполнять несколько операций GROUP BY в одном операторе, потенциально экономя много времени и вычислительных ресурсов. Они могут предоставить всю информацию, необходимую для составления отчетов, включая итоги, обеспечивая при этом хорошую производительность для больших таблиц и помогая оптимизатору запросов разработать хороший план выполнения.

Дополнительные строки «суперагрегат» предоставляют сводные значения, что позволяет использовать несколько «агрегатов», таких как SUM () или MAX (), в одном результате. NULL в этих строках в результате предназначены для обозначения «все», а не «неизвестно». Это позволяет получить все необходимые агрегаты за один проход по таблице. Из-за наличия дополнительных строк в результатах предусмотрены дополнительные функции GROUPING (), и GROUPING_ID () , чтобы указать эти дополнительные «супергагрегированные» строки и то, какие столбцы агрегируются.

Это имеет большой смысл, если у вас есть приложение, которое должно запускать несколько отчетов без дополнительных вычислений или без возврата к базе данных: у вас есть все, что вам нужно, в одном результате.

Возьмите этот стандартный пример ROLLUP (здесь я использую AdventureWorks 2012).

Помимо простых агрегатных строк GROUP BY с итоговой суммой за каждый месяц, которые вы получите при простой группировке, вы также получите промежуточные или супергрегатные строки, а также строку общего итога. (вот начало результата)

Тот NULL, который я выделил, означает, что строка представляет собой агрегат за «все» месяцы 2005 года во Франции (часть региона Европы)

Помимо всего этого, вы получаете общую сумму к оплате за каждый год, для каждой территории и территориальной группы, а также полную сумму к оплате.(с конца)

Эти NULL означают «Все», помните. Последняя строка — это общая сумма, а над ней — сумма для Тихоокеанского региона. Выше этого — вклад Австралии в Тихоокеанский регион. Четвертая строка снизу — это вклад Австралии за 2008 год. Количество возвращаемых группировок на единицу больше, чем количество выражений в списке составных элементов, предоставленных оператору GROUP BY.

Чтобы получить тот же эффект без использования свертки, вам нужно сделать что-то вроде этого (AdventureWorks2012)

Которая намного дороже процессора и ввода-вывода.Обратите внимание, что стандартный синтаксис предложения GROUP BY в последних версиях —

.

Этот новый синтаксис дает вам некоторые дополнительные функции. Помните также, что порядок столбцов влияет на группировку вывода ROLLUP и может влиять на количество строк в наборе результатов.

CUBE делает то же самое, но вместо предоставления иерархии итогов в упорядоченных строках суперагрегатов, он предоставляет все перестановки «суперагрегатов» (строки «симметричных суперагрегатов»), так называемые перекрестные табличные строки. Если вы хотите знать, какая территория отдала больше всего приказов в марте или какая территория была наименее успешной в 2006 году, вам понадобится CUBE. Вы подводите к результату все возможные суммы.

GROUPING SET позволяет точно настроить результат для предоставления более специализированной информации помимо CUBE.Он может предоставить сводную информацию о комбинациях измерений. Вы можете получить точно такой же результат, что и в нашем примере ROLLUP, используя GROUPING SETS, но с гораздо большим набором текста.

Это просто для того, чтобы показать, как они связаны. На самом деле вы прибегли бы к GROUPING SETS, чтобы получить результаты, которые невозможны с ROLLUP или CUBE.

Почти все эти сводные данные можно получить, используя только GROUP BY, но только путем многократного группирования результата GROUP BY или выполнения более одного прохода через данные.

Когда вы используете CUBE, ROLLUP или GROUPING SETS, вы не можете использовать ключевое слово DISTINCT в агрегатных выражениях, таких как AVG (DISTINCT имя_столбца), COUNT (DISTINCT имя_столбца) и SUM (DISTINCT имя_столбца)

2. Почему мне могут быть полезны ROLLUP или CUBE?

Расцвет

ROLLUP и CUBE был еще до SSAS.Они были полезны для предоставления тех же возможностей, что и куб в OLAP. Тем не менее, он все еще имеет свое применение. В AdventureWorks это излишне, но если вы обрабатываете большие объемы данных, вам нужно передать свои данные только один раз и сделать как можно больше с данными, которые были агрегированы. События, которые произошли в прошлом, изменить нельзя, поэтому редко требуется сохранять исторические данные в активной OLTP-системе. Вместо этого вам нужно только сохранить агрегированные данные на уровне детализации («детализации»), необходимом для всех прогнозируемых отчетов.

Представьте, что вы отвечаете за сообщение о телефонном коммутаторе, который принимает около двух миллионов звонков в день. Если вы сохраните все эти вызовы на своем OLTP-сервере, вы скоро обнаружите, что SQL Server трудится над отчетами об использовании. Вы должны сохранять исходную информацию о звонках в течение установленного законом периода времени, но вы определяете из бизнеса, что их, самое большее, интересует только количество звонков в минуту. Затем вы снизили требования к хранилищу на сервере OLTP до 1.4% от того, что было, а записи звонков могут быть заархивированы на другой SQL Server для специальных запросов и заявлений клиентов. Скорее всего, стоит сэкономить. Предложения CUBE и ROLLUP позволяют даже сохранять итоги по строкам, итоги по столбцам и общие итоги без необходимости выполнять сканирование сводной таблицы таблицей или кластеризованным индексом.

Пока в эти данные не вносятся ретроспективные изменения и все периоды времени завершены, вам никогда не придется повторять или изменять агрегаты, основанные на прошлых периодах времени, хотя общие итоги придется переписать !.

Давайте притворимся, но используем AdventureWorks2012, чтобы вы могли подыгрывать.

Сначала создадим временную сводную таблицу.

Обратите внимание, что мы добавляем дополнительные «битовые» столбцы, которые сообщают нам, какие строки содержат строки сводки. Если вы по ошибке добавите их в какие-либо дополнительные агрегаты, вы получите серьезно завышенные результаты. Очевидно, что вы не можете использовать Grouping (), или Grouping_ID для сохраненного результата, поэтому вам следует предоставить что-то взамен.

Теперь мы можем очень быстро создать сводную таблицу

Итак, менеджеры быстро улыбаются, увидев это, но затем они ярко говорят: «Я уверен, что я также попросил разбивку по территории в месяц.

Вы делаете это с коротким смешком.

Но если вы использовали CUBE вместо Rollup, последняя строка «total» уже была бы вычислена.В реальном примере на создание отчета потребуется время. Вы можете создать КУБ в десяти измерениях; хотя они имеют тенденцию увеличивать агрегирование, они не слишком дороги.

3. Это стандартный SQL или только Microsoft?

Теперь это стандартный ANSI SQL с 1999 года, хотя WITH CUBE и WITH ROLLUP были впервые представлены Microsoft. Это включение несколько удивительно, поскольку они вводят второе значение, «все», для значения NULL помимо «неизвестно». Когда Microsoft впервые представила CUBE и ROLLUP, синтаксис немного отличался, но обе формы разрешены в SQL Server.В одном операторе SELECT можно использовать только один стиль синтаксиса, и вы должны использовать синтаксис, совместимый с ISO, для всей новой работы.

4. Могу ли я исключить один или несколько столбцов из ROLLUP?

Если хотите! Представьте, что мне не нужен суперсводный итог по всем регионам ( т. [Группа])

Здесь мы используем синтаксис, совместимый с ANSI SQL 2006. То же самое можно сделать и с кубом. Я никогда не находил для этого практического применения, но вы могли встретить это

5. Что же тогда такое НАБОРЫ ГРУППИРОВКИ? Я должен знать о них?

GROUPING SET означает, что вы просите SQL сгруппировать результат несколько раз.Вы можете использовать синтаксис GROUPING SETS, чтобы точно указать, какие агрегаты вычислять. Вот пример.

Здесь вы запрашиваете разбивку по группам территорий для каждого месяца каждого года с итогами за месяц и год, за которыми следует итоговая сумма по названию территории, но без итоговой суммы. В отличие от ROLLUP, вы получаете тот же результат независимо от порядка столбцов в каждом НАБОРЕ ГРУППИРОВКИ и порядка НАБОРОВ ГРУППИРОВКИ.

Наборы

GROUPING SET могут дать вам именно то, что дают вам CUBE и ROLLUP, и многое другое. Как вы можете видеть в последнем примере, вы можете использовать стандартные «table d’hôte» CUBE и ROLLUP, смешанные с прямо выраженными «à la carte» GROUPING SET.

6. Зачем нам нужно объединять столбцы в агрегации?

Если в некоторых отчетах необходимо объединить два столбца, полезно объявить агрегирование, которое объединяет два столбца. В первом примере мы объединяем год и месяц для сведения, в результате чего итоги ограничиваются только каждой территорией,

.

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

Это может быть очень полезно для определенных данных. Мы избежали необходимости комбинировать столбцы здесь. Если бы вы сделали КУБ, и термины для территорий использовали бы такие слова, как «Северный» или «Южный» для описания территории в более чем одном регионе, у вас были бы некоторые причудливые агрегаты, применимые к «северным» территориям, которые не связаны между собой. .Комбинируя столбцы, вы избежите этого.

7. Может ли ГРУППИРОВАТЬ НАБОРЫ нечто большее, чем способ создания КУБОВ ‘à la carte’?

Не уверен, что стесняюсь задать этот вопрос. SQL: 1999 GROUPING SETS предоставляет богатый рекурсивный синтаксис, который позволяет агрегировать комбинации столбцов и определять все виды эзотерических отчетов, содержащих до десяти измерений. Агрегации могут быть вложенными, и вы можете вкладывать CUBE в ROLLUP и вкладывать ROLLUP в CUBE. Вам нужно будет прочитать специализированную публикацию, чтобы узнать больше об этом.

8. Почему предусмотрены функции

Grouping () и Grouping_ID () ?

Использование NULL для обозначения того, что столбец является агрегатом, — не лучшая идея. Проблема в том, что если группирующий столбец содержит значения NULL, все значения NULL считаются равными и помещаются в одну группу NULL, которая маскируется под сводку. Чтобы обойти очевидную трудность использования значений NULL в исходных данных, предусмотрены две функции: Grouping () и Grouping_ID () .

Функции Grouping () передается имя столбца, который участвовал в ROLLUP, CUBE или GROUPING SET. Он возвращает ноль, если эта строка является сводкой для этого столбца со значением NULL, означающим «все», или если она содержит значение.

Функции GROUPING_ID передается список, который должен точно соответствовать выражению в списке GROUP BY. GROUPING_ID создается как растровое изображение соответствующих итоговых столбцов. Если, например, в столбце территории есть NULL, означающее «все» территории, а не название территории, и он указан во втором столбце, то устанавливается второй слева бит.Затем возвращается это целое число.

Grouping_ID () обычно используется, чтобы указать, является ли строка первичной или вторичной агрегацией (0 или> 0) и, если она вторична, то исключена из любых дальнейших манипуляций GROUP BY.

Обычно считается хорошей практикой включать битовый столбец для каждого измерения (например, «Территория» или «Регион» в нашем примере), который устанавливается, если строка является сводкой для этого измерения, вместе с Grouping_ID () значение, чтобы облегчить дальнейшую группировку результата.

Чтобы проиллюстрировать, как на самом деле работает Grouping_ID, здесь мы рассмотрим способ установки битов в Grouping_ID в соответствии с типом сводки. Мы будем использовать функцию ToBinaryString Фила Фактора, чтобы показать биты.

Это дает (конечно, только образец)…

Здесь интересно то, что каждая «1» в битовой карте группировки представляет соответствующий элемент группировки.В верхнем ряду есть тихоокеанский регион (0), все территории (1), все годы, (1), 12-й месяц (0). В последней строке указаны все регионы (1), Австралия (0), 2008 (0) и все месяцы (1). С помощью простых манипуляций с битами вы можете избежать работы с нулями!

Два способа объединения — IT Jungle

В трех мощных словах SQL я показал, как улучшить сводные запросы с помощью агрегированных значений по подмножествам группирующих столбцов. Сегодня я продолжу это обсуждение, показав два синтаксиса объединения и проиллюстрировав разницу между ними.

Во-первых, предположим, что есть таблица бухгалтерских операций.

выберите отдел, счет, сумму
  от xacts2
 заказать по 1, 2
 
СУММА СЧЕТА ОТДЕЛА
     1 10 200,00
     1 10 250,00
     1 30 300,00
     2 10 125,00
     2 20 175,00
     2 20 225,00
 

Вот типичный сводный запрос с использованием знакомого предложения GROUP BY.

выберите отдел, счет, сумму (сумму) в качестве тамта
  от xacts2
 группировать по отделам, аккаунтам
 заказать по 1,2

УЧЕТНАЯ ЗАПИСЬ ОТДЕЛА TAMT
     1 10 450.00
     1 30 300,00
     2 10 125,00
     2 20 400,00
 

Столбец суммы представлен двумя столбцами — отделом и счетом.

Чтобы добавить дополнительные совокупные итоги, просто добавьте WITH ROLLUP.

выберите отдел, счет, сумму (сумму) в качестве тамта
  от xacts2
 группировать по отделам, аккаунтам
  со сверткой
 заказать по 1,2
 
УЧЕТНАЯ ЗАПИСЬ ОТДЕЛА TAMT
     1 10 450.00
     1 30 300,00
     1 - 750,00
     2 10 125,00
     2 20 400,00
     2 - 525,00
      - - 1 275,00
 

Это дает мне общие и общие итоги по отделам.

Вот еще одна форма объединения, которая делает то же самое.

выберите отдел, счет, сумму (сумму) в качестве тамта
  от xacts2
 группировать по свертке (отдел, аккаунт)
 заказать по 1,2

УЧЕТНАЯ ЗАПИСЬ ОТДЕЛА TAMT
     1 10 450.00
     1 30 300,00
     1 - 750,00
     2 10 125,00
     2 20 400,00
     2 - 525,00
      - - 1 275,00
 

Итак, одна форма так же хороша, как другая? Рассмотрим следующие вопросы.

выберите отдел, счет, сумму (сумму) в качестве тамта
  от xacts2
 группировать по свертке (отделу), аккаунту
 заказать по 1,2

УЧЕТНАЯ ЗАПИСЬ ОТДЕЛА TAMT
     1 10 450.00
     1 30 300,00
     2 10 125,00
     2 20 400,00
      - 10 575,00
      - 20 400,00
      - 30 300,00
 

Обратите внимание, что есть агрегаты для счета, но не для отдела.

Вот еще один вариант.

выберите отдел, счет, сумму (сумму) в качестве тамта
  от xacts2
 группировка по отделам, свертка (аккаунт)
 заказать по 1,2

УЧЕТНАЯ ЗАПИСЬ ОТДЕЛА TAMT
     1 10 450.00
     1 30 300,00
     1 - 750,00
     2 10 125,00
     2 20 400,00
     2 - 525,00
 

Здесь у нас есть сводные цифры по отделу, но не по счету.

Рассмотрим последний пример.

выберите отдел, счет, сумму (сумму) в качестве тамта
  от xacts2
 группировать по свертке (отдел), свертке (аккаунт)
 заказать по 1,2

УЧЕТНАЯ ЗАПИСЬ ОТДЕЛА TAMT
     1 10 450.00
     1 30 300,00
     1 - 750,00
     2 10 125,00
     2 20 400,00
     2 - 525,00
      - 10 575,00
      - 20 400,00
      - 30 300,00
      - - 1 275,00
 

У нас есть итоги по отделам, итоги по счетам и общие итоги. Это эквивалентно WITH CUBE.

Какая форма лучше — GROUP BY ROLLUP или WITH ROLLUP? Вероятно, ГРУППА ПО РОЛИКЕ, по двум причинам:

1. Насколько я могу судить, GROUP BY ROLLUP кажется более стандартной и широко распространенной в мире реляционных баз данных, чем WITH ROLLUP.

2. Синтаксис GROUP BY ROLLUP позволяет нам создавать наборы результатов, которые мы не можем получить с помощью WITH ROLLUP.

По этим причинам я планирую уделить больше времени освоению нюансов синтаксиса GROUP BY ROLLUP.

РОДСТВЕННЫЕ ИСТОРИИ

Три мощных слова SQL

Сокровищница новых возможностей DB2 6.1, Часть 2: Группирующие наборы и супергруппы

Центр знаний IBM

Расширенное агрегирование, куб, группирование и сведение — Apache Hive

Этот документ описывает расширенные функции агрегирования для предложения GROUP BY операторов SELECT.

Версия

GROUPING__ID совместима с семантикой в ​​других механизмах SQL, начиная с Hive 2.3.0 (см. HIVE-16102).
Поддержка группировки SQL Функция также была добавлена ​​в Hive 2.3.0 (см. HIVE-15409).

Для получения общей информации о GROUP BY см. GroupBy в Руководстве по языку.

Предложение GROUPING SETS

Предложение GROUPING SETS в GROUP BY позволяет нам указать более одной опции GROUP BY в одном наборе записей. Все предложения GROUPING SET могут быть логически выражены в терминах нескольких запросов GROUP BY, связанных UNION.Таблица-1 показывает несколько таких эквивалентных утверждений. Это полезно при формировании идеи предложения GROUPING SETS. Пустой set () в предложении GROUPING SETS вычисляет общий агрегат.

Таблица 1 — Запросы GROUPING SET и эквивалентные запросы GROUP BY

Функция Grouping__ID

Когда агрегаты отображаются для столбца, его значение равно null.Это может привести к конфликту, если в самом столбце есть значения NULL. Должен быть способ идентифицировать NULL в столбце, что означает агрегирование, и NULL в столбце, что означает значение. Решением этой проблемы является функция GROUPING__ID.

Эта функция возвращает битовый вектор, соответствующий тому, присутствует ли каждый столбец или нет. Для каждого столбца создается значение «1» для строки в наборе результатов, если этот столбец был агрегирован в этой строке, в противном случае значение равно «0». Это можно использовать для различения, когда в данных есть нули.

Рассмотрим следующий пример:

Следующий запрос:

 клавиша SELECT, значение, GROUPING__ID, count (*)
ОТ Т1
GROUP BY ключ, значение WITH ROLLUP; 

будет иметь следующие результаты:

2

Обратите внимание, что третий столбец представляет собой битовый вектор выбранных столбцов.
Для первой строки ни один столбец не выбран.
Для второй строки выбирается только первый столбец, что объясняет значение 1.
Для третьей строки выбираются оба столбца (а второй столбец оказывается пустым), что объясняет значение 0.

Функция группирования

Функция группирования указывает, агрегируется ли выражение в предложении GROUP BY для данной строки. Значение 0 представляет столбец, который является частью набора группировок, а значение 1 представляет столбец, который не является частью набора группировок.

Возвращаясь к нашему примеру выше, рассмотрим следующий запрос:

 ключ SELECT, значение, GROUPING__ID,
  группировка (ключ, значение), группировка (значение, ключ), группировка (ключ), группировка (значение),
  считать(*)
ОТ Т1
GROUP BY ключ, значение WITH ROLLUP; 

Этот запрос даст следующие результаты.

900 29

0

9152 Кубы Общий синтаксис — WITH CUBE / ROLLUP.Он используется только с GROUP BY. CUBE создает промежуточный итог всех возможных комбинаций набора столбцов в своем аргументе. После того, как мы вычислим CUBE для набора измерений, мы сможем получить ответы на все возможные вопросы агрегирования по этим измерениям.

Здесь также стоит упомянуть, что
GROUP BY a, b, c WITH CUBE эквивалентно
GROUP BY a, b, c GROUPING SETS ((a, b, c), (a, b), (b , в), (а, в), (а), (б), (в), ()).

Предложение ROLLUP используется с GROUP BY для вычисления агрегата на уровнях иерархии измерения.
GROUP BY a, b, c с ROLLUP предполагает, что иерархия — это «a» с переходом вниз до «b» с переходом вниз до «c».

GROUP BY a, b, c, WITH ROLLUP эквивалентно GROUP BY a, b, c GROUPING SETS ((a, b, c), (a, b), (a), ()).

hive.new.job.grouping.set.cardinality

Следует ли запускать новое задание сокращения карты для группировки наборов / сверток / кубов.
Для такого запроса: выберите a, b, c, count (1) из группы T с помощью a, b, c с объединением;
В каждой строке создается 4 строки: (a, b, c), (a, b, null), (a, null, null), (null, null, null)
Это может привести к взрыву через границу уменьшения карты если мощность T очень высока
и агрегирование на стороне карты не очень хорошо работает.

Этот параметр определяет, должен ли куст добавить дополнительное задание уменьшения карты. Если количество элементов набора
группирования (4 в приведенном выше примере) больше, чем это значение, новое задание MR добавляется в соответствии с предположением
, что исходная группа по уменьшит размер данных.

Функция Grouping__ID (до Hive 2.3.0)

Функция Grouping__ID была исправлена ​​в Hive 2.3.0, поэтому поведение до этого выпуска отличается (это ожидается). Для каждого столбца функция вернет значение «0», если этот столбец был агрегирован в этой строке, в противном случае значение равно «1».

Отсюда следующий запрос:

 клавиша SELECT, значение, GROUPING__ID, count (*)
ОТ Т1
GROUP BY ключ, значение WITH ROLLUP; 

будет иметь следующие результаты.

0

0

NULL

90 037

столбец

Обратите внимание, что бит-вектор выбранных столбцов.
Для первой строки ни один столбец не выбран.
Для второй строки выбирается только первый столбец, что объясняет счетчик 2.
Для третьей строки выбираются оба столбца (а второй столбец оказывается нулевым), что объясняет счетчик 1

Microsoft SQL Server — GROUP BY с ROLLUP и CUBE

Пример

Оператор ROLLUP полезен при создании отчетов, содержащих промежуточные и итоговые суммы.

• CUBE создает набор результатов, который показывает агрегаты для всех комбинаций значений в выбранных столбцах.

• ROLLUP создает набор результатов, который показывает агрегаты для иерархии значений в выбранных столбцах.

  Артикул	Цвет	Кол-во
  Стол	Синий	124
  Стол	Красный	223
  Стул	Синий	101
  Стул	Красный	210

  ВЫБЕРИТЕ СЛУЧАЙ, КОГДА (ГРУППИРОВКА (Элемент) = 1) ТОГДА 'ВСЕ'
            ELSE ISNULL (элемент; "НЕИЗВЕСТНО")
       КОНЕЦ КАК Позиция,
       СЛУЧАЙ, КОГДА (ГРУППИРОВКА (Цвет) = 1) ТОГДА 'ВСЕ'
            ELSE ISNULL (цвет; 'НЕИЗВЕСТНО')
       КОНЕЦ КАК Цвет,
       SUM (количество) как QtySum
ИЗ инвентаря
ГРУППА ПО ПУНКТУ, Цвет В КОЛЛЕКЦИИ

Цвет товара Кол-во
-------------------- -------------------- ---------- ----------------
Стул Blue 101.00
Стул Красный 210.00
Стул ALL 311.00
Стол Синий 124.00
Стол Красный 223.00
Стол ВСЕ 347.00
ВСЕ ВСЕ 658.00
  

(затронуты 7 строк)

Если ключевое слово ROLLUP в запросе изменено на CUBE, набор результатов CUBE будет таким же, за исключением того, что эти две дополнительные строки возвращаются в конце:

  ВСЕ Синий 225.00
ВСЕ Красный 433.00
  

https://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

Альтернатива функции SQL GROUP BY ROLLUP в Redshift

Последнее обновление 6 апреля 2021 г. от Vithal S

В других моих статьях мы обсуждали GROUP BY с GROUPING SETS и GROUP BY с альтернативами CUBE.В этой статье мы проверим функцию SQL GROUP BY ROLLUP, альтернативу в Amazon Redshift.

Реляционные базы данных, такие как Oracle, Teradata и т. Д., Поддерживают функцию GROUP BY ROLLUP для группировки строк результатов. Однако Amazon Redshift не поддерживает функцию GROUP BY ROLLUP.

SQL GROUP BY ROLLUP Альтернатива в Redshift

Подобно GROUP BY с CUBE, GROUP BY ROLLUP является расширением предложения GROUP BY, которое создает строки промежуточных итогов. Строки промежуточных итогов — это строки, которые дополнительно агрегируются, значения которых получены путем вычисления тех же агрегатных функций, которые использовались для создания сгруппированных строк.

Например, следующий запрос определяет предложение group by с функцией ROLLUP, которая включает pid и county. Это запрос Teradata, который будет выполняться без каких-либо проблем.

  SELECT pid, county, SUM (продажа) как total_sale
ОТ sale_view
ГРУППА ПО РОЛИКЕ (пид, округ)
порядок по 1,2;  

Ниже приведен вывод на сервере Teradata.

  PID County total_sale
--- ------------- -----------
  ? ? 247350
  1? 194225
  1 Лос-Анджелес 111350
  1 Сан-Диего Сан 82875
  2? 53125
  2 Лос-Анджелес 53125  

Но тот же запрос с функцией ROLLUP не будет работать на сервере Amazon Redshift.При попытке выполнить вышеуказанный запрос вы получите « Недопустимая операция: набор функций (целое число, разные символы) не существует» «Ошибка в Redshift. Это потому, что Redshift не поддерживает функцию SQL GROUP BY ROLLUP.

Альтернатива функции GROUP BY ROLLUP с использованием UNION ALL

По сути, функция GROUP BY ROLLUP представляет собой комбинацию нескольких предложений GROUP BY. Результаты эквивалентны UNION ALL для указанных групп. Вы можете переписать исходный запрос, используя оператор набора UNION ALL Redshift, чтобы вернуть тот же набор результатов.

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

  выберите pid, county, SUM (sale) как total_sale
из sale_view
группа по пид, округ
СОЮЗ ВСЕ
выберите pid, null как округ, сумму (продажа) как total_sale
из sale_view
группа по pid
СОЮЗ ВСЕ
выберите NULL, NULL, сумма (продажа) как total_sale
из sale_view
порядок по 1,2;  

Ниже приводится результат.

  pid | округ | total_sale
----- + ------------- + ------------
   1 | Лос-Анджелес | 111350
   1 | Сан-Диего | 82875
   1 | | 194225
   2 | Лос-Анджелес | 53125
   2 | | 53125
     | | 247350
(6 рядов)  

Обратите внимание, что пустое значение представляет NULL.

Обсуждаемый подход хорошо работает, если у вас есть предсказуемые столбцы в функции ROLLUP.

Статьи по теме,

Надеюсь, это поможет

SQL Server — TSQL — ГРУППА ПО С ROLLUP

GROUP BY группирует набор результатов в итоговые строки по предоставленным столбцам.Например, рассмотрим данные ниже, которые содержат данные о продажах по регионам.

Название группы Продажи, год выпуска

————————————- —————

Северная Америка Северо-запад 123237,00

Северная Америка Северо-запад 37534,00

Северная Америка Северо-запад 48003,00

Северная Америка Юго-запад 164232,00

Юго-восток Северной Америки 39667.00

Северная Америка Юго-восток 1058 10,00

Европа Франция 74569,00

Европа Германия 59456,00

Тихоокеанский регион Австралия 93403,00
Европа Великобритания 78327,00

Эти данные можно резюмировать с помощью предложения GROUP BY , как показано ниже:

— © 2011 — Vishal (http://SqlAndMe.com)

ВЫБЕРИТЕ [Группа], [Имя], СУММ ([SalesYTD]) КАК «Общий объем продаж»

ИЗ #TempTable

ГРУППА ПО [Группа], [Имя]

ЗАКАЗАТЬ [Группа], [Имя]

Результат набора:

Название группы Общий объем продаж

—————————————————–

Европа Франция 74569.00

Европа Германия 59456,00

Европа Великобритания 78327,00

Северная Америка Северо-запад 208774,00

Северная Америка Юго-восток 145477,00

Северная Америка Юго-запад 164232,00

Тихоокеанский регион Австралия 93403,00

(затронуты 7 строк)

В приведенном выше результате мы видим, что данные суммированы по [Группа] и [Имя].

С РОЛИКОМ:

ROLLUP можно использовать для создания строк промежуточных итогов и строк общего итога для строк агрегатов.

ВЫБЕРИТЕ [Группа], [Имя], СУММ ([SalesYTD]) КАК «Общий объем продаж»

ИЗ #TempTable

ГРУППА ПО [Группа], [Имя] С РОЛИКОМ

Результат набора:

Название группы Общий объем продаж

——————- ——————- ———————

Европа Франция 74569.00

Европа Германия 59456,00

Европа Великобритания 78327,00

Европа NULL 212352,00

Северная Америка Северо-запад 208774,00

Северная Америка Юго-восток 145477,00

Северная Америка Юго-запад 164232,00

Северная Америка NULL 518483,00

Тихоокеанская Австралия 93403.00

Pacific NULL 93403.00

NULL NULL 824238.00

(затронуты 11 строк)

Как мы видим в результирующем наборе, было добавлено 4 строки, по 3 для промежуточной суммы каждой [Группы] и 1 строка для общей суммы.

ГРУППИРОВКА ():

GROUPING () Функция может использоваться для проверки, агрегирована ли строка или нет.Он возвращает 1 для агрегированных строк.

— © 2011 — Vishal (http://SqlAndMe.com)

ВЫБЕРИТЕ [Группа], [Имя], СУММ ([SalesYTD]) КАК «Общий объем продаж»,

GROUPING ([Group]) AS ‘Aggregated’,

GROUPING ([Name]) AS ‘Aggregated’

ИЗ #TempTable

ГРУППА ПО [Группа], [Имя] С РОЛИКОМ

Результат набора:

Название группы Совокупный совокупный объем продаж

——————- ——————– ————- ————————

Европа Франция 74569.00 0 0

Европа Германия 59456,00 0 0

Европа Великобритания 78327,00 0 0

Европа NULL 212352,00 0 1

Северная Америка Северо-запад 208774,00 0 0

Северная Америка Юго-восток 145477,00 0 0

Северная Америка, юго-запад 164232.00 0 0

Северная Америка NULL 518483,00 0 1

Тихоокеанский регион Австралия 93403,00 0 0

Pacific NULL 93403.00 0 1

NULL NULL 824238.00 1 1

(затронуты 11 строк)

Наконец, мы можем использовать функцию GROUPING () для идентификации и замены «NULL» в агрегированных строках на что-то значимое.например «ВСЕ» !!!!.

ВЫБРАТЬ ГРУППУ СЛУЧАЙ ([Группа])

КОГДА 1 ТО «ВСЕ»

ELSE [Группа] END AS ‘Группа’,

ГРУППА СЛУЧАЙ ([Имя])

КОГДА 1 ТО «ВСЕ»

ELSE [Имя] КОНЕЦ КАК «Имя»,

SUM ([SalesYTD]) КАК «Общий объем продаж»

ИЗ #TempTable

ГРУППА ПО [Группа], [Имя] С РОЛИКОМ

Результат набора:

Название группы Общий объем продаж

————————————- ——————–

Европа Франция 74569.00

Европа Германия 59456,00

Европа Великобритания 78327,00

Европа ВСЕ 212352.00

Северная Америка Северо-запад 208774,00

Северная Америка Юго-восток 145477,00

Северная Америка Юго-запад 164232,00

Северная Америка ВСЕ 518483.