聚合函数

最近更新时间: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}