聚合函数用于对一组数据进行汇总计算。本文介绍云原生数据仓库 AnalyticDB MySQL 版聚合函数的用法与示例。
AnalyticDB for MySQL支持如下聚合函数:
ARBITRARY:随机返回一组数据中的任意一个值。
AVG:该函数用于计算平均值。
BIT_AND:返回参数所有位按位AND后的结果。
BIT_OR:返回参数所有位按位OR后的结果。
BIT_XOR:返回参数所有位按位异或后的结果。
COUNT:该函数用于计算记录数。
MAX:该函数用于计算最大值。
MIN:该函数用于计算最小值。
STD、STDDEV或STDDEV_POP:返回数值的总体标准差。
STDDEV_SAMP:返回一组数值(整数、小数或浮点)的样本标准差。
SUM:该函数用于计算汇总值。
VARIANCE(非标准SQL函数):返回一组数值(整数、小数或浮点)的总体方差。
VAR_POP(标准SQL函数):返回一组数值(整数、小数或浮点)的总体方差。
VAR_SAMP:返回一组数值(整数、小数或浮点)的样本方差。
GROUP_CONCAT:该函数用于将
GROUP BY
返回结果中属于同一个分组的值连接起来,返回一个字符串结果。
除GROUP_CONCAT
函数外,本文中的其他聚合函数均以testtable
表为例,建表语句如下:
CREATE TABLE testtable(a INT) DISTRIBUTED BY HASH(a);
已使用如下语句往testtable
表中插入测试数据:
INSERT INTO testtable VALUES (1),(2),(3);
ARBITRARY
arbitrary(x)
命令说明:随机返回一组数据中的任意一个值。
输入值类型:支持输入任意类型的参数。
返回值类型:与该函数的输入值类型保持一致。
示例:
SELECT arbitrary(a) FROM testtable;
返回结果如下:
+--------------+ | arbitrary(a) | +--------------+ | 2 | +--------------+
AVG
avg(x)
命令说明:该函数用于计算平均值。
输入值类型:BIGINT、DOUBLE或FLOAT。
返回值类型:DOUBLE。
示例:
SELECT avg(a) FROM testtable;
返回结果如下:
+--------+ | avg(a) | +--------+ | 2.0 | +--------+
BIT_AND
bit_and(x)
命令说明:返回参数所有位按位
AND
后的结果。输入值类型:BIGINT、DOUBLE或FLOAT。
返回值类型:BIGINT。
示例:
SELECT bit_and(a) FROM testtable;
返回结果如下:
+------------+ | bit_and(a) | +------------+ | 0 | +------------+
BIT_OR
bit_or(x)
命令说明:返回参数所有位按位
OR
后的结果。输入值类型:BIGINT、DOUBLE或FLOAT。
返回值类型:BIGINT。
示例:
SELECT bit_or(a) FROM testtable;
返回结果如下:
+-----------+ | bit_or(a) | +-----------+ | 3 | +-----------+
BIT_XOR
bit_xor(x)
命令说明:返回参数所有位按位异或后的结果。
输入值类型:BIGINT、DOUBLE或FLOAT。
返回值类型:BIGINT。
示例:
SELECT bit_xor(a) FROM testtable;
返回结果如下:
+------------+ | bit_xor(a) | +------------+ | 0 | +------------+
COUNT
count([distinct|all] x)
命令说明:该函数用于计算记录数。
说明distinct
、all
指明在计数时是否去除重复记录,默认all
,即返回全部记录。如果指定distinct
,返回结果只计算唯一值数量。输入值类型:数值、字符串类型或BOOLEAN类型。
返回值类型:BIGINT。
示例:
计算
testtable
中值是唯一的记录数,语句如下:SELECT count(distinct a) FROM testtable;
返回结果如下:
+-------------------+ | count(distinct a) | +-------------------+ | 3 | +-------------------+
计算
testtable
中所有的记录数,语句如下:SELECT count(all a) FROM testtable;
返回结果如下:
+--------------+ | count(all a) | +--------------+ | 3 | +--------------+
MAX
max(x)
命令说明:该函数用于计算最大值。
输入值类型:该函数支持输入任意类型的参数,但是BOOLEAN类型的数据不允许参与运算。
说明当列中的值为
NULL
时,该行不参与计算。返回值类型:与该函数的输入值类型保持一致。
示例:
SELECT max(a) FROM testtable;
返回结果如下:
+--------+ | max(a) | +--------+ | 3 | +--------+
MIN
min(value x)
命令说明:该函数用于计算最小值。
输入值类型:该函数支持输入任意类型的参数,但是BOOLEAN类型的数据不允许参与运算。
说明当列中的值为
NULL
时,该行不参与计算。返回值类型:与该函数的输入值类型保持一致。
示例:
SELECT min(a) FROM testtable;
返回结果如下:
+--------+ | min(a) | +--------+ | 1 | +--------+
STD、STDDEV或STDDEV_POP
std(x)
stddev(x)
stddev_pop(x)
命令说明:返回数值的总体标准差。
输入值类型:BIGINT或DOUBLE。
返回值类型:DOUBLE。
示例:
示例一:
SELECT std(a) FROM testtable;
返回结果如下:
+-------------------+ | std(a) | +-------------------+ | 0.816496580927726 | +-------------------+
示例二:
SELECT stddev_pop(a) FROM testtable;
返回结果如下:
+-------------------+ | stddev_pop(a) | +-------------------+ | 0.816496580927726 | +-------------------+
STDDEV_SAMP
stddev_samp(x)
命令说明:返回一组数值(整数、小数或浮点)的样本标准差。
输入值类型:BIGINT或DOUBLE。
返回值类型:DOUBLE。
示例:
SELECT stddev_samp(a) FROM testtable;
返回结果如下:
+----------------+ | stddev_samp(a) | +----------------+ | 1.0 | +----------------+
SUM
sum(x)
命令说明:该函数用于计算汇总值。
输入值类型:BIGINT、DOUBLE或FLOAT。
返回值类型:BIGINT。
示例:
SELECT sum(a) FROM testtable;
返回结果如下:
+--------+ | sum(a) | +--------+ | 6 | +--------+
VARIANCE
variance(x)
命令说明:返回一组数值(整数、小数或浮点)的总体标准方差。
说明VARIANCE()
会忽略值为NULL的行。因此若一组数值全为NULL,VARIANCE()
会直接返回NULL。VARIANCE()
函数作为标准SQL的延伸,您也可以使用标准SQL函数VAR_POP()
来代替。
输入值类型:BIGINT或DOUBLE。
返回值类型:DOUBLE。
示例:
SELECT variance(a) FROM testtable; +----------------------------+ | variance(a) | +----------------------------+ | 0.6666666666666666 |
VAR_POP
var_pop(x)
命令说明:返回一组数值
x
(整数、小数或浮点)的总体标准方差。说明VAR_POP()
会忽略值为NULL的行。因此若一组数值全为NULL,VAR_POP()
会直接返回NULL。也可以使用
VARIANCE()
函数,具有相同的意义,但VARIANCE()
不是标准的SQL函数。
输入值类型:BIGINT或DOUBLE。
返回值类型:DOUBLE。
示例:
SELECT var_pop(a) FROM testtable;
返回结果如下:
+--------------------+ | var_pop(a) | +--------------------+ | 0.6666666666666666 | +--------------------+
VAR_SAMP
var_samp(x)
命令说明:返回一组数值(整数、小数或浮点)的样本方差。
输入值类型:BIGINT或DOUBLE。
返回值类型:DOUBLE。
示例:
SELECT var_samp(a) FROM testtable;
返回结果如下:
+-------------+ | var_samp(a) | +-------------+ | 1.0 | +-------------+
GROUP_CONCAT
GROUP_CONCAT([DISTINCT] col_name
[ORDER BY col_name [ASC | DESC]]
[SEPARATOR str_val])
参数 | 是否必填 | 说明 |
| 选填 | 指定需要去重的列。 |
| 指定需要在分组内部进行排序的列,支持如下排序方式:
若未指定排序方式,默认按照升序排序。 | |
| 指定分组内部各值间的分隔符。 若未指定分隔符,默认使用英文逗号(,)分隔。 |
命令说明:该函数用于将
GROUP BY
返回结果中属于同一个分组的值连接起来,返回一个字符串结果。说明仅当需要使用
GROUP_CONCAT
函数进行连接的列中所有取值均为NULL时,才会输出NULL
。输入值类型:字符串。
返回值类型:字符串。
示例
本示例以
person
表为例介绍如何使用GROUP_CONCAT
函数,person
表创建语句如下:CREATE TABLE person(id INT,name VARCHAR,age INT ) DISTRIBUTED BY HASH(id);
使用如下语句往表中插入数据:
INSERT INTO person VALUES (1,'mary',13),(2,'eva',14),(2,'adam',13),(3,'eva',13),(3,null,13),(3,null,null),(4,null,13),(4,null,null);
现需要根据
id
列进行分组,并通过GROUP_CONCAT
函数将ID相同的name
列展示出来,展示结果时需要对name
列去重,并按照name
列进行降序排序,多个name
列间用#
分隔,语句如下:SELECT id, GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#') FROM person GROUP BY id;
返回结果如下:
+------+--------------------------------------------------------------+ | id | GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#') | +------+--------------------------------------------------------------+ | 2 | eva#adam | | 1 | mary | | 4 | NULL | | 3 | eva | +------+--------------------------------------------------------------+