全部产品
阿里云办公

聚合函数

更新时间:2017-10-17 17:20:31

聚合函数主要应用于一组数据计算出一个结果。

除了 count(), count_if(), max_by(), min_by() 和 approx_distinct() 之外, 所有这些聚合函数均会忽略null值并且在没有输入数据或者所有数据 均是null值的情况下返回空结果。比如,sum() 会返回null而不是0, 以及 avg() 不会包含null值计数。 coalesce 函数会把null值转换成0。

常规聚合函数

arbitrary(x) → [类型与输入参数相同]

返回 x 的任意非null值。

array_agg(x) → array<[类型和输入参数相同]>

返回以输入参数 x 为元素的数组。

avg(x) → double

返回所有输入值的平均数(算数平均数)。

bool_and(boolean) → boolean

只有所有参数均为 TRUE 则返回 TRUE ,否则返回 FALSE 。

bool_or(boolean) → boolean

任何一个参数为 TRUE ,则返回 TRUE , 否则返回 FALSE 。

checksum(x) → varbinary

返回不受给定参数值顺序影响的校验值。

count(*) → bigint

返回输入数据行的统计个数。

count(x) → bigint

返回非null值的输入参数个数。

count_if(x) → bigint

返回输入参数中 TRUE 的个数。 这个函数和 count(CASE WHEN x THEN 1 END) 等同。

every(boolean) → boolean

这个函数是 bool_and() 的别名。

geometric_mean(x) → double

返回所有输入参数的几何平均值。

max_by(x, y) → [与x类型相同]

返回 x 与 y 的全部关联中,y 最大值所关联的第一个 x 值。

max_by(x, y, n) → array<[与x类型相同]>

x 与 y 的全部关联中, 以 y 降序排列前 n 个最大值所关联的 x 值中, 返回前 n 个值

min_by(x, y) → [与x类型相同]

返回 x 与 y 的全部关联中,y 最小值所关联的第一个 x 值。

min_by(x, y, n) → array<[与x类型相同]>

x 与 y 的全部关联中, 以 y 升序排列前 n 个值所关联的 x 值中, 返回前 n 个值

max(x) → [与输入类型相同]

返回输入参数中最大的值。

max(x, n) → array<[与x类型相同]>

返回所有参数 x 中前 n 大的值.

min(x) → [与输入类型相同]

返回所有输入参数中最小的值。

min(x, n) → array<[与x类型相同]>

返回所有输入参数 x 中,前 n 小的值。

sum(x) → [和输入类型相同]

返回所有输入参数的和。

位聚合函数

bitwise_and_agg(x) → bigint

返回所有输入值二进制与的结果,以二进制补码表示。

bitwise_or_agg(x) → bigint

返回所有输入值二进制或的结果,以二进制补码表示。

映射表聚合函数

histogram(x) → map<K,bigint>

返回一个包含输入参数出现次数的映射表。

map_agg(key, value) → map<K,V>

返回一个根据输入 key 和 value 对构造的映射表。

map_union(x<K, V>)map<K,V>

返回所有输入映射表的联合。如果一个键同时出现在多个输入映射表中,结果中对应键值会取任意输入映射表中的一个。

multimap_agg(key, value) → map<K,array<V>>

返回一个由输入参数 key 和 value 对组成的映射表。每个键可以对应多个值。

近似计算聚合函数

approx_distinct(x) → bigint

返回输入参数中不重复值的近似个数。 这个函数提供 count(DISTINCT x) 这个方法的近似计算。 如果所有输入参数均为null,则返回0。

这个函数会产生2.3%的标准误差。这个误差是基于所有可能集合上的标准误差的正常分布。 它并不能保证在特定集合上的误差上限。

approx_distinct(x, e) → bigint

返回输入参数中不重复值的近似个数。 这个函数提供 count(DISTINCT x) 这个方法的近似计算。 如果所有输入参数均为null,则返回0。

这个函数会产生一个标准误差不会超过 e 的结果。这个误差是基于所有可能集合上的标准误差的正常分布。 它并不能保证在特定集合上的误差上限。当前这个函数的实现需要 e 值在范围[0.01150, 0.26000]之间。

approx_percentile(x, percentage) → [与x类型相同]

返回输入值 x 中处于 percentage 占比的近似百分位数值。 percentage 的数值范围必须在0到1之间且 对每个输入行均是常量。如近似计算中位数为 approx_percentile(x, 0.5) 。

approx_percentile(x, percentages) → array<[与x类型相同]>

返回输入值 x 中处于每个特定百分占比的近似百分位值。其中 percentages 数组中每个元素值范围必须 在0到1且数组对每个输入行必须是常数。

approx_percentile(x, w, percentage) → [与x类型相同]

按照百分比 percentage 的每项权重 w 返回 x 的所有输入值的近似百分位数。权重必须至少有一个 整型数值。 它实际上是百分位数集合中的值 x 的复制计数。 percentage 的值必须在0和1之间且对所有 输入行必须是常数。

approx_percentile(x, w, percentage, accuracy) → [与x类型相同]

