跳到主要内容
版本:2.13.0

标量函数 ( Scalar Function )

标量函数的参数可以是字段或者常量,标量函数完成计算之后会生成一个新的字段。

例如:

  1. 对字段host可以使用标量函数UPPER将值转换成大写。

    SELECT UPPER(_host) AS upper_case_host FROM _internal
  2. 嵌套使用标量函数,对url取子串,并转成大写。

    SELECT UPPER(SUBSTR(url, 5)) AS host FROM data_set

预定义标量函数列表

函数名签名函数功能
ABS(<x>)计算x的绝对值
ACOS(<x>)计算x的反余弦值
ARRAY_APPEND(<multi_value>, <element>)向数组multi_value的尾部追加一个元素element
ARRAY_APPEND_AT(<multi_value>, <element>,<index>)在数组的第index处插入一个元素,注意:index从0开始
ARRAY_CAT(<multi_value1>,<multi_value2>)将两个数组合并为一个数组
ARRAY_CONTAINS(<multi_value>, <element>)检查数组multi_value是否包含某个元素element
ARRAY_DISTINCT(<multi_value>)返回一个新的数组,其中只包含输入数组中的不同元素。该函数将排除输入的数组中的任何重复元素
ARRAY_GENERATE_RANGE(<start>,<stop>,<step>)start:返回的数字范围中的第一个数字;stop:范围内的最后一个数字。请注意,这个数字不包括在返回的数字范围内。step:数组中每个后续数字的递增或递减量。注意,该数字只能为整数
ARRAY_JOIN(<multi_value>,<delimiter>)使用分隔符delimiter将数组multi_value连接起来,并返回一个字符串。注意,分隔符delimiter的类型为字符串
ARRAY_LENGTH(<multi_value>)计算数组的元素个数
ARRAY_MAX(<multi_value>)计算数组的最大值
ARRAY_MIN(<multi_value>)计算数组的最小值
ARRAY_POSITION(<multi_value>, <element>)返回数组multi_value中元素 element首次出现的索引。注意,索引从0开始
ARRAY_PREPEND(<multi_value>, <element>)向数组multi_value的头部追加一个元素element
ARRAY_REGEX_LIKE(<multi_value>, <regex_expr>)对数组multi_value的每个元素应用正则表达式regex_expr,符合正则表达式regex_expr的元素将被保留到作为结果输出的数组中。注意:该正则表达式应符合posix regex正则表达式标准
ARRAY_REMOVE_AT(<multi_value>, <index>)在数组的第index处移除该元素,注意:index从0开始
ARRAY_SLICE(<multi_value>, <from>,<to>)返回输入的数组multi_value的子数组。from:源数组中的某个索引。第一个元素的位置为 0,小于 from 位置的元素不包含在结果数组中。to:源数组中的某个索引。结果数组中不包含来自等于或大于 to 的位置的元素。
ARRAY_SORT(<multi_value>, <sort_ascending>)返回一个数组,其中包含按升序或降序排序的输入multi_value的元素。sort_ascending指定 "true",按升序对元素排序。sort_ascending指定 "false",按降序对元素排序。
ARRAY_SPLIT(<string>,<separator>)用给定的string类型的分隔符separator分割给定的字符串,并将结果以数组的形式返回。
ASCII(<x>)返回x的ASCII编码值
ASIN(<x>)计算x的反正弦值
ATAN(<x>)计算x的反正切值
BIT_LENGTH(<str>)计算给定字符串的比特位数
BIN(<int>)计算给定整数的二进制字符串
BITWISE_AND(<x>, <y>)计算给定两个数值的按位与
BITWISE_NOT(<x>)计算给定数值的按位取反
BITWISE_OR(<x>, <y>)计算给定两个数值的按位或
BITWISE_XOR(<x>, <y>)计算给定两个数值的按位异或
BROUND(<x>)HALF_EVEN规则x进行取整运算
BTRIM(<str>)去除字符串两端的空格
BTRIM(<str>, <chars>)去除字符串两端的字符,如果这些字符在第二个参数给出的字符集合内
CBRT(<x>)计算给定输入的立方根
CEIL(<x>)计算x向上取整值
CHAR_LENGTH(<str>)返回字符串的长度
CHARACTER_LENGTH(<str>)CHAR_LENGTH(<str>)
CHR(<num>)返回参数num对应的ASCII字符
CIDR_MATCH(<ip_field>, <cidr_str>)ip_field字段使用cidr_str进行过滤
COALESCE(<opt_1>, <opt_2>, ...)返回所给参数中第一个非空的值,可用作填充空值空能
CONCAT(<str1>, <str2>, ...)把字符串str1, str2, ... 串联在一起,最多可连接10个字符串
CONCAT_WS(<con>, <str1>, <str2>, ...)把字符串str1, str2, ... 用连接符con串联在一起,最多可连接5个字符串
CONTAINS(<str>)检查原始数据中是否存在关键字strstr必须是字符串类型的字面量,且大小写不敏感
COS(<x>)计算x的余弦值
COSH(<x>)计算x的双曲余弦函数值
COT(<x>)计算x的余切值
CRC32(<str>)计算strCRC32
DATE_ADD(<time_unit>, <delta>)基于当前的系统时间,增加时间的偏移量;<time_unit>只能是常量字符串s,m,h,d等,可参考时间字面量;delta是一个整数类型的表达式,表示要改变的时间偏移量
DATE_ADD(<time_unit>, <delta>, <base_timestamp>)在给定出的<base_timestamp>的基础上,增加时间的偏移量;<time_unit>只能是常量字符串s,m,h,d等,可参考时间字面量;delta是一个整数类型的表达式,表示要改变的时间偏移量;base_timestamp是时间戳类型的表达式,可参考时间字面量
DATE_DIFF(<time_unit>, <start_timestamp>, <end_timestamp>)计算给定时间单位上两个时间戳的差值 end_timestamp - start_timestamp<time_unit>只能是常量字符串s,m,h,d等,可参考时间字面量;例如 DATE_DIFF('year', '2021-02-02T00:00:00', '2022-03-02T00:00:00')得到结果1
DATE_PART(<time_unit>, <timestamp>)计算给定时间单位上时间戳对应的值;<time_unit>可以是常量字符串s,m,h,d等,可参考时间字面量,此外还可以是millennium,century,decade,doy(day of year),dow(day of week),dom(day of month),woy(week of year);例如 DATE_PART('year', '2021-02-02T00:00:00')得到结果2021
DATE_TRUNC(<time_unit>, <timestamp>)计算精确到给定时间单位上时间戳的值;<time_unit>只能是常量字符串s,m,h,d等,可参考时间字面量;例如 DATE_TRUNC('year', '2021-02-02T00:00:00')得到结果2021-01-01T00:00:00
DEGREES(<x>)计算弧度x对应的角度值
ELT(<idx>, <str1>, <str2>, ...)返回给定字符串str1,str2 ...(最多可有5个选项)中第idx个选项
ENDS_WITH(<str>, <sub_str>)检测字符串str是否以sub_str结尾,返回boolean;如果返回true,就表示第一个参数的值的结尾是sub_str;如果是false,则表示不是以sub_str结尾
EXP(<x>)计算 $e^x$,x 表示参数,e 是欧拉常数(Euler's Constant),自然对数的底数
FACTORIAL(<x>)计算x的阶乘
FLOOR(<x>)计算x向下取整值
GREATEST(<opt_1>, <opt_2>, ...)返回所给参数中的最大值
HASH(<input>)计算给出的字段的32位整数哈希值,参数可以是一个常量
HASH32(<input>)HASH(<input>)
HASH64(<input>)计算给出的字段的64位整数哈希值,参数可以是一个常量
HASH_MD5(<input>)计算给出的字段的MD5加密哈希值,参数可以是一个常量
HASH_SHA1(<input>)计算给出的字段的SHA1加密哈希值,参数可以是一个常量
HASH_SHA256(<input>)计算给出的字段的SHA256加密哈希值,参数可以是一个常量
HEX(<input>)计算input的16进制的值
ILIKE(<source>, <pattern>)返回source是否符合pattern的格式;pattern_表示任意字符,%表示任意字符串,大小写不敏感
INITCAP(<input>)返回将input转成单词首字母大写的字符串;例如 INITCAP('server FAILED On tUesDAy')返回'Server Failed On Tuesday'
INT_TO_IP(<input>)计算给出的整数对应的ip地址;例如 INT_TO_IP(3232287244)返回'192.168.202.12'
INT_TO_IP(<input>, <is_from_low_to_high>)计算给出的整数对应的ip地址,如果is_from_low_to_highTRUE将按照从低位到高位处理给定的整数;例如INT_TO_IP(3232287244, TRUE)返回'12.202.168.192'INT_TO_IP(3232287244, FALSE)返回'192.168.202.12'
IP_TO_INT(<input>)计算给出的ip地址字符串对应的整数值;例如 IP_TO_INT('192.168.202.12')返回3232287244
IP_TO_INT(<input>, <is_from_low_to_high>)计算给出的ip地址字符串对应的整数值,如果is_from_low_to_highTRUE将按照从低位到高位处理返回的整数;例如IP_TO_INT('192.168.202.12', TRUE)返回214608064IP_TO_INT('192.168.202.12', FALSE)返回3232287244
IS_SUBSTR(<str>, <str_sub>)检测字符串str是否包含sub_str,返回boolean;如果返回true,则表示包含,否则是不包含
JSON_POINTER(str1, str2)解析json字符串str1,并用str2所表达的json pointer路径返回json文档的值,结果为字符串类型
JSON_POINTER_MV(str1, str2)解析json字符串str1,并用str2所表达的json pointer路径返回json文档的值,结果为字符串的多值类型
LEFT(<source>, <chars_num>)返回source左边chars_num个字符串
LEAST(<opt_1>, <opt_2>, ...)返回所给参数中的最小值
LENGTH(<str>)CHAR_LENGTH(<str>)
LEVENSHTEIN(<str1>, <str2>)计算str1str2LEVENSHTEIN距离
LOCATE(<sub_str>, <str>)查找sub_strstr中第一次出现的位置;注意,位置是从1开始的;例如LOCATE('he', 'hello')返回1;如果sub_strstr没有出现,返回0
LOCATE(<sub_str>, <str>, <start_position>)sub_str的第start_position个字符开始查找,查找sub_strstr中第一次出现的位置;例如LOCATE('ll', 'hello', 2)返回3,LOCATE('ll', 'hello', 4)返回0
LOG(<x>)计算一个数的自然对数, $ln(x)$,x 表示参数,对数的底数是欧拉常数(Euler's Constant)
LOG(<base>, <x>)计算一个数的对数, 第一个参数是对数的底数
LOG10(<x>)计算一个数的10位底数的对数, $LOG(x)$,x表示参数,对数的底数是10
LOWER(<str>)将输入的字符串转换成小写
LPAD(<source>, <chars_num>, <padstr>)source左侧填充字符串padstr,直到总字符串长度达到chars_num后返回结果
LPAD(<source>, <chars_num>)等价于LPAD(<source>, <chars_num>, ' ')
LTRIM(<str>)去除输入字符串开头的空格
LTRIM(<str>, <chars>)去除输入字符串开头的字符,如果这些字符在第二个参数给出的字符集合内
MASK_FIRST_N(<str>, <a>)str中的前a个字符用字符 x 或 n 代替
MASK_LAST_N(<str>, <a>)str中的后a个字符用字符 x 或 n 代替
MOD(<x>, <y>)计算xy取模的值
NOW()返回系统当前时间
NULLIF(<x>, <y>)如果x等于y,返回空值,否则返回x的值
OCTET_LENGTH(<str>)返回字符串所占字节的长度
PMOD(<x>, <y>)返回xy的正取余值
POSITION(<sub_str>, <str>)LOCATE(<sub_str>, <str>)
POSITION(<sub_str>, <str>, <start_position>)LOCATE(<sub_str>, <str>, <start_position>)
POW(<x>, <y>)幂次方运算, 相当于计算 $x^y$ 第一个参数是幂次方的底数
POWER(<x>, <y>)POW(<x>, <y>)
QUOTE(<str>)返回str被单引号引用的结果
RADIANS(<x>)计算角度x对应的弧度值
RAND()生成一个[0, 1)之间的随机数;范围包括0,但是不包括1;在同一投影(Projection)中同时调用多次的结果并不相同
RAND(<int>)生成一个[0, 1)之间的随机数;范围包括0,但是不包括1;在同一投影(Projection)中同时调用多次,且传入的int值相同,则结果相同;int必须是字面量整数
RANDOM()RAND()
RANDOM(<int>)RAND(<int>)
REGEXP_REPLACE(<str>, <regex>, <rep>)str中匹配正则表达式regex的部分用rep替换
REGEX_LIKE(<source>,<regex>,<parameter>)返回source是否符合regex表达式。总计有4个参数可以组合选择:(i,c,n,m)。其中'i' 表示不区分大小写的匹配。'c' 表示区分大小写的匹配。'n' 允许点(.),即匹配任何字符的通配符字符,匹配换行符。如果省略此参数,则点不匹配换行符。'm' 将源字符串视为多行。炎凰数据平台将 ^ 和 $ 解释为源字符串中的任何位置的行的开头和结尾,而不仅仅是整个源字符串的开头或结尾。如果省略此参数,则将源字符串视为单行。例如REGEXP_LIKE (last_name, '([aeiou])\1', 'inm');
REMOVE_CHARS(<str>, <chars_to_remove>)chars_to_remove中的每个字符从str中移除;例如,REMOVE_CHARS('$960,000.00', '$,')返回'960000.00'
REPEAT(<str>, <x>)返回str重复x次的结果
REPLACE(<str>, <str1>, <str2>)str中所有出现的子串str1替换成str2
REVERSE(<str>)将输入的字符串反转
RIGHT(<source>, <chars_num>)返回source右边chars_num个字符串
ROUND(<x>)计算x的约数,只取整数部分,小数部分四舍五入
ROUND(<x>, <y>)计算x的约数,即四舍五入值,y为保留/舍弃的位数;当y大于零时,y表示保留小数点后的位数;当y小于零时,y的绝对值表示舍弃小数点前的位数;当y等于零或者缺省时,表示舍弃所有小数且仅保留整数部分
RPAD(<source>, <chars_num>, <padstr>)source右侧填充字符串padstr,直到总字符串长度达到chars_num后返回结果
RPAD(<source>, <chars_num>)等价于RPAD(<source>, <chars_num>, ' ')
RTRIM(<str>)去除字符串结尾的空格
RTRIM(<str>, <chars>)去除字符串结尾的字符,如果这些字符在第二个参数给出的字符集合内
SIN(<x>)计算x的正弦值
SINH(<x>)计算x的双曲正弦函数值
SOUNDEX(<str>)返回strSOUNDEX值
SPACE(<num>)返回num个空白符' '
SPLIT_PART(<base_str>, <split_str>, <index>)split_str把SPLIT_PARTbase_str分割,返回第index个分割值;例如SPLIT_PART('hello', 'll', 1)返回'he'
STARTS_WITH(<str>, <sub_str>)检测字串str是否以sub_str开头,返回boolean;如果返回true,就表示第一个参数的值的开头是sub_str;如果是false,则表示不是以sub_str开头
STRFTIME(<microsecond_since_epoch>, <format>)把epoch以微秒为单位的时间戳microsecond_since_epoch根据给定的格式format转换成时间戳字符串,时区为偏好设置中的时区format支持的格式可以参考这里。具体例子可以看这里
STRFTIME(<microsecond_since_epoch>, <format>, <timezone>)把epoch以微秒为单位的时间戳microsecond_since_epoch根据给定的格式format和时区timezone转换成时间戳字符串;format支持的格式可以参考这里timezone参考这个页面TZ database name。具体例子可以看这里;某些场景中,亦可实现日期分桶的效果,参考用例
STRPTIME(<timestamp_string>, <format>)把时间戳字符串timestamp_string根据给定的格式format转换成时间戳字符串以**微秒为单位的时间戳,如给定的格式format中没有指定时区信息,将按照偏好设置中的时区处理;format支持的格式可以参考这里
STRPTIME(<timestamp_string>, <format>, <timezone>)把时间戳字符串timestamp_string根据给定的格式format和时区timezone转换成时间戳字符串以**微秒为单位的时间戳,如format中有合法的时区信息,将忽略timezone的内容;format支持的格式可以参考这里timezone参考这个页面TZ database name。具体例子可以看这里
SUBSTR(<str>, <start>)返回第一个参数输入的字符串的一部分;start是一个整数,表示返回的子串在str的起始位置,如果start>0,则表示从头数的位数,需要注意的是位数从1开始;如果start<0, 则表示从字符串尾部开始向前数的位数;子串一直从起始位置到字符串结束; 例如:SUBSTR('hello', 1)返回helloSUBSTR('hello', -2)返回lo
SUBSTR(<str>, <start>, <len>)返回第一个参数输入的字符串的一部分;start是一个整数,表示返回的子串在str的起始位置,如果start>0,则表示从头数的位数,需要注意的是位数从1开始;如果start<0, 则表示从字符串尾部开始向前数的位数;len是子串的长度; 例如:SUBSTR('hello', 1, 2)返回heSUBSTR('hello', -2, 3)返回lo
SUBSTRING(<str>, <start>)SUBSTR(<str>, <start>)
SUBSTRING(<str>, <start>, <len>)SUBSTR(<str>, <start>, <len>)
TAN(<x>)计算x的正切值
TANH(<x>)计算x的双曲正切函数值
TIME_BUCKET(<time_unit>, <field>)field字段按照time_unit进行时间聚合,支持的单位为秒s,分钟m,小时h,天d,周w,月M,季度q,年y。例如TIME_BUCKET('1d', _time)可以将_time字段的时间聚合到以1天为单位的时间上。时间单位之前必须是一个正整数
TIME_BUCKET(<time_unit>, <field>, <origin_timestamp>)将桶的起始时间以origin_timestamp对齐,例如TIME_BUCKET('1d', _time,'2021-12-21T00:06:40.000+08:00')可以将_time字段的时间聚合到以1天为单位的时间上并以2021-12-21T00:06:40.000+08:00时间戳为分桶起点,假设_time2021-08-07T09:12:13.000+08:00,则会输出2021-08-07T00:06:40.000+08:00
TRUNC(<x>)计算x的舍位值,仅保留整数部分
TRUNC(<x>, <y>)计算x的舍位值,y为保留/舍弃的位数;当y大于零时,y表示保留小数点后的位数;当y小于零时,y的绝对值表示舍弃小数点前的位数;当y等于零或者缺省时,表示舍弃所有小数且仅保留整数部分
TRUNCATE(<x>)TRUNC(<x>)
TRUNCATE(<x>, <y>)TRUNC(<x>, <y>)
UPPER(<str>)将输入的字符串转换成大写

标量函数使用示例

coalesce填充空值

WITH cte AS (
SELECT
'Ann' AS name
FROM
generate_series(1, 3)
UNION
ALL
SELECT
*
FROM
generate_series(1, 2)
)
SELECT
name AS old_name,
coalesce(name, 'Ted') AS name
FROM cte

返回结果集:

old_namename
nullTed
nullTed
AnnAnn
AnnAnn
AnnAnn

时间标量函数示例

  1. 当前时间

    SELECT NOW() FROM main LIMIT 1

    -- 返回结果集
    -- | NOW() |
    -- | 2021-09-24 11:57:59.563 |
  2. 将时间字段_time按一小时分桶,并将分桶值转换成可读时间

    SELECT _time, strftime(time_bucket('1h', _time), '%Y/%m/%d %H:%M') as tb FROM main ORDER BY _time

    返回结果集:

    _timetb
    14499411110000002015/12/12 17:00
    14499411110000002015/12/12 17:00
    14499414680000002015/12/12 17:00
    14499789120000002015/12/13 03:00
    14499799160000002015/12/13 04:00
    14500019740000002015/12/13 10:00
    14500019750000002015/12/13 10:00
    14500025210000002015/12/13 10:00
    14501900530000002015/12/15 14:00
    14501900570000002015/12/15 14:00

SQL 用户定义标量函数 ( User Define Scalar Function )

SQL用户定义标量函数操作语法如下:

-- 创建标量函数
CREATE [ OR REPLACE ] FUNCTION
function_name ( [ named_parameter ]* ) RETURNS return_type
RETURN expression

-- 删除标量函数
DROP FUNCTION function_name( [signature])

-- 列出用户定义标量函数
SHOW FULL FUNCTIONS where return_type != 'TABLE'
  • named_parameter@<parameter_name> <parameter_type> [default|= <default_value>]组成, 目前支持的参数类型有:STRING,INT,FLOAT,BOOL,参数列表可省略,参数默认值可省略
  • return_type :目前支持的返回类型有:STRING,INT,FLOAT,BOOL,DOUBLE,BOOLEAN
  • 允许创建同名不同参数个数列表的函数,但不支持同名函数,同样参数个数,不同参数类型的表函数,这主要源于炎凰数据平台系统可以自适应转换数据类型
  • 删除函数定义时,需要指定函数签名(signature)。如果只有一个同名函数的话,暂时也不支持省略参数列表。如果删除时给了正确的参数个数,但是类型不匹配的话系统将报错提示
  • 使用函数列表的默认值时,使用关键字DEFAULT替代

样例:

  1. 定义SQL标量函数,根据输入的key值返回相对应的字符串。

    CREATE FUNCTION case_when_sf(@key int) RETURNS string RETURN CASE @key 
    WHEN 1
    THEN 'abc'
    WHEN 2
    THEN true
    ELSE 'unknown' END

    SELECT case_when_sf(1)

    SELECT case_when_sf(generate_series) FROM generate_series(1,10,1)

    DROP FUNCTION case_when_sf(int)
  2. 定义带default 值的SQL表函数

    CREATE FUNCTION cast_default_func(@key STRING = '2') RETURNS INT RETURN cast(@key AS INT)

    SELECT cast_default_func(default)

    DROP FUNCTION cast_default_func(STRING)
  3. 定义同名SQL函数

    CREATE FUNCTION sf(@key INT) RETURNS INT RETURN 1+@key

    CREATE FUNCTION sf(@length INT, @key STRING) RETURNS STRING RETURN cast(round(@key, @length) AS STRING)

    select sf(1,'1.234')

    select sf(1)

    DROP FUNCTION sf(INT, STRING)
    DROP FUNCTION sf(INT)