SQL分析支持的聚合函数 - 阿里云物联网平台

聚合（Aggregate）函数的输入与输出是多对一的关系，即将多条输入记录聚合成一条输出值，可以与group by语句配合使用。本文为您提供SQL分析支持的聚合函数的命令格式、参数说明及示例，指导您使用聚合函数完成开发。

SQL分析支持的聚合函数如下。

函数	功能
ANY_VALUE	在指定范围内任选一个值返回。
APPROX_DISTINCT	返回输入的非重复值的近似数目。
ARG_MAX	返回指定列的最大值对应行的列值。
ARG_MIN	返回指定列的最小值对应行的列值。
AVG	计算平均值。
BITWISE_AND_AGG	计算输入Value的bit AND聚合值。
BITWISE_OR_AGG	计算输入Value的bit OR聚合值。
COUNT	计算记录数。
COUNT_IF	计算指定表达式为True的记录数。
MAX	计算最大值。
MAX_BY	返回指定列的最大值对应行的列值。
MEDIAN	计算中位数。
MIN	计算最小值。
MIN_BY	返回指定列的最小值对应行的列值。
STDDEV	计算总体标准差。
STDDEV_SAMP	计算样本标准差。
SUM	计算汇总值。
WM_CONCAT	用指定的分隔符连接字符串。

ANY_VALUE

命令格式。
```
any_value(<colname>)
```
命令说明。
在指定范围内任选一个值返回。
参数说明。
colname：必填。可以为任意类型。
返回值说明。
返回值类型同colname对应值类型。colname值为NULL时，该行不参与计算。

示例。

示例1：在所有职工中任选一名。命令示例如下：

select any_value(ename) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| SMITH      |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，并在各组中任选一名。命令示例如下：

select deptno, any_value(ename) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | CLARK      |
| 20         | SMITH      |
| 30         | ALLEN      |
+------------+------------+

APPROX_DISTINCT

命令格式。
```
approx_distinct(<colname>)
```
命令说明。
计算指定列的非重复值的近似数目。
参数说明。
colname：必填。需要统计去重的列。
返回值说明。
返回BIGINT类型。此函数会产生5%的标准误差。colname值为NULL时，该行不参与计算。

示例。

示例1：统计薪资（sal）列非重复值的近似数目。命令示例如下：

select approx_distinct(sal) from emp;

返回结果如下：

+-------------------+
| numdistinctvalues |
+-------------------+
| 12                |
+-------------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，统计薪资（sal）列非重复值的近似数目。命令示例如下：

select deptno, approx_distinct(sal) from emp group by deptno;

返回结果如下：

+------------+-------------------+
| deptno     | numdistinctvalues |
+------------+-------------------+
| 10         | 3                 |
| 20         | 4                 |
| 30         | 5                 |
+------------+-------------------+

ARG_MAX

命令格式。

arg_max(<valueToMaximize>, <valueToReturn>)

命令说明。
返回valueToMaximize最大值对应行的valueToReturn。
参数说明。
- valueToMaximize：必填。可以为任意类型。
- valueToReturn：必填。可以为任意类型。
返回值说明。
返回值类型和valueToReturn类型相同，如果存在多行最大值时，随机返回最大值中的一行对应的值。valueToMaximize值为NULL时，该行不参与计算。

示例。

示例1：返回薪资最高的员工姓名。命令示例如下：

select arg_max(sal, ename) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| KING       |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，并返回各组中薪资最高职工姓名。命令示例如下：

select deptno, arg_max(sal, ename) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | KING       |
| 20         | SCOTT      |
| 30         | BLAKE      |
+------------+------------+

ARG_MIN

命令格式。

arg_min(<valueToMinimize>, <valueToReturn>)

命令说明。
返回valueToMinimize最小值对应行的valueToReturn。
参数说明。
- valueToMinimize：必填。可以为任意类型。
- valueToReturn：必填。可以为任意类型。
返回值说明。
返回值类型和valueToReturn类型相同，如果存在多行最小值时，随机返回最小值其中的一行对应的值。valueToMinimize值为NULL时，该行不参与计算。

示例。

示例1：返回薪资最低的员工姓名。命令示例如下：

select arg_min(sal, ename) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| SMITH      |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，并返回各组中薪资最低职工姓名。命令示例如下：

