全部产品
弹性计算 会员服务 网络 安全 移动云 数加·大数据分析及展现 数加·大数据应用 管理与监控 云通信 阿里云办公 培训与认证 更多
存储与CDN 数据库 域名与网站(万网) 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 智能硬件
MaxCompute

聚合函数

更新时间:2018-05-10 13:56:58

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

COUNT

函数声明

  1. bigint count([distinct|all] value)

函数说明

该函数用于计算记录数。

参数说明

  • distinct|all:指明在计数时是否去除重复记录,默认是 all,即计算全部记录,如果指定 distinct,则可以只计算唯一值数量。

  • value:可以为任意类型,当 value 值为 NULL 时,该行不参与计算,value 可以为 *,当 count(*) 时,返回所有行数。

返回值

返回 Bigint 类型。

示例如下

  1. -- 如表tbla有列col1类型为 Bigint
  2. +------+
  3. | COL1 |
  4. +------+
  5. | 1 |
  6. +------+
  7. | 2 |
  8. +------+
  9. | NULL |
  10. +------+
  11. select count(*) from tbla; -- 值为3,
  12. select count(col1) from tbla; -- 值为2

聚合函数可以和 group by 一同使用,例如:假设存在表 test_src,存在如下两列:key string 类型,value double 类型。

  1. -- test_src的数据为
  2. +-----+-------+
  3. | key | value |
  4. +-----+-------+
  5. | a | 2.0 |
  6. +-----+-------+
  7. | a | 4.0 |
  8. +-----+-------+
  9. | b | 1.0 |
  10. +-----+-------+
  11. | b | 3.0 |
  12. +-----+-------+
  13. -- 此时执行如下语句,结果为:
  14. select key, count(value) as count from test_src group by key;
  15. +-----+-------+
  16. | key | count |
  17. +-----+-------+
  18. | a | 2 |
  19. +-----+-------+
  20. | b | 2 |
  21. +-----+-------+
  22. -- 聚合函数将对相同key值得value值做聚合计算。下面介绍的其他聚合函数使用方法均与此例相同,不一一举例。

AVG

函数声明

  1. double avg(double value)
  2. decimal avg(decimal value)

函数说明

该函数用于计算平均值。

参数说明

value:Double 类型或 Decimal 类型,若输入为 String 或 Bigint,会隐式转换到 Double 类型后参与运算,其它类型抛异常。当 value 值为 NULL 时,该行不参与计算。Boolean 类型不允许参与计算。

返回值

若输入 Decimal 类型,返回 Decimal 类型,其他合法输入类型返回 Double 类型。

示例如下

  1. -- 如表tbla有一列value,类型为 Bigint
  2. +-------+
  3. | value |
  4. +-------+
  5. | 1 |
  6. | 2 |
  7. | NULL |
  8. +-------+
  9. -- 则对该列计算avg结果为(1+2)/2=1.5
  10. select avg(value) as avg from tbla;
  11. +------+
  12. | avg |
  13. +------+
  14. | 1.5 |
  15. +------+

MAX

函数声明

  1. max(value)

函数说明

该函数用于计算最大值。

参数说明

value:可以为任意类型,当列中的值为 NULL 时,该行不参与计算。Boolean 类型不允许参与运算。

返回值

返回值的类型与 value 类型相同。

示例如下

  1. -- 如表tbla有一列col1,类型为 Bigint
  2. +------+
  3. | col1 |
  4. +------+
  5. | 1 |
  6. +------+
  7. | 2 |
  8. +------+
  9. | NULL |
  10. +------+
  11. select max(value) from tbla; -- 返回值为2

MIN

函数声明

  1. MIN(value)

函数说明

该函数用于计算最小值。

参数说明

value:可以为任意类型,当列中的值为 NULL 时,该行不参与计算。Boolean 类型不允许参与计算。

示例如下

  1. -- 如表tbla有一列value,类型为 Bigint
  2. +------+
  3. | value|
  4. +------+
  5. | 1 |
  6. +------+
  7. | 2 |
  8. +------+
  9. | NULL |
  10. +------+
  11. select min(value) from tbla; -- 返回值为1

