本文分享自华为云社区《GaussDB(DWS) SQL进阶之SQL操作之聚集函数》，作者：两杯咖啡。

聚集操作是 SQL 语言中除扫描、投影、连接外的另一个常用基本操作，主要用于对海量数据进行分组，然后在组内进行统计计算的场景。在 AP 场景下，经常面临海量数据处理的场景，而最终用户希望通过海量数据获取汇总信息，聚集操作的使用将更加广泛。本文从基本聚集操作入手，介绍常用的 SQL 语法，以及一些扩展的聚集功能，同时会讲到在 GaussDB(DWS)里聚集相关的一些优化思路。

一．典型语法

SQL 的聚集操作的典型语法是：

SELECT <column1>, <column2>, Agg_func() FROM t GROUP BY 1, 2 HAVING <filter>;

其中基本元素及概念如下：

• 聚集操作子句

在 SQL 中，聚集操作子句通过 GROUP BY 实现，后面紧接聚集分组列，可以是列名，或者本层输出列的顺序号，从 1 开始。

• 聚集分组列

聚集分组列表明本聚集操作是以哪些列的值进行分组的，聚集分组列值均相等的元组会被划分到同一组。聚集分组列可以是一个，也可以是多个。

• 聚集函数

聚集函数即进行分组后，每组进行统计计算的函数，分为简单的和复杂的聚集函数。其中常用简单聚集函数包括以下五种：

1. COUNT()：用于进行分组内的计数。对于 COUNT (column)，计数不包含 column 为 NULL 值的元组；对于 COUNT (*)，计数包含所有元组。

2. SUM()：用于计算分组内列或表达式的和，计算不包含列为 NULL 值的元组。

3. AVG()：用于计算分组内列或表达式的平均值，AVG(col)等价于 SUM(col)/ COUNT(col)（分组内存在元组）。

4. MIN()：用于计算分组内列或表达式的最小值。

5. MAX()：用于计算分组内列或表达式的最大值。

注：

1. 如果缺少 GROUP BY 且包含聚集函数，则所有元组视为一个分组。

2. 聚集函数不能嵌套。

• 聚集分组过滤条件

该条件为进行完聚集操作后，以分组为单位进行过滤的条件。聚集分组过滤条件是 HAVING 条件，在聚集后进行过滤，而我们通常使用的 WHERE 条件，需要在分组前进行过滤。

语法要求：

由于聚集操作是对聚集列进行去重分组，并进行聚集函数的分组计算，因为聚集操作的输出列和过滤条件中只能包含聚集列、聚集函数和常量，以及由它们组成的表达式。当出现非聚集列时，查询会报错。

特殊地，GaussDB(DWS)支持在主键列或唯一约束列上进行聚集的操作（尽管该操作为冗余操作），此时可以在输出列和过滤条件中包含任何列。

以 TPC-H 测试集的 lineitem 表举例说明，该表记录订单里的每种类型的零件，所属的订单号，零件所属的供应商，在订单中的序号以及价格、发货等信息。

表定义如下：

CREATE TABLE LINEITEM(
   L_ORDERKEY    BIGINT NOT NULL  , L_PARTKEY     BIGINT NOT NULL  , L_SUPPKEY     BIGINT NOT NULL  , L_LINENUMBER  BIGINT NOT NULL  , L_QUANTITY    DECIMAL(15,2) NOT NULL  , L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL  , L_DISCOUNT    DECIMAL(15,2) NOT NULL  , L_TAX         DECIMAL(15,2) NOT NULL  , L_RETURNFLAG  CHAR(1) NOT NULL  , L_LINESTATUS  CHAR(1) NOT NULL  , L_SHIPDATE    DATE NOT NULL  , L_COMMITDATE  DATE NOT NULL  , L_RECEIPTDATE DATE NOT NULL  , L_SHIPINSTRUCT CHAR(25) NOT NULL  , L_SHIPMODE     CHAR(10) NOT NULL  , L_COMMENT      VARCHAR(44) NOT NULL)with (orientation = column)distribute by hash(L_ORDERKEY);

SELECT MAX(l_receiptdate) FROM lineitem; -- 正确，获得所有零件的最后收货时间
SELECT SUM(l_quantity) FROM lineitem where l_orderkey=100000; -- 正确，获得订单号为100000的零件总数
SELECT l_orderkey, MAX(l_shipdate), MIN(l_shipdate) FROM lineitem GROUP BY l_orderkey; -- 正确，求每个订单的最早发货日期和最晚发货日期
SELECT l_orderkey, MAX(l_shipdate), MIN(l_shipdate) FROM lineitem GROUP BY 1; -- 正确，等价于上一条语句
SELECT l_orderkey, MAX(l_shipdate), MIN(l_shipdate) FROM lineitem GROUP BY 1 HAVING MIN(l_shipdate) < ‘1999-01-01’; -- 正确，求零件最早发货日期在1999-01-01之前的，每个订单的最早和最晚的发货日期（每个零件可能单独发货）
SELECT l_orderkey || ‘_’ || SUM(l_quantity), SUM(L_EXTENDEDPRICE) FROM lineitem GROUP BY l_orderkey; -- 正确，求每个订单的组合标识（订单号+零件个数），以及总价格
SELECT l_orderkey, l_partkey, AVG(l_discount) FROM lineitem GROUP BY 1; -- 错误，l_partkey不是聚集列，但出现在输出列中

