创建样本表

让我们创建一个名为inventory的新表来演示GROUPING SETS的功能。

首先，创建一个名为inventory的新表：

CREATE TABLE inventory (
    warehouse VARCHAR(255),
    product VARCHAR(255) NOT NULL,
    model VARCHAR(50) NOT NULL,
    quantity INT,
    PRIMARY KEY (warehouse,product,model)
);

第二步，将数据插入inventory表：

snippet.sql

INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Jose', 'iPhone','6s',100);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Fransisco', 'iPhone','6s',50);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Jose','iPhone','7',50);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Fransisco', 'iPhone','7',10);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Jose','iPhone','X',150);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Fransisco', 'iPhone','X',200);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Jose','Samsung','Galaxy S',200);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Fransisco','Samsung','Galaxy S',200);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Fransisco','Samsung','Note 8',100);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Jose','Samsung','Note 8',150);

第三，查询inventory表中的数据：

SELECT 
    *
FROM
    inventory;
+---------------+---------+----------+----------+
| warehouse     | product | model    | quantity |
+---------------+---------+----------+----------+
| San Jose      | iPhone  | 6s       |      100 |
| San Fransisco | iPhone  | 6s       |       50 |
| San Jose      | iPhone  | 7        |       50 |
| San Fransisco | iPhone  | 7        |       10 |
| San Jose      | iPhone  | X        |      150 |
| San Fransisco | iPhone  | X        |      200 |
| San Jose      | Samsung | Galaxy S |      200 |
| San Fransisco | Samsung | Galaxy S |      200 |
| San Fransisco | Samsung | Note 8   |      100 |
| San Jose      | Samsung | Note 8   |      150 |
+---------------+---------+----------+----------+
10 rows in set

GROUPING SETS简介

分组集是一组使用GROUP BY子句进行分组的列。通常，单个聚合查询定义单个分组集。

以下示例定义分组集(仓库，产品)。它返回仓库和产品中存储在库存中的库存单位数(SKU)。

snippet.sql

SELECT
    warehouse,
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse,
    product;

执行上面查询语句，得到以下结果：

   warehouse   | product | qty 
---------------+---------+-----
 San Fransisco | iPhone  | 260
 San Fransisco | Samsung | 300
 San Jose      | iPhone  | 300
 San Jose      | Samsung | 350

以下查询查找仓库的SKU数量。它定义了分组集(warehouse)：

snippet.sql

SELECT
    warehouse, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse;
+---------------+-----+
| warehouse     | qty |
+---------------+-----+
| San Fransisco | 560 |
| San Jose      | 650 |
+---------------+-----+
2 ROWS IN SET

以下查询返回产品的SKU数。它定义了分组集(product)：

snippet.sql

SELECT
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    product;

执行上面查询语句，得到以下结果：

snippet.shell

+---------+-----+
| product | qty |
+---------+-----+
| iPhone  | 560 |
| Samsung | 650 |
+---------+-----+
2 rows in set

以下查询查找所有仓库和产品的SKU数。它定义了一个空的分组集()。

snippet.sql

SELECT
    SUM(quantity) qty
FROM
    inventory;

执行上面示例代码，得到以下结果：

+------+
| qty  |
+------+
| 1210 |
+------+
1 row in set

到目前为止，我们有四个分组集:(warehouse, product)，(warehouse)，(product)和()。要使用单个查询返回所有分组集，可以使用UNION ALL运算符组合上面的所有查询。

UNION ALL要求所有结果集具有相同的列数，因此，需要将NULL添加到每个查询的选择列表中，如下所示：

snippet.sql

SELECT
    warehouse,
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse,
    product
UNION ALL
SELECT
    warehouse, 
    NULL,
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse
UNION ALL
SELECT
    NULL,
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    product
UNION ALL
SELECT
    NULL,
    NULL,
    SUM(quantity) qty
FROM
    inventory;
SQL

执行上面查询语句，得到以下结果：

+---------------+---------+------+
| warehouse     | product | qty  |
+---------------+---------+------+
| San Fransisco | iPhone  | 260  |
| San Fransisco | Samsung | 300  |
| San Jose      | iPhone  | 300  |
| San Jose      | Samsung | 350  |
| San Fransisco | NULL    | 560  |
| San Jose      | NULL    | 650  |
| NULL          | iPhone  | 560  |
| NULL          | Samsung | 650  |
| NULL          | NULL    | 1210 |
+---------------+---------+------+
9 rows in set

从输出中可以清楚地看到，查询生成了一个结果集，其中包含所有分组集的聚合。尽管查询按预期工作，但它有两个主要问题：

首先，查询语句很难阅读，因为它很冗长。
其次，它存在性能问题，因为数据库系统必须多次扫描库存表。

为解决这些问题，SQL提供了GROUPING SETS。GROUPING SETS是GROUP BY子句的一个选项。GROUPING SETS在同一查询中定义多个分组集。

以下是GROUPING SETS选项的一般语法：

snippet.sql

SELECT
    c1,
    c2,
    aggregate (c3)
FROM
    TABLE
GROUP BY
    GROUPING SETS (
        (c1, c2),
        (c1),
        (c2),
        ()
);

此查询定义了四个分组集(c1，c2)，(c1)，(c2)和()。可以使用GROUPING SETS将上面的UNION ALL子句查询重写：

snippet.sql

SELECT
    warehouse,
    product, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    GROUPING SETS(
        (warehouse,product),
        (warehouse),
        (product),
        ()
    );

执行上面查询语句，得到以下结果：

+---------------+---------+------+
| warehouse     | product | qty  |
+---------------+---------+------+
| San Fransisco | iPhone  | 260  |
| San Fransisco | Samsung | 300  |
| San Jose      | iPhone  | 300  |
| San Jose      | Samsung | 350  |
| San Fransisco | NULL    | 560  |
| San Jose      | NULL    | 650  |
| NULL          | iPhone  | 560  |
| NULL          | Samsung | 650  |
| NULL          | NULL    | 1210 |
+---------------+---------+------+
9 rows in set

此查询比上面的查询更具可读性和执行速度，因为数据库系统不必多次读取库存表。