聚合(Aggregate)函数的输入与输出是多对一的关系,即将多条输入记录聚合成一条输出值。可以与SQL中的GROUP BY语句联用。本文为您介绍MaxCompute支持的各个聚合函数的功能,并提供命令格式、参数说明、返回值说明和参考示例。
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类型。
COLLECT_SET
- 命令格式
ARRAY COLLECT_SET(col)
- 命令说明
在给定Group内,将col指定的表达式聚合为一个无重复元素的集合数组。
- 参数说明
col:表的某列,可为任意数据类型。
- 返回值说明
返回ARRAY类型。
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 | +------------+------------+