聚合函数 聚合函数

发布时间:2020-07-08 09:01:22   来源:网络 关键词:聚合函数

聚合函数


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

    用于计算中位数。

  • 参数说明

    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)时,需要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)
  • 命令说明

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

  • 参数说明

    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)时,需要用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] |+------+

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]。

CC攻击防御
抗ddos
猜你喜欢