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)
);
Code language: SQL (Structured Query Language) (sql)

其次，將資料插入 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);Code language: JavaScript (javascript)

第三，從 inventory 資料表查詢資料

SELECT 
    *
FROM
    inventory;
Code language: SQL (Structured Query Language) (sql)

SQL GROUPING SETS 簡介

分組集合是一組使用 GROUP BY 子句進行分組的欄位。通常，單個彙總查詢會定義單個分組集合。

以下範例定義一個分組集合 (warehouse, product)。它會傳回倉庫和產品中儲存的庫存單位 (SKU) 數量。

SELECT
    warehouse,
    product, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    warehouse,
    product;
Code language: SQL (Structured Query Language) (sql)

以下查詢會找出每個倉庫的 SKU 數量。它定義了分組集合 (warehouse)

SELECT
    warehouse, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    warehouse;
Code language: SQL (Structured Query Language) (sql)

以下查詢會傳回每個產品的 SKU 數量。它定義了分組集合 (product)

SELECT
    product, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    product;
Code language: SQL (Structured Query Language) (sql)

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

SELECT
    SUM(quantity) qty
FROM
    inventory;
Code language: SQL (Structured Query Language) (sql)

到目前為止，我們有四個分組集合：(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;
Code language: SQL (Structured Query Language) (sql)

以下為輸出結果

從輸出結果可以清楚看到，該查詢產生了單個結果集，其中包含所有分組集合的彙總結果。

即使查詢如預期般運作，它仍有兩個主要問題

• 首先，查詢冗長，難以閱讀。
• 其次，它有效能問題，因為資料庫系統必須多次掃描 inventory 資料表。

為了解決這些問題，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),
        ()
);
Code language: SQL (Structured Query Language) (sql)

這個查詢定義了四個分組集合 (c1,c2)、(c1)、(c2) 和 ()。

您可以應用 GROUPING SETS 來重寫上述具有 UNION ALL 子句的查詢

SELECT
    warehouse,
    product, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    GROUPING SETS(
        (warehouse,product),
        (warehouse),
        (product),
        ()
    );Code language: SQL (Structured Query Language) (sql)

以下為輸出結果

此查詢更易於閱讀，且執行速度比上述查詢更快，因為資料庫系統不必多次讀取 inventory 資料表。

現在，您應該知道如何使用 SQL 的 GROUPING SETS，透過單個查詢產生多個分組集合。