MEDIAN

函数声明

  1. double median(double number)
  2. decimal median(decimal number)

函数说明

该函数用于计算中位数。

参数说明

number:Double 类型或者 Decimal 类型。若输入为 String 类型或 Bigint 类型,会隐式转换到 Double 类型后参与运算,其他类型报错。

返回值

返回 Double 类型或者 Decimal 类型。

示例如下

  1. -- 如表tbla有一列value,类型为 Bigint
  2. +------+
  3. | value|
  4. +------+
  5. | 1 |
  6. +------+
  7. | 2 |
  8. +------+
  9. | 3 |
  10. +------+
  11. | 4 |
  12. +------+
  13. | 5 |
  14. +------+
  15. select MEDIAN(value) from tbla; -- 返回值为 3.0

STDDEV

函数声明

  1. double stddev(double number)
  2. decimal stddev(decimal number)

函数说明

该函数用于计算总体标准差。

参数说明

number:Double 类型或者 Decimal 类型。若输入为 String 类型或 Bigint 类型,会隐式转换到 Double 类型后参与运算,其他类型时报错。

返回值

返回 Double 类型或者 Decimal 类型。

示例如下

  1. -- 如表tbla有一列value,类型为 Bigint
  2. +------+
  3. | value|
  4. +------+
  5. | 1 |
  6. +------+
  7. | 2 |
  8. +------+
  9. | 3 |
  10. +------+
  11. | 4 |
  12. +------+
  13. | 5 |
  14. +------+
  15. select STDDEV(value) from tbla; -- 返回值为 1.4142135623730951

STDDEV_SAMP

函数声明

  1. double stddev_samp(double number)
  2. decimal stddev_samp(decimal number)

函数说明

该函数用于计算样本标准差。

参数说明

number:Double 类型或者 Decimal 类型。若输入为string类型或 Bigint 类型会隐式转换到double类型后参与运算,其他类型时报错。

返回值

返回 Double 类型或者 Decimal 类型。

示例如下

  1. -- 如表tbla有一列value,类型为 Bigint
  2. +------+
  3. | value|
  4. +------+
  5. | 1 |
  6. +------+
  7. | 2 |
  8. +------+
  9. | 3 |
  10. +------+
  11. | 4 |
  12. +------+
  13. | 5 |
  14. +------+
  15. select STDDEV_SAMP(value) from tbla; -- 返回值为 1.5811388300841898

SUM

函数声明

  1. sum(value)

函数说明

该函数用于计算汇总值。

参数说明

value:Double、Decimal 或 Bigint 类型,若输入为 String 会隐式转换到 Double 类型后参与运算,当列中的值为 NULL 时,该行不参与计算。Boolean 类型不允许参与计算。

返回值

输入为 Bigint 时,返回 Bigint,输入为 Double 或 String 时,返回 Double 类型。

示例如下

  1. -- 如表tbla有一列value,类型为 Bigint
  2. +------+
  3. | value|
  4. +------+
  5. | 1 |
  6. +------+
  7. | 2 |
  8. +------+
  9. | NULL |
  10. +------+
  11. select sum(value) from tbla; -- 返回值为3

WM_CONCAT

函数声明

  1. 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

函数声明

  1. ARRAY collect_list(col)

函数说明

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

参数说明

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

返回值

返回 ARRAY 类型。

注意:请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

COLLECT_SET

函数声明

  1. ARRAY collect_set(col)

函数说明

该函数在给定 group 内,将 col 指定的表达式聚合为一个无重复元素的集合数组。

参数说明

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

返回值

返回 ARRAY 类型。

注意:请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

VARIANCE/VAR_POP

函数声明

  1. DOUBLE variance(col)
  2. DOUBLE var_pop(col)

函数说明

该函数用于求指定数字列的方差。

参数说明

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

返回值

返回double类型。

示例如下

