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