Hologres支持的通用聚合函数有哪些_实时数仓 Hologres(Hologres)-阿里云帮助中心

Hologres兼容PostgreSQL，支持使用标准的PostgreSQL语法进行开发。

兼容PostgreSQL聚合函数

Hologres已支持的通用聚合函数列表如下。当前Hologres版本支持的函数是PostgreSQL的一个子集，函数的使用方法请参见通用聚合函数。

函数名	描述	用例	结果
array_agg(anyelement)	将表达式的值串联到数组中。暂不支持JSON、JSONB、TIMETZ、INTERVAL、INET、OID、UUID数据类型和数组类型。	array_agg(c1)	`{1,2} {true,false} {a,b} {1.1,2.2}`
avg(bigint)	求BIGINT类型表达式中非空值的平均值。	avg(c1)	2.000000
avg(float8)	求FLOAT8类型表达式中非空值的平均值。	avg(c1)	2
avg(float4)	求FLOAT4类型表达式中非空值的平均值。	avg(c1)	2
avg(int)	求INT类型表达式中非空值的平均值。	avg(c1)	2.000000
bit_and(bigint)	对BIGINT类型表达式中的非空值执行按位与运算。	bit_and(c1)	0
bit_and(int)	对INT类型表达式中的非空值执行按位与运算。	bit_and(c1)	0
bit_or(bigint)	对BIGINT类型表达式中的非空值执行按位或运算。	bit_or(c1)	3
bit_or(int)	对INT类型表达式中的非空值执行按位或运算。	bit_or(c1)	3
bool_and(bool)	如果BOOL表达式的值均为TRUE，则函数结果返回TRUE，否则返回FALSE。	bool_and(c1)	f
bool_or(bool)	如果BOOL表达式的值包含TRUE，则函数结果返回TRUE，否则返回FALSE。	bool_or(c1)	t
count(*)	返回指定表的行数。	count(*)	3
count(bigint)	求BIGINT类型表达式的输入行数。说明 BIGINT类型表达式的值不为NULL。	count(c1)	3
count(numeric)	求NUMERIC类型表达式的输入行数。说明 NUMERIC类型表达式的值不为NULL。	count(c1)	3
every(bool)	如果BOOL表达式的值均为TRUE，则函数结果返回TRUE，否则返回FALSE。	无	无
max(bigint)	求BIGINT类型表达式的最大值。	max(c1)	3
max(float8)	求FLOAT8类型表达式的最大值。	max(c1)	3
max(float4)	求FLOAT4类型表达式的最大值。	max(c1)	3
max(int)	求INT类型表达式的最大值。	max(c1)	3
max(numeric)	求NUMERIC类型表达式的最大值。	max(c1)	3
min(bigint)	求BIGINT类型表达式的最小值。	min(c1)	1
min(float8)	求FLOAT8类型表达式的最小值。	min(c1)	1
min(float4)	求FLOAT4类型表达式的最小值。	min(c1)	1
min(int)	求INT类型表达式的最小值。	min(c1)	1
min(numeric)	求NUMERIC类型表达式的最小值。	min(c1)	1
sum(bigint)	求BIGINT类型表达式所有值的总和。	sum(c1)	6
sum(float8)	求FLOAT8类型表达式所有值的总和。	sum(c1)	6
sum(float4)	求FLOAT4类型表达式所有值的总和。	sum(c1)	6
sum(int)	求INT类型表达式所有值的总和。	sum(c1)	6
sum(numeric)	求NUMERIC类型表达式所有值的总和。	sum(c1)	6.0
string_agg(expression, delimiter)	使用指定分隔符将指定表达式的非空值串联成字符串。	string_agg(c1, '-')	a-b-c
corr(Y, X)	求相关系数。	corr(c1, c2)	无
covar_pop(Y, X)	求总体协方差。	covar_pop(c1, c2)	无
covar_samp(Y, X)	求样本协方差。	covar_samp(c1, c2)	无
regr_avgx(Y, X)	求自变量的平均值。	reg_avgx(c1, c2)	无
regr_avgy(Y, X)	求因变量的平均值。	reg_avgy(c1, c2)	无
regr_count(Y, X)	求两个输入参数中都不为空的行数。	regr_count(c1, c2)	无
regr_intercept(Y, X)	求由(X,Y)确定的最小方差拟合的纵轴截距。	reg_intercept(c1, c2)	无
regr_r2(Y, X)	求相关系数的平方。	regr_r2(c1, c2)	无
regr_slope(Y, X)	求由(X,Y)确定的最小方差拟合的斜率。	regr_slope(c1, c2)	无
regr_sxx(Y, X)	求自变量的平方和`sum(X^2) - sum(X)^2/N` 。	regr_sxx(c1, c2)	无
regr_sxy(Y, X)	求自变量和因变量的乘积和`sum(XY) - sum(X) sum(Y)/N`。	regr_sxy(c1, c2)	无
regr_syy(Y, X)	求因变量的平方和`sum(Y^2) - sum(Y)^2/N`。	regr_syy(c1, c2)	无
stddev(int)	求INT类型表达式的样本标准差。	stddev(c1)	无
stddev(numeric)	求NUMERIC类型表达式的样本标准差。	stddev(c1)	无
stddev(float8)	求FLOAT8类型表达式的样本标准差。	stddev(c1)	无
stddev_pop(int)	求INT类型表达式的总体标准差。	stddev_pop(c1)	无
stddev_pop(numeric)	求NUMERIC类型表达式的总体标准差。	stddev_pop(c1)	无
stddev_pop(float8)	求FLOAT8类型表达式的总体标准差。	stddev_pop(c1)	无
stddev_samp(int)	求INT类型表达式的样本标准差。	stddev_samp(c1)	无
stddev_samp(numeric)	求NUMERIC类型表达式的样本标准差。	stddev_samp(c1)	无
stddev_samp(float8)	求FLOAT8类型表达式的样本标准差。	stddev_samp(c1)	无
variance(int)	求INT类型表达式的样本方差。	variance(c1)	无
variance(numeric)	求NUMERIC类型表达式的样本方差。	variance(c1)	无
var_pop(float8)	求FLOAT8类型表达式的总体方差。	var_pop(c1)	无
var_pop(int)	求INT类型表达式的总体方差。	var_pop(c1)	无
var_pop(numeric)	求NUMERIC类型表达式的总体方差。	var_pop(c1)	无
var_samp(float8)	求FLOAT8类型表达式的样本方差。	var_samp(c1)	无
var_samp(int)	求INT类型表达式的样本方差。	var_samp(c1)	无
var_samp(numeric)	求NUMERIC类型表达式的样本方差。	var_samp(c1)	无

