PostgreSQL 教程: GROUPING SETS

九月 4, 2023

摘要：在本教程中，您将了解分组集合，以及如何使用 PostgreSQL 的GROUPING SETS子句在查询中生成多个分组集。

目录

设置样例表

让我们开始创建一个新表，名为sales，用于演示。

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
    brand VARCHAR NOT NULL,
    segment VARCHAR NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (brand, segment)
);

INSERT INTO sales (brand, segment, quantity)
VALUES
    ('ABC', 'Premium', 100),
    ('ABC', 'Basic', 200),
    ('XYZ', 'Premium', 100),
    ('XYZ', 'Basic', 300)
RETURNING *;

输出：

 brand | segment | quantity
-------+---------+----------
 ABC   | Premium |      100
 ABC   | Basic   |      200
 XYZ   | Premium |      100
 XYZ   | Basic   |      300
(4 rows)

该sales表存储按品牌和细分市场销售的产品数量。

GROUPING SETS 简介

分组集是一组列，您可以使用GROUP BY子句对其进行分组。

分组集由放置在括号内的以逗号分隔的列列表表示：

(column1, column2, ...)

例如，以下查询使用GROUP BY子句返回按品牌和细分市场销售的产品数量。换句话说，它定义了品牌和细分市场的分组集，表示为(brand, segement)。

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand,
    segment;

输出：

 brand | segment | sum
-------+---------+-----
 XYZ   | Basic   | 300
 ABC   | Premium | 100
 ABC   | Basic   | 200
 XYZ   | Premium | 100
(4 rows)

以下查询查找某个品牌销售的产品数量。它定义了一个分组集(brand)：

SELECT
    brand,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand;

输出：

 brand | sum
-------+-----
 ABC   | 300
 XYZ   | 400
(2 rows)

以下查询查找按细分市场销售的产品数量。它定义了一个分组集(segment)：

SELECT
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment;

输出：

 segment | sum
---------+-----
 Basic   | 500
 Premium | 200
(2 rows)

以下查询查找所有品牌和细分市场销售的产品数量。它定义了一个空分组集，用()表示。

SELECT SUM (quantity) FROM sales;

输出：

 sum
-----
 700
(1 row)

假设您想要使用单个查询获取所有分组集。为了实现这一点，您可以使用UNION ALL来组合上面的所有查询。

由于UNION ALL要求所有结果集具有相同数量的具有兼容数据类型的列，因此您需要通过添加NULL到每个结果集的SELECT列表来调整查询，如下所示：

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand,
    segment

UNION ALL

SELECT
    brand,
    NULL,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand

UNION ALL

SELECT
    NULL,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment

UNION ALL

SELECT
    NULL,
    NULL,
    SUM (quantity)
FROM
    sales;

输出：

 brand | segment | sum
-------+---------+-----
 XYZ   | Basic   | 300
 ABC   | Premium | 100
 ABC   | Basic   | 200
 XYZ   | Premium | 100
 ABC   | null    | 300
 XYZ   | null    | 400
 null  | Basic   | 500
 null  | Premium | 200
 null  | null    | 700
(9 rows)

此查询生成一个结果集，其中包含所有分组集的聚合。

尽管上面的查询按您的预期工作，但它有两个主要问题。

  • 首先，它相当长。
  • 其次，它存在性能问题，因为 PostgreSQL 必须为每个查询单独扫描sales表。

为了提高效率，PostgreSQL 提供了GROUPING SETS子句，它是GROUP BY子句的子子句。

GROUPING SETS允许您在同一查询中定义多个分组集。

GROUPING SETS的一般语法如下：

SELECT
    c1,
    c2,
    aggregate_function(c3)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (c1, c2),
        (c1),
        (c2),
        ()
);

在此语法中，我们有四个分组集：(c1,c2)、(c1)、(c2)和()。

要将此语法应用于上面的示例，您可以使用GROUPING SETS子句而不是像UNION ALL这样的子句：

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    GROUPING SETS (
        (brand, segment),
        (brand),
        (segment),
        ()
    );

输出：

 brand | segment | sum
-------+---------+-----
 null  | null    | 700
 XYZ   | Basic   | 300
 ABC   | Premium | 100
 ABC   | Basic   | 200
 XYZ   | Premium | 100
 ABC   | null    | 300
 XYZ   | null    | 400
 null  | Basic   | 500
 null  | Premium | 200
(9 rows)

该查询更短且更具可读性。另外，PostgreSQL 会优化扫描sales表的次数，不会多次扫描。

GROUPING 函数

GROUPING()函数接受一个参数，该参数可以是列名或表达式：

GROUPING( column_name | expression)

column_name或expression必须与GROUP BY子句中指定的列名或表达式匹配。

如果参数是当前分组集的成员，则GROUPING()函数返回位 0，否则返回位 1 。

请参见以下示例：

SELECT
	GROUPING(brand) grouping_brand,
	GROUPING(segment) grouping_segment,
	brand,
	segment,
	SUM (quantity)
FROM
	sales
GROUP BY
	GROUPING SETS (
		(brand),
		(segment),
		()
	)
ORDER BY
	brand,
	segment;

PostgreSQL GROUPING SETS - GROUPING function.

如图所示，当grouping_brand的值为 0 时，sum列会显示brand的分组小计。

当grouping_segment中的值为零时，sum列显示segment的分组小计。

您可以在HAVING子句中使用GROUPING()函数来查找每个品牌的小计，如下所示：

SELECT
	GROUPING(brand) grouping_brand,
	GROUPING(segment) grouping_segment,
	brand,
	segment,
	SUM (quantity)
FROM
	sales
GROUP BY
	GROUPING SETS (
		(brand),
		(segment),
		()
	)
HAVING GROUPING(brand) = 0	
ORDER BY
	brand,
	segment;

PostgreSQL GROUPING SETS - GROUPING function in HAVING clause

在本教程中，您学习了如何使用 PostgreSQL 的GROUPING SETS生成多个分组集。

搜索

分类

标签