在本教程中,您将学习如何使用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
使用单个查询生成多个分组集。