select deptno, arg_min(sal, ename) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | MILLER     |
| 20         | SMITH      |
| 30         | JAMES      |
+------------+------------+

AVG

命令格式。
```
DECIMAL｜DOUBLE  avg(<colname>)
```
命令说明。
计算平均值。
参数说明。
colname：必填。列值支持所有数据类型，可以转换为DOUBLE类型后参与运算。
返回值说明。
如果colname值为NULL时，该行不参与计算。其他类型返回规则如下：
输入类型
返回类型
TINYINT
DOUBLE
SMALLINT
DOUBLE
INT
DOUBLE
BIGINT
DOUBLE
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL

示例。

示例1：计算所有职工的平均薪资（sal）。命令示例如下：

select avg(sal) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| 2222.0588235294117 |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，计算各部门员工的平均薪资（sal）。命令示例如下：

select deptno, avg(sal) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2916.6666666666665 |
| 20         | 2175.0     |
| 30         | 1566.6666666666667 |
+------------+------------+

BITWISE_AND_AGG

函数声明。
```
bigint bitwise_and_agg(bigint value)
```
命令说明。
对于输入的value，按照bit AND操作计算聚合值。
参数说明。
value：必填。BIGINT类型的值，NULL值不参与计算。
返回值说明。
返回BIGINT类型。

示例。

select id, bitwise_and_agg(v) from
    values (1L, 2L), (1L, 1L), (2L, null), (1L, null) t(id, v) group by id;

返回结果如下。

+------------+------------+
| id         | _c1        |
+------------+------------+
| 1          | 0          |
| 2          | NULL       |
+------------+------------+

BITWISE_OR_AGG

函数声明。
```
bigint bitwise_or_agg(bigint value)
```
命令说明。
对于输入的value，按照bit OR操作计算聚合值。
参数说明。
value：必填。BIGINT类型的值，NULL值不参与计算。
返回值说明。
返回BIGINT类型。

示例。

select id, bitwise_or_agg(v) from
    values (1L, 2L), (1L, 1L), (2L, null), (1L, null) t(id, v) group by id;

返回结果如下。

+------------+------------+
| id         | _c1        |
+------------+------------+
| 1          | 3          |
| 2          | NULL       |
+------------+------------+

COUNT

命令格式。
```
bigint count([distinct|all] <colname>)
```
命令说明。
计算记录数。
参数说明。
- distinct|all：可选。表示在计数时是否去除重复记录，默认为all，即计算全部记录。如果指定distinct，则只计算唯一值数量。
- colname：必填。列值可以为任意类型。colname可以为*，即count(*)，返回所有行数。
返回值说明。
返回BIGINT类型。colname值为NULL时，该行不参与计算。

示例。

示例1：计算所有部门的总职工人数。命令示例如下：

select count(*) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| 17         |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，计算各部门（deptno）的职工人数。命令示例如下：

select deptno, count(*) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 6          |
| 20         | 5          |
| 30         | 6          |
+------------+------------+

示例3：通过distinct去重，计算部门数量。命令示例如下：

select count(distinct deptno) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| 3          |
+------------+

COUNT_IF

命令格式。
```
bigint count_if(boolean <expr>)
```
命令说明。
计算expr值为True的记录数。
参数说明。
expr：必填。BOOLEAN类型表达式。
返回值说明。
返回BIGINT类型。expr值为False或expr中指定的列的值为NULL时，该行不参与计算。

示例。

select count_if(sal > 1000), count_if(sal <=1000) from emp;

返回结果如下：

+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 15         | 2          |
+------------+------------+

MAX

命令格式。
```
max(<colname>)
```
命令说明。
计算最大值。
参数说明。
colname：必填。列值可以为除BOOLEAN外的任意类型。
返回值说明。
返回值的类型与colname类型相同。返回规则如下：
- colname值为NULL时，该行不参与计算。
- colname为BOOLEAN类型时，不允许参与运算。

示例。

示例1：计算所有职工的最高薪资（sal）。命令示例如下：

select max(sal) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| 5000       |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，计算各部门员工的最高薪资（sal）。命令示例如下：

select deptno, max(sal) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 5000       |
| 20         | 3000       |
| 30         | 2850       |
+------------+------------+

MAX_BY

命令格式。

max_by(<valueToReturn>,<valueToMaximize>)