二．GaussDB(DWS)聚集执行及调优

在 GaussDB(DWS)中，由于是分布式系统，数据计算应该尽量在各个 DN 上并行计算以得到最优的性能。因此，支持以下聚集操作计算方式：

• 如果分布键是 GROUP BY 列的子集，此时在各个 DN 上分别计算，结果汇总即可。

例如：lineitem 表以 l_orderkey 作为分布键，则聚集列包含 l_orderkey 的均可以在各 DN 执行后汇总。

• 对于不满足（1）的场景，各 DN 分别执行后，DN 间仍然可能存在聚集列相等的数据，需要二次聚集，此时 GaussDB(DWS)支持三种计算方式。

示例语句（TPC-H Q1，输出列部分省略）：

select        l_returnflag,        l_linestatus,        sum(l_quantity) as sum_qtyfrom        lineitemwhere        l_shipdate <= date '1998-12-01' - interval '90' day (3)group by        l_returnflag,        l_linestatusorder by        l_returnflag,        l_linestatus;

<1> 各 DN 上进行一次聚集，将结果汇总到 CN 上进行二次聚集。

lineitem 总共行数为 59 亿行。该方法中，经过 DN 一次聚集后，各 DN 输出 4 行数据（全局 96 行），这些数据汇总到 CN 上，由 CN 进行 96 行数据的二次聚集，最终输出 6 行数据。（数据信息均为估算值）

<2> 选择聚集列的子集列进行重分布，回退到（1）的情况后，各 DN 分别聚集后进行结果汇总。

该方法中，首先按聚集的两列进行重分布，重分布数据量为 59 亿，然后各 DN 完成聚集，并将结果返回 CN。

<3> 各 DN 上进行一次聚集，然后选择聚集列的子集列进行重分布，各 DN 上进行二次聚集后结果汇总。

该方法中，各 DN 进行一次聚集，行数由 59 亿减少到 4 行，然后按聚集的两列进行重分布，各 DN 进行二次聚集。

可以看出，该查询适合用<1>和<3>的方式进行执行，因为聚集后的行数比较少，在 CN 上执行或重分布的数据量都不大，所以开销较小。而<2>的方式要对 59 亿行数据进行网络重分布，网络占用较大。可以总结出三种方法的适用场景：

<1> 该方法适合于一次聚集后行数较少且 DN 数较少的场景，这样汇聚到 CN 的行数较少，不会导致 CN 成为计算的瓶颈。

<2> 相较于<3>方法，该方法适合于 DN 一次聚集后行数缩减不明显的场景，这时可以以所有数据重分布的代价，省略 DN 的一次聚集操作。

<3> 与<2>相反，该方法适合于 DN 一次聚集后行数缩减明显的场景，例如上面的示例。

在 GaussDB(DWS)中，以上三种方法的选择是根据代价来自动选择的，也可以通过参数 best_agg_plan 来强制控制选择某种方法进行执行。best_agg_plan=1, 2, 3 分别对应于上述三种方法，0 为默认值，表示由产品自动选择最优计划。

在单 DN 上执行时，GaussDB(DWS)支持以下三种算法：

<1> Plain Agg：最终仅输出一行数据，适合于无聚集列的场景。

<2> HashAgg：使用 Hash 表来进行元组的去重，首先计算聚集列的 hash 值，hash 值相同的再进行列值的比较，避免与所有数据比较后进行去重。去重时进行聚集函数的计算。适合于聚集后行数缩减较多的场景。

<3> Sort + GroupAgg：首先对数据按照聚集列进行排序，这样聚集列相等的元组均相邻，通过遍历一遍排序后的数据，即可完成元组的去重和聚集函数的计算。相较于<2>，适合于聚集后行数缩减较少的场景。

以上<2>和<3>的方法可以通过参数 enable_sort 和 enable_hashagg 来控制（默认均为 on）。当 enable_hashagg=on 且 enable_sort=off 时，优先选择<2>；当 enable_sort=on 且 enable_hashagg=off 时，优先选择<3>。大数据量场景，通常 HashAgg 可以获得较好的性能，所以 GaussDB(DWS)对 HashAgg 进行了较深入的优化。对于个别场景选择<3>的方法导致性能问题，可以通过关闭 enable_sort 来进行调优。

