SQL Server Rollup

在本教學中，將學習如何使用SQL Server ROLLUP生成多個分組集。

SQL Server ROLLUP簡介

SQL Server ROLLUP是GROUP BY子句的子句，它提供了定義多個分組集的簡寫。 與CUBE子句不同，ROLLUP不會根據維度列創建所有可能的分組集; CUBE是其中的一部分。

生成分組集時，ROLLUP假定維度列之間存在層次結構，並且僅基於此層次結構生成分組集。

ROLLUP通常用於生成小計和總計來生成報告目的。
考慮一個例子。 以下CUBE(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是維列。 該語句將根據層次結構d1> d2> d3計算列c4中的值的聚合。
還可以執行部分匯總以減少使用以下語法生成的小計：

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

SQL Server ROLLUP示例

在這個示例中將重用在GROUPING SETS教學中創建的sales.sales_summary表進行演示。 如果尚未創建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);

執行上面查詢語句，得到以下結果：