SQL Grouping Sets運算子

在本教學中，您將學習如何使用SQL GROUPING SETS運算子生成多個分組集。

建立樣本表

讓我們建立一個名為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表：

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

1. SQL GROUPING SETS簡介

分組集是一組使用GROUP BY子句進行分組的列。通常，單個聚合查詢定義單個分組集。

以下範例定義分組集(倉庫，產品)。它返回倉庫和產品中儲存在庫存中的庫存單位數(SKU)。

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

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

以下查詢查詢倉庫的SKU數量。它定義了分組集(warehouse)：

SELECT
    warehouse, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse;
+---------------+-----+
| warehouse     | qty |
+---------------+-----+
| San Fransisco | 560 |
| San Jose      | 650 |
+---------------+-----+
2 rows in set

以下查詢返回產品的SKU數。它定義了分組集(product)：

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

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

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

以下查詢查詢所有倉庫和產品的SKU數。它定義了一個空的分組集()。

SELECT
    SUM(quantity) qty
FROM
    inventory;

執行上面範例程式碼，得到以下結果：

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

到目前為止，我們有四個分組集:(warehouse, product)，(warehouse)，(product)和()。要使用單個查詢返回所有分組集，可以使用UNION ALL運算子組合上面的所有查詢。

UNION ALL要求所有結果集具有相同的列數，因此，需要將NULL新增到每個查詢的選擇列表中，如下所示：

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;

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

+---------------+---------+------+
| 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選項的一般語法：

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

此查詢定義了四個分組集(c1，c2)，(c1)，(c2)和()。可以使用GROUPING SETS將上面的UNION ALL子句查詢重寫：

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

此查詢比上面的查詢更具可讀性和執行速度，因為資料庫系統不必多次讀取庫存表。
現在，應該知道如何使用SQL GROUPING SETS使用單個查詢生成多個分組集。