# 聚合函数 表1聚合函数列表 | 函数名 | 与MySQL的差异 | |:---|:---| | AVG() | - GaussDB中当expr中的列为BIT、BOOL、整数类型，且所有行的和超过BIGINT的范围时，会发生溢出导致整数翻转。 - GaussDB在AVG函数入参为TEXT/BLOB类型时行为存在差异： - MySQL 5.7中，AVG(TEXT/BLOB)返回值类型为MEDIUMTEXT类型；MySQL 8.0中，AVG(TEXT/BLOB)返回值类型为DOUBLE类型。 - 在GaussDB中，AVG(TEXT/BLOB)返回值类型与MySQL 8.0版本保持一致。 | | BIT_AND() | BIT_AND函数入参为NULL且被其他函数嵌套时行为有差异：在MySQL 5.7中，结果为-1；在MySQL 8.0中，结果为NULL；在GaussDB中，此函数嵌套的表现与MySQL 8.0版本保持一致。 ``` -- GaussDB： m_db=# SELECT acos(bit_and(null)); acos ------ (1 row) -- MySQL 5.7： mysql> SELECT acos(bit_and(null)); +---------------------+ | acos(bit_and(null)) | +---------------------+ | 3.141592653589793 | +---------------------+ 1 row in set (0.03 sec) -- MySQL 8.0 mysql> SELECT acos(bit_and(null)); +---------------------+ | acos(bit_and(null)) | +---------------------+ | NULL | +---------------------+ 1 row in set (0.01 sec) ``` | | BIT_OR() | - | | BIT_XOR() | - | | COUNT() | GaussDB支持count(tablename.\*)语法，MySQL不支持。 | | GROUP_CONCAT() | - GaussDB中当GROUP_CONCAT参数中同时有DISTINCT和ORDER BY语法时，所有ORDER BY后的表达式必须也在DISTINCT的表达式之中。 - GaussDB中GROUP_CONCAT(... ORDER BY 数字)不代表按照第几个参数的顺序，数字只是一个常量表达式，相当于不排序。 - GaussDB中使用参数group_concat_max_len限制GROUP_CONCAT最大返回长度，超长截断，目前能返回的最大长度是1073741823，小于MySQL。 - 默认UTF8字符集下，由于GaussDB的UTF8字符集的最大字节数与MySQL的UTF8字符集最大字节数不同。会导致创建的表结构与MySQL存在差异。 ``` -- GaussDB： m_db=# SET m_format_behavior_compat_options='enable_precision_decimal'; SET m_db=# CREATE TABLE t1 AS SELECT * FROM (SELECT CASE WHEN 1 < 2 THEN group_concat(1.23, 3.24) ELSE 12.34 END v1) c1; INSERT 0 1 m_db=# DESC t1; Field | Type | Null | Key | Default | Extra -------+--------------+------+-----+---------+------- v1 | varchar(256) | YES | | | (1 row) -- MySQL 5.7： mysql> CREATE TABLE t1 AS SELECT * FROM (SELECT CASE WHEN 1 < 2 THEN group_concat(1.23, 3.24) ELSE 12.34 END v1) c1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> DESC t1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | v1 | varchar(341) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec) ``` - GROUP_CONCAT函数作为NULLIF函数的入参，嵌套场景行为有差异：在MySQL 5.7中，NULLIF入参嵌套GROUP_CONCAT与非嵌套GROUP_CONCAT的数值会判断为相等，返回NULL；在MySQL 8.0中，因精度差异会判断为不等；在GaussDB中，此函数嵌套的表现与MySQL 8.0版本保持一致。 ``` -- GaussDB： m_db=# SELECT nullif(group_concat(1/7), 1/7); nullif -------- 0.1429 (1 row) -- MySQL 5.7： mysql> SELECT nullif(group_concat(1/7), 1/7); +--------------------------------+ | nullif(group_concat(1/7), 1/7) | +--------------------------------+ | NULL | +--------------------------------+ 1 row in set (0.00 sec) -- MySQL 8.0： mysql> SELECT nullif(group_concat(1/7), 1/7); +--------------------------------+ | nullif(group_concat(1/7), 1/7) | +--------------------------------+ | 0.1429 | +--------------------------------+ 1 row in set (0.00 sec) ``` | | MAX() | - 当参数为非表字段时，MAX函数返回值类型和MySQL 5.7不一致。 - 开启精度传递时，MAX函数嵌套time、date、datetime、timestamp类型的时间间隔运算，返回值及返回类型与MySQL 8.0保持一致。 - 开启精度传递时，MAX函数和INTERVAL的时间间隔运算，返回值及返回类型与MySQL 8.0保持一致。 | | MIN() | - 当参数为非表字段时，MAX函数返回值类型和MySQL 5.7不一致。 - 开启精度传递时，MAX函数嵌套time、date、datetime、timestamp类型的时间间隔运算，返回值及返回类型与MySQL 8.0保持一致。 - 开启精度传递时，MAX函数和INTERVAL的时间间隔运算，返回值及返回类型与MySQL 8.0保持一致。 | | SUM() | GaussDB中当expr中的列为BIT、BOOL、整数类型，且所有行的和超过BIGINT的范围时，会发生溢出导致整数翻转。 | | STD() | - | | 聚合函数 | - GaussDB中指定DISTINCT且SQL语句包含GROUP BY子句时，不对结果进行排序，MySQL会进行排序。 - ORDER BY语句中包含聚合函数时GaussDB不报错，MySQL会报错。 - 在未开启精度传递（没有设置m_format_behavior_compat_options = 'enable_precision_decimal'）的情况下，当聚合函数以其他函数、操作符或SELECT子句等表达式作为入参时（如SELECT sum(abs(n)) FROM t），聚合函数将获取不到入参表达式传递的精度信息，导致函数的结果精度与MySQL有差异。 - 聚合函数的结果与数据输入顺序相关，不同的数据输入顺序会导致结果存在差异。 - 例如与ORDER BY同时使用时，改变了聚合函数的执行顺序，会导致结果与MySQL不一致。 ``` --准备基表： CREATE TABLE test_n(col_unumeric1 decimal(4,3) unsigned, col_znumeric2 decimal(3,2) unsigned zerofill, col_znumeric3 decimal(5,3) unsigned zerofill); Query OK, 0 rows affected (0.01 sec) INSERT INTO test_n VALUES(1.010, 2.02, 3.303),(1.190, 2.29, 3.339),(1.180, 2.28, 3.338); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 CREATE TABLE test_n_2(col_unumeric1 decimal(4,3) unsigned, col_znumeric2 decimal(3,2) unsigned zerofill, col_znumeric3 decimal(5,3) unsigned zerofill); Query OK, 0 rows affected (0.02 sec) INSERT INTO test_n_2 VALUES(1.180, 2.28, 3.338),(1.190, 2.29, 3.339),(1.010, 2.02, 3.303); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 CREATE TABLE IF NOT EXISTS fun_op_case_tb_1 (id int, name varchar(20), col_unumeric1 NUMERIC(4,3) unsigned, col_znumeric2 DECIMAL(3,2) zerofill,col_znumeric3 DEC(5,3) zerofill); CREATE TABLE INSERT INTO fun_op_case_tb_1 (id, name, col_unumeric1, col_znumeric2, col_znumeric3) VALUES (1, '计算机', 1.11, 2.12, 3.133), (2, '计算机', 2.11, 2.22, 3.233), (3, '计算机', 3.11, 2.32, 3.333), (4, '计算机', 1.41, 2.42, 3.343), (5, '计算机', 1.51, 2.52, 3.353), (6, '计算机', 1.61, 2.26, 3.363), (7, '计算机', 1.17, 2.27, 3.337), (8, '计算机', 1.18, 2.28, 3.338), (9, '计算机', 1.19, 2.29, 3.339), (10, '计算机', 1.01, 2.02, 3.303), (1,'软件', 1.11, 2.12, 3.133), (2,'软件', 2.11, 2.22, 3.233), (3,'软件', 3.11, 2.32, 3.333), (4,'软件', 1.41, 2.42, 3.343), (5,'软件', 1.51, 2.52, 3.353), (6,'软件', 1.61, 2.26, 3.363), (7,'软件', 1.17, 2.27, 3.337), (8,'软件', 1.18, 2.28, 3.338), (9,'软件', 1.19, 2.29, 3.339), (10,'软件', 1.01, 2.02, 3.303), (1, '数据库', 1.11, 2.12, 3.133), (2, '数据库', 2.11, 2.22, 3.233), (3, '数据库', 3.11, 2.32, 3.333), (4, '数据库', 1.41, 2.42, 3.343), (5, '数据库', 1.51, 2.52, 3.353), (6, '数据库', 1.61, 2.26, 3.363), (7, '数据库', 1.17, 2.27, 3.337), (8, '数据库', 1.18, 2.28, 3.338), (9, '数据库', 1.19, 2.29, 3.339), (10, '数据库', 1.01, 2.02, 3.303); INSERT 0 30 --GaussDB： m_db=# SELECT * FROM test_n; col_unumeric1 | col_znumeric2 | col_znumeric3 ---------------+---------------+--------------- 1.010 | 2.02 | 03.303 1.190 | 2.29 | 03.339 1.180 | 2.28 | 03.338 m_db=# SELECT * FROM test_n_2; col_unumeric1 | col_znumeric2 | col_znumeric3 ---------------+---------------+--------------- 1.180 | 2.28 | 03.338 1.190 | 2.29 | 03.339 1.010 | 2.02 | 03.303 m_db=# SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM test_n_2 ; std --------------------- 0.24779023386727736 (1 row) m_db=# SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM test_n ; std --------------------- 0.24779023386727742 (1 row) m_db=# SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM fun_op_case_tb_1 GROUP BY name ORDER BY name; std -------------------- 1.8167446160646796 1.8167446160646794 1.8167446160646796 (3 rows) --MySQL: mysql> SELECT * FROM test_n; +---------------+---------------+---------------+ | col_unumeric1 | col_znumeric2 | col_znumeric3 | +---------------+---------------+---------------+ | 1.010 | 2.02 | 03.303 | | 1.190 | 2.29 | 03.339 | | 1.180 | 2.28 | 03.338 | +---------------+---------------+---------------+ 3 rows in set (0.00 sec) mysql> SELECT *FROM test_n_2; +---------------+---------------+---------------+ | col_unumeric1 | col_znumeric2 | col_znumeric3 | +---------------+---------------+---------------+ | 1.180 | 2.28 | 03.338 | | 1.190 | 2.29 | 03.339 | | 1.010 | 2.02 | 03.303 | +---------------+---------------+---------------+ 3 rows in set (0.00 sec) mysql> SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM test_n_2 ; +----------------------------------------------------+ | std(col_unumeric1*(col_znumeric2 | col_znumeric3)) | +----------------------------------------------------+ | 0.24779023386727736 | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM test_n; +----------------------------------------------------+ | std(col_unumeric1*(col_znumeric2 | col_znumeric3)) | +----------------------------------------------------+ | 0.24779023386727742 | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM fun_op_case_tb_1 GROUP BY name ORDER BY name; +----------------------------------------------------+ | std(col_unumeric1*(col_znumeric2 | col_znumeric3)) | +----------------------------------------------------+ | 1.8167446160646794 | | 1.8167446160646794 | | 1.8167446160646794 | +----------------------------------------------------+ 3 rows in set (0.00 sec) --删除基表： DROP TABLE test_n; DROP TABLE DROP TABLE test_n_2; DROP TABLE DROP TABLE fun_op_case_tb_1; DROP TABLE ``` - 例如与WITH ROLLUP同时使用时，改变了聚合函数的执行顺序，会导致结果与MySQL不一致。 ``` --基表准备： CREATE TABLE IF NOT EXISTS t1 (name VARCHAR(20), c1 INT(100), c2 FLOAT(7,5)); INSERT INTO t1 VALUES ('计算机', 666,-55.155), ('计算机', 789,-15.593), ('计算机', 928,-53.963), ('计算机', 666,-54.555), ('计算机', 666,-55.555), ('数据库', 666,-55.155), ('数据库', 789,-15.593), ('数据库', 928,-53.963), ('数据库', 666,-54.555), ('数据库', 666,-55.555); --GaussDB： m_db=# SELECT name, std(c1/c2) c5 FROM t1 GROUP BY name WITH rollup; name | c5 --------+-------------------- 数据库 | 15.02396266299967 计算机 | 15.023962662999669 | 15.02396266299967 (3 rows) --MySQL mysql> SELECT name, std(c1/c2) c5 FROM t1 GROUP BY name WITH rollup; +-----------+--------------------+ | name | c5 | +-----------+--------------------+ | 数据库 | 15.023962662999669 | | 计算机 | 15.023962662999669 | | NULL | 15.02396266299967 | +-----------+--------------------+ 3 rows in set (0.00 sec) --删除基表： DROP TABLE t1; DROP TABLE ``` - 聚合函数与GROUP BY同时存在的场景下，存在中间结果为DECIMAL数据类型参与运算时，MySQL存在数据失真问题，GaussDB保留完整精度的数据。 ``` --基表准备： CREATE TABLE IF NOT EXISTS fun_op_case_tb_1 (id int,name varchar(20),col_znumeric2 DECIMAL(3,2) zerofill,col_znumeric3 DEC(5,3) zerofill, col_bit1 BIT(3), col_time2 time); INSERT INTO fun_op_case_tb_1 VALUES (1, '计算机', 0.01, 3.130, b'101', '08:30:23.01'), (2, '计算机', 1.20, 30.990, b'101', '08:30:23.01'), (3, '计算机', 1.33, 43.500, b'101', '08:30:23.01'), (4, '计算机', 2.24, 30.990, b'101', '08:30:23.01'), (5, '计算机', 1.25, 43.600, b'101', '08:30:23.01'), (6,'计算机',2.20,'20.900',b'101','08:30:23.01'), (7,'计算机',2.20,'20.900',b'101','08:30:23.01'), (8,'计算机',2.20,'20.900',b'101','08:30:23.01'), (9,'计算机',2.29,'22.780',b'101','08:30:23.01'), (10,'计算机',2.02,'20.900',b'101','08:30:23.01'); --GaussDB： m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal'; m_db=# SELECT avg(col_znumeric3/col_znumeric2) FROM fun_op_case_tb_1 WHERE id<=10 GROUP BY name; avg ---------------- 46.90407212526 (1 row) m_db=# SELECT sum(col_bit1/col_time2) FROM fun_op_case_tb_1 WHERE id<=10 GROUP BY name; sum -------- 0.0006 (1 rows) --MySQL： mysql> SELECT avg(col_znumeric3/col_znumeric2) FROM fun_op_case_tb_1 WHERE id<=10 GROUP BY name; +----------------------------------+ | avg(col_znumeric3/col_znumeric2) | +----------------------------------+ | 46.90407213000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT sum(col_bit1/col_time2) FROM fun_op_case_tb_1 WHERE id<=10 GROUP BY name; +-------------------------+ | sum(col_bit1/col_time2) | +-------------------------+ | 0.0010 | +-------------------------+ 1 row in set (0.00 sec) --删除基表： DROP TABLE fun_op_case_tb_1; DROP TABLE ``` |