SQL Server GROUPING SETS

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

Настройте сводную таблицу продаж

Давайте создадим новую таблицу с именем sales.sales_summary для демонстрации.

SELECT
    b.brand_name AS brand,
    c.category_name AS category,
    p.model_year,
    round(
        SUM(
            quantity * i.list_price *(1 - discount)
        ),
        0
    ) sales INTO sales.sales_summary
FROM
    sales.order_items i
INNER JOIN production.products p ON p.product_id = i.product_id
INNER JOIN production.brands b ON b.brand_id = p.brand_id
INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY
    b.brand_name,
    c.category_name,
    p.model_year
ORDER BY
    b.brand_name,
    c.category_name,
    p.model_year;

В этом запросе мы извлекаем данные об объеме продаж по брендам и категориям и заполняем ими таблицу sales.sales_summary.

Следующий запрос возвращает данные из таблицы sales.sales_summary:

SELECT
 *
FROM
 sales.sales_summary
ORDER BY
 brand,
 category,
 model_year;

Начало работы с SQL Server GROUPING SETS

По определению, набор группировки — это группа столбцов, по которым вы группируете. Обычно один запрос с агрегатом определяет один набор группировки.

Например, следующий запрос определяет набор группировки, включающий бренд и категорию, которая обозначается как(бренд, категория). Запрос возвращает объем продаж, сгруппированный по бренду и категории:

SELECT
    brand,
    category,
    SUM(sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    category
ORDER BY
    brand,
    category;

Следующий запрос возвращает объем продаж по бренду. Он определяет набор группировки(бренд):

SELECT
    brand,
    SUM(sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand
ORDER BY
    brand;

Следующий запрос возвращает сумму продаж по категориям. Он определяет набор группировки(категорию):

SELECT
    category,
    SUM(sales) sales
FROM
    sales.sales_summary
GROUP BY
    category
ORDER BY
    category;

Следующий запрос определяет пустой набор группировки(). Он возвращает объем продаж по всем брендам и категориям.

SELECT
    SUM(sales) sales
FROM
    sales.sales_summary;

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

(brand, category)
(brand)
(category)
()

Чтобы получить единый набор результатов с агрегированными данными по всем группирующим наборам, можно использовать оператор UNION ALL.

Поскольку оператор UNION ALL требует, чтобы все результирующие наборы имели одинаковое количество столбцов, необходимо добавить NULL в список выбора запросов следующим образом:

SELECT
    brand,
    category,
    SUM(sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    category
UNION ALL
SELECT
    brand,
    NULL,
    SUM(sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand
UNION ALL
SELECT
    NULL,
    category,
    SUM(sales) sales
FROM
    sales.sales_summary
GROUP BY
    category
UNION ALL
SELECT
    NULL,
    NULL,
    SUM(sales)
FROM
    sales.sales_summary
ORDER BY brand, category;

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

Однако у него есть две основные проблемы:

1. Запрос довольно длинный.
2. Запрос выполняется медленно, поскольку SQL Server необходимо выполнить четыре подзапроса и объединить результирующие наборы в один.

Для устранения этих проблем SQL Server предоставляет подпункт предложения GROUP BY, который называется GROUPING SETS.

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

SELECT
    column1,
    column2,
    aggregate_function(column3)
FROM
    table_name
GROUP BY
    GROUPING SETS(
       (column1, column2),
       (column1),
       (column2),
       ()
);

Этот запрос создает четыре группировочных набора:

(column1,column2)
(column1)
(column2)
()

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

SELECT
 brand,
 category,
 SUM(sales) sales
FROM
 sales.sales_summary
GROUP BY
 GROUPING SETS(
(brand, category),
(brand),
(category),
()
 )
ORDER BY
 brand,
 category;

Как видите, запрос выдает тот же результат, что и запрос, использующий оператор UNION ALL. Однако этот запрос гораздо более читабелен и, конечно, более эффективен.

Функция ГРУППИРОВКИ

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

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

SELECT
    GROUPING(brand) grouping_brand,
    GROUPING(category) grouping_category,
    brand,
    category,
    SUM(sales) sales
FROM
    sales.sales_summary
GROUP BY
    GROUPING SETS(
       (brand, category),
       (brand),
       (category),
       ()
    )
ORDER BY
    brand,
    category;

Значение в столбце grouping_brand указывает, агрегирована ли строка или нет:

• 1 означает, что объем продаж суммируется по брендам
• 0 означает, что объем продаж не агрегируется по брендам.

Та же логика применяется к столбцу grouping_category.

Краткое содержание

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