Hologres兼容PostgreSQL,支持使用标准的PostgreSQL语法进行开发。
兼容PostgreSQL聚合函数
Hologres已支持的通用聚合函数列表如下。当前Hologres版本支持的函数是PostgreSQL的一个子集,函数的使用方法请参见通用聚合函数。
函数名 | 描述 | 用例 | 结果 |
array_agg(anyelement) | 将表达式的值串联到数组中。暂不支持JSON、JSONB、TIMETZ、INTERVAL、INET、OID、UUID数据类型和数组类型。 | array_agg(c1) |
|
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) | 求自变量的平方和 | regr_sxx(c1, c2) | 无 |
regr_sxy(Y, X) | 求自变量和因变量的乘积和 | regr_sxy(c1, c2) | 无 |
regr_syy(Y, X) | 求因变量的平方和 | 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
需要比较大小的列。
x
另外一列的字段名。
当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)