Rollup group by sql: SQL Server ROLLUP By Practical Examples

GROUP BY ROLLUP для анализа данных

Введение

В этой статье мы рассмотрим команды T-SQL GROUP BY ROLLUP для агрегирования данных и улучшения аналитических возможностей данных в SQL Server.

Во-первых, давайте посмотрим, какой пример набора данных мы собираемся использовать. Мы будем использовать представление vTargetMail и
Таблица DimGeograohy в образце базы данных AdventureWorksDW.

Давайте посмотрим на пример набора данных из следующего запроса.

1

2

3

4

5

6

SELECT G.EnglishCountryRegionName

,G.StateProvinceName

,G.City

,M.[YearlyIncome]

ИЗ [AdventureWorksDW2017].[dbo].[vTar getMail] M

ВНУТРЕННЕЕ СОЕДИНЕНИЕ [dbo].[DimGeography] G ON m.GeographyKey = G.GeographyKey

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

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

1

2

3

4

5

6

7

8

9 9 0005

10

11

12

SELECT G.EnglishCountryRegionName

,G.StateProvinceName

,G.City

,SUM(M.[YearlyIncome]) TotalIncome

ИЗ [AdventureWorksDW2017].[ dbo].[vTargetMail] M

INNER JOIN [dbo].[DimGeography] G ON m.GeographyKey = G.GeographyKey

GROUP BY G.EnglishCountryRegionName

,G.StateProvinceName

,G.City

ЗАКАЗ ПО G.EnglishCountryRegionName

,G. StateProvinceName

,G.City

Ниже будет вывод для вышеуказанного запроса.

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

В приведенном выше запросе, если вы хотите найти общую сумму только для Австралии, вам нужно запустить другую команду GROUP BY с
EnglishCountryRegionName и выполните UNION ALL. Это будет очень некрасивый метод. Используя GROUP BY ROLLUP, вы можете выполнить указанную выше задачу, как показано в следующем запросе.

1

2

3

4

5

6

7

SELECT G.EnglishCountryRegionName

,G.StateProvinceName

,G.City

,SUM(M.[YearlyIncome]) TotalIncome

ИЗ [AdventureWorksDW2017]. [ dbo].[vTargetMail] M

ВНУТРЕННЕЕ СОЕДИНЕНИЕ [dbo].[DimGeography] G ON m.GeographyKey = G.GeographyKey

ГРУППИРОВАТЬ ПО ОБЪЕМУ (G.EnglishCountryRegionName, G.StateProvinceName, G.City)

Вывод вышеуказанного запроса имеет два преимущества, которых не было в параметре GROUP BY.

Во-первых, выходные данные будут отсортированы по столбцам, указанным в операторе GROUP BY ROLLUP. Помимо порядка, у вас также есть агрегация для различных комбинаций. Как мы заметили в операторе GROUP BY,
все данные агрегируются во все три столбца. Однако с оператором GROUP BY ROLLUP будет много агрегаций. Например, в приведенном выше выводе строка 32 агрегирована с Австралией, Южной Австралией, а строка 34 агрегирована с Австралией и Тасманией, а строка номер 45 агрегирована с Австралией.

Помимо простой агрегации, в GROUP BY ROLLUP доступно несколько функций.

ГРУППИРОВКА

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

1

2

3

4

5

6

7

8

9 9 0005

10

SELECT ISNULL(G.EnglishCountryRegionName, ‘ALL’) CountryRegion

,ISNULL(G.StateProvinceName, ‘ALL’) StateProvinceName

,ISNULL(G.City, ‘ALL’) City

,SUM(M.[YearlyIncome]) TotalIncome

,GROUPING(G.EnglishCountryRegion Name) IsEnglishCountryRegionNameGrouping

,GROUPING(G.StateProvinceName) IsStateProvinceNameGrouping

,GROUPING(G.City) IsCityGrouping

FROM [AdventureWorksDW2017].[dbo].[vTargetMail] M 9 0005

ВНУТРЕННЕЕ СОЕДИНЕНИЕ [dbo].[DimGeography] G ON m.GeographyKey = G.GeographyKey

ГРУППА ПО ОБЪЕМУ (G.EnglishCountryRegionName, G.StateProvinceName, G.City)

Помимо функции GROUPING, мы использовали функцию ISNULL, чтобы заменить значения NULL на ALL.

В строке 108 значение City заменено ВСЕМИ значениями в столбце isCityGrouping, показанном как 1, чтобы указать, что столбец City является агрегированным.

Вы можете использовать функцию GROUPING в предложении HAVING в GROUP BY ROLLUP, как показано в приведенном ниже запросе.

1

2

3

4

5

6

7

8

9 9 0005

10

SELECT ISNULL(G.EnglishCountryRegionName, ‘ALL’) CountryRegion

,ISNULL(G.StateProvinceName, ‘ALL’) StateProvinceName

