聚合(Aggregate)函数,其输入与输出是多对一的关系,即将多条输入记录聚合成一条输出值。可以与SQL中的group by语句联用。

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)
  • 命令说明

    用于计算中位数。

  • 参数说明

    number: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)
  • 命令说明

    用于计算总体标准差。

  • 参数说明

    number: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)
  • 命令说明

    用于计算样本标准差。

  • 参数说明

    number: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)
  • 命令说明

    用指定的spearator做分隔符,链接str中的值。

  • 参数说明
    • separator:STRING类型常量,分隔符。其他类型或非常量将引发异常。
    • str:STRING类型。如果输入为BIGINT,DOUBLE或者DATETIME类型,会隐式转换为STRING后参与运算。输入其他类型时会发生报错。
  • 返回值说明

    返回STRING类型。

说明 语句select wm_concat(',', name) from test_src;中,如果test_src为空集合,此条语句返回NULL值。

COLLECT_LIST

  • 命令格式
    ARRAY collect_list(col)
  • 命令说明

    在给定Group内,将col指定的表达式聚合为一个数组。

  • 参数说明

    col:表的某列,可为任意数据类型。

  • 返回值

    返回ARRAY类型。

说明 目前MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句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)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句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)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句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)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句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)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句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)
  • 命令说明

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

  • 参数说明

    col1col2:数字类型列。其他类型返回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)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句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] |
    +------+