配置SQL分析任务时,需编写SQL语句,以查询和调度产出所需数据。本文介绍SQL分析任务支持的函数。

背景信息

SQL分析任务的更多信息,请参见概述

本文介绍SQL分析任务支持的以下函数。

时间和日期函数

函数名 返回类型 描述 用例 结果
to_char(timestamp, text) TEXT 将时间戳转换为字符串。 to_char(current_timestamp, 'HH12:MI:SS') 06:26:33
to_char(int, text) TEXT 将整数转换为字符串。 to_char(125, '999') 125
to_char(double precision, text) TEXT 将实数或双精度数转换为字符串。 to_char(125.8::real, '999D9') 125.8
to_date(text, text) DATE 将字符串转换为日期。
  • to_date('05 Dec 2000', 'DD Mon YYYY')
  • to_date('2000 12 05', 'YYYY MM DD')
2000-12-05
to_number(text, text) NUMERIC 将字符串转换为数字。 to_number('12,454.8-', '99G999D9S') -12454.8
to_timestamp(text, text) TIMESTAMP 将字符串转换为时间戳。 to_timestamp('05 Dec 2000', 'DD Mon YYYY') 2000-12-05 00:00:00
date - date INTEGER 日期的减法运算。 date '2001-10-01' - date '2001-09-28' 3
clock_timestamp() TIMESTAMP WITH TIME ZONE 获取当前的实际时间。 clock_timestamp() 2020-05-03 06:32:28.814918+08
current_date DATE 获取当前日期。 current_date 2020-05-03
current_timestamp TIMESTAMP WITH TIME ZONE 获取当前时间戳。 current_timestamp 2020-05-03 06:33:36.113682+08
date_part(text, timestamp) DOUBLE PRECISION 从时间戳中获取子字段。 date_part('hour', timestamp '2001-02-16 20:38:40') 20.0
date_trunc(text, timestamp) TIMESTAMP 截断时间戳到指定精度。 date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00
extract(field from timestamp) DOUBLE PRECISION 从时间戳中获取子字段。 extract(hour from timestamp '2001-02-16 20:38:40') 20.0
isfinite(date) BOOLEAN 判断日期是否为有限值。 isfinite(date '2001-02-16') true
isfinite(timestamp) BOOLEAN 判断时间戳是否为有限值。 isfinite(timestamp '2001-02-16 21:28:30') true
make_date(year int, month int, day int) DATE 使用年月日创建日期。 make_date(2013, 7, 15) 2013-07-15
localtimestamp TIMESTAMP 获取不包含时区的当前时间。 localtimestamp 2020-08-21 12:02:21.178031
now() TIMESTAMP WITH TIME ZONE 获取当前时间戳。 now() 2020-05-03 06:38:48.492168+08
statement_timestamp() TIMESTAMP WITH TIME ZONE 获取当前语句开始的时间。 statement_timestamp() 2020-05-05 06:39:11.125957+08
timeofday() TEXT 获取当前实际时间。
说明 时间使用格式化文本字符串格式。
timeofday() Tue May 03 06:39:43.195368 2020 CST
transaction_timestamp() TIMESTAMP WITH TIME ZONE 获取当前时间戳。 transaction_timestamp() 2020-05-03 06:40:08.023623+08
to_timestamp(double precision) TIMESTAMP 将Unix Epoch转换为时间戳。
说明 从1970-01-01 00:00:00+00的秒数开始转换。
to_timestamp(1284352323) 2010-09-13 04:32:03

字符串函数

