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表返回數據：

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;

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

該查詢生成了一個結果，其中包含了我們所期望的所有分組集的聚合。

但是，它有以下兩個主要問題：

• 查詢非常冗長(看起來是不是很累？)
• 查詢很慢，因為SQL Server需要執行四個查詢並將結果集合並為一個查詢。

為了解決這些問題，SQL Server提供了一個名為GROUPING SETS的GROUP BY子句的子句。

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函數
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列。