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)
);

第二步,将数据插入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 SETSGROUP 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使用单个查询生成多个分组集。


猿狮妹
2022-11-26
SQLGroupingSets 运算符 在线教程
热门教程
1 SQL复制表 如果要将SQL表复制到同一数据库中的另一个表中,可以使用select语句。从一个表复制到另一个表的语法如下:SELECT * INTO FROM 例如,可以使用以下语句将hr_employee表的记录复制到employee表中。SELECT * INTO employee FROM hr_employee;注意:SELECT INTO与INSERT INTO语句完全不同...
2 SQL Max()函数 在本教程中,您将学习如何使用SQL Max函数查找组中的最大值。1. SQL MAX函数简介SQL提供MAX()函数,用于在一组值中查找最大值。 以下是MAX函数的语法。MAX(expression)MAX函数忽略NULL值。与SUM,COUNT和AVG函数不同,DISTINCT选项不适用于MAX函数。2. SQL MAX函数示例...
3 SQL Sum()函数 在本教程中,我们将演示如何使用SQL SUM函数,此函数计算所有值或不同值的总和。1. SQL SUM函数简介SQL SUM函数是一个聚合函数,它返回所有或不同值的总和。需要注意的是,只能将SUM函数应用于数字列。以下说明了SUM函数的语法。SUM([ALL|DISTINCT] expression)ALL运算符用于将聚合应用于所有值。 SUM函数默认使用ALL运算符。例如,如果有一组集合值:(1,2,3,3,NULL)。 使用SUM函数将返回9,请注意,SUM函...
4 SQL Insert语句 在本教程中,我们将学习如何使用SQL INSERT语句来将数据插入表中。1. SQL INSERT语句简介SQL提供了INSERT语句,用于将一行或多行插入表中。 INSERT语句用于:向表中插入一行向表中插入多行将行从一个表复制到另一个表中。...
5 SQL Count()函数 本教程将向您展示如何使用SQL COUNT函数来获取组中的项目数。1. SQL COUNT函数简介SQL COUNT函数是一个聚合函数,它返回符合条件行数。 可以使用SELECT语句中的COUNT函数来获取员工数量,每个部门的员工数量,指定工作岗位的员工数量等。以下是SQL COUNT函数的语法:COUNTC ([ALL | DISTINCT] expression);COUNT函数的结果取决于传递给它的参数。默认情况下,COUNT函数使用ALL关键字,无论是否...
6 SQL Update语句 在本教程中,您将学习如何使用SQL UPDATE语句来修改表中现有行的数据。
7 SQL Order By排序 本教程将演示如何使用SQL ORDER BY子句根据指定的标准按升序或降序对结果集进行排序。1. SQL ORDER BY子句简介当使用SELECT语句查询表中的数据时,结果集中显示的行的顺序可能与您的预期不符。在某些情况下,结果集中显示的行按其物理存储在表中的顺序排列。 但是,如果查询优化器使用索引来处理查询,则行记录将显示为它们以索引键顺序存储。 因此,结果集中的行顺序未确定或不可预测。查询优化器是数据库系统中的内置软件组件,用于确定SQL语句查询请求数据的最有效方...
8 SQL自连接 本教程将向您展示如何使用SQL自连接技术将表连接到自身。1. SQL自连接简介有时,将表连接到自身是很有用的。 这种类型的连接称为自连接。我们将一张表连接到自身来评估同一个表中其他行的行。 要执行自联接,我们使用内连接或左连接子句。因为同一个表在单个查询中出现两次,所以必须使用表别名。 以下语句说明了如何将表连接到自身。...
9 SQL检查约束 在本教程中,将学习如何使用SQL CHECK约束来验证基于布尔表达式的列或一组列中的数据。1. SQL CHECK约束简介CHECK约束是SQL中的完整性约束,它允许您指定列或列集中的值必须满足布尔表达式。您可以在单个列或整个表上定义CHECK约束。 如果在单个列上定义CHECK约束,则CHECK约束仅检查此列的值。 但是,如果在表上定义CHECK约束,则会根据同一行的其他列中的值限制列中的值。...
10 SQL Delete语句 在本教程中,您将学习如何使用SQL DELETE语句删除表中的一行或多行。1. SQL DELETE语句简介要从表中删除一行或多行,请使用DELETE语句。 DELETE语句的一般语法如下:
  • Copyright © 2021 猿狮院, All rights reserved.