本文介绍AnalyticDB MySQL版支持的聚合函数。
AnalyticDB 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
std(x)
stddev(x)
- 命令说明:返回数值的样本标准差。
- 输入值类型:BIGINT或DOUBLE。
- 返回值类型:DOUBLE。
- 示例:
SELECT std(a) FROM testtable;
返回结果如下:+-------------------+ | std(a) | +-------------------+ | 0.816496580927726 | +-------------------+
STDDEV_POP
stddev_pop(x)
- 命令说明:返回数值的总体标准差。
- 输入值类型:BIGINT或DOUBLE。
- 返回值类型:DOUBLE。
- 示例:
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])
参数 | 是否必填 | 说明 |
---|---|---|
DISTINCT |
选填 | 指定需要去重的列。 |
ORDER BY |
指定需要在分组内部进行排序的列,支持如下排序方式:
若未指定排序方式,默认按照升序排序。 |
|
SEPARATOR |
指定分组内部各值间的分隔符。
若未指定分隔符,默认使用英文逗号(,)分隔。 |
- 命令说明:该函数用于将
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 | +------+--------------------------------------------------------------+