函数名 描述 用例 结果
string || string 连接两个字符串。 'Holo' || 'greSQL' HologreSQL
bit_length(string) 获取字符串的位长度。 bit_length('jose') 32
char_length(string) 获取字符串的字符长度。 char_length('jose') 4
lower(string) 转换字符串为小写格式。 lower('TOM') tom
octet_length(string) 返回字符串的字节数。 octet_length('jose') 4
position(substring in string) 查找子字符串在字符串中的位置。 position('om' in 'Thomas') 3
substring(string [from int] [for int]) 从字符串中找出指定的子字符串。 substring('Thomas' from 2 for 3) hom
substring(string from pattern) 从字符串中找出与POSIX正则表达式匹配的子字符串。 substring('Thomas' from '...$') mas
substring(string from pattern for escape) 从字符串中找出与SQL正则表达式匹配的子字符串。 substring('Thomas' from '%#"o_a#"_' for '#') oma
trim([leading | trailing | both] [characters] from string) 从字符串String的开始、结尾或两端删除仅包含Characters中字符的最长字符串。
说明
  • 默认从两端删除。
  • 默认仅包含Characters中字符的最长字符串为空格
trim(both 'xyz' from 'yxTomxx') Tom
trim([lea| tra| both] [from] string [, char] ) 从字符串String的开始、结尾或两端删除仅包含Characters中字符的最长字符串。
说明
  • 默认从两端删除。
  • 默认仅包含Characters中字符的最长字符串为空格
trim(both from 'yxTomxx', 'xyz') Tom
upper(string) 转换字符串为大写格式。 upper('tom') TOM
函数名 描述 用例 结果
ascii(string) 返回参数第一个字符的ASCII码。 ascii('x') 120
btrim(string text [, characters text]) 从字符串String的开始和结尾删除仅包含Characters中字符的最长字符串。
说明 默认仅包含Characters中字符的最长字符串为空格
btrim('xyxtrimyyx', 'xyz') trim
chr(int) 返回指定编码值对应的字符。
说明 参数必须是合法的ASCII或UTF8编码值,并且参数值不能为0
chr(65) A
concat(str "any" [, str "any" [, ...] ]) 连接所有参数。忽略NULL参数。 concat('abcde', 2, NULL, 22) abcde222
concat_ws(sep text, str "any" [, str "any" [, ...] ]) 使用分隔符连接除第一个参数外的所有参数。
说明 第一个参数用作分隔符字符串。忽略NULL参数。
concat_ws(',', 'abcde', 2, NULL, 22) abcde,2,22
initcap(string) 将每个单词的第一个字母转换为大写,其余字母转换为小写。
说明 单词是由一系列字母和数字组成的字符,使用非字母或数字分隔。
initcap('hi THOMAS') Hi Thomas
length(string) 返回字符串中字符的个数。 length('jose') 4
lpad(string text, length int [, fill text]) 用Fill填充在String头部,将String填充为长度是Length的字符串。
说明
  • 如果String的长度已经超过Length,则从右侧将String截断为长度是Length的字符串。
  • 如果没有指定Fill的值,则Fill默认为空格
lpad('hi', 5, 'xy') xyxhi
ltrim(string text [, characters text]) 从字符串String的开始删除只包含Characters 中字符的最长的字符串。
说明 如果没有指定Characters的值,则Characters默认是空格。
ltrim('zzzytest', 'xyz') test
md5(string) 计算String的MD5哈希值。结果表示为十六进制的形式。 md5('abc') 900150983cd24fb0d6963f7d28e17f72
parse_ident(quali_iden text [,...] ) 解析字符串。 parse_ident('"SomeSchema".someTable') {SomeSchema,sometable}
quote_ident(string text) 使用String作为合法的SQL标识符。
说明 当字符串包含非标识符字符或者字符串会转换大小写时,需要添加引号。
quote_ident('Foo bar') "Foo bar"
quote_literal(string text) 将String转换为合法的SQL语句字符串的常量形式。 quote_literal(E'O\'Reilly') 'O''Reilly'
regexp_matches(string text, pattern text ) 返回与POSIX正则表达式匹配的子字符串。 regexp_match('foobarbequebaz', '(bar)(beque)') {bar,beque}
regexp_replace(str text, pat text, replace text ) 使用Replacement替换与POSIX正则表达式匹配的子字符串。 regexp_replace('Thomas', '.[mN]a.', 'M') ThM
regexp_split_to_array(string text, pattern text ) 使用POSIX正则表达式分割字符串。 regexp_split_to_array('hello world', '\s+') {hello,world}
regexp_split_to_table(string text, pattern text ) 使用POSIX正则表达式分割字符串。 regexp_split_to_table('hello world', '\s+')