,ISNULL(G.City, ‘ALL’) City

,SUM(M. [Годовой доход]) Общий доход

ОТ [AdventureWorksDW2017].[dbo].[vTargetMail] M

INNER JOIN [dbo].[DimGeography] G ON m.GeographyKey = G.GeographyKey

GROUP BY ROLLUP(G.EnglishCountryRegionName, G.StateProvinceName, G.City)

HAVING GROUPING(G.EnglishCountryRegionName) = 1 9000 5

ИЛИ ГРУППА(G.StateProvinceName) = 1

ИЛИ ГРУППА(G.City) = 1

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

Если в исходных значениях есть значения NULL, будут проблемы с функцией ISNULL. Следовательно
следует использовать разные методы, и в следующем запросе функции CASE и ISNULL используются вместе для достижения
этот.

1

2

3

4

5

6

7

8

9 9 0005

10

11

12

13

14

15

16

17

18

19

SELECT CASE

    WHEN GROUPING(G.EnglishCountryRegionName) = 1

        THEN ‘ALL’

    ELSE ISNULL(G.EnglishCountryRegionName, ‘UNKNOWN’)

    END CountryRegion

,CASE

    WHEN GROUPING(G.StateProvinceName) = 1

        THEN ‘ALL’

    ELSE ISNULL (G.StateProvinceName, ‘НЕИЗВЕСТНО’)

    END StateProvinceName

,CASE

    WHEN GROUPING(G.City) = 1

        THEN ‘ALL’

    ELSE ISNULL(G. City, ‘UNKNOWN’)

    END City

,SUM(M.[YearlyIncome]) TotalIncome

FROM [AdventureWorksDW2017].[dbo].[vTargetMail] M

INNER JOIN [dbo].[ DimGeography] G ON m.GeographyKey = G.GeographyKey

GROUP BY ROLLUP(G.EnglishCountryRegionName, G.StateProvinceName, G.City)

В приведенном выше запросе функция ISNULL используется для отображения UNKNOWN для значений NULL, а функция GROUPING используется для отображения ALL для совокупных столбцов. Поскольку в выбранном наборе данных нет значений NULL, не будет никакой разницы с тем выходом, который вы наблюдали ранее.

Функция КУБ

Вместо функции GROUP BY ROLLUP мы можем использовать функцию GROUP BY CUBE для получения другого вывода. В ROLLUP агрегат выполняется в порядке предоставленных столбцов. Функция КУБ агрегирует все комбинации.

Давайте объединим функции КУБ и ГРУППИРОВКА, как показано в приведенном ниже запросе.

1

2

3

4

5

6

7

8

9 9 0005

10

11

12

13

14

15

16

17 9000 5

18

19

20

21

22

SELECT CASE

    WHEN GROUPING(G. EnglishCountryRegionName) = 1

        THEN ‘ALL’

    ELSE ISNULL(G.EnglishCountryRegionName, ‘UNKNOWN’)

    END CountryRegion

,CASE

    WHEN GROUPING(G.StateProvinceName) = 1

        THEN ‘ALL’

    ELSE ISNULL(G.StateProvin ceName, ‘UNKNOWN’)

    END StateProvinceName

,CASE

    WHEN GROUPING(G.City) = 1

        THEN ‘ALL’

    ELSE ISNULL(G.City, ‘UNKNOWN’)

    END City

,SUM(M.[YearlyIncome]) TotalIncome

FROM [AdventureWorksDW2017].[dbo].[vTarget Mail] M

INNER JOIN [dbo].[DimGeography] G ON m.GeographyKey = G.GeographyKey

GROUP BY CUBE(G.EnglishCountryRegionName, G.StateProvinceName, G.City)

HAVING GROUPING(G.EnglishCountryRegionName) = 1

ИЛИ ГРУППИРОВКА(G.StateProvinceName) = 1

ИЛИ ГРУППИРОВКА(G.City) = 1

Результат вышеуказанного запроса показан ниже.

НАБОРЫ ДЛЯ ГРУППИРОВАНИЯ

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

1

2

3

4

5

6

7

8

9

10

900 04 11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

SELECT CASE

    WHEN GROUPING(G.EnglishCountryRegionName) = 1

        THEN ‘ALL’

    ELSE ISNULL(G.EnglishCountryRegionName, ‘UNKNOWN’)

    END CountryRegion

,CASE

    WHEN GROUPING(G.StateProvinceName) = 1

        THEN ‘ALL’

    ELSE ISNULL(G.StateProvinceName, ‘UNKNOWN’)

    END StateProvinceName

,CASE

    WHEN GROUPING(G.City) = 1

        THEN ‘ALL’

    ELSE ISNULL(G. City, ‘UNKNOWN’)

    END City

,SUM(M.[Годовой доход]) Общий доход

ОТ [AdventureWorksDW2017].[dbo].[vTargetMail] M