命令说明。
说明
MAX_BY函数与ARG_MAX函数功能相同，只是参数顺序不同，为了兼容开源语法，新增MAX_BY函数以便您适配开源写法。
返回valueToMaximize最大值对应行的valueToReturn。
参数说明。
- valueToMaximize：必填。可以为任意类型。
- valueToReturn：必填。可以为任意类型。
返回值说明。
返回值类型和valueToReturn类型相同，如果存在多行最大值时，随机返回最大值其中的一行对应的值。valueToMaximize值为NULL时，该行不参与计算。

示例。

示例1：返回薪资最高的员工姓名。命令示例如下：

select max_by(ename,sal) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| KING       |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，并返回各组中薪资最高职工的姓名。命令示例如下：

select deptno, max_by(ename,sal) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | KING       |
| 20         | SCOTT      |
| 30         | BLAKE      |
+------------+------------+

MEDIAN

命令格式。

double median(double <colname>)
decimal median(decimal <colname>)

命令说明。
计算中位数。
参数说明。
colname：必填。列值可以为DOUBLE或DECIMAL类型。如果输入为STRING或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。
返回值说明。
如果colname值为NULL时，该行不参与计算。其他类型返回规则如下：
输入类型
返回类型
TINYINT
DOUBLE
SMALLINT
DOUBLE
INT
DOUBLE
BIGINT
DOUBLE
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL

示例。

示例1：计算所有职工的薪资（sal）中位数。命令示例如下：

select median(sal) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| 1600.0     |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，计算各部门员工的薪资（sal）中位数。命令示例如下：

select deptno, median(sal) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2450.0     |
| 20         | 2975.0     |
| 30         | 1375.0     |
+------------+------------+

MIN

命令格式。
```
min(<colname>)
```
命令说明。
计算最小值。
参数说明。
colname：必填。列值可以为除BOOLEAN外的任意类型。
返回值说明。
返回值的类型与colname类型相同。返回规则如下：
- colname值为NULL时，该行不参与计算。
- colname为BOOLEAN类型时，不允许参与运算。

示例。

示例1：计算所有职工的最低薪资（sal）。命令示例如下：

select min(sal) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| 800        |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，计算各部门员工的最低薪资（sal）。命令示例如下：

select deptno, min(sal) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1300       |
| 20         | 800        |
| 30         | 950        |
+------------+------------+

MIN_BY

命令格式。

min_by(<valueToReturn>,<valueToMinimize>)

命令说明。
说明
MIN_BY函数与ARG_MIN函数功能相同，只是参数顺序不同，为了兼容开源语法，新增MIN_BY函数以便您适配开源写法。
返回valueToMinimize最小值对应行的valueToReturn。
参数说明。
- valueToMinimize：必填。可以为任意类型。
- valueToReturn：必填。可以为任意类型。
返回值说明。
返回值类型和valueToReturn类型相同，如果存在多行最小值时，随机返回最小值其中的一行对应的值。valueToMinimize值为NULL时，该行不参与计算。

示例。

示例1：返回薪资最低的员工姓名。命令示例如下：

 select min_by(ename,sal) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| SMITH      |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，并返回各组中薪资最低职工姓名。命令示例如下：

select deptno, min_by(ename,sal) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | MILLER     |
| 20         | SMITH      |
| 30         | JAMES      |
+------------+------------+

STDDEV

命令格式。

double stddev(double <colname>)
decimal stddev(decimal <colname>)

命令说明。
计算总体标准差。
参数说明。
colname：必填。DOUBLE或DECIMAL类型。如果输入为STRING或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。
返回值说明。
如果colname值为NULL时，该行不参与计算。其他类型返回规则如下：
输入类型
返回类型
TINYINT
DOUBLE
SMALLINT
DOUBLE
INT
DOUBLE
BIGINT
DOUBLE
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL

示例。

示例1：计算所有职工的薪资（sal）的总体标准差。命令示例如下：

select stddev(sal) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| 1262.7549932628976 |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，计算各部门员工的薪资（sal）总体标准差。命令示例如下：

select deptno, stddev(sal) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1546.1421524412158 |
| 20         | 1004.7387720198718 |
| 30         | 610.1001739241043 |
+------------+------------+

STDDEV_SAMP

命令格式。

double stddev_samp(double <colname>)
decimal stddev_samp(decimal <colname>)

