SQL分析任务支持的函数

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

背景信息

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

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

时间和日期函数

函数名返回类型描述用例结果
current_dateDATE获取当前日期。
select current_date;
2020-05-03
current_timestampTIMESTAMPTZ获取当前事务的开始时刻。
说明 在事务的整个运行周期内不改变。
select current_timestamp;
2020-05-03 06:33:36.113682+08
clock_timestamp()TIMESTAMPTZ获取当前时刻。
说明 在同一条命令中也会发生变化。
select clock_timestamp();
2020-05-03 06:32:28.814918+08
localtimestampTIMESTAMP获取不包含时区的当前时间。
select localtimestamp;
2020-08-21 12:02:21.178031
now()TIMESTAMPTZ获取当前事务的开始时刻,等效于transaction_timestamp()
说明 在事务的整个运行周期内不改变。
select now();
2020-05-03 06:38:48.492168+08
statement_timestamp()TIMESTAMPTZ获取当前语句的开始时刻。
说明 在事务的不同命令中返回值不同。
select statement_timestamp();
2020-05-05 06:39:11.125957+08
timeofday()TEXT获取当前时刻。
说明 clock_timestamp()类似,但时间使用格式化文本字符串格式。
select timeofday();
Tue May 03 06:39:43.195368 2020 CST
transaction_timestamp()TIMESTAMPTZ获取当前事务的开始时刻,等效于current_timestamp
说明 在事务的整个运行周期内不改变。
select transaction_timestamp();
2020-05-03 06:40:08.023623+08

字符串函数