hello

world

(2 rows)
repeat(string text, number int) 将String重复指定Nnumber次。 repeat('Pg', 4) PgPgPgPg
replace(string text, from text, to text) 替换String中所有From子字符串为To。 replace('abcdefabcdef', 'cd', 'XX') abXXefabXXef
rpad(string text, length int [, fill text]) 用Fill填充在String尾部,将String填充为长度是Length的字符串。
说明
  • 如果String的长度已经超过Length,则从左侧将String截断为长度是Length的字符串。
  • 如果没有指定Fill的值,则Fill默认为空格
rpad('hi', 5, 'xy') hixyx
rtrim(string text [, characters text]) 从字符串String的末尾删除只包含Characters中字符的最长的字符串。
说明 如果没有指定Characters的值,则Characters默认是空格。
rtrim('testxxzx', 'xyz') test
strpos(string, substring) 返回Substring在String中的位置。 strpos('high', 'ig') 2
substr(string, from [, count]) 从字符串中找出指定的子字符串。 substr('alphabet', 3, 2) ph
starts_with(string, prefix) 如果字符串以前缀开头,则返回true starts_with('alphabet', 'alph') true
to_hex(number int or bigint) 将数字转换为十六进制的表示形式。 to_hex(2147483647) 7fffffff
translate(string text, from text, to text) 使用字符串To中的字符替换From中的字符。 translate('12345', '143', 'ax') a2x5

数学函数

函数名 描述 用例 结果
abs(bigint) 返回BIGINT类型表达式的绝对值。 abs(-17) 17
abs(int) 返回INT类型表达式的绝对值。 abs(-17) 17
abs(float8) 返回FLOAT8类型表达式的绝对值。 abs(-17.4) 17.4
abs(float4) 返回FLOAT4类型表达式的绝对值。 abs(-17.4) 17.4
abs(numeric) 返回NUMERIC类型表达式的绝对值。 abs(-17.4) 17.4
cbrt(dp) 返回DP类型表达式的立方根。 cbrt(27.0) 3.0000000000000004
ceil(dp ) 对DP类型的表达式向上取整。 ceil(-42.8) -42.0
ceil(numeric) 对NUMERIC类型的表达式向上取整。 ceil(-42.8) -42.0
ceiling(dp ) 对DP类型的表达式向上取整。 ceil(-42.8) -42.0
ceiling(numeric) 对NUMERIC类型的表达式向上取整。 ceil(-42.8) -42.0
degrees(dp) 将DP类型表达式的弧度转换为角度。 degrees(0.5) 28.64788975654116
exp(dp) 返回DP类型表达式的指数。 exp(1.0) 2.718281828459045
exp(numeric) 返回NUMERIC类型表达式的指数。 exp(1.0) 2.718281828459045
floor(dp ) 对DP类型表达式向下取整。 floor(-42.8) -43.0
floor(numeric) 对NUMERIC类型表达式向下取整。 floor(-42.8) -43.0
ln(dp) 返回DP类型表达式的自然对数。 ln(2.0) 0.6931471805599453
ln(numeric) 返回NUMERIC类型表达式的自然对数。 ln(2.0) 0.6931471805599453
log(dp) 返回DP类型表达式的常用对数。 log(100.0) 2.0
log(numeric) 返回NUMERIC类型表达式的常用对数。 log(100.0) 2.0
log(b numeric, x numeric) 返回NUMERIC类型表达式的对数。 log(2.0, 64.0) 6.0
mod(bigint, x) 求BIGINT类型表达式除以x的余数。 mod(9,4) 1
mod(int, x) 求INT类型表达式除以x的余数。 mod(9,4) 1
pi() 返回π常量。 pi() 3.141592653589793
power(a dp, b dp) 求a的b次幂,a和b使用DP类型的表达式。 power(9.0, 3.0) 729.0
power(a numeric, b numeric) 求a的b次幂,a和b使用NUMERIC类型的表达式。 power(9.0, 3.0) 729.0
radians(dp) 将DP类型表达式的角度转换为弧度。 radians(45.0) 0.7853981633974483
round(dp ) 返回DP类型表达式四舍五入后的整数值。 round(42.4) 42.0
round(numeric) 返回NUMERIC类型表达式四舍五入后的整数值。 round(42.4) 42.0
round(v numeric, s int) 保留小数位数字到s位。 round(42.4382, 2) 42.44
sign(dp ) 返回DP类型表达式的符号。参数值大于0返回1,小于0返回 -1 ,等于0返回0 sign(-8.4) -1
sign(numeric) 返回NUMERIC类型表达式的符号。参数值大于0返回1,小于0返回 -1 ,等于0返回0 sign(-8.4) -1
sqrt(dp) 返回DP类型表达式的平方根。 sqrt(2.0) 1.414213562373095
sqrt(numeric) 返回NUMERIC类型表达式的平方根。 sqrt(2.0) 1.414213562373095
trunc(dp) 去掉DP类型表达式的小数位。 trunc(42.8) 42.0
trunc(numeric) 去掉NUMERIC类型表达式的小数位。 trunc(42.8) 42.0
trunc(v numeric, s int) 截断NUMERIC类型表达式的小数位置到s位。 trunc(42.4382, 2) 42.43
width_bucket(operand numeric, b1 numeric, ...) 返回OPERAND在BUCKET中的位置。 width_bucket(5.35, 0.024, 10.06, 5) 3
random() 获取一个随机数,返回值范围为[0.0,1.0)。 random() 0.3977345246821642

