GROUP BY

GROUP BY 算子主要用于在 SQL 中进行分组聚合计算操作。

GROUP BY 算子类型

OceanBase 数据库用于对数据进行分组的算法有 HASH 算法和 MERGE 算法，因此根据算法可以将 GROUP BY 算子分为两种：HASH GROUP BY 和 MERGE GROUP BY。执行计划生成时根据 SQL 优化器对于两种算子的代价评估，来选择使用哪种 GROUP BY 算子。

对于普通的聚合函数（SUM/MAX/MIN/AVG/COUNT/STDDEV）也是通过分配 GROUP BY 算子来完成，而对于只有聚合函数而不含有 GROUP BY 的 SQL，分配的是 SCALAR GROUP BY 算子，因此 GROUP BY 算子又可以分为三种：SCALAR GROUP BY、HASH GROUP BY 和 MERGE GROUP BY。

SCALAR GROUP BY

SCALAR GROUP BY 算子用于执行不含 GROUP BY 子句的标量聚合查询，即对整个结果集计算一个单一的聚合值。

SCALAR GROUP BY 示例

1. 创建表 tbl1。

   obclient> CREATE TABLE tbl1 (col1 INT, col2 INT);
   

2. Q1：对表 tbl1 中 col1 列进行求和，同时查看该查询的执行计划。

   obclient> EXPLAIN SELECT SUM(col1) FROM tbl1;
   

   返回结果如下：

   +-------------------------------------------------------------------------+
   | Query Plan                                                              |
   +-------------------------------------------------------------------------+
   | =================================================                       |
   | |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                       |
   | -------------------------------------------------                       |
   | |0 |SCALAR GROUP BY  |    |1       |3           |                       |
   | |1 |└─TABLE FULL SCAN|TBL1|1       |3           |                       |
   | =================================================                       |
   | Outputs & filters:                                                      |
   | -------------------------------------                                   |
   |   0 - output([T_FUN_SUM(T_FUN_SUM(TBL1.COL1))]), filter(nil), rowset=16 |
   |       group(nil), agg_func([T_FUN_SUM(T_FUN_SUM(TBL1.COL1))])           |
   |   1 - output([T_FUN_SUM(TBL1.COL1)]), filter(nil), rowset=16            |
   |       access([TBL1.COL1]), partitions(p0)                               |
   |       is_index_back=false, is_global_index=false,                       |
   |       range_key([TBL1.__pk_increment]), range(MIN ; MAX)always true,    |
   |       pushdown_aggregation([T_FUN_SUM(TBL1.COL1)])                      |
   +-------------------------------------------------------------------------+
   15 rows in set
   

在 Q1 查询返回结果中，除了 SCALAR GROUP BY 算子（0 号算子），还展示了以下执行算子：

• TABLE FULL SCAN：表示全表扫描。该算子属于 TABLE SCAN 算子，详细信息参见 TABLE SCAN。

上述 Q1 查询的执行计划展示中的 Outputs & filters 详细列出了 SCALAR GROUP BY 算子的输出信息如下：

HASH GROUP BY

HASH GROUP BY 算子用于执行使用哈希算法进行的分组聚合操作。

HASH GROUP BY 示例

1. 创建表 tbl2。

   obclient> CREATE TABLE tbl2 (col1 INT, col2 INT);
   

2. Q2：对表 tbl2 按 col1 分组并计算每组 col2 的和，同时筛选出总和大于 2 的分组，查看该查询的执行计划。

   obclient> EXPLAIN SELECT SUM(col2)
       FROM tbl2
       GROUP BY col1
       HAVING SUM(col2) > 2;
   

   返回结果如下：

   +-------------------------------------------------------------------------------------+
   | Query Plan                                                                          |
   +-------------------------------------------------------------------------------------+
   | =================================================                                   |
   | |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                   |
   | -------------------------------------------------                                   |
   | |0 |HASH GROUP BY    |    |1       |3           |                                   |
   | |1 |└─TABLE FULL SCAN|TBL2|1       |3           |                                   |
   | =================================================                                   |
   | Outputs & filters:                                                                  |
   | -------------------------------------                                               |
   |   0 - output([T_FUN_SUM(TBL2.COL2)]), filter([T_FUN_SUM(TBL2.COL2) > 2]), rowset=16 |
   |       group([TBL2.COL1]), agg_func([T_FUN_SUM(TBL2.COL2)])                          |
   |   1 - output([TBL2.COL1], [TBL2.COL2]), filter(nil), rowset=16                      |
   |       access([TBL2.COL1], [TBL2.COL2]), partitions(p0)                              |
   |       is_index_back=false, is_global_index=false,                                   |
   |       range_key([TBL2.__pk_increment]), range(MIN ; MAX)always true                 |
   +-------------------------------------------------------------------------------------+
   14 rows in set
   

