[译文] PostgreSQL 分组集：ROLLUP & CUBE

原创 Hans-Jürgen Schönig 2021-08-27

1349

PostgreSQL 是世界上最好的 OLTP 数据库之一（OLTP = 在线事务处理）。但是，它可以做的不仅仅是 OLTP。PostgreSQL 提供了许多与更多 OLAP 风格的工作负载相关的附加功能。其中一项功能称为“分组集”。

在我们深入研究细节之前，我已经编译了一些示例数据，您可以轻松地将它们加载到您的 SQL 数据库中：

CREATE TABLE t_sales
(
    country        text,
    product_name   text,
    year           int,
    amount_sold    numeric
);
 
INSERT INTO t_sales VALUES
    ('Argentina', 'Shoes', 2020, 12),
    ('Argentina', 'Shoes', 2021, 14),
    ('Argentina', 'Hats', 2020, 54),
    ('Argentina', 'Hats', 2021, 57),
    ('Germany', 'Shoes', 2020, 34),
    ('Germany', 'Shoes', 2021, 29),
    ('Germany', 'Hats', 2020, 19),
    ('Germany', 'Hats', 2021, 22),
    ('USA', 'Shoes', 2020, 99),
    ('USA', 'Shoes', 2021, 103),
    ('USA', 'Hats', 2020, 81),
    ('USA', 'Hats', 2021, 90)
;
复制

请注意，您将在本文中看到的所有内容都非常符合 SQL 标准，因此您可以期望大多数内容也可以在其他专业 SQL 数据库中使用。

让我们从一个简单的聚合开始：

test=# SELECT  country, sum(amount_sold)
       FROM    t_sales
       GROUP BY 1;
 country   | sum
-----------+-----
 USA       | 373
 Germany   | 104
 Argentina | 137
(3 rows)
复制

这里没有什么可说的，除了我们将为每组获得一笔钱这一事实。然而，正在进行一些哲学讨论。“GROUP BY 1”基本上意味着“GROUP BY country”，相当于SELECT子句中的第一列。因此，“GROUP BY country”和“GROUP BY 1”是一回事：

test=# SELECT  country, product_name, sum(amount_sold)
       FROM    t_sales
       GROUP BY 1, 2
       ORDER BY 1, 2;
 country   | product_name | sum
-----------+--------------+-----
 Argentina | Hats         | 111
 Argentina | Shoes        |  26
 Germany   | Hats         |  41
 Germany   | Shoes        |  63
 USA       | Hats         | 171
 USA       | Shoes        | 202
(6 rows)
复制

当然，这也适用于不止一列。不过，我想指出一点。考虑以下示例：

test=# SELECT CASE WHEN country = 'USA'
                THEN 'USA'
                ELSE 'non-US'
              END,
              sum(amount_sold)
       FROM t_sales
       GROUP BY 1;
 case   | sum
--------+-----
 USA    | 373
 non-US | 241
(2 rows)
复制

大多数人按列分组。在某些情况下，按表达式分组是有意义的。就我而言，我们正在即时组建小组（= 一组用于美国，一组用于非美国销售）。这个功能经常被低估。但是，它在许多实际场景中很有用。请记住，您将看到的所有内容也适用于表达式，这意味着可以进行更灵活的分组。

分组集：基本构建块

GROUP BY 会将列中的每个不同条目转换为一个组。有时您可能想要一次进行更多分组。为什么这是必要的？假设您正在处理一个 10 TB 的表。显然，读取这些数据通常是性能方面的限制因素。因此，一次读取数据并立即产生更多结果很有吸引力。这正是您可以使用 GROUP BY GROUP SETS 做的事情。假设我们想一次产生两个结果：

按国家分组
按产品名称分组

这是它的工作原理：

test=# SELECT  country, product_name, sum(amount_sold)
       FROM    t_sales
       GROUP BY GROUPING SETS ((1), (2))
       ORDER BY 1, 2;
 country   | product_name | sum
-----------+--------------+-----
 Argentina |              | 137
 Germany   |              | 104
 USA       |              | 373
           | Hats         | 323
           | Shoes        | 291
(5 rows)
复制

在这种情况下，PostgreSQL 只是简单地附加结果。前三行代表“GROUP BY country”。接下来的两行包含“GROUP BY product_name”的结果。从逻辑上讲，它相当于以下查询：

test=# SELECT  NULL AS country , product_name, sum(amount_sold)
       FROM    t_sales
       GROUP BY  1, 2
       UNION ALL
       SELECT  country, NULL, sum(amount_sold)
       FROM    t_sales
       GROUP BY  1, 2
       ORDER BY 1, 2;
 country   | product_name | sum
-----------+--------------+-----
 Argentina |              | 137
 Germany   |              | 104
 USA       |              | 373
           | Hats         | 323
           | Shoes        | 291
(5 rows)
复制