test表的c1列数据如下所示:

  1. +------------+
  2. | c1 |
  3. +------------+
  4. | 8 |
  5. | 9 |
  6. | 10 |
  7. | 11 |
  8. +------------+

执行下述语句求该表c1列的方差。

  1. select variance(c1) from test;
  2. --或
  3. select var_pop(c1) from test;
  4. --执行结果如下:
  5. +------------+
  6. | _c0 |
  7. +------------+
  8. | 1.25 |
  9. +------------+

注意:若涉及新数据类型,请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

VAR_SAMP

函数声明

  1. DOUBLE var_samp(col)

函数说明

该函数用于求指定数字列的样本方差。

参数说明

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

返回值

返回double类型。

示例如下

test表的c1列数据如下所示:

  1. +------------+
  2. | c1 |
  3. +------------+
  4. | 8 |
  5. | 9 |
  6. | 10 |
  7. | 11 |
  8. +------------+

执行下述语句求该表c1列的方差。

  1. select var_samp(c1) from test;
  2. --执行结果如下:
  3. +------------+
  4. | _c0 |
  5. +------------+
  6. | 1.6666666666666667 |
  7. +------------+

注意:若涉及新数据类型,请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

COVAR_POP

函数声明

  1. DOUBLE covar_pop(col1, col2)

函数说明

该函数用于求指定两个数字列的总体协方差。

参数说明

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

示例如下

test表(c1 bigint,c2 bigint)数据如下所示:

  1. +------------+------------+
  2. | c1 | c2 |
  3. +------------+------------+
  4. | 3 | 2 |
  5. | 14 | 5 |
  6. | 50 | 14 |
  7. | 26 | 75 |
  8. +------------+------------+

执行下述语句求c1,c2的总体协方差。

  1. select covar_pop(c1,c2) from test;
  2. --结果如下:
  3. +------------+
  4. | _c0 |
  5. +------------+
  6. | 123.49999999999997|
  7. +------------+

注意:若涉及新数据类型,请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

COVAR_SAMP

函数声明

  1. DOUBLE covar_samp(col1, col2)

函数说明

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

参数说明

col1/col2:数字类型列,其他类型返回null。

示例如下

test表(c1 bigint,c2 bigint)数据如下所示:

  1. +------------+------------+
  2. | c1 | c2 |
  3. +------------+------------+
  4. | 3 | 2 |
  5. | 14 | 5 |
  6. | 50 | 14 |
  7. | 26 | 75 |
  8. +------------+------------+

执行下述语句求c1,c2的样本协方差。

  1. select covar_samp(c1,c2) from test;
  2. --结果如下:
  3. +------------+
  4. | _c0 |
  5. +------------+
  6. | 164.66666666666663|
  7. +------------+

注意:若涉及新数据类型,请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

PERCENTILE

函数声明

  1. DOUBLE percentile(BIGINT col, p)
  2. array<double> percentile(BIGINT col, array(p1 [, p2]...))

函数说明

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

注意:只有整数值才能计算出真正的百分位数。

参数说明

  • col:bigint类型

  • p:必须是大于等0小于等于1

示例如下

test表的c1列数据如下所示:

  1. +------------+
  2. | c1 |
  3. +------------+
  4. | 8 |
  5. | 9 |
  6. | 10 |
  7. | 11 |
  8. +------------+

执行下述语句求该表c1列的第P位百分数。

  1. select percentile(c1,0),percentile(c1,0.3),percentile(c1,0.5),percentile(c1,1) from var_test;
  2. --执行结果如下:
  3. +------------+------------+------------+------------+
  4. | _c0 | _c1 | _c2 | _c3 |
  5. +------------+------------+------------+------------+
  6. | 8.0 | 8.9 | 9.5 | 11.0 |
  7. +------------+------------+------------+------------+
  8. --执行
  9. select percentile(c1,array(0,0.3,0.5,1))from var_test;
  10. 结果如下:
  11. +------+
  12. | _c0 |
  13. +------+
  14. | [8, 8.9, 9.5, 11] |--返回array<double>的话看起来有问题,不应该是8.011.0么?
  15. +------+
本文导读目录