函数名返回类型描述用例结果
to_number(text, text)NUMERIC转换字符串为数字。
select to_number('12,454.8-', '99G999D9S');
12454.8
string_agg(expression,delimiter)TEXT使用指定分隔符将指定表达式的非空值串联成字符串。可作为列转行使用。
create table city_test(
  country text,
  city text
);
insert into city_test values ('中国' ,'上海'),('中国','台湾'),('日本','东京'),('法国','巴黎'),('英国','伦敦');
select string_agg(city,',') from city_test;
string_agg
------------------------
上海,台湾,东京,巴黎,伦敦
string_agg (expression [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]TEXT使用指定分隔符将指定表达式的非空值串联成字符串。可作为列转行使用,同时在表达式中支持filter过滤条件。
说明 Hologres V1.3及以上版本支持。
create table name_text(student_id int,name text)
insert into name_text values (1,'张三'),(2,'李四'),(3,'王五'),(4,'周六');
select string_agg(name,',') filter (where student_id >2) from name_text ;
string_agg
----------
王五,周六
regexp_split_to_table(string text, pattern text)TEXT字符串分割函数,将分割出的数据转换成行,可作为行转列使用。
create table  interests_test(name text,intrests text);
insert into interests_test values ('张三','唱歌,跳舞'),('李四','踢球,跑步,画画'),('王五','插花,书法,弹琴,睡觉');
select name,regexp_split_to_table(intrests, ',') from interests_test;
name | regexp_split_to_table
---------------------------
张三 | 唱歌
张三 | 跳舞
李四 | 踢球
李四 | 跑步
李四 | 画画
王五 | 插花
王五 | 书法
王五 | 弹琴
王五 | 睡觉
regexp_split_to_array(string text, pattern text)ARRAY字符串分割函数,将分割出的数据转换成数组,可作为行转数组使用。
create table  interests_test(name text,intrests text);
insert into interests_test values ('张三','唱歌,跳舞'),('李四','踢球,跑步,画画'),('王五','插花,书法,弹琴,睡觉');
select name,regexp_split_to_array(intrests, ',') from interests_test;
name | regexp_split_to_array
----------------------------
张三 | {唱歌,跳舞}
李四 | {踢球,跑步,画画}
王五 | {插花,书法,弹琴,睡觉}
replace(string text, old_text, new_text) TEXT将字符串中原有的子字串替换成新的子字串。
create table animal_test(
  animal text,
  color text );
insert into animal_test values ('狗','白色'),('猫','白色'),('老虎','黄色');
select animal,replace(color,'白','五彩斑斓') from animal_test;
animal |  replace   
-------------------
狗     | 五彩斑斓色
猫     | 五彩斑斓色
老虎   | 黄色
regexp_replace(string, regex, replacement[, flags]) TEXT通过POSIX 正则表达式将子字符串替换为新的子字符串。
create table a_test(a text);
insert into a_test values ('Abcd1234abCd');
SELECT regexp_replace(a, '1234.', '77', 'ig') from a_test;
regexp_replace 
--------------
Abcd77bCd
regexp_match(string text, pattern text) ARRAY对字符串按正则表达式进行匹配,如果存在则会在结果数组中表示出来。
select regexp_match('foobarbequebaz', '(bar)(beque)')
regexp_match
------------
{bar,beque}
row_to_jsonJSON将一行数据转换为JSON格式。
说明 Hologres V1.3及以上版本支持。
create table  interests_test(name text,intrests text);
insert into interests_test values ('张三','唱歌,跳舞'),('李四','踢球,跑步,画画'),('王五','插花,书法,弹琴,睡觉');
select row_to_json(t) from (select name,intrests from interests_test) as t;
row_to_json                
------------------------------
{"f1":"张三","f2":"唱歌,跳舞"}
{"f1":"李四","f2":"踢球,跑步,画画"}
{"f1":"王五","f2":"插花,书法,弹琴,睡觉"}
row()ARRAY/TEXT返回公式所在的行号。
说明 Hologres V1.3及以上版本支持。
create table row_test(a text,b text);
insert into row_test values('0','0'),('1','0');
select a,b from row_test where row(a,b)=row(b,a);
a | b 
------
0 | 0
函数名返回类型描述用例结果
split_part(string text ,delimiter text,n int)TEXT使用指定的分隔符拆分字符串,并返回第n个字串。
create table split_part_test(a text);
insert into split_part_test values ('a/b/c/d/e'),('a1/b1/c1/d1/e1');
select split_part(a, '/', 2) from split_part_test;
split_part 
----------
b
b1
concat(str "any" [, str "any" [, ...] ])TEXT连接所有参数。忽略NULL参数。
select concat('abcde', 2, NULL, 22);
concat
---------
abcde222
concat_ws(sep text, str "any" [, str "any" [, ...] ])TEXT使用分隔符连接除第一个参数外的所有参数。
说明 第一个参数用作分隔符字符串。忽略NULL参数。
select concat_ws(',', 'abcde', 2, NULL, 22);
concat_ws
----------
abcde,2,22
substring(string [from int] [for int])TEXT从字符串中找出指定的子字符串。
select substring('Thomas' from 2 for 3);
substring
----------
hom
substring(string from pattern)TEXT从字符串中找出与POSIX正则表达式匹配的子字符串。
select substring('Thomas' from '...$');
substring
----------
mas
substring(string from pattern for escape)TEXT从字符串中找出与SQL正则表达式匹配的子字符串。
select substring('Thomas' from '%#"o_a#"_' for '#');
substring
----------
oma
left(string text, n int)TEXT返回字符串的前n个字符。n为负数时,返回除最后-n个字符之外的所有字符。
select left('hologres', 4);
left
----
holo
right(string text, n int)TEXT返回字符串的后n个字符。n为负数时,返回除前-n个字符之外的所有字符。
select right('hologres', 4);
right
----------
gres
string || stringTEXT连接两个字符串。
select 'Holo' || 'greSQL';
HologreSQL
bit_length(string) TEXT获取字符串的位长度。
select bit_length('jose');
32
char_length(string)TEXT获取字符串的字符长度。
select char_length('jose');
4
length(string)TEXT获取字符串的字节长度,使用utf8编码字符集时,一个汉字是3字节,一个数字或字母算一个字节。
说明 length函数的计算结果和char_length函数相同,因为英文字符的个数和所占字节相同,一个字符占一个字节。
select length('jose');
4
lower(string)TEXT转换字符串为小写格式。
select lower('TOM');
tom
upper(string)TEXT转换字符串为大写格式。
select upper('tom');
TOM
initcap(string)TEXT将每个单词的第一个字母转换为大写,其余字母转换为小写。
说明 单词是由一系列字母和数字组成的字符,使用非字母或数字分隔。
select initcap('hi THOMAS');
Hi Thomas
octet_length(string)TEXT返回字符串的字节数。
select octet_length('jose');
4
position(substring in string)TEXT查找子字符串在字符串中的位置。
select position('om' in 'Thomas');
3
strpos(string, substring)TEXT查找子字符串在目标字符串中的位置。
select strpos('high', 'ig');
2
trim([leading | trailing | both] [characters] from string)TEXT从字符串String的开始、结尾或两端删除仅包含Characters中字符的最长字符串。
说明
  • 默认从两端删除。
  • 默认仅包含Characters中字符的最长字符串为空格
select trim(both 'xyz' from 'yxTomxx');
Tom
btrim(string text [, characters text])TEXT从字符串String的开始和结尾删除仅包含Characters中字符的最长字符串。
说明 默认仅包含Characters中字符的最长字符串为空格
select btrim('xyxtrimyyx', 'xyz');
trim
ltrim(string text [, characters text])TEXT从字符串String的开始删除只包含Characters 中字符的最长的字符串。
说明 如果没有指定Characters的值,则Characters默认是空格。
select ltrim('zzzytest', 'xyz');
test
rtrim(string text [, characters text])TEXT从字符串String的末尾删除只包含Characters中字符的最长的字符串。
说明 如果没有指定Characters的值,则Characters默认是空格。
select rtrim('testxxzx', 'xyz');
test
lpad(string text, length int [, fill text])TEXT用Fill填充在String头部,将String填充为长度是Length的字符串。
说明
  • 如果String的长度已经超过Length,则从右侧将String截断为长度是Length的字符串。
  • 如果没有指定Fill的值,则Fill默认为空格
select lpad('hi', 5, 'xy');
xyxhi
rpad(string text, length int [, fill text])TEXT对字符串的右边进行填充,填充内容为指定长度的字符串。
select rpad('hi', 5, 'xy');
hixyx
md5(string)TEXT计算String的MD5哈希值。结果表示为十六进制的形式。
select md5('abc');
900150983cd24fb0d6963f7d28e17f72
parse_ident(quali_iden text [,...] )TEXT解析字符串。
select parse_ident('"SomeSchema".someTable');
{SomeSchema,sometable}
quote_ident(string text)TEXT使用String作为合法的SQL标识符。
说明 当字符串包含非标识符字符或者字符串会转换大小写时,需要添加引号。
select quote_ident('Foo bar');
"Foo bar"
quote_literal(string text)TEXT将String转换为合法的SQL语句字符串的常量形式。
select quote_literal(E'O\'Reilly');
'O''Reilly'
ascii(string)TEXT返回参数第一个字符的ASCII码。
select ascii('x');
120
chr(int)TEXT返回指定编码值对应的字符。
说明 参数必须是合法的ASCII或UTF8编码值,并且参数值不能为0
select chr(65);
A
repeat(string text, number int)TEXT将String重复指定Nnumber次。
select repeat('Pg', 4);
PgPgPgPg
starts_with(string, prefix)BOOLEAN如果字符串以前缀开头,则返回true;否则返回false
select starts_with('alphabet', 'alph');
t
to_hex(number int or bigint)TEXT将数字转换为十六进制的表示形式。
select to_hex(2147483647);
7fffffff
translate(string text, from text, to text)TEXT使用字符串To中的字符替换From中的字符。
select 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位小数,计算的类型必须是numeric类型。round((100/400::numeric),4);0.2500
sign(dp )返回DP类型表达式的符号。参数值大于0返回1,小于0返回 -1 ,等于0返回0sign(-8.4)-1
sign(numeric)返回NUMERIC类型表达式的符号。参数值大于0返回1,小于0返回 -1 ,等于0返回0sign(-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(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.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/Nregr_sxx(c1, c2)
regr_sxy(Y, X)求自变量和因变量的乘积和sum(X*Y) - sum(X) * sum(Y)/Nregr_sxy(c1, c2)
regr_syy(Y, X)求因变量的平方和sum(Y^2) - sum(Y)^2/Nregr_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)
阿里云首页 阿里云物联网平台 相关技术圈