聚合函数 - 开发| 阿里云

COUNT

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

示例

--例如表tbla，列col1类型为BIGINT。
+------+
| COL1 |
+------+
| 1 |
+------+
| 2 |
+------+
| NULL |
+------+
SELECT COUNT(*) FROM tbla; --值为3。
SELECT COUNT(col1) FROM tbla; --值为2。

聚合函数可以和GROUP BY一起使用。例如表test_src存在两列数据，key为STRING类型，value为DOUBLE类型。

-- test_src的数据如下。
+-----+-------+
| key | value |
+-----+-------+
| a | 2.0 |
+-----+-------+
| a | 4.0 |
+-----+-------+
| b | 1.0 |
+-----+-------+
| b | 3.0 |
+-----+-------+
-- 此时执行如下语句，结果如下。
SELECT key, COUNT(value) AS count FROM test_src GROUP BY key;
+-----+-------+
| key | count |
+-----+-------+
| a | 2 |
+-----+-------+
| b | 2 |
+-----+-------+
-- 聚合函数将对相同key值的value值做聚合计算。下面介绍的其他聚合函数使用方法均与此例相同，不一一举例。

AVG

命令格式

DOUBLE AVG(DOUBLE value)
DECIMAL AVG(DECIMAL value)

命令说明
用于计算平均值。
参数说明
value：DOUBLE或DECIMAL类型。如果输入为STRING或BIGINT类型，会隐式转换为DOUBLE类型后参与运算，其它类型返回异常。当value值为NULL时，该行不参与计算。BOOLEAN类型不允许参与计算。
返回值
如果输入DECIMAL类型，则返回DECIMAL类型。输入其他合法的类型时，会返回DOUBLE类型。

示例

-- 例如表tbla，列value类型为BIGINT。
+-------+
| value |
+-------+
| 1 |
| 2 |
| NULL |
+-------+
-- 则对该列计算avg结果为(1+2)/2=1.5。
SELECT AVG(value) AS avg FROM tbla;
+------+
| avg |
+------+
| 1.5 |
+------+

MAX

命令格式
```
MAX(value)
```
命令说明
用于计算最大值。
参数说明
value：可以为任意类型。当列中的值为NULL时，该行不参与计算。BOOLEAN类型不允许参与运算。
返回值说明
返回值的类型与value类型相同。

示例

--例如表tbla，列col1类型为BIGINT。
+------+
| col1 |
+------+
| 1 |
+------+
| 2 |
+------+
| NULL |
+------+
SELECT MAX(value) FROM tbla; --返回值为2。

MIN

命令格式
```
MIN(value)
```
命令说明
用于计算最小值。
参数说明
value：可以为任意类型。当列中的值为NULL时，该行不参与计算。BOOLEAN类型不允许参与计算。

示例

--例如表tbla，列value类型为BIGINT。
+------+
| value|
+------+
| 1 |
+------+
| 2 |
+------+
| NULL |
+------+
SELECT MIN(value) FROM tbla; --返回值为1。

MEDIAN

命令格式

DOUBLE MEDIAN(DOUBLE number)
DECIMAL MEDIAN(DECIMAL number)

命令说明
用于计算中位数。
参数说明
value：DOUBLE或DECIMAL类型。如果输入为STRING或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。输入其它类型会返回异常。
返回值说明
返回DOUBLE或DECIMAL类型。

示例

--例如表tbla，列value类型为BIGINT。
+------+
| value|
+------+
| 1 |
+------+
| 2 |
+------+
| 3 |
+------+
| 4 |
+------+
| 5 |
+------+
SELECT MEDIAN(value) FROM tbla; --返回值为3.0。

STDDEV

命令格式

DOUBLE STDDEV(DOUBLE number)
DECIMAL STDDEV(DECIMAL number)

命令说明
用于计算总体标准差。
参数说明
value：DOUBLE或DECIMAL类型。如果输入为STRING或BIGINT类型，会隐式转换成DOUBLE类型后参与运算。输入其他类型会返回异常。
返回值说明
返回DOUBLE或DECIMAL类型。

示例

--例如表tbla，列value类型为BIGINT。
+------+
| value|
+------+
| 1 |
+------+
| 2 |
+------+
| 3 |
+------+
| 4 |
+------+
| 5 |
+------+
SELECT STDDEV(value) FROM tbla; --返回值为1.4142135623730951。

STDDEV_SAMP

命令格式

DOUBLE STDDEV_SAMP(DOUBLE number)
DECIMAL STDDEV_SAMP(DECIMAL number)