其他聚合函数

APPROX_COUNT_DISTINCT

语法
APPROX_COUNT_DISTINCT函数用于计算某一列去重后的行数，结果只能返回一个值，并且该值为近似值。
```
APPROX_COUNT_DISTINCT ( <column> )
```
参数说明如下表所示。
参数
描述
column
需要近似计算去重后行数的列。
APPROX_COUNT_DISTINCT采用HyperLogLog基数估计的方式进行非精确的COUNT DISTINCT计算。非精确的COUNT DISTINCT计算能提升查询性能，尤其是对于column的离散值比较大的情况，误差率平均可以控制在0.1%-1%以内。该函数适用于对性能敏感并且可以接受误差的场景。
同时，您也可以通过COUNT DISTINCT ( column )的方式进行精确的COUNT DISTINCT计算，使用时资源开销会比较大。
调整误差率
通过使用以下参数调整误差率。
```
SET hg_experimental_approx_count_distinct_precision = 20;
```
- 支持取值范围为[12,20]，默认值为17。
- 精度参数含义为HyperLogLog算法的分桶bit位个数，参数越大，代表分桶越多，理论精度越高。
- 精度参数取值越高，计算时间和内存开销也会相应增大，但都远远小于精确的COUNT DISTINCT ( column )语句带来的开销，因此，推荐选用APPROX_COUNT_DISTINCT替换COUNT DISTINCT ( column )。
- 当精度参数设置为17以上时，Hologres采用HyperLogLog++算法，会对返回值做误差修正，以进一步降低误差、稳定误差。例如hg_experimental_approx_count_distinct_precision取值为20时，多数情况下，可以降低到0.01-0.2%不等的误差率。

