AnalyticDB MySQL聚合函数用法详解 - 云原生数据仓库AnalyticDB

聚合函数用于对一组数据进行汇总计算。本文介绍云原生数据仓库 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])

参数	是否必填	说明
`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                                                          |
+------+--------------------------------------------------------------+