ВНУТРЕННЕЕ СОЕДИНЕНИЕ [dbo].[DimGeography] G ON m.GeographyKey = G.GeographyKey

9 0004 GROUP BY GROUPING SETS((

        G.EnglishCountryRegionName

        ,G.StateProvinceName

        ,G.City

        ), (

        G.EnglishCountryRegionName

        ,G.City

        ), ()

)

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

Заключение

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

  • Автор
  • Последние сообщения

Динеш Асанка

Динеш Асанка является MVP в категории SQL Server за последние 8 лет. Работает с SQL Server более 15 лет, пишет статьи и книги в соавторстве. Он является ведущим в различных группах пользователей и университетах. Он всегда готов учиться и делиться своими знаниями.

Просмотреть все сообщения Динеша Асанки

Последние сообщения Динеша Асанки (посмотреть все)

SQL Rollup — Scaler Topics

Учебник по SQL

Сводка по SQL

Сводка по SQL

share-outline Курс по СУБД — Освоение основ и расширенных концепций

Шрикант Варма

Бесплатно

Зарегистрировано:

1000

Курс по СУБД — Освоение основ и расширенных концепций

Шрикант Варма

Бесплатно

Начать обучение

Обзор

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

Знакомство с SQL ROLLUP

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

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

Уровни агрегирования

Если n — количество группируемых столбцов, то ROLLUP создает n+1 уровней промежуточных и общих итогов. Поскольку ROLLUP удаляет крайний правый столбец на каждом шаге, порядок столбцов необходимо указывать тщательно.

Например —
ROLLUP(A, B, C) создает четыре группы, предполагая иерархию A > B > C, следующим образом:

ROLLUP удаляет крайний правый столбец на каждом шаге. Для (A, C) и (B, C) групп нет.

Синтаксис

Приведенный ниже пример иллюстрирует основной раздел SQL ROLLUP.

В приведенном выше синтаксисе входной столбец имеет вид (col1,col2) , имеющий иерархию col1 > col2 . SQL ROLLUP генерирует все наборы группировок с учетом этой иерархии. По этой причине мы часто используем ROLLUP для получения промежуточных итогов и общего итога 9.0636 для отчетности.

Параметры

group‑expression — Предложение SQL ROLLUP принимает в качестве параметра любое выражение, не являющееся агрегатом или функцией группирования, которое включает константы и ссылки на столбцы в таблицах , указанных FROM.

Ограничения

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

Пример

  • Агрегирование полного набора данных

    Рассмотрим таблицу «Сотрудники», в которой указана заработная плата сотрудников многонациональной компании. Таблица содержит идентификатор , имя, пол, зарплату и страну в качестве полей.

    9 0011

    ID Имя Пол Зарплата Страна
    1 Ясень Наружная резьба 5000 США
    2 Джеймс Мужчина 4500 Индия
    3 Нэнси Женщина 5500 90 025

    		 США
    4 Сара Женщина 4000 Индия
    5 9002 5

    		 Томас Мужчина 3500 Индия
    6 Мари Женщина 5000 9 0025

    		 Великобритания
    7 Бенджамин Мужчина 6500 Великобритания
    8 Элиза Женщина 900 25

    		 7000 США
    9 Гэри Мужчина 5500 Великобритания
    10 Кори Мужчина 5000 США

    Использование приведенного выше запроса GROUP BY вместе с параметром ROLLUP помогает нам сгруппировать зарплату по стране и полу. ROLLUP в SQL также вычисляет промежуточный итог на уровне страны и общий итог .

    Вывод:

    9002 6 4000

    Страна Пол Общая зарплата
    Индия Женщина
    Индия Мужской 8000
    Индия NULL 12000 9002 5
    Великобритания Женский 5000
    Великобритания Мужской 12000
    Великобритания NULL 17000
    США 90 NULL 22500
    NULL NULL 51500

  • Использование SQL ROLLUP с пунктом HAVING

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

    Следующий запрос использует функцию GROUPING, принимая одно из выражений GROUP BY в качестве аргумента. Для каждой строки GROUPING возвращает одно из следующих значений :

    .

    • 0: столбец является частью группы для этой строки
    • 1: столбец не входит в группу для этой строки

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

    Вывод:

    900 26 12000

    Страна Пол Общая зарплата
    Индия NULL
    Великобритания NULL 17000
    США NULL 22500
    NULL NULL 51500

  • Как выполнить частичный SQL ROLLUP?

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

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

    Вывод:

    9002 6 Мужской

    9 0026 12500

    Страна Пол Общая зарплата
    Индия Женщина 4000
    Индия Мужчина 8000
    Индия NULL 12000
    Великобритания Женщина 5000
    Великобритания 12000
    Великобритания NULL 17000
    США Женский
    США Мужской 10000
    США NULL 22500

Заключение

  • ROLLUP в агрегации SQL используется для выполнения нескольких уровней анализа с помощью одного запроса.