示例

计算O_CUSTKEY列去重后行数的近似值，示例语句如下。

SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

--全局设置精度20，计算O_CUSTKEY列去重后行数的近似值
ALTER DATABASE dbname SET hg_experimental_approx_count_distinct_precision = 20;
SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

--在Session级别设置精度20
SET hg_experimental_approx_count_distinct_precision = 20;
SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

UNIQ

使用限制
- 仅Hologres V1.3及以上版本支持使用UNIQ函数，如果您的实例是V1.3以下版本，请您使用自助升级或加入Hologres钉钉交流群反馈，详情请参见如何获取更多的在线支持？。
- SQL中必须包含GROUP BY，且GROUP BY的字段比较均匀（不倾斜），UNIQ才能发挥比COUNT DISTINCT更好的性能。
语法
UNIQ函数用于计算某一列去重后的行数，结果返回一个去重值。
```
UNIQ ( < column > );
```
- 参数说明如下。
  参数
  描述
  column
  需要计算去重后行数的列。
  支持SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION、TEXT、VARCHAR、TIMESTAMP、TIMESTAMPTZ、DATE、TIMETZ、UUID类型。
- 使用说明如下。
  - 一般来说，UNIQ在GROUP BY KEY的KEY基数较高时，比COUNT DISTINCT性能更好，同时UNIQ比COUNT DISTINCT更节省内存，如果使用COUNT DISTINCT遇到内存超限时，可以换用UNIQ。
  - 从Hologres V2.1版本开始，Hologres针对COUNT DISTINCT场景做了非常多的性能优化（包括单个COUNT DISTINCT、多个COUNT DISTINCT、数据倾斜、SQL没有GROUP BY字段等场景），无需再手动改写成UNIQ实现，即可实现更好的性能。详情请参见Count Distinct优化。

示例

计算ORDERS表的O_CUSTKEY列去重后行的数值语句如下。

SELECT UNIQ ( O_CUSTKEY ) FROM ORDERS;

--不同O_ORDERSTATUS下O_CUSTKEY去重数
SELECT O_ORDERSTATUS, UNIQ ( O_CUSTKEY ) FROM ORDERS GROUP BY O_ORDERSTATUS;

MAX_BY与MIN_BY

使用限制
该函数适用于Hologres V1.3.36及以上版本的实例，若实例低于该版本，请您通过加入实时数仓Hologres交流群申请升级实例，详情请参见如何获取更多的在线支持？。

语法

MAX_BY与MIN_BY函数用于比较某一列（y列）取值的大小，当y列取值为最大值或最小值时，返回对应着的另外一列（x列）的取值。语法如下。

说明

数字类型按数字大小进行比较，非数字类型按照字典排序进行比较。

MAX_BY(x, y);
MIN_BY(x, y);

参数说明如下。

参数

描述

需要比较大小的列。

另外一列的字段名。

当y列取值为最大值时，MAX_BY函数返回此列的取值结果。
当y列取值为最小值时，MIN_BY函数返回此列的取值结果。

使用说明如下。
- 当MAX_BY函数中y列的最大值存在多个时，则返回对应的多个x值中的最大值。
- 当MIN_BY函数中y列的最小值存在多个时，则返回对应的多个x值中的最小值。
- y列的NULL值不参与计算。当y列所有值均为NULL时，函数返回值为NULL。

示例
- 使用MAX_BY函数，通过GROUP BY对id进行分组，查询每组cost最大值对应的name值。示例语句如下。
```
SELECT id, max_by(name, cost) FROM test GROUP BY id;

 id | max_by
----+--------
  2 | bb
  1 | aaa
  3 | c
(3 rows)
```
- 使用MAX_BY函数，查询cost最大值（存在多个）对应的name值。示例语句如下。
```
select max_by(name, cost) from test;

 max_by
--------
 bb
(1 row)
```
- 使用MIN_BY函数，查询cost最小值对应的name值。示例语句如下。
```
SELECT min_by(name, cost) FROM test;

 min_by
--------
 cc
(1 row)
```

参数	描述
column	需要近似计算去重后行数的列。