在 Q2 查询返回结果中，除了 HASH GROUP BY 算子（0 号算子），还展示了以下执行算子：

• TABLE FULL SCAN：表示全表扫描。该算子属于 TABLE SCAN 算子，详细信息参见 TABLE SCAN。

上述 Q2 查询的执行计划展示中的 Outputs & filters 详细列出了 HASH GROUP BY 算子的输出信息如下：

MERGE GROUP BY

MERGE GROUP BY 算子用于执行使用排序合并算法进行的分组聚合操作。当优化器选择此算法时，它通常需要其输入数据已按分组键排序。

MERGE GROUP BY 示例

1. 创建表 tbl3。

   obclient> CREATE TABLE tbl3 (col1 INT, col2 INT);
   

2. Q3：使用 Hint 强制优化器使用 MERGE GROUP BY，对表 tbl3 按 col1 分组并计算每组 col2 的和，同时筛选出总和大于 2 的分组，查看该查询的执行计划。

   obclient> EXPLAIN SELECT /*+NO_USE_HASH_AGGREGATION*/ SUM(col2)
       FROM tbl3
       GROUP BY col1
       HAVING SUM(col2) > 2;
   

   返回结果如下：

   +-------------------------------------------------------------------------------------+
   | Query Plan                                                                          |
   +-------------------------------------------------------------------------------------+
   | ===================================================                                 |
   | |ID|OPERATOR           |NAME|EST.ROWS|EST.TIME(us)|                                 |
   | ---------------------------------------------------                                 |
   | |0 |MERGE GROUP BY     |    |1       |3           |                                 |
   | |1 |└─PARTITION SORT   |    |1       |3           |                                 |
   | |2 |  └─TABLE FULL SCAN|TBL3|1       |3           |                                 |
   | ===================================================                                 |
   | Outputs & filters:                                                                  |
   | -------------------------------------                                               |
   |   0 - output([T_FUN_SUM(TBL3.COL2)]), filter([T_FUN_SUM(TBL3.COL2) > 2]), rowset=16 |
   |       group([TBL3.COL1]), agg_func([T_FUN_SUM(TBL3.COL2)])                          |
   |   1 - output([TBL3.COL1], [TBL3.COL2]), filter(nil), rowset=16                      |
   |       sort_keys([HASH(TBL3.COL1), ASC], [TBL3.COL1, ASC])                           |
   |   2 - output([TBL3.COL1], [TBL3.COL2]), filter(nil), rowset=16                      |
   |       access([TBL3.COL1], [TBL3.COL2]), partitions(p0)                              |
   |       is_index_back=false, is_global_index=false,                                   |
   |       range_key([TBL3.__pk_increment]), range(MIN ; MAX)always true                 |
   +-------------------------------------------------------------------------------------+
   17 rows in set
   

上述示例中，Q3 查询的执行计划展示中的 Outputs & filters 中详细列出了 MERGE GROUP BY 算子的信息，与 HASH GROUP BY 算子相比，其 output、filter、group、agg_func 等信息的含义是相同的。最大的区别在于执行时选择的分组算法不一样，且由于 MERGE GROUP BY 需要输入数据有序，而 TABLE FULL SCAN 返回的是无序结果，因此优化器在 MERGE GROUP BY 算子之下分配了一个 PARTITION SORT（或 SORT）算子来对数据进行排序。

PARTITION SORT：表示分区排序。该算子属于 SORT 算子，详细信息参见 SORT。