按照百分比 percentage 的每项权重 w 计算所有 x 的输入值的近似百分位数,以精度为 accuracy 。 权重必须至少有一个整型数值。它实际上是百分位数集合中的值 x 的复制计数。 percentage 的值必须在0和1之 间且对所有输入行必须是常数。 accuracy 必须要大于0并且小于1并且对于所有输入行为常量。

approx_percentile(x, w, percentages) → array<[与x类型相同]>

按照百分比 percentage 的每个项目权重 w 返回 x 的所有输入值的近似称重百分位数。 权重必须至少有一个整型数值。它实际上是百分位数集合中的值 x 的复制计数。 percentages 的每个值 值必须在0和1之间且对所有输入行必须是常数。

numeric_histogram(buckets, value, weight) → map<double, double>

按照 buckets 桶的数量,为所有的 value 计算近似直方图, 每一项的权重使用 weight 。本算法大体基于:

Yael Ben-Haim and Elad Tom-Tov, "A streaming parallel decision tree algorithm", J. Machine Learning Research 11 (2010), pp. 849--872.

buckets 必须是 bigint 类型. value 和 weight 必须是数值类型。

numeric_histogram(buckets, value) → map<double, double>按照 buckets 桶的数量,为所有的 value 计算近似直方图,本函数与 numeric_histogram() 相同,只是 weight 为1.

统计聚合函数

corr(y, x) → double

返回输入值的相关系数。

covar_pop(y, x) → double

返回输入值的总体协方差。

covar_samp(y, x) → double

返回输入值的样本协方差。

regr_intercept(y, x) → double

返回输入值的线性回归截距。 y 是因变量。 x 是自变量。

regr_slope(y, x) → double

返回输入值的线性回归斜率。 y 是因变量。 x 是自变量。

stddev(x) → double

这个函数是 stddev_samp() 的别名函数。

stddev_pop(x) → double

返回所有输入值的总体标准偏差。

stddev_samp(x) → double

返回所有输入值的样本标准偏差。

variance(x) → double

这个函数是 var_samp() 的别名函数

var_pop(x) → double

返回所有输入值的总体方差。

var_samp(x) → double

返回所有输入值的样本方差。

特色聚合函数

UDF_SYS_COUNT_COLUMN

  1. 作用:用于做多group by的聚合, 可以将多个group by的语句合并成多个UDF,写到一条sql中

  2. 格式:UDF_SYS_COUNT_COLUMN(columnName, columnName2…), 参数必须是列名

  3. 返回:一个json的字符串列,类似: {“0”:3331656,”2”:3338142,”1”:3330202}

  4. 实例:

a. select UDF_SYS_COUNT_COLUMN(c1) from table 等价于select count(*) from table group by c1

b. select UDF_SYS_COUNT_COLUMN(c1,c2) from table 等价于select count(*) from table group by c1,c2

c. select UDF_SYS_COUNT_COLUMN(c1), UDF_SYS_COUNT_COLUMN(c2),UDF_SYS_COUNT_COLUMN(c3), UDF_SYS_COUNT_COLUMN(c4)

等价于如下四条sql语句:select count() from table group by c1;select count() from table group by c2;select count() from table group by c3;select count() from table group by c4;

d. 一个真实的实例:select UDF_SYS_COUNT_COLUMN(user_gender), UDF_SYS_COUNT_COLUMN(user_level) from db_name.userbase返回1行,2列:{“0”:3331656,”2”:3338142,”1”:3330202},{“0”:4668150,”2”:1891176,”1”:1984606,”6”:5818}

UDF_SYS_RANGECOUNT_COLUMN

  1. 作用:用于做静态样本分段(老的函数UDF_SYS_SEGCOUNT_COLUMN已经弃用,请使用该函数)

  2. 格式:UDF_SYS_RANGECOUNT_COLUMN(columnName, count, min, max)

    • 第一个参数是列名
    • 第二个参数分段数目
    • 第三个参数是参与分段的该列在全表的最小值
    • 第四个参数是参与分段的该列在全表的最大值
  3. 返回:一个json的字符串列,类似:{“ranges”:[{“start”:0,”end”:599}, {“start”:600,”end”:1899}, {“start”:1900,”end”:65326003}]}

  1. 使用说明:使用该函数进行动态分段统计,需要三个步骤:

    • 第一步,通过min, max求出符合条件的最小值和最大值
    • 第二步,通过UDF_SYS_RANGECOUNT_COLUMN获取各个分段。
    • 第三步,通过case when+group by获取每个分段中真实聚合数据。
  2. 特别说明UDF_SYS_RAGNECOUNT_COLUMN和通过UDF_SYS_RANGECOUNT_SAMPLING_COLUMN的区别在于,前者是静态分段,也就是根据(max-min+1)/segcount进行分段,而后在是动态分段,可以保证每个分段区间内的数目是大致均衡的。

group_concat

字符串聚合函数。

目前仅支持在聚合时Group By中包括所有参与计算的表分分区列(维度表)时可以使用。

语法:GROUP_CONCAT([DISTINCT] expr [,expr …] [ORDER BY col_name [ASC | DESC] [,col_name …]] [SEPARATOR str_val])

distinct用于将组内多个相同的字符串仅输出一个。若含有ORDER BY结构,则组内字符串会根据col_name列表排序输出。使用SEPARATOR可以指定聚合后的字符串分隔符,默认是逗号。