聚合(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类型。

说明 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级别开启数据类型时的注意事项,请参见数据类型版本说明

VARIANCE/VAR_POP

  • 命令格式
    DOUBLE VARIANCE(col)
    DOUBLE VAR_POP(col)
  • 命令说明

    用于计算指定数字列的方差。

  • 参数说明

    col:数据类型为数值的列。如果参数为其他类型的列则返回NULL。

  • 返回值说明

    返回DOUBLE类型。

  • 示例
    test表的c1列数据如下。
    +------------+
    | c1 |
    +------------+
    | 8 |
    | 9 |
    | 10 |
    | 11 |
    +------------+
    执行下述语句计算该表c1列的方差。
    SELECT VARIANCE(c1) FROM test;
    --或
    SELECT VAR_POP(c1) FROM test;
    --执行结果如下。
    +------------+
    | _c0 |
    +------------+
    | 1.25 |
    +------------+
说明 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级别开启数据类型时的注意事项,请参见数据类型版本说明

VAR_SAMP

  • 命令格式
    DOUBLE VAR_SAMP(col)
  • 命令说明

    用于计算指定数字列的样本方差。

  • 参数说明

    col:数值类型列。其他类型返回NULL。

  • 返回值说明

    返回DOUBLE类型。

  • 示例
    test表的c1列数据如下。
    +------------+
    | c1 |
    +------------+
    | 8 |
    | 9 |
    | 10 |
    | 11 |
    +------------+
    执行如下语句计算该表c1列的方差。
    SELECT VAR_SAMP(c1) FROM test;
    --执行结果如下。
    +------------+
    | _c0 |
    +------------+
    | 1.6666666666666667 |
    +------------+
说明 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级别开启数据类型时的注意事项,请参见数据类型版本说明

COVAR_POP

  • 命令格式
    DOUBLE COVAR_POP(col1, col2)
  • 命令说明

    用于计算指定两个数字列的总体协方差。

  • 参数说明

    col1,col2:数值类型列。其他类型返回NULL。

  • 示例
    test表(c1为BIGINT类型,c2为BIGINT类型)数据如下。
    +------------+------------+
    | c1 | c2 |
    +------------+------------+
    | 3 | 2 |
    | 14 | 5 |
    | 50 | 14 |
    | 26 | 75 |
    +------------+------------+
    执行如下语句求c1c2的总体协方差。
    SELECT COVAR_POP(c1,c2) FROM test;
    --结果如下。
    +------------+
    | _c0 |
    +------------+
    | 123.49999999999997|
    +------------+
说明 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级别开启数据类型时的注意事项,请参见数据类型版本说明

COVAR_SAMP

  • 命令格式
    DOUBLE COVAR_SAMP(col1, col2)
  • 命令说明

    用于计算指定两个数字列的样本协方差。

  • 参数说明

    col1,col2:数值类型列。其他类型返回NULL。

  • 示例
    test表(c1为BIGINT类型,c2为BIGINT类型)数据如下。
    +------------+------------+
    | c1 | c2 |
    +------------+------------+
    | 3 | 2 |
    | 14 | 5 |
    | 50 | 14 |
    | 26 | 75 |
    +------------+------------+
    执行如下语句计算c1c2的样本协方差。
    SELECT COVAR_SAMP(c1,c2) FROM test;
    --结果如下。
    +------------+
    | _c0 |
    +------------+
    | 164.66666666666663|
    +------------+
说明 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级别开启数据类型时的注意事项,请参见数据类型版本说明

PERCENTILE

  • 命令格式
    DOUBLE PERCENTILE(BIGINT col, p)
    ARRAY PERCENTILE(BIGINT col, ARRAY(p1 [, p2]...))
  • 命令说明

    返回指定列精确的第p位百分数。p必须在0和1之间。

  • 参数说明
    • col:BIGINT类型的列名称。
    • p:需要精确的百分位数。取值必须在0和1之间。
  • 示例
    test表的c1列数据如下。
    +------------+
    | c1 |
    +------------+
    | 8 |
    | 9 |
    | 10 |
    | 11 |
    +------------+
    执行如下语句计算该表c1列的第p位百分数。
    SELECT PERCENTILE(c1,0),PERCENTILE(c1,0.3),PERCENTILE(c1,0.5),PERCENTILE(c1,1) FROM var_test;
    --执行结果如下。
    +------------+------------+------------+------------+
    | _c0 | _c1 | _c2 | _c3 |
    +------------+------------+------------+------------+
    | 8.0 | 8.9 | 9.5 | 11.0 |
    +------------+------------+------------+------------+
    SELECT PERCENTILE(c1,ARRAY(0,0.3,0.5,1))FROM var_test;
    --执行结果如下。
    +------+
    | _c0 |
    +------+
    | [8, 8.9, 9.5, 11] |
    +------+

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对应的值。

  • 参数说明
    • 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       |
    +------------+------------+