SQL GROUPING SETS

概要: このチュートリアルでは、SQL GROUPING SETS 演算子を使用して複数のグルーピングセットを生成する方法を学習します。

サンプルテーブルの設定

GROUPING SETS の機能を説明するために、inventory という名前の新しいテーブルを設定しましょう。

まず、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)

3 番目に、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)、および () の 4 つのグルーピングセットがありました。単一のクエリを使用してすべてのグルーピングセットを返すには、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)

出力は次のとおりです。

出力から明確にわかるように、クエリはすべてのグルーピングセットの集計を含む単一の結果セットを生成しました。

クエリは期待どおりに機能しますが、主に 2 つの問題があります。

• まず、クエリが長いため、読みづらいです。
• 次に、データベースシステムが在庫テーブルを複数回スキャンする必要があるため、パフォーマンスの問題があります。

これらの問題を解決するために、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)

このクエリは、4 つのグルーピングセット (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 を使用して、単一のクエリで複数のグルーピングセットを生成する方法がわかるはずです。