命令说明
用于计算样本标准差。
参数说明
value：DOUBLE或DECIMAL类型。如果输入为STRING或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。输入其它类型会返回异常。
返回值说明
返回DOUBLE或DECIMAL类型。

示例

--例如表tbla，列value类型为BIGINT。
+------+
| value|
+------+
| 1 |
+------+
| 2 |
+------+
| 3 |
+------+
| 4 |
+------+
| 5 |
+------+
SELECT STDDEV_SAMP(value) FROM tbla; --返回值为1.5811388300841898。

SUM

命令格式
```
SUM(value)
```
命令说明
用于计算汇总值。
参数说明
value：DOUBLE、DECIMAL或BIGINT类型。如果输入为STRING类型，会隐式转换为DOUBLE类型后参与运算。当列中的值为NULL时，该行不参与计算。BOOLEAN类型不允许参与计算。
返回值说明
输入BIGINT类型时，返回BIGINT类型。输入为DOUBLE或STRING类型时，返回DOUBLE类型。

示例

--例如表tbla，列value类型为BIGINT。
+------+
| value|
+------+
| 1 |
+------+
| 2 |
+------+
| NULL |
+------+
SELECT SUM(value) FROM tbla; --返回值为3。

WM_CONCAT

命令格式

STRING WM_CONCAT(STRING separator, STRING str)

命令说明
用指定的separator做分隔符，链接str中的值。
参数说明
- separator：STRING类型常量，分隔符。其他类型或非常量将返回异常。
- str：STRING类型。如果输入为BIGINT、DOUBLE或DATETIME类型，会隐式转换为STRING类型后参与运算。输入其它类型会返回异常。
返回值说明
返回STRING类型。

说明 SELECT WM_CONCAT(',', name) FROM test_src;语句中，如果test_src为空集合，此条语句返回NULL值。

示例：对表进行分组排序后合并。

--创建表test。
CREATE TABLE test(id int , alphabet string);
--给表test中插入数据。
INSERT INTO test VALUES (1,'a'),(1,'b'),(1,'c'),(2,'D'),(2,'E'),(2,'F');
--对表test按照id列进行分组排序，并将同组的内容进行合并。
SELECT id,WM_CONCAT('',alphabet) FROM test GROUP BY id ORDER BY id LIMIT 100;
+------------+------------+
| id         | _c1        |
+------------+------------+
| 1          | abc        |
| 2          | DEF        |
+------------+------------+

COLLECT_LIST

命令格式
```
ARRAY COLLECT_LIST(col)
```
命令说明
在给定Group内，将col指定的表达式聚合为一个数组。
参数说明
col：表的某列，可为任意数据类型。
返回值
返回ARRAY类型。

说明 MaxCompute SQL中使用到新数据类型（TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP和BINARY）时，需要执行如下命令打开新数据类型开关：

Session级别：需要在SQL语句前加set odps.sql.type.system.odps2=true;语句，并与SQL语句一起提交执行。
Project级别：支持对Project级别打开新数据类型。Project Owner可根据需要对Project进行设置，命令为：
```
setproject odps.sql.type.system.odps2=true;
```
对setproject的详细说明请参见项目空间操作。关于Project级别开启数据类型时的注意事项，请参见数据类型版本说明。

COLLECT_SET

命令格式
```
ARRAY COLLECT_SET(col)
```
命令说明
在给定Group内，将col指定的表达式聚合为一个无重复元素的集合数组。
参数说明
col：表的某列，可为任意数据类型。
返回值说明
返回ARRAY类型。

说明 MaxCompute SQL中使用到新数据类型（TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP和BINARY）时，需要执行如下命令打开新数据类型开关：

Session级别：需要在SQL语句前加set odps.sql.type.system.odps2=true;语句，并与SQL语句一起提交执行。
Project级别：支持对Project级别打开新数据类型。Project Owner可根据需要对Project进行设置，命令为：
```
setproject odps.sql.type.system.odps2=true;
```
对setproject的详细说明请参见项目空间操作。关于Project级别开启数据类型时的注意事项，请参见数据类型版本说明。

NUMERIC_HISTOGRAM

命令格式

MAP<DOUBLE, DOUBLE> NUMERIC_HISTOGRAM(BIGINT buckets, DOUBLE value)

命令说明
统计指定列的近似直方图。
参数说明
- buckets：BIGINT类型，表示返回的近似直方图列的最大个数。
- Value：DOUBLE类型，需要统计近似直方图的数据列。
返回值说明
MAP<DOUBLE,DOUBLE>类型，返回值中key是近似直方图的X轴坐标点，value是直方图一列的近似高度。