三．DISTINCT 表达式

聚集函数中，均可以通过关键字 DISTINCT 对聚集列进行去重后进行计算，例如：COUNT(DISTINCT col)表示分组内 col 值不同的值的个数。

SELECT COUNT(DISTINCT(l_partkey)) FROM lineitem GROUP BY l_returnflag, l_linestatus; -- 计算每种发货状态下的不同零件数量

在分布式环境下，为了避免 l_partkey 相同的值在不同的 DN 上导致无法去重，GaussDB(DWS)对 DISTINCT 类操作进行了转换，上面语句等价于：

SELECT COUNT(l_partkey) FROM (select l_returnflag, l_linestatus, l_partkey FROM lineitem GROUP BY l_returnflag, l_linestatus, l_partkey) GROUP BY l_returnflag, l_linestatus;

这样，在 GaussDB(DWS)中实际上使用两次 Agg 来计算 DISTINCT 表达式的值，计划如下：

通过计划可以看出，第 8-9 层为 lineitem 基表扫描，上面有两次 Agg 处理 COUNT(DISTINCT)算子。第 6-7 行为第一次 Agg，聚集列为：l_returnflag, l_linestatus, l_partkey，选择 Hashagg 的方法二；第 3-5 行为第二次 Agg，聚集列为：l_returnflag, l_linestatus，选择 Hashagg 的方法三。

注：目前 SQL 标准仅支持聚集函数中出现一列，对于要求多列的 COUNT(DISTINCT)，例如：COUNT(DISTINCT l_partkey, l_suppkey)，实际可以通过手动使用上述改写方式进行求解：

SELECT COUNT(1) FROM (select l_returnflag, l_linestatus, l_partkey, l_suppkey FROM lineitem GROUP BY l_returnflag, l_linestatus, l_partkey, l_suppkey) GROUP BY l_returnflag, l_linestatus;

四．聚集扩展功能

在 SQL 1999 标准中，对聚集函数进行了扩展，新增了 OLAP 函数 ROLLUP(), CUBE(), GROUPING SETS()，用于更灵活的多维数据分组统计功能。其实，这三个函数都可以使用简单的 GROUP BY 的集合合并操作（UNION ALL）来实现，本文中使用 UNION ALL(GROUP BY x)来替代，例如：

GROUP BY a UNION ALL GROUP BY b 的表达式中，x 包括：(a), (b)。本文下面的讨论着重针对 x 进行。

• ROLLUP()是聚集列前缀的聚集结果的合并实现的，例如：

ROLLUP(a, b, c)中，x 包括：(a,b,c), (a,b), (a), ()。（其中 GROUP BY()表示所有行聚集到一组的无 GROUP BY 语义），对于 n 个聚集列，x 中包含 n+1 个聚集组合。

ROLLUP()中的元素可以是列的集合，例如：

ROLLUP((a, b), (b, c))，x 包括：(a,b,b,c)（等价于(a,b,c)）, (a,b), ()。

• CUBE()是聚集列组合的枚举的聚集结果合并实现的，例如：

CUBE(a, b, c)中，x 包括：(a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ()，对于 n 个聚集列，x 中包含 2^n 个聚集组合。

• GROUPING SETS()是聚集列的枚举的聚集结果合并实现的，例如：

GROUPING SETS(a, b, c, d)中，x 包括：(a), (b), (c), (d)，对于 n 个聚集列，x 中包含 n 个聚集组合。

由于 OLAP 函数中，并不是聚集列均出现在每一个聚集结果中，所以增加 GROUPING 函数来标识参数列是否参与每一行聚集结果的运算，例如：对于 CUBE(a, b, c)，其中 x 包括：(a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ()时，对于 x 为(a,b,c), (a,b), (a,c), (a)的聚集结果行，GROUPING(a)的值为 0，其它为 1。

对于包含 OLAP 函数的如下语句：

select l_returnflag, l_linestatus, l_shipmode, sum(l_extendedprice), grouping(l_returnflag) from lineitem group by cube(1,2,3) order by 1,2,3;

GaussDB(DWS)的计划如下：

目前 GaussDB(DWS)中使用 Sort+GroupAgg 来实现 OLAP 函数，后续版本会支持 HashAgg 进行执行，提高性能。

五．总结

聚集操作是 SQL 语言中的基本操作，只有深入了解聚集操作的语法、语义和支持的功能范围，才能更灵活地驾驭灵活的 SQL 语言进行开发，为学习更高阶的 SQL 语言打下良好的基础。

想了解 GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的 PB 级数仓黑科技，后台还可获取众多学习资料哦~

点击关注，第一时间了解华为云新鲜技术~​