GaussDB(DWS) SQL进阶之SQL操作之聚集函数

1. 前言

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

2. 典型语法

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不是聚集列，但出现在输出列中

2. 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_qty
from
        lineitem
where
        l_shipdate <= date '1998-12-01' - interval '90' day (3)
group by
        l_returnflag,
        l_linestatus
order by
        l_returnflag,
        l_linestatus;

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

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

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

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

1. 各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来进行调优。

3. 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;

4. 聚集扩展功能

在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进行执行，提高性能。

5. 总结

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