聚合函数

函数名 描述 用例 结果
array_agg(bigint) 将BIGINT类型表达式的值串联到数组中。 array_agg(c1) {1,2}
array_agg(bool) 将BOOL类型表达式的值串联到数组中。 array_agg(c1) {true,false}
array_agg(text) 将TEXT类型表达式的值串联到数组中。 array_agg(c1) {a,b}
array_agg(float8) 将FLOAT8类型表达式的值串联到数组中。 array_agg(c1) {1.1,2.2}
array_agg(float4) 将FLOAT4类型表达式的值串联到数组中。 array_agg(c1) {1.1,2.2}
array_agg(int) 将INT类型表达式的值串联到数组中。 array_agg(c1) {1, 2}
avg(bigint) 求BIGINT类型表达式中非空值的平均值。 avg(c1) 2.000000
avg(float8) 求FLOAT8类型表达式中非空值的平均值。 avg(c1) 2.000000
avg(float4) 求FLOAT4类型表达式中非空值的平均值。 avg(c1) 2.000000
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.0
max(float4) 求FLOAT4类型表达式的最大值。 max(c1) 3.0
max(int) 求INT类型表达式的最大值。 max(c1) 3
max(numeric) 求NUMERIC类型表达式的最大值。 max(c1) 3.0
min(bigint) 求BIGINT类型表达式的最小值。 min(c1) 1
min(float8) 求FLOAT8类型表达式的最小值。 min(c1) 1.0
min(float4) 求FLOAT4类型表达式的最小值。 min(c1) 1.0
min(int) 求INT类型表达式的最小值。 min(c1) 1
min(numeric) 求NUMERIC类型表达式的最小值。 min(c1) 1.0
sum(bigint) 求BIGINT类型表达式所有值的总和。 sum(c1) 6
sum(float8) 求FLOAT8类型表达式所有值的总和。 sum(c1) 6.0
sum(float4) 求FLOAT4类型表达式所有值的总和。 sum(c1) 6.0
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(X*Y) - 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)