1、group by 分组语句

在SQL中group by主要用来进行分组统计，分组字段放在group by的后面；分组结果一般需要借助聚合函数实现。

group by语法结构 1、常用语法

语法结构 SELECT column_name1,column_name2, … 聚合函数1,聚合函数2 , … FROM table_name GROUP BY column_name1,column_name2, … 说明：1、group by中的分组字段和select后的字段要保持一致；2、通常group by和聚合函数一起使用，但也可以不包含聚合函数，根据业务需求决定；3、常用的聚合函数有max()、min()、avg()、sum()、count()等。

2、影响group by的内存参数

work_mem:因为group by 语句很会用到sort、hash中的任何一中方式，如果work_mem 配置不合适就是用到temp file 进行排序，调整work_mem的目的是使相关操作尽可能在内存中执行

max_parallel_workers：并行相关参数可以条并行扫描相关的速度，是通过消耗cpu资源提升查询效率得一种方法

max_parallel_workers_per_gather：同上

3、聚合方式对group 的影响（重点介绍）

金仓数据库中针对group by 函数有两种聚合方式hashagg、groupagg

HashAggregate

对于hash聚合来说，数据库会根据group by字段后面的值算出hash值，并根据前面使用的聚合函数在内存中维护对应的列表。如果select后面有两个聚合函数，那么在内存中就会维护两个对应的数据。同样的，有n个聚合函数就会维护n个同样的数组。对于hash算法来说，数组的长度肯定是大于group by的字段的distinct值的个数的，且跟这个值应该呈线性关系，group by后面的值越唯一，使用的内存也就越大。

执行计划示例：

1. aligputf8=# explain select count(1) from pg_class group by oid;
2. QUERY PLAN
3. ----------------------------------------------------------------------
4. HashAggregate  (cost=1721.40..2020.28 rows=23910 width=4)
5. Group By: oid
6. ->  Seq Scan on pg_class  (cost=0.00..1004.10 rows=143460 width=4)
7. Settings:  enable_seqscan=on
8. (4 rows)

GroupAggregate

对于普通聚合函数，使用group聚合，其原理是先将表中的数据按照group by的字段排序，这样子同一个group by的值就在一起，这样就只需要对排好序的数据进行一次全扫描，就可以得到聚合的结果了。

执行计划示例：

1. aligputf8=# set enable_hashagg = off;
2. SET
3. aligputf8=#  explain select count(1) from pg_class group by oid;
4. QUERY PLAN
5. ----------------------------------------------------------------------------
6. GroupAggregate  (cost=13291.66..14666.48 rows=23910 width=4)
7. Group By: oid
8. ->  Sort  (cost=13291.66..13650.31 rows=143460 width=4)
9. Sort Key: oid
10. ->  Seq Scan on pg_class  (cost=0.00..1004.10 rows=143460 width=4)
11. Settings:  enable_hashagg=off; enable_seqscan=on
12. (6 rows)

在不考虑聚合函数的前提下，从上面的两个执行计划的cost来说，GroupAgg 由于需要排序，效率很差，消耗是HashAggregate的7倍。

但是HashAgg也不是万能的，从定义中可以看到在select 查询中用到一个聚合函数hashagg就需要额外维护一个数组。如果的情况下hashagg效率会随着聚合函数的增加直线上升。

对比两种聚合方式

• hashagg 不进行排序操作，所以如果对结果集顺序不做要求的情况下有限hashagg

• hashagg 的资源消耗会随着聚合函数的增加直线上升。

实验数据

1、构造1000万行测试数据

create table t1(id int primary key, a int, b int);
declare
i int;
begin
i=1;
while(i<=10000000) loop
insert into t1 values(i, i, i);
i=i+1;
end loop;
end;

测试场景一：不带聚合函数

HashAggregate聚合

kingbase=# explain (analyze true ,buffers true) SELECT /*+hashagg*/  a%10 as m,b%100 n  from t1 group by m ,n;

GroupAggregate聚合

kingbase=# explain (analyze true ,buffers true) SELECT   a%10 as m,b%100 n  from t1 group by m ,n;

针对1000万数据在没有聚合函数情况下 hashagg性能是groupagg的两倍以上

测试场景二：少量聚合函数

HashAggregate聚合

kingbase=#explain (analyze true,buffers true) SELECT /+hashagg/ a%10 as m,b%100 n ,max(a),max(b),avg(a),avg(b) from t1 group by m ,n;

GroupAggregate聚合

kingbase=# explain (analyze true ,buffers true) SELECT   a%10 as m,b%100 n ,max(a),max(b),avg(a),avg(b) from t1 group by m ,n;

测试场景三：大量聚合函数

HashAggregate聚合

本次只是大量聚合函数测试可以看到HashAggregate 相较于场景二时间变长1s中左右， 具体多少聚合函数会对hashagg 有比较大的影响还需要大家深入探讨

测试场景四：hashagg 走索引

总结

可以看出，对于GroupAgg来说，消耗的内存基本上是恒定的，无论group by哪个字段。当聚合函数较少的时候，速度也相对较慢，但是相对稳定。

HashAgg在少数聚合函数是表现优异，但是很多聚合函数，性能跟消耗的内存差异很明显。尤其是受group by字段的唯一性很明显，字段count（district）值越大，hash聚合消耗的内存越多，性能下降剧烈。

所以在sql中有大量聚合函数，group by 的字段由相对比较唯一的时候，应该用GroupAgg，而不能用HashAgg。

本栏目推荐文章
• 自定义jQuery插件Step by Step
• 4- if 流程语句和案例
• ES--RestClient查询文档
• CF550C. Divisibility by Eight
• ES--高亮
• ES--分页
• ES--排序
• ES--布尔查询
• ES---控制相关性算分
• 解决前后端的跨域问题：Access to XMLHttpRequest at '**' from origin '**' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.

语句 kingbase group ES by语句kingbase group es 语句group by 函数kingbase案例es only_full_group_by mysql group by only-full_group_by 函数kingbase es only_full_group_by mysql group only only_full_group_by incompatible sql_mode group clause group only_full_group_by nonaggregated