聚合函数

聚合函数用于对一组数据进行汇总计算。本文介绍云原生数据仓库 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)             
  • 命令说明:该函数用于计算记录数。

    说明

    distinctall指明在计数时是否去除重复记录,默认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])

参数

是否必填

说明

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