本文介绍AnalyticDB MySQL版支持的聚合函数。

AnalyticDB MySQL版支持如下聚合函数:
  • AVG:该函数用于计算平均值。
  • BIT_AND:返回参数所有位按位AND后的结果。
  • BIT_OR:返回参数所有位按位OR后的结果。
  • BIT_XOR:返回参数所有位按位异或后的结果。
  • COUNT:该函数用于计算记录数。
  • MAX:该函数用于计算最大值。
  • MIN:该函数用于计算最小值。
  • STD或STDDEV:返回数值的样本标准偏差。
  • STDDEV_POP:返回数值的总体标准差。
  • STDDEV_SAMP:返回一组数值(整数、小数或浮点)的总体标准差。
  • SUM:该函数用于计算汇总值。
  • VAR_POP:返回一组数值(整数、小数或浮点)的总体方差。
  • VAR_SAMP:返回一组数值(整数、小数或浮点)的样本方差。
  • VARIANCE:返回一组数值(整数、小数或浮点)的总体方差。
  • GROUP_CONCAT:该函数用于将GROUP BY返回结果中属于同一个分组的值连接起来,返回一个字符串结果。
说明
GROUP_CONCAT函数外,本文中的其它聚合函数均以testtable表为例,建表语句如下:
CREATE TABLE testtable(a INT) DISTRIBUTED BY HASH(a);           

已使用如下语句往testtable表中插入测试数据:

INSERT INTO testtable VALUES (1),(2),(3);               

AVG

avg(bigint x)
avg(double x)
avg(float x)                
  • 命令说明:该函数用于计算平均值。
  • 返回值类型:DOUBLE。
  • 示例:
    select avg(a) from testtable;
    +--------+
    | avg(a) |
    +--------+
    |    2.0 |                       

BIT_AND

bit_and(float x)
bit_and(bigint x)
bit_and(double x)               
  • 命令说明:返回参数所有位按位AND后的结果。
  • 返回值类型:BIGINT。
  • 示例:
    select bit_and(a) from testtable;
    +------------+
    | bit_and(a) |
    +------------+
    |          0 |                       

BIT_OR

bit_or(float x)
bit_or(bigint x)
bit_or(double x)               
  • 命令说明:返回参数所有位按位OR后的结果。
  • 返回值类型:BIGINT。
  • 示例:
    select bit_or(a) from testtable;
    +----------------------------+
    |          bit_or(a)         |
    +----------------------------+
    |           3                |                        

BIT_XOR

bit_xor(double x)
bit_xor(bigint x)
bit_xor(float x)                
  • 命令说明:返回参数所有位按位异或后的结果。
  • 返回值类型:BIGINT。
  • 示例:
    select bit_xor(a) from testtable;
    +----------------------------+
    |          bit_xor(a)        |
    +----------------------------+
    |          0                 |                      

COUNT

count([distinct|all] value x)             
  • 命令说明:该函数用于计算记录数。

    distinctall指明在计数时是否去除重复记录,默认all,即返回全部记录。如果指定distinct,返回结果只计算唯一值数量。

  • 返回值类型:BIGINT。
  • 示例:
    select count(distinct a) from testtable;
    +-------------------+
    | count(DISTINCT a) |
    +-------------------+
    |                 3 |                       

MAX

max(value x)               
  • 命令说明:该函数用于计算最大值。

    value可以为任意数据类型,但是BOOLEAN类型的数据不允许参与运算。

    当列中的值为null时,该行不参与计算。

  • 返回值类型:LONG。
  • 示例:
    select max(a) from testtable;
    +--------+
    | max(a) |
    +--------+
    |      3 |                        

MIN

min(value x)               
  • 命令说明:该函数用于计算最小值。

    value可以为任意数据类型,但是BOOLEAN类型的数据不允许参与运算。

    当列中的值为null时,该行不参与计算。

  • 返回值类型:LONG。
  • 示例:
    select min(a) from testtable;
    +--------+
    | min(a) |
    +--------+
    |      1 |                       

STD或STDDEV

std(double x)
std(bigint x)
stddev(double x)
stddev(bigint x)              
  • 命令说明:返回数值的样本标准偏差。
  • 返回值类型:DOUBLE。
  • 示例:
    select std(a) from testtable;
    +-------------------+
    | std(a)            |
    +-------------------+
    | 0.816496580927726 |                      

STDDEV_POP

stddev_pop(double x)
stddev_pop(bigint x)               
  • 命令说明:返回数值的总体标准差。
  • 返回值类型:DOUBLE。
  • 示例:
    select stddev_pop(a) from testtable;
    +----------------------------+
    |    stddev_pop(a)           |
    +----------------------------+
    |    0.816496580927726       |                       

STDDEV_SAMP

stddev_samp(double x)
stddev_samp(bigint x)             
  • 命令说明:返回一组数值(整数、小数或浮点)的总体标准差。
  • 返回值类型:DOUBLE。
  • 示例:
    select stddev_samp(a) from testtable;
    +----------------------------+
    |     stddev_samp(a)         |
    +----------------------------+
    |         1.0                |                       

SUM

sum(double x)
sum(float x)
sum(bigint x)             
  • 命令说明:该函数用于计算汇总值。
  • 返回值类型:BIGINT。
  • 示例:
    select sum(a) from testtable;
    +----------------------------+
    |         sum(a)             |
    +----------------------------+
    |          6                 |                     

VAR_POP

var_pop(double x)
var_pop(bigint x)               
  • 命令说明:返回一组数值x(整数、小数或浮点)的总体标准方差。也可以使用VARIANCE()函数,具有相同的意义,但VARIANCE()不是标准的SQL。若找不到匹配的项,则VAR_POP()返回NULL。
  • 返回值类型:DOUBLE。
  • 示例:
    select var_pop(a)  from testtable;
    +----------------------------+
    |         var_pop(a)         |
    +----------------------------+
    |   0.6666666666666666       |                       

VAR_SAMP

var_samp(double x)
var_samp(bigint x)              
  • 命令说明:返回一组数值(整数、小数或浮点)的样本方差。
  • 返回值类型:DOUBLE。
  • 示例:
    select var_samp(a)  from testtable;
    +----------------------------+
    |         var_samp(a)        |
    +----------------------------+
    |            1.0             |                

VARIANCE

variance(double x)
variance(bigint x)      
  • 命令说明:返回一组数值(整数、小数或浮点)的总体标准方差。VARIANCE()作为标准SQL的延伸,也可以使用标准SQL函数VAR_POP()来代替。若找不到匹配的项,则VARIANCE()返回NULL。
  • 返回值类型:DOUBLE。
  • 示例:
    select variance(a)  from testtable;
    +----------------------------+
    |         variance(a)        |
    +----------------------------+
    |    0.6666666666666666      |               

GROUP_CONCAT

GROUP_CONCAT([DISTINCT] col_name
             [ORDER BY col_name [ASC | DESC]]
             [SEPARATOR str_val])
参数 是否必填 说明
DISTINCT 选填 指定需要去重的列。
ORDER BY 指定需要在分组内部进行排序的列,支持如下排序方式:
  • ASC:升序。
  • DESC:降序。

若未指定排序方式,默认按照升序排序。

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                                                         |
    +----+------------------------------------------------------------+