聚合函数

最近更新时间：2023-12-08 15:00:23
我的收藏
本页目录：

APPROX_COUNT_DISTINCT
函数语法：
APPROX_COUNT_DISTINCT(<expr> any [, <relativeSD> integer|double|decimal])
支持引擎：SparkSQL
使用说明：返回HyperLogLog++估计的基数。relativeSD定义了允许的最大相对标准差。
返回类型：bigint
示例：
SELECT approx_count_distinct(col1) FROM (VALUES (1), (1), (2), (2), (3)) tab(col1);
 3
AVG
函数语法：
AVG(<expr> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回根据组的值计算的平均值
返回类型：double
示例：
> SELECT avg(col) FROM (VALUES (1), (2), (3)) AS tab(col);
 2.0
> SELECT avg(col) FROM (VALUES (1), (2), (NULL)) AS tab(col);
 1.5
CORR
函数语法：
CORR(<expr> integer|double|decimal, <expr> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回一组数字对之间的Pearson相关系数。
返回类型：double
示例：
> SELECT corr(c1, c2) FROM (VALUES (3, 2), (3, 3), (6, 4)) as tab(c1, c2);
 0.8660254037844387
COUNT
函数语法：
COUNT(*)
COUNT([DISTINCT] <col1> ANY, <col2> ANY, ...)
支持引擎：SparkSQL、Presto
使用说明：
COUNT(*)：返回检索到的行总数，包括包含null的行。
COUNT(<col1> ANY, <col2> ANY, ...)：返回提供的表达式均为非空的行数。
COUNT([DISTINCT] <col1> ANY,<col2> ANY, ...)：返回提供的表达式唯一且非空的行数。
返回类型：integer
示例：
> SELECT count(*) FROM (VALUES (NULL), (5), (5), (20)) AS tab(col);
 4
> SELECT count(col) FROM (VALUES (NULL), (5), (5), (20)) AS tab(col);
 3
> SELECT count(DISTINCT col) (FROM VALUES (NULL), (5), (5), (10)) AS tab(col);
 2
COUNT_IF
函数语法：
COUNT_IF(<expr> ANY)
支持引擎：SparkSQL、Presto
使用说明：返回表达式为TRUE的行数。
返回类型：int
示例：
> SELECT count_if(col % 2 = 0) FROM (VALUES (NULL), (0), (1), (2), (3)) AS tab(col);
 2
> SELECT count_if(col IS NULL) FROM (VALUES (NULL), (0), (1), (2), (3)) AS tab(col);
 1
COVER_POP
函数语法：
COVAR_POP(<expr1> integer|double|decimal, <expr2> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回一组数字对的总体协方差。
返回类型：double
示例：
> SELECT covar_pop(c1, c2) FROM (VALUES (1,1), (2,2), (3,3)) AS tab(c1, c2);
 0.6666666666666666
COVER_SAMP
函数语法：
COVAR_SAMP(<expr1> integer|double|decimal, <expr2> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回一组数字对的样本协方差。
返回类型：double
示例：
> SELECT covar_samp(c1, c2) FROM (VALUES (1,1), (2,2), (3,3)) AS tab(c1, c2);
 1.0
FIRST_VALUE
函数语法：
FIRST_VALUE(<expr> T[, <isIgnoreNull> boolean])
支持引擎：SparkSQL、Presto
使用说明：返回一组行的expr的第一个值。如果isIgnoreNull为true，则仅返回非null值。
返回类型：T
示例：
> SELECT first_value(col) FROM (VALUES (10), (5), (20)) AS tab(col);
 10
> SELECT first_value(col) FROM (VALUES (NULL), (5), (20)) AS tab(col);
 NULL
> SELECT first_value(col, true) FROM (VALUES (NULL), (5), (20)) AS tab(col);
 5
FIRST
函数语法：
FIRST(<expr> T[, <isIgnoreNull> boolean])
支持引擎：SparkSQL
使用说明：返回一组行的expr的第一个值。如果isIgnoreNull为true，则仅返回非null值。
返回类型：T
示例：
> SELECT first(col) FROM (VALUES (10), (5), (20)) AS tab(col);
 10
> SELECT first(col) FROM (VALUES (NULL), (5), (20)) AS tab(col);
 NULL
> SELECT first(col, true) FROM (VALUES (NULL), (5), (20)) AS tab(col);
 5
KURTOSIS
函数语法：
KURTOSIS(<expr> integer|double|decimal)
支持引擎：SparkSQL
使用说明：返回根据组的值计算得出的峰度值。
返回类型：double
示例：
> SELECT kurtosis(col) FROM (VALUES (-10), (-20), (100), (1000)) AS tab(col);
 -0.7014368047529627
> SELECT kurtosis(col) FROM (VALUES (1), (10), (100), (10), (1)) as tab(col);
 0.19432323191699075s
LAST_VALUE
函数语法：
LAST_VALUE(<expr> T[, <isIgnoreNull> boolean])
支持引擎：SparkSQL、Presto
使用说明：返回一组行的expr的最后一个值。如果isIgnoreNull为true，则仅返回非null值。
返回类型：T
示例：
> SELECT last_value(col) FROM (VALUES (10), (5), (20)) AS tab(col);
 20
> SELECT last_value(col) FROM (VALUES (10), (5), (NULL)) AS tab(col);
 NULL
> SELECT last_value(col, true) FROM (VALUES (10), (5), (NULL)) AS tab(col);
 5
LAST
函数语法：
LAST(<expr> T[, <isIgnoreNull> boolean])
支持引擎：SparkSQL
使用说明：返回一组行的expr的最后一个值。如果isIgnoreNull为true，则仅返回非null值。
返回类型：T
示例：
> SELECT last(col) FROM (VALUES (10), (5), (20)) AS tab(col);
 20
> SELECT last(col) FROM (VALUES (10), (5), (NULL)) AS tab(col);
 NULL
> SELECT last(col, true) FROM (VALUES (10), (5), (NULL)) AS tab(col);
 5
MEAN
函数语法：
MEAN(<expr> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回根据组的值计算的平均值
返回类型：double
示例：
> SELECT mean(col) FROM (VALUES (1), (2), (3)) AS tab(col);
 2.0
> SELECT mean(col) FROM (VALUES (1), (2), (NULL)) AS tab(col);
 1.5
PERCENTILE
函数语法：
PERCENTILE(<col> ANY, <percentage> integer|double|decimal|array<double> [, <frequency> integer])
支持引擎：SparkSQL、Presto
使用说明：返回给定百分比下数值列col的精确百分位值。percentage值必须介于0.0和1.0之间。frequency应为正整数
返回类型：double
示例：
> SELECT percentile(col, 0.3) FROM (VALUES (0), (10)) AS tab(col);
 3.0
> SELECT percentile(col, array(0.25, 0.75)) FROM (VALUES (0), (10)) AS tab(col);
 [2.5,7.5]
SKEWNESS
函数语法：
SKEWNESS(<col> integer|double|decimal)
支持引擎：SparkSQL
使用说明：返回根据组的值计算的偏度值
返回类型：double
示例：
> SELECT skewness(col) FROM (VALUES (-10), (-20), (100), (1000)) AS tab(col);
 1.1135657469022011
> SELECT skewness(col) FROM (VALUES (-1000), (-100), (10), (20)) AS tab(col);
 -1.1135657469022011
PERCENTILE_APPROX
函数语法：
PERCENTILE_APPROX(<col> integer|double|decimal, <percentage> double|array<double>[, <accuracy> integer])
支持引擎：SparkSQL、Presto
使用说明：返回数值列col的近似百分比，该数值是有序col值（从最小到最大排序）中的最小值，因此不超过col值的百分比小于或等于该值。percentage的值必须介于0.0和1.0之间。accuracy参数（默认值：10000）是一个正数值文字，它以内存为代价控制近似精度。精度值越高，精度越好，1.0/accuracy是近似的相对误差。当percentage是一个数组时，percentage数组的每个值必须介于0.0和1.0之间。在这种情况下，返回给定percentage数组中列col的近似百分比数组。
返回类型：integer |array<integer>
示例：
> SELECT percentile_approx(col, array(0.5, 0.4, 0.1), 100) FROM (VALUES (0), (1), (2), (10)) AS tab(col);
 [1,1,0]
> SELECT percentile_approx(col, 0.5, 100) FROM (VALUES (0), (6), (7), (9), (10)) AS tab(col);
 7
APPROX_PERCENTILE
函数语法：
APPROX_PERCENTILE(<col> integer|double|decimal, <percentage> double|array<double>[, <accuracy> integer])
支持引擎：SparkSQL、Presto
使用说明：返回数值列col的近似百分比，该数值是有序col值（从最小到最大排序）中的最小值，因此不超过col值的百分比小于或等于该值。percentage的值必须介于0.0和1.0之间。accuracy参数（默认值：10000）是一个正数值文字，它以内存为代价控制近似精度。精度值越高，精度越好，1.0/accuracy是近似的相对误差。当percentage是一个数组时，percentage数组的每个值必须介于0.0和1.0之间。在这种情况下，返回给定percentage数组中列col的近似百分比数组。
返回类型：integer |array<integer>
示例：
> SELECT APPROX_PERCENTILE(col, array(0.5, 0.4, 0.1), 100) FROM (VALUES (0), (1), (2), (10)) AS tab(col);
 [1,1,0]
> SELECT APPROX_PERCENTILE(col, 0.5, 100) FROM (VALUES (0), (6), (7), (9), (10)) AS tab(col);
 7
MAX
函数语法：
MAX(<col> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回col的最大值。
返回类型：与col一致
示例：
> SELECT max(col) FROM (VALUES (10), (50), (20)) AS tab(col);
 50
MAX_BY
函数语法：
MAX_BY(<x> T, <y> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回与y的最大值关联的x值。
返回类型：T
示例：
> SELECT max_by(x, y) FROM (VALUES (('a', 10)), (('b', 50)), (('c', 20))) AS tab(x, y);
 b
MIN
函数语法：
MIN(<col> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回col的最小值。
返回类型：与col一致
示例：
> SELECT min(col) FROM (VALUES (10), (50), (20)) AS tab(col);
 10
MIN_BY
函数语法：
MIN_BY(<x> T, <y> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回与y的最小值关联的x值。
返回类型：T
示例：
> SELECT min_by(x, y) FROM (VALUES (('a', 10)), (('b', 50)), (('c', 20))) AS tab(x, y);
 a
STD
函数语法：
STD(<col> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明
SparkSQL：返回根据组的值计算的样本标准偏差。
Presto：返回根据组的值计算的总体标准偏差。
返回类型：double
示例：
> SELECT std(col) FROM (VALUES (1), (2), (3)) AS tab(col);
 1.0
STDDEV
函数语法：
STDDEV(<col> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回根据组的值计算的样本标准偏差。
返回类型：double
示例：
> SELECT stddev(col) FROM (VALUES (1), (2), (3)) AS tab(col);
 1.0
STDDEV_POP
函数语法：
STDDEV_POP(<col> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回根据组的值计算的总体标准偏差。
返回类型：double
示例：
> SELECT stddev_pop(col) FROM (VALUES (1), (2), (3)) AS tab(col);
 0.816496580927726
STDDEV_SAMP
函数语法：
STDDEV_SAMP(<col> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回根据组的值计算的样本标准偏差。
返回类型：double
示例：
> SELECT stddev_samp(col) FROM (VALUES (1), (2), (3)) AS tab(col);
 1.0
SUM
函数语法：
SUM(<col> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回根据组的值计算的总和。
返回类型：与col一致
示例：
> SELECT sum(col) FROM (VALUES (5), (10), (15)) AS tab(col);
 30
> SELECT sum(col) FROM (VALUES (NULL), (10), (15)) AS tab(col);
 25
> SELECT sum(col) FROM (VALUES (NULL), (NULL)) AS tab(col);
 NULL
VARIANCE
函数语法：
VARIANCE(<col> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回根据组的值计算的样本方差。
返回类型：double
示例：
> SELECT VARIANCE(col) FROM (VALUES (1), (2), (3)) AS tab(col);
 1.0
VAR_POP
函数语法：
VAR_POP(<col> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回根据组的值计算的总体方差。
返回类型：double
示例：
> SELECT var_pop(col) FROM (VALUES (1), (2), (3)) AS tab(col);
 0.6666666666666666
VAR_SAMP
函数语法：
VAR_SAMP(<col> integer|double|decimal)
支持引擎：SparkSQL、Presto
使用说明：返回根据组的值计算的样本方差。
返回类型：double
示例：
> SELECT var_samp(col) FROM (VALUES (1), (2), (3)) AS tab(col);
 1.0
HISTOGRAM_NUMERIC
函数语法：
HISTOGRAM_NUMERIC(<col> integer, <nb> integer)
支持引擎：Presto
使用说明：使用nb个非均匀间隔的bin计算组中数字列的直方图。输出是一个大小为nb的（x，y）坐标数组，表示bin的中心和高度。
返回类型：array<struct {'x','y'}>
示例：
> SELECT histogram_numeric(col, 5) FROM (VALUES (0), (1), (2), (10)) AS tab(col);
 [{"x":0,"y":1.0},{"x":1,"y":1.0},{"x":2,"y":1.0},{"x":10,"y":1.0}]
COLLECT_LIST
函数语法：
COLLECT_LIST(<col> T)
支持引擎：SparkSQL、Presto
使用说明：收集并返回非唯一元素的列表。
返回类型：array<T>
示例：
> SELECT collect_list(col) FROM (VALUES (1), (2), (1)) AS tab(col);
 [1,2,1]
COLLECT_SET
函数语法：
COLLECT_SET(<col> T)
支持引擎：SparkSQL、Presto
使用说明：收集并返回一组唯一的元素。
返回类型：array<T>
示例：
> SELECT collect_set(col) FROM (VALUES (1), (2), (1)) AS tab(col);
 [1,2]
COUNT_MIN_SKETCH
函数语法：
COUNT_MIN_SKETCH(<col> T, <eps> double, <confidence> double, <seed> integer)
支持引擎：SparkSQL
使用说明：返回具有给定esp、confidence和seed的列的count min sketch。结果是binary，可以在使用前反序列化为CountMinSketch
返回类型：binary
示例：
> SELECT hex(count_min_sketch(col, 0.5d, 0.5d, 1)) FROM (VALUES (1), (2), (1)) AS tab(col);
0000000100000000000000030000000100000004000000005D8D6AB90000000000000000000000000000000200000000000000010000000000000000
EVERY
函数语法：
EVERY(<col> boolean)
支持引擎：SparkSQL、Presto
使用说明：如果col的所有值均为true，则返回true。
返回类型：boolean
示例：
> SELECT every(col) FROM (VALUES (true), (true), (true)) AS tab(col);
 true
> SELECT every(col) FROM (VALUES (NULL), (true), (true)) AS tab(col);
 true
> SELECT every(col) FROM (VALUES (true), (false), (true)) AS tab(col);
 false
BOOL_AND
函数语法：
BOOL_AND(<col> boolean)
支持引擎：SparkSQL、Presto
使用说明：如果col的所有值均为true，则返回true。
返回类型：boolean
示例：
> SELECT bool_and(col) FROM (VALUES (true), (true), (true)) AS tab(col);
 true
> SELECT bool_and(col) FROM (VALUES (NULL), (true), (true)) AS tab(col);
 true
> SELECT bool_and(col) FROM (VALUES (true), (false), (true)) AS tab(col);
 false
AND
函数语法：
<expr1> AND <expr2>
支持引擎：SparkSQL、Presto
使用说明
逻辑与
返回类型：boolean
示例：
> SELECT true and true;
 true
> SELECT true and false;
 false
> SELECT true and NULL;
 NULL
> SELECT false and NULL;
 false
OR
函数语法：
<expr1> OR <expr2>
支持引擎：SparkSQL、Presto
使用说明：逻辑或
返回类型：boolean
示例：
> SELECT true or false;
 true
> SELECT false or false;
 false
> SELECT true or NULL;
 true
> SELECT false or NULL;
 NULL
ANY
函数语法：
ANY(<col> boolean)
支持引擎：SparkSQL、Presto
使用说明：如果col的至少一个值为true，则返回true。
返回类型：boolean
示例：
> SELECT any(col) FROM (VALUES (true), (false), (false)) AS tab(col);
 true
> SELECT any(col) FROM (VALUES (NULL), (true), (false)) AS tab(col);
 true
> SELECT any(col) FROM (VALUES (false), (false), (NULL)) AS tab(col);
 false
SOME
函数语法：
SOME(<col> boolean)
支持引擎：SparkSQL、Presto
使用说明：如果col的至少一个值为true，则返回true。
返回类型：boolean
示例：
> SELECT some(col) FROM (VALUES (true), (false), (false)) AS tab(col);
 true
> SELECT some(col) FROM (VALUES (NULL), (true), (false)) AS tab(col);
 true
> SELECT some(col) FROM (VALUES (false), (false), (NULL)) AS tab(col);
 false
BOOL_OR
函数语法：
BOOL_OR(<col> boolean)
支持引擎：SparkSQL、Presto
使用说明：如果col的至少一个值为true，则返回true。
返回类型：boolean
示例：
> SELECT BOOL_OR(col) FROM (VALUES (true), (false), (false)) AS tab(col);
 true
> SELECT BOOL_OR(col) FROM (VALUES (NULL), (true), (false)) AS tab(col);
 true
> SELECT BOOL_OR(col) FROM (VALUES (false), (false), (NULL)) AS tab(col);
 false
BIT_AND
函数语法：
BIT_AND(<col> integer|bigint)
支持引擎：SparkSQL、Presto
使用说明：返回所有非空输入值的按位AND，如果没有，则返回null。
返回类型：与col一致
示例：
> SELECT bit_and(col) FROM (VALUES (3), (5)) AS tab(col);
 1
BIT_OR
函数语法：
BIT_OR(<col> integer|bigint)
支持引擎：SparkSQL、Presto
使用说明：返回所有非空输入值的按位OR，如果没有，则返回null。
返回类型：与col一致
示例：
> SELECT bit_or(col) FROM (VALUES (3), (5)) AS tab(col);
 7
BIT_XOR
函数语法：
BIT_XOR(<col> integer|bigint)
支持引擎：SparkSQL、Presto
使用说明：返回所有非空输入值的按位XOR，如果没有，则返回null。
返回类型：与col一致
示例：
> SELECT bit_xor(col) FROM (VALUES (3), (5)) AS tab(col);
 6
ARG_MIN
函数语法：
ARG_MIN(<col1>, <col2> | expr(col2))
支持引擎：SparkSQ
使用说明：指定列col1最小的那一行，按指定的计算表达式返回。
col1: 可排序的列名，如果为常量，那么会返回任意值。如果有多个最小值，会随机返回其中一行。
返回类型：与col2或expr(col2)类型一致。
示例：
> SELECT arg_min(dt, uid) from values (1, 'm1'), (2, 't2'), (3, 'z3') as tab(dt, uid);
m1
> SELECT arg_min(dt, upper(uid)) from values (1, 'm1', 1), (2, 't2', 1), (3, 'z3', 2) as tab(dt, uid, gid) group by gid;
M1
Z3
ARG_MAX
函数语法：
ARG_MAX(<col1>, <col2> | expr(col2))
支持引擎：SparkSQL
使用说明：指定列col1最大的那一行，按指定的计算表达式返回。
col1: 可排序的列名，如果为常量，那么会返回任意值。如果有多个最大值，会随机返回其中一行。
返回类型：与col2或expr(col2)类型一致。
示例：
> SELECT arg_max(dt, uid) from values (1, 'm1'), (2, 't2'), (3, 'z3') as tab(dt, uid);
z3
> SELECT arg_max(dt, upper(uid)) from values (1, 'm1', 1), (2, 't2', 1), (3, 'z3', 2) as tab(dt, uid, gid) group by gid;
T2
Z3
MAP_UNION_SUM
函数语法：
MAP_UNION_SUM(map<k, v> input)
支持引擎：SparkSQL
使用说明：指定列，将map按key值求和所有的value。
注意1：允许聚合的数据为null，全部为null时输出为null。
注意2：允许key的value为null，此时会转换为数字0。
返回类型：与输入类型一致。
示例：
> SELECT map_union_sum(ids) from values (map(1, 1)), (map(1,2)), (map(2,1)) as tab(ids);
{1:3,2:1}
> SELECT idx, map_union_sum(ids) from values (map(1, 1), 1), (map(1,2), 1), (map(2,1), 1) as tab(ids, idx) group by idx;
1 {1:3,2:1}
﻿