SQL Grouping Sets运算符

2023-03-15 07:27| 来源: 网络整理| 查看: 265

在本教程中，您将学习如何使用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表中的数据：

分组集是一组使用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;

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

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

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

为解决这些问题，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), () );

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

此查询比上面的查询更具可读性和执行速度，因为数据库系统不必多次读取库存表。现在，应该知道如何使用SQL GROUPING SETS使用单个查询生成多个分组集。

上一篇： SQL Having子句下一篇： SQL Rollup

【本文地址】

公司简介

联系我们