PERCENTILE_APPROX

命令格式

DOUBLE PERCENTILE_APPROX(DOUBLE col, p [, B])) 
ARRAY<DOUBLE>  PERCENTILE_APPROX(DOUBLE col, ARRAY(p1 [, p2]...) [, B])

命令说明
计算近似百分位数，适用于大数据量。返回指定列col上指定百分比p对应的值。PERCENTILE_APPROX是从编号1开始计算，例如某列数据为100、200、300，列数据的编号顺序为1、2、3，计算该列的0.5百分位点，PERCENTILE_APPROX结果是3×0.5=1.5，编号为1和2的中间值，即150。
参数说明
- p：百分比，取值为[0.0,1.0]。可以指定返回单个百分比值，也可以指定返回一个百分比数组。
- B：精度参数。精度越高产生的近似值误差越小。如果不设置该参数，默认值为10000。当col中值的数目小于B时，将给出精确的百分位值。
返回值说明
percentile_approx(double col, p [, B])) 返回单个百分比值。percentile_approx(double col, array(p1 [, p2]...) [, B])返回一个百分比数组。

示例

SELECT PERCENTILE_APPROX(10.0, 0.5, 100); --返回10.0。
SELECT PERCENTILE_APPROX(10.0, array(0.5, 0.4, 0.1), 100); --返回[10.0,10.0,10.0]。

APPROX_DISTINCT

命令格式
```
APPROX_DISTINCT(value)
```
命令说明
返回输入的非重复值的近似数目。
参数说明
value：需要统计去重的输入数据。
返回值说明
返回BIGINT类型，如果所有输入值都为空，则返回0。此函数会产生5%的标准误差。

示例

SELECT key, APPROX_DISTINCT(value) 
FROM  VALUES
    (1, 99),
    (1, 100),
    (2, 100),
    (2, 100),
    (3, NULL) 
AS t(key,value) 
GROUP BY key;
--执行结果如下。
+------------+------------+
| key        | _c1        |
+------------+------------+
| 1          | 2          |
| 2          | 1          |
| 3          | 1          |
+------------+------------+

ANY_VALUE

命令格式
```
ANY_VALUE(value)
```
命令说明
在范围内任选一个值返回。
参数说明
value：可以为任意类型。当值为NULL时，该行不参与计算。
返回值说明
返回值类型和输入类型相同。

示例

SELECT key, ANY_VALUE(value) 
FROM VALUES 
    (1, 'value1'),
    (1, 'value2'),
    (2, 'value3'),
    (2, NULL),
    (3, NULL) 
AS t(key, value) 
GROUP BY key;
--执行结果如下。
+------------+------------+
| key        | _c1        |
+------------+------------+
| 1          | value1     |
| 2          | value3     |
| 3          | NULL       |
+------------+------------+

ARG_MAX

命令格式
```
ARG_MAX(valueToMaximize, valueToReturn)
```
命令说明
返回valueToMaximize最大值对应行的valueToReturn。
参数说明
- valueToMaximize：可以为任意类型。
- valueToReturn：可以为任意类型。
返回值说明
返回值类型和valueToReturn的类型相同。

示例

SELECT key, ARG_MAX(comp, value) 
FROM VALUES 
    (1, 99,   'value1'),
    (1, 100,  'value2'),
    (2, 99,   'value3'),
    (2, 100,   NULL),
    (3, NULL, 'value4') 
AS t(key,comp, value) 
GROUP BY key;

--执行结果如下。
+------------+------------+
| key        | _c1        |
+------------+------------+
| 1          | value2     |
| 2          | NULL       |
| 3          | NULL       |
+------------+------------+

ARG_MIN

命令格式
```
ARG_MIN(valueToMinimize, valueToReturn)
```
命令说明
返回valueToMinimize最小值对应行的valueToReturn。
参数说明
- valueToMinimize：可以为任意类型。
- valueToReturn：可以为任意类型。
返回值说明
返回值类型和valueToReturn的类型相同。

示例

SELECT key, ARG_MIN(comp, value) 
FROM VALUES 
  (1, 99, 'value1'),
  (1, 100,  'value2'),
  (2, 99, 'value3'),
  (2, 100, NULL),
  (3, NULL, 'value4') 
AS t(key,comp, value) 
GROUP BY key;

--执行结果如下。
+------------+------------+
| key        | _c1        |
+------------+------------+
| 1          | value1     |
| 2          | value3     |
| 3          | NULL       |
+------------+------------+