但是，GROUPING SETS 版本的效率更高，因为它只需要读取一次数据。

汇总：添加“底线”

创建报告时，您通常需要“底线”来总结表中显示的内容。在 SQL 中这样做的方法是使用“GROUP BY ROLLUP”：

test=# SELECT  country, product_name, sum(amount_sold)
       FROM    t_sales
       GROUP BY ROLLUP (1, 2)
       ORDER BY 1, 2;
 country   | product_name | sum
-----------+--------------+-----
 Argentina | Hats         | 111
 Argentina | Shoes        |  26
 Argentina |              | 137
 Germany   | Hats         |  41
 Germany   | Shoes        |  63
 Germany   |              | 104
 USA       | Hats         | 171
 USA       | Shoes        | 202
 USA       |              | 373
           |              | 614
(10 rows)
复制

PostgreSQL 将在结果中注入几行。如您所见，“Argentina”返回 3 而不仅仅是 2 行。ROLLUP 添加了“product_name = NULL”条目。它包含所有阿根廷销售额的总和 (116 + 27 = 137)。为其他两个国家/地区注入了额外的行。最后，为全球总销售额添加一行。

通常那些 NULL 条目不是人们想要看到的，因此用其他类型的条目替换它们是有意义的。这样做的方法是使用一个子选择来检查 NULL 条目并进行替换。这是它的工作原理：

test=# SELECT   CASE WHEN country IS NULL
                      THEN 'TOTAL' ELSE country END,
                CASE WHEN product_name IS NULL
                      THEN 'TOTAL' ELSE product_name END,
                sum
       FROM (SELECT    country, product_name, sum(amount_sold)
             FROM      t_sales
             GROUP BY ROLLUP (1, 2)
             ORDER BY 1, 2
           ) AS x;
 country   | product_name | sum
-----------+--------------+-----
 Argentina | Hats         | 111
 Argentina | Shoes        |  26
 Argentina | TOTAL        | 137
 Germany   | Hats         |  41
 Germany   | Shoes        |  63
 Germany   | TOTAL        | 104
 USA       | Hats         | 171
 USA       | Shoes        | 202
 USA       | TOTAL        | 373
 TOTAL     | TOTAL        | 614
(10 rows)
复制

如您所见，所有 NULL 条目都已替换为“TOTAL”，这在许多情况下是显示此数据的更理想方式。

CUBE：在 PostgreSQL 中高效创建数据立方体

如果您想添加“底线”，ROLLUP 很有用。但是，您通常希望查看国家和产品的所有组合。GROUP BY CUBE 将做到这一点：

test=# SELECT  country, product_name, sum(amount_sold)
       FROM    t_sales
       GROUP BY CUBE (1, 2)
       ORDER BY 1, 2;
 country   | product_name | sum
-----------+--------------+-----
 Argentina | Hats         | 111
 Argentina | Shoes        |  26
 Argentina |              | 137
 Germany   | Hats         |  41
 Germany   | Shoes        |  63
 Germany   |              | 104
 USA       | Hats         | 171
 USA       | Shoes        | 202
 USA       |              | 373
           | Hats         | 323
           | Shoes        | 291
           |              | 614
(12 rows)
复制

在这种情况下，我们得到了所有的组合。从技术上讲，它等同于：GROUP BY country + GROUP BY product_name + GROUP BY country_product_name + GROUP BY()。我们可以使用多个语句来做到这一点，但一次做起来更容易——而且效率更高。

同样，已添加 NULL 值以指示各种聚合级别。

分组集：执行计划

分组集不只是简单地重写查询以将其转换为 UNION ALL——数据库引擎中实际上有特定的代码来执行这些聚合。

您将看到一个“MixedAggregate”，它能够同时在不同级别进行聚合。下面是一个例子：

test=# explain SELECT country, product_name, sum(amount_sold)
           FROM t_sales
           GROUP BY CUBE (1, 2)
           ORDER BY 1, 2;
                          QUERY PLAN
-----------------------------------------------------------
 Sort  (cost=64.15..65.65 rows=601 width=96)
   Sort Key: country, product_name
   ->  MixedAggregate  (cost=0.00..36.41 rows=601 width=96)
     Hash Key: country, product_name
     Hash Key: country
     Hash Key: product_name
     Group Key: ()
     ->  Seq Scan on t_sales ...
(8 rows)
复制

查看 MixedAggregate 还揭示了哪些聚合是作为分组集的一部分执行的。

最后

一般来说，分组集是一个非常酷的功能，但通常不为人所知或被忽视。我们强烈建议使用这个很棒的东西来加速你的聚合。如果您正在处理大型数据集，它特别有用。

postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

[译文] PostgreSQL 分组集：ROLLUP & CUBE

分组集：基本构建块

汇总：添加“底线”

CUBE：在 PostgreSQL 中高效创建数据立方体

分组集：执行计划

最后

评论

相关阅读