SQL分组集

摘要：在本教程中，你将了解如何使用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分组集简介

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

以下示例定义了一个分组集(warehous，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)

以下是输出

从输出中可以清楚地看到，查询生成了一个单个的结果集，其中包含所有分组集的聚合。

尽管查询按预期工作，但它有两个主要问题

• 首先，查询很难读，因为它很长。
• 其次，它有一个性能问题，因为数据库系统必须多次扫描库存表。

为了解决这些问题，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表。

现在，你应该知道如何使用SQLGROUPING SETS使用单个查询生成多个分组集。