SQL Server ROLLUP

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

Введение в SQL Server ROLLUP

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

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

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

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

Давайте рассмотрим пример. Следующий КУБ(d1,d2,d3) определяет восемь возможных группирующих наборов:

(d1, d2, d3)
(d1, d2)
(d2, d3)
(d1, d3)
(d1)
(d2)
(d3)
()

А ROLLUP(d1,d2,d3) создает только четыре группирующих набора, предполагая иерархию d1 > d2 > d3, как показано ниже:

(d1, d2, d3)
(d1, d2)
(d1)
()

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

Синтаксис SQL Server ROLLUP

Общий синтаксис SQL Server ROLLUP выглядит следующим образом:

SELECT
    d1,
    d2,
    d3,
    aggregate_function(c4)
FROM
    table_name
GROUP BY
    ROLLUP(d1, d2, d3);

В этом синтаксисе d1, d2 и d3 являются столбцами измерений. Оператор вычислит агрегацию значений в столбце c4 на основе иерархии d1 > d2 > d3.

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

SELECT
    d1,
    d2,
    d3,
    aggregate_function(c4)
FROM
    table_name
GROUP BY
    d1, 
    ROLLUP(d2, d3);

Примеры SQL Server ROLLUP

Мы повторно используем таблицу sales.sales_summary, созданную в руководстве 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;

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

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

Вот что получилось:

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

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

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

В этом примере иерархия выглядит следующим образом: бренд > сегмент:

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

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

Выход:

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