命令说明。
计算样本标准差。
参数说明。
colname：必填。列值可以为DOUBLE或DECIMAL类型。如果输入为STRING或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。
返回值说明。
如果colname值为NULL时，该行不参与计算。其他类型返回规则如下：
输入类型
返回类型
TINYINT
DOUBLE
SMALLINT
DOUBLE
INT
DOUBLE
BIGINT
DOUBLE
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL

示例。

示例1：计算所有职工的薪资（sal）的样本标准差。命令示例如下：

select stddev_samp(sal) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| 1301.6180541247609 |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，计算各部门员工的薪资（sal）样本标准差。命令示例如下：

select deptno, stddev_samp(sal) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1693.7138680032901 |
| 20         | 1123.3320969330487 |
| 30         | 668.3312551921141 |
+------------+------------+

SUM

命令格式。

DECIMAL｜DOUBLE｜BIGINT  sum(<colname>)

命令说明。
计算汇总值。
参数说明。
colname：必填。列值支持所有数据类型，可以转换为DOUBLE类型后参与运算。列值可以为DOUBLE、DECIMAL或BIGINT类型。如果输入为STRING类型，会隐式转换为DOUBLE类型后参与运算。
返回值说明。
如果colname值为NULL时，该行不参与计算。其他类型返回规则如下：
输入类型
返回类型
TINYINT
BIGINT
SMALLINT
BIGINT
INT
BIGINT
BIGINT
BIGINT
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL

示例。

示例1：计算所有职工的薪资（sal）总和。命令示例如下：

select sum(sal) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| 37775      |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，计算各部门员工的薪资（sal）总和。命令示例如下：

select deptno, sum(sal) from emp group by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 17500      |
| 20         | 10875      |
| 30         | 9400       |
+------------+------------+

WM_CONCAT

命令格式。

string wm_concat(string <separator>, string <colname>)

命令说明。
用指定的separator做分隔符，连接colname中的值。
参数说明。
- separator：必填。STRING类型常量，分隔符。
- colname：必填。STRING类型。如果输入为BIGINT、DOUBLE或DATETIME类型，会隐式转换为STRING类型后参与运算。
返回值说明（使用group by分组，组内返回值不排序）。
返回STRING类型。返回规则如下：
- separator非STRING类型常量时，返回报错。
- colname非STRING、BIGINT、DOUBLE或DATETIME类型时，返回报错。
- colname值为NULL时，该行不会参与计算。
说明
select wm_concat(',', name) from table_name;语句中，如果table_name为空集合，该语句返回NULL值。

示例。

示例1：对所有职工的姓名（ename）进行合并。命令示例如下：

select wm_concat(',', ename) from emp;

返回结果如下：

+------------+
| _c0        |
+------------+
| SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER,JACCKA,WELAN,TEBAGE |
+------------+

示例2：与group by配合使用，对所有职工按照部门（deptno）进行分组，并将同组的职工姓名（ename）进行合并。命令示例如下：

select deptno, wm_concat(',', ename) from emp group by deptno order by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | CLARK,KING,MILLER,JACCKA,WELAN,TEBAGE |
| 20         | SMITH,JONES,SCOTT,ADAMS,FORD |
| 30         | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
+------------+------------+

示例3：与group by配合使用，对所有职工按照部门（deptno）进行分组，并将同组的薪资（sal）去重后进行合并。命令示例如下：

select deptno, wm_concat(distinct ',', sal) from emp group by deptno order by deptno;

返回结果如下：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1300,2450,5000 |
| 20         | 1100,2975,3000,800 |
| 30         | 1250,1500,1600,2850,950 |
+------------+------------+

示例4：与 group by、 order by配合使用，对所有职工按照部门（deptno）进行分组，并将同组的薪资（sal）进行合并排序。命令示例如下：

select deptno, wm_concat(',',sal) within group(order by sal) from emp group by deptno order by deptno;

返回结果如下：

+------------+------------+
|deptno|_c1|
+------------+------------+
|10|1300,1300,2450,2450,5000,5000|
|20|800,1100,2975,3000,3000|
|30|950,1250,1250,1500,1600,2850|
+------------+------------+

输入类型	返回类型
TINYINT	DOUBLE
SMALLINT	DOUBLE
INT	DOUBLE
BIGINT	DOUBLE
FLOAT	DOUBLE
DOUBLE	DOUBLE
DECIMAL	DECIMAL