Blink SQL内置函数大全

字符串函数

STR_TO_MAP

功能描述

使用listDelimiter将text分隔成K-V对，然后使用keyValueDelimiter分隔每个K-V对，组装成MAP返回。默认listDelimiter为（,）, keyValueDelimiter为（=）。

语法

MAP STR_TO_MAP(VARCHAR text)
MAP STR_TO_MAP(VARCHAR text, VARCHAR listDelimiter, VARCHAR keyValueDelimiter) 
1
2

入参

这里的Delimiter使用的是Java的正则表达式，遇到特殊字符需要转义。

示例

测试语句

SELECT
  STR_TO_MAP('k1=v1,k2=v2')['k1'] as a
FROM T1;
1
2
3

测试结果

POSITION

功能描述

返回目标字符串x在被查询字符串y里第一次出现的位置。如果目标字符串x在被查询字符串y中不存在，返回值为0。

语法

INTEGER POSITION( x IN  y)
1

入参

示例

测试语句

POSITION('in' IN 'china') as result
FROM T1; 
1
2

测试结果

TRIM

功能描述

除掉一个字串中的字头或字尾。最常见的用途是移除字首或字尾的空格。

语法

VARCHAR TRIM( VARCHAR x )
1

入参

示例

测试语句

SELECT TRIM('   Sample   ') as result
FROM   T1;    
1
2

测试结果

OVERLAY

功能描述

用y替换x的子串。从start_position开始，替换length+1个字符。

语法

VARCHAR OVERLAY ( (VARCHAR x PLACING VARCHAR y FROM INT start_position [ FOR INT length ]) )
1

入参

示例

测试语句

OVERLAY('abcdefg' PLACING 'hij' FROM 2 FOR 2) as result
FROM  T1;
1
2

测试结果

INITCAP

功能描述

返回字符串，每个字转换器的第一个字母大写，其余为小写。

语法

 VARCHAR INITCAP(A)
1

入参

示例

测试数据

测试语句

SELECT INITCAP(var1)as aa
FROM T1;  
1
2

测试结果

REPLACE

功能描述

字符串替换函数。

语法

VARCHAR REPLACE(str1, str2, str3)     
1

入参

示例

测试数据

测试语句

SELECT REPLACE(str1, str2, str3) as `result`
FROM T1;     
1
2

测试结果

JSON_VALUE

功能描述

从JSON字符串中提取指定path的值，不合法的JSON和null都统一返回null。

语法

VARCHAR JSON_VALUE(VARCHAR content, VARCHAR path)
1

入参

• content

  VARCHAR类型，需要解析的JSON对象，使用字符串表示。

• path

  VARCHAR类型，解析JSON的路径表达式。目前path支持如下表达式。

  符号	功能
  $	根对象
  []	数组下标
  *	数组通配符
  .	取子元素

示例

测试数据

测试语句

SELECT 
    id,
    JSON_VALUE(json, path1) AS `value`
FROM 
    T1;
1
2
3
4
5

测试结果

CHR

功能描述

将ASCII码转换为字符。

语法

VARCHAR CHR(INT ascii)
1

入参

示例

测试数据

测试语句

SELECT CHR(int1) as var1, CHR(int2) as var2, CHR(int3) as var3
FROM T1;          
1
2

测试结果

CONCAT

功能描述

连接两个或多个字符串值从而组成一个新的字符串。如果任一参数为NULL时，则跳过该参数。

语法

 VARCHAR CONCAT(VARCHAR var1, VARCHAR var2, ...)  
1

入参

示例

测试数据

测试语句

SELECT CONCAT(var1, var2, var3) as var
FROM T1;              
1
2

测试结果

CONCAT_WS

功能描述

将每个参数值和第一个参数separator指定的分隔符依次连接到一起组成新的字符串，长度和类型取决于输入值。

如果separator取值为null，则将separator视作与空串进行拼接。如果其它参数为NULL，在执行拼接过程中跳过取值为NULL的参数。

语法

VARCHAR CONCAT_WS(VARCHAR separator, VARCHAR var1, VARCHAR var2, ...)
1

入参

示例

测试数据

测试语句

SELECT CONCAT_WS(sep, str1, str2, str3) as var FROM T1;
1

测试结果

LPAD

功能描述

字符串str左端填充若干个字符串pad，直到新的字符串达到指定长度len为止。

• 任意参数为null时返回null。

• len为负数时返回为null。

• pad为空串时，如果len不大于str长度，返回str裁剪后的结果。如果len大于str长度时，则返回null。

语法

VARCHAR LPAD(VARCHAR str, INT len, VARCHAR pad)    
1

入参

示例

测试数据

测试语句

SELECT LPAD(str, len, pad) AS result
FROM T1;                
1
2

测试结果

RPAD

功能描述

字符串str右端填充若干个字符串pad，直到新的字符串达到指定长度len为止。

• 如果任意参数为null时，则返回null。
• 如果len长度为负数时，则返回null。
• 当pad为空串，如果len不大于str长度时，则返回str裁剪后的结果。
• 如果len大于str长度，则返回null。

语法

VARCHAR RPAD(VARCHAR str, INT len, VARCHAR pad) 
1

入参

示例

测试数据

测试语句

SELECT  RPAD(str, len, pad) as result
FROM T1;           
1
2

测试结果

REPEAT

功能描述

返回以字符串值为str，重复次数为N的新的字符串。如果参数为null时，则返回null。如果重复次数为0或负数，则返回空串。

语法

VARCHAR REPEAT(VARCHAR str, INT n)
1

入参

示例

测试数据

测试语句

SELECT  REPEAT(str,n) as var1
FROM T1;        
1
2

测试结果

SUBSTRING

功能描述

获取字符串子串。截取从位置start开始，长度为len的子串。如果未指定len，则截取到字符串结尾。start从1开始，start为0当1看待，为负数时表示从字符串末尾倒序计算位置。

语法

VARCHAR SUBSTRING(VARCHAR a, INT start)
VARCHAR SUBSTRING(VARCHAR a, INT start, INT len)      
1
2

入参

示例

测试数据

测试语句

SELECT SUBSTRING('', 222222222) as var1,
       SUBSTRING(str, 2) as var2,
       SUBSTRING(str, -2) as var3,
       SUBSTRING(str, -2, 1) as var4, 
       SUBSTRING(str, 2, 1) as var5,
       SUBSTRING(str, 22) as var6,
       SUBSTRING(str, -22) as var7,
       SUBSTRING(str, 1) as var8,
       SUBSTRING(str, 0) as var9,
       SUBSTRING(nullstr, 0) as var10
FROM T1;         
1
2
3
4
5
6
7
8
9
10
11

测试结果

REVERSE

功能描述

反转字符串，返回字符串值的相反顺序。如果任一参数为null时，则返回null。

语法

VARCHAR REVERSE(VARCHAR str) 
1

入参

示例

测试数据

测试语句

SELECT  REVERSE(str1) as var1,REVERSE(str2) as var2,
        REVERSE(str3) as var3,REVERSE(str4) as var4
FROM T1;          
1
2
3

测试结果

SPLIT_INDEX

功能描述

以sep作为分隔符，将字符串str分隔成若干段，取其中的第index段。index从0开始，如果取不到字段，则返回null。如果任一参数为NULL，则返回null。

语法

VARCHAR SPLIT_INDEX(VARCHAR str, VARCHAR sep, INT index)
1

入参

示例

测试数据

测试语句

SELECT  SPLIT_INDEX(str, sep, index) as var1
FROM T1;         
1
2

测试结果

REGEXP_REPLACE

功能描述

用字符串replacement替换字符串str中正则模式为pattern的部分，并返回新的字符串。如果参数为NULL或者正则不合法时，则返回NULL。

如果您的业务数据中存在特殊字符（即系统使用的字符或者SQL关键字），则需要对特殊字符进行转义处理。常见的特殊字符和转义方式如下表所示。

语法

VARCHAR REGEXP_REPLACE(VARCHAR str, VARCHAR pattern, VARCHAR replacement)
1

入参

需要按照Java代码编写正则常量。Codegen会自动将SQL常量字符串转化为Java代码。描述一个数值（\d）的正则表达式和Java中一样，为 '\d'。

示例

测试数据

测试语句

SELECT  REGEXP_REPLACE(str1, pattern1, replace1) as result
FROM T1;
1
2

测试结果

REGEXP_EXTRACT

功能描述

使用正则模式Pattern匹配抽取字符串Str中的第Index个子串，Index从1开始，正则匹配提取。当参数为NULL或者正则不合法时，则返回NULL。

语法

VARCHAR REGEXP_EXTRACT(VARCHAR str, VARCHAR pattern, INT index)       
1

入参

正则常量请按照Java代码来写。CodeGen会将SQL常量字符串自动转化为Java代码。如果要描述一个数字**\d**，需要写成**‘\d’**，即和Java中正则相同。

示例

测试数据

测试语句

SELECT  REGEXP_EXTRACT(str1, pattern1, index1) as result
FROM T1;    
1
2

测试结果

KEYVALUE

功能描述

解析str字符串中，匹配有split1（kv对的分隔符）和split2（kv的分隔符）的key-value对，根据key_name返回对应的数值。如果key_name值不存在或异常时，返回NULL。

语法

VARCHAR KEYVALUE(VARCHAR str, VARCHAR split1, VARCHAR split2, VARCHAR key_name)      
1

入参

示例

测试数据

测试语句

SELECT  KEYVALUE(str, split1, split2, key1) as `result`
FROM T1;         
1
2

测试结果

CHAR_LENGTH

功能描述

返回字符串中的字符的数量。

语法

CHAR_LENGTH(A)      
1

入参

示例

测试数据

测试语句

SELECT  CHAR_LENGTH(var1) as aa
FROM T1;            
1
2

测试结果

HASH_CODE

功能描述

返回字符串的HASH_CODE()的绝对值。

语法

INT HASH_CODE(VARCHAR str)    
1

入参

示例

测试数据

测试语句

SELECT  HASH_CODE(str1) as var1, HASH_CODE(str2) as var2, HASH_CODE(nullstr) as var3
FROM T1;
1
2

测试结果

CHR

功能描述

返回字符串的MD5值。如果参数为空串（即参数为"）时，则返回空串。

语法

VARCHAR MD5(VARCHAR str)
1

入参

• str
• VARCHAR类型

示例

测试数据

测试语句

SELECT
   MD5(str1) as var1,
   MD5(str2) as var2
FROM T1;         
1
2
3
4

测试结果

REGEXP

功能描述

对指定的字符串执行一个正则表达式搜索，并返回一个Boolean值表示是否找到指定的匹配模式。如果str或者pattern为空或为NULL时，则返回false。

语法

BOOLEAN REGEXP(VARCHAR str, VARCHAR pattern) 
1

入参

示例

测试数据

测试语句

SELECT  REGEXP(str1, pattern1) AS result
FROM T1;              
1
2

测试结果

TO_BASE64

功能描述

将BINARY类型数据转换成对应base64编码的字符串输出。

语法

VARCHAR TO_BASE64(bin)         
1

入参

示例

测试数据

测试语句

SELECT  TO_BASE64(FROM_BASE64(c)) as var1
FROM T1;       
1
2

测试结果

FROM_BASE64

功能描述

将base64编码的字符串str解析成对应的binary类型数据输出。

语法

BINARY FROM_BASE64(str)
1

入参

示例

测试数据

测试语句

SELECT 
from_base64(c) as var1,from_base64('SGVsbG8gd29ybGQ=') as var2 
FROM T1; 
1
2
3

测试结果

INSTR

功能描述

返回目标字符串在源字符串中的位置，如果在源字符串中未找到目标字符串，则返回0。

语法

INT instr( string1, string2 ) 
INT instr( string1, string2 [, start_position [, nth_appearance ] ] )   
1
2

入参

示例

测试数据

测试语句

SELECT 
instr('helloworld','lo') as res1,
instr('helloworld','l',-1,1) as res2,   
instr('helloworld','l',3,2) as res3
FROM T1;            
1
2
3
4
5

测试结果

UPPER

功能描述

返回转换为大写字符的字符串。

语法

VARCHAR UPPER(A)     
1

入参

示例

测试数据

测试语句

SELECT UPPER(var1) as aa
FROM T1;            
1
2

测试结果

LOWER

功能描述

返回转换为小写字符的字符串。

语法

VARCHAR LOWER(A)       
1

入参

示例

测试数据

测试语句

SELECT LOWER(var1) as aa
FROM T1;                 
1
2

测试结果

PARSE_URL

功能描述

返回urlStr中指定的部分解析后的值。如果urlStr参数值为null时，则返回值为null。

语法

VARCHAR PARSE_URL(VARCHAR urlStr, VARCHAR partToExtract [, VARCHAR key])
1

入参

示例

测试数据

测试语句

SELECT PARSE_URL(url1, 'QUERY', 'query') as var1,
       PARSE_URL(url1, 'QUERY') as var2,
       PARSE_URL(url1, 'HOST') as var3,
       PARSE_URL(url1, 'PATH') as var4,
       PARSE_URL(url1, 'REF') as var5,
       PARSE_URL(url1, 'PROTOCOL') as var6,
       PARSE_URL(url1, 'FILE') as var7,
       PARSE_URL(url1, 'AUTHORITY') as var8,
       PARSE_URL(nullstr, 'QUERY') as var9,
       PARSE_URL(url1, 'USERINFO') as var10,
       PARSE_URL(nullstr, 'QUERY', 'query') as var11
FROM T1; 
1
2
3
4
5
6
7
8
9
10
11
12

测试结果

数学函数

加

功能描述

返回A加B的结果。

语法

A + B
1

入参

示例

测试数据

测试语句

SELECT int1+int2+int3 as aa
FROM T1;               
1
2

测试结果

减

功能描述

返回A减B的结果。

语法

A - B
1

入参

示例

测试数据

测试语句

SELECT int3 - int2 - int1 as aa
FROM T1;                  
1
2

测试结果

乘

功能描述

返回A乘以B的结果。

语法

A * B
1

入参

示例

测试数据

测试语句

SELECT int1*int2*int3 as aa
FROM T1;      
1
2

测试结果

除

功能描述

返回A除以B的结果。

语法

A / B
1

入参

示例

测试数据

测试语句

SELECT int1 / int2 as aa
FROM T1;      
1
2

测试结果

ABS

功能描述

返回A的绝对值。

语法

DOUBLE ABS(A)
1

入参

示例

测试数据

测试语句

SELECT ABS(in1) as aa
FROM T1;            
1
2

测试结果

ACOS

功能描述

返回A的反余弦值。

语法

ACOS(A)
1

入参

示例

测试数据

测试语句

SELECT ACOS(in1) as aa
FROM T1;         
1
2

测试结果

BIN

功能描述

将长整型参数转换为二进制形式，返回类型为字符串。

语法

VARCHAR BIN(BIGINT number) 
1

入参

示例

测试数据

测试语句

SELECT id, bin(x) as var1
FROM T1;       
1
2

测试结果

ASIN

功能描述

返回A的反正弦值。

语法

DOUBLE ASIN(A)
1

入参

示例

测试数据

测试语句

SELECT ASIN(in1) as aa
FROM T1;          
1
2

测试结果

ATAN

功能描述

返回A的反正切值。

语法

DOUBLE ATAN(A)
1

入参

示例

测试数据

测试语句

SELECT ATAN(in1) as aa
FROM T1;
1
2

测试结果

BITAND

功能描述

运算符按位“与”操作。输入和输出类型均为INT整型，且类型一致。

语法

INT BITAND(INT number1,INT number2)        
1

入参

示例

测试数据

测试语句

SELECT BITAND(a, b) as intt
FROM T1;              
1
2

测试结果

BITNOT

功能描述

按位取反。输入和输出类型均为整型，且类型一致。

语法

INT BITNOT(INT number)     
1

入参

示例

测试数据

测试语句

SELECT BITNOT(a) as var1
FROM T1;             
1
2

测试结果

BITOR

功能描述

按位取“或”。输入和输出类型均为整型，且类型一致。

语法

INT BITOR(INT number1, INT number2)
1

入参

示例

测试数据

测试语句

SELECT BITOR(a, b) as var1
FROM T1
1
2

测试结果

BITXOR

功能描述

按位取“异或”。输入和输出类型均为整型，且类型一致。

语法

INT BITXOR(INT number1, INT number2)
1

入参

示例

测试数据

测试语句

SELECT BITXOR(a, b) as var1
FROM T1;               
1
2

测试结果

CARDINALITY

功能描述

返回一个集合中的元素数量。

语法

SELECT BITXOR(a, b) as var1
FROM T1;               
1
2

入参

示例

测试语句

SELECT cardinality(array[1,2,3]) AS `result`
FROM T1; 
1
2

测试结果

COS

功能描述

返回A的余弦值。

语法

DOUBLE COS(A)     
1

入参

示例

测试数据

测试语句

SELECT COS(in1) as aa
FROM T1;               
1
2

测试结果

COT

功能描述

返回A的余切值。

语法

DOUBLE COT(A) 
1

入参

示例

测试数据

测试语句

SELECT COT(in1) as aa
FROM T1;              
1
2

测试结果

EXP

功能描述

返回自然常数e的A次幂的DOUBLE类型数值。

语法

DOUBLE EXP(A)       
1

入参

示例

测试数据

测试语句

SELECT EXP(in1) as aa
FROM T1;            
1
2

测试结果

E

功能描述

返回自然常数e的DOUBLE类型值。

语法

DOUBLE E()
1

入参

示例

测试数据

测试语句

SELECT  e() as dou1, E() as dou2
FROM T1; 
1
2

测试结果

FLOOR

功能描述

向下取整。

语法

B FLOOR(A)
1

入参

示例

测试数据

测试语句

SELECT 
FLOOR(in1) as out1,
FLOOR(in2) as out2
FROM T1;      
1
2
3
4

测试结果

LN

功能描述

返回number的自然对数。

语法

DOUBLE ln(DOUBLE number)
1

入参

示例

测试数据

测试语句

SELECT id, ln(x) as dou1, ln(e()) as dou2
FROM T1;         
1
2

测试结果

LOG

功能描述

返回以base为底的x的自然对数。如果是只有一个参数的，返回以x为底的自然对数。

语法

DOUBLE LOG(DOUBLE base, DOUBLE x)
DOUBLE LOG(DOUBLE x)
1
2

入参

示例

测试数据

测试语句

SELECT id, LOG(base, x) as dou1, LOG(2) as dou2
FROM T1;       
1
2

测试结果

LOG10

功能描述

返回以10为底的自然对数。若x为NULL，则返回NULL。若x为负数会引发异常。

语法

DOUBLE LOG10(DOUBLE x)   
1

入参

示例

测试数据

测试语句

SELECT id, log10(x) as dou1
FROM T1;         
1
2

测试结果

LOG2

功能描述

返回以2为底的自然对数。若x为NULL，则返回NULL。若x为负数，会引发异常。

语法

DOUBLE LOG2(DOUBLE x)  
1

入参

示例

测试数据

测试语句

SELECT id, log2(x) as dou1
FROM T1;          
1
2

测试结果

PI

功能描述

获取圆周率常量PI值。

语法

DOUBLE PI()  
1

入参

无。

示例

测试数据

测试语句

SELECT id, PI() as dou1
FROM T1;            
1
2

测试结果

POWER

功能描述

返回A的B次幂。

语法

DOUBLE POWER(A, B)   
1

入参

示例

测试数据

测试语句

SELECT POWER(in1, in2) as aa
FROM T1;        
1
2

测试结果

RAND

功能描述

返回大于等于0小于1的DOUBLE类型随机数。

语法

DOUBLE RAND([BIGINT seed])
1

入参

示例

测试数据

测试语句

SELECT id, rand(1) as dou1, rand(3) as dou2
FROM T1;         
1
2

测试结果

SIN

功能描述

返回A的正弦值。

语法

DOUBLE SIN(A)   
1

入参

示例

测试数据

测试语句

SELECT SIN(in1) as aa
FROM T1;             
1
2

测试结果

SQRT

功能描述

返回A的平方根。

语法

DOUBLE SQRT(A)         
1

入参

示例

测试数据

测试语句

SELECT SQRT(in1) as aa
FROM T1;        
1
2

测试结果

TAN

功能描述

计算A的正切值。

语法

DOUBLE TAN(A)    
1

入参

示例

测试数据

测试语句

SELECT TAN(in1) as aa
FROM T1;              
1
2

测试结果

DEGREES

功能描述

将弧度x转换为数值。

语法

DOUBLE DEGREES( double x )
1

入参

示例

测试语句

SELECT DEGREES (PI()) as result
FROM T1;
1
2

测试结果

CHARACTER_LENGTH

功能描述

返回字符串x中的字符数。

语法

INTEGER CHARACTER_LENGTH( VARCHAR x )       
1

入参

示例

测试数据

测试语句

SELECT CHARACTER_LENGTH( x ) as result
FROM T1;                 
1
2

测试结果

ROUND

功能描述

将数值按照指定的小数位四舍五入。

语法

T ROUND( T x, INT n)   
1

入参

示例

测试数据

测试语句

SELECT ROUND(in1,2) as `result`
FROM T1;         
1
2

测试结果

MOD

功能描述

求整数x除以整数y的余数。当x为负值时，或者x、y均为负值时，结果为负值。

语法

INTEGER MOD(INTEGER x,INTEGER y) 
1

入参

示例

测试数据

测试语句

SELECT MOD(x,y) as result
FROM T1;        
1
2

测试结果

CONV

功能描述

将长整型数字或字符串形式数字从一种进制转换为另一种进制，CONV()精度为64位。当number是null或非法字符时，结果返回为NULL。

语法

VARCHAR CONV(BIGINT number, INT FROM_BASE, INT TO_BASE)
or 
VARCHAR CONV(VARCHAR number, INT FROM_BASE, INT TO_BASE)
1
2
3

入参

示例

测试数据

测试语句

SELECT  id, conv(x, 10, 16) as var1, conv(y, 10, 2) as var2
FROM T1;
1
2

测试结果

CEIL

功能描述

向上取整。

语法

B CEIL(A)  
1

入参

示例

测试数据

测试语句

SELECT
CEIL （in1） as out1
CEIL （in2） as out2
FROM T1;           
1
2
3
4

测试结果

日期函数

TO_TIMESTAMP

功能描述

将BIGINT类型或VARCHAR类型的日期转换成TIMESTAMP类型。

语法

TIMESTAMP TO_TIMESTAMP(BIGINT time)
TIMESTAMP TO_TIMESTAMP(VARCHAR date)
TIMESTAMP TO_TIMESTAMP(VARCHAR date, VARCHAR format)
1
2
3

入参

示例

测试数据

测试语句

SELECT TO_TIMESTAMP(timestamp1) as var1,
 TO_TIMESTAMP(timestamp2) as var2,
 TO_TIMESTAMP(timestamp3, 'yyyyMMddHHmmss') as var3
FROM T1;
1
2
3
4

测试结果

LOCALTIMESTAMP

功能描述

返回当前系统的时间戳。

语法

timestamp LOCALTIMESTAMP
1

入参

无

示例

测试语句

SELECT 
LOCALTIMESTAMP as `result`
FROM T1;
1
2
3

测试结果

CURRENT_TIMESTAMP

功能描述

返回当前UTC（GMT+0）时间戳，时间戳单位为毫秒。

语法

TIMESTAMP CURRENT_TIMESTAMP
1

入参

无

示例

测试语句

SELECT CURRENT_TIMESTAMP as var1
FROM T1;
1
2

测试结果

NOW

功能描述

• 未指定参数时返回当前时区时间的时间戳，单位为秒。
• 可以指定INT类型参数作为偏移值（单位：秒），返回偏移后的时间戳。例如，now(100)返回当前时间戳加100秒的时间戳。偏移值为NULL时，返回值为NULL。

语法

BIGINT NOW()
BIGINT NOW(a)
1
2

入参

示例

测试数据

测试语句

SELECT 
 NOW() as now,
 NOW(100) as now_100,
 NOW(a) as now_null
FROM T1;
1
2
3
4
5

测试结果

DATE_FORMAT

功能描述

将字符串类型的日期从源格式转换至目标格式。

语法

VARCHAR DATE_FORMAT(TIMESTAMP time, VARCHAR to_format)
VARCHAR DATE_FORMAT(VARCHAR date, VARCHAR to_format)
VARCHAR DATE_FORMAT(VARCHAR date, VARCHAR from_format, VARCHAR to_format)
1
2
3

入参

第一个参数（time 或 date）为源字符串。

第二个参数from_format可选，为源字符串的格式，默认为yyyy-MM-dd hh:mm:ss。

第三个参数为返回日期的格式，返回值为转换格式后的字符串类型日期。

如果有参数为NULL或解析错误，则返回NULL。

示例

测试数据

测试语句

SELECT DATE_FORMAT(datetime1, 'yyMMdd') as var1,
 DATE_FORMAT(nullstr, 'yyMMdd') as var2,
 DATE_FORMAT(datetime1, nullstr) as var3,
 DATE_FORMAT(date1, 'MMdd-yyyy', nullstr) as var4,
 DATE_FORMAT(date1, 'MMdd-yyyy', 'yyyyMMdd') as var5,
 DATE_FORMAT(TIMESTAMP '2017-09-15 23:00:00', 'yyMMdd') as var6
FROM T1;
1
2
3
4
5
6
7

测试结果

UNIX_TIMESTAMP

功能描述

返回date转换成的长整型时间戳，单位为秒。无参数时返回当前时间戳，单位为秒，与now语义相同。如果有参数为null或解析错误，返回null。

语法

BIGINT UNIX_TIMESTAMP()
BIGINT UNIX_TIMESTAMP(VARCHAR date)
BIGINT UNIX_TIMESTAMP(TIMESTAMP timestamp)
BIGINT UNIX_TIMESTAMP(VARCHAR date, VARCHAR format)
1
2
3
4

入参

示例

测试数据

测试语句

SELECT UNIX_TIMESTAMP() as big1,
       UNIX_TIMESTAMP(nullstr) as big2
FROM T1;
1
2
3

测试结果

TO_DATE

功能描述

将INT类型或VARCHAR类型的日期转换成DATE类型。

语法

Date TO_DATE(INT time)
Date TO_DATE(VARCHAR date)
Date TO_DATE(VARCHAR date,VARCHAR format)
1
2
3

入参

示例

测试数据

测试语句

SELECT TO_DATE(date1) as var1,
 TO_DATE(date2) as var2,
 TO_DATE(date3,'yyyyMMdd') as var3
FROM T1;
1
2
3
4

测试结果

FROM_UNIXTIME

功能描述

返回为VARCHAR类型的日期值，默认格式：yyyy-MM-dd HH:mm:ss，若指定日期格式按指定格式输出任一输入参数是NULL，返回NULL。

语法

VARCHAR FROM_UNIXTIME(BIGINT unixtime[, VARCHAR format])
1

入参

参数format可选，日期格式，默认格式为yyyy-MM-dd HH:mm:ss，表示返回符合指定格式的日期，如果有参数为null或解析错误，则返回null。

示例

测试数据

测试语句

SELECT FROM_UNIXTIME(unixtime1) as var1, 
 FROM_UNIXTIME(unixtime1,'MMdd-yyyy') as var2,
 FROM_UNIXTIME(unixtime1,nullstr) as var3
FROM T1;               
1
2
3
4

测试结果

DATEDIFF

功能描述

计算从enddate到startdate两个时间的天数差值，返回整数。若有参数为NULL或解析错误，返回NULL。

语法

INT DATEDIFF(VARCHAR enddate, VARCHAR startdate)
INT DATEDIFF(TIMESTAMP enddate, VARCHAR startdate)
INT DATEDIFF(VARCHAR enddate, TIMESTAMP startdate)
INT DATEDIFF(TIMESTAMP enddate, TIMESTAMP startdate)
1
2
3
4

入参

VARCHAR日期格式：yyyy-MM-dd或yyyy-MM-dd HH:mm:ss。

示例

测试数据

测试语句

SELECT  DATEDIFF(datetime1, datetime2) as int1, 
        DATEDIFF(TIMESTAMP '2017-10-15 23:00:00',datetime2) as int2, 
        DATEDIFF(datetime2,TIMESTAMP '2017-10-15 23:00:00') as int3, 
        DATEDIFF(datetime2,nullstr) as int4, 
        DATEDIFF(nullstr,TIMESTAMP '2017-10-15 23:00:00') as int5, 
        DATEDIFF(nullstr,datetime2) as int6, 
        DATEDIFF(TIMESTAMP '2017-10-15 23:00:00',TIMESTAMP '2017-9-15 00:00:00')as int7
FROM T1;
1
2
3
4
5
6
7
8

测试结果

DATE_SUB

功能描述

返回startdate减去days天数的日期。返回VARCHAR类型的yyyy-MM-dd日期格式。如果有参数为null或解析错误，返回null。

语法

VARCHAR DATE_SUB(VARCHAR startdate, INT days)
VARCHAR DATE_SUB(TIMESTAMP time, INT days)
1
2

入参

VARCHAR类型日期格式：yyyy-MM-dd或yyyy-MM-dd HH:mm:ss。

示例

测试数据

测试语句

SELECT DATE_SUB(date1, 30) as var1,
 DATE_SUB(TIMESTAMP '2017-10-15 23:00:00',30) as var2,
 DATE_SUB(nullstr,30) as var3
FROM T1;
1
2
3
4

测试结果

DATE_ADD

功能描述

返回指定startdate日期days天数后的VARCHAR类型日期，返回string格式的日期为yyyy-MM-dd。如果有参数为null或解析错误，返回null。

语法

VARCHAR DATE_ADD(VARCHAR startdate, INT days)
VARCHAR DATE_ADD(TIMESTAMP time, INT days)
1
2

入参

VARCHAR类型日期格式：yyyy-MM-dd或yyyy-MM-dd HH:mm:ss。

示例

测试数据

测试语句

SELECT DATE_ADD(datetime1, 30) as var1,
 DATE_ADD(TIMESTAMP '2017-09-15 23:00:00',30) as var2,
 DATE_ADD(nullstr,30) as var3
FROM T1;   
1
2
3
4

测试结果

WEEK

功能描述

计算指定日期在一年中的第几周，周数取值区间1~53。

语法

BIGINT WEEK(DATE date)
BIGINT WEEK(TIMESTAMP timestamp)
1
2

入参

示例

测试数据

测试语句

SELECT WEEK(TIMESTAMP '2017-09-15 00:00:00') as int1,
 WEEK(date1) as int2,
 WEEK(ts1) as int3,
 WEEK(CAST(dateStr AS DATE)) as int4
FROM T1;
1
2
3
4
5

测试结果

YEAR

功能描述

返回输入时间的年份。

语法

BIGINT YEAR(TIMESTAMP timestamp)
BIGINT YEAR(DATE date)
1
2

入参

示例

测试数据

测试语句

SELECT YEAR(TIMESTAMP '2016-09-15 00:00:00') as int1,
 YEAR(DATE '2017-09-22') as int2,
 YEAR(tdate) as int3,
 YEAR(ts) as int4,
 YEAR(CAST(dateStr AS DATE)) as int5,
 YEAR(CAST(tsStr AS TIMESTAMP)) as int6
FROM T1;          
1
2
3
4
5
6
7

测试结果

MONTH

功能描述

返回输入时间参数中的月，范围1～12。

语法

BIGINT MONTH(TIMESTAMP timestamp) 
BIGINT MONTH(DATE date)
1
2

入参

示例

测试数据

测试语句

SELECT
 MONTH(cast( a as TIMESTAMP)) as int1,
 MONTH(cast( b as DATE)) as int2
FROM T1;     
1
2
3
4

测试结果

HOUR

功能描述

返回输入时间参数time或timestamp中的24小时制的小时数，范围0～23。

语法

BIGINT HOUR(TIME time)
BIGINT HOUR(TIMESTAMP timestamp)    
1
2

入参

示例

测试数据

测试语句

SELECT HOUR(TIMESTAMP '2016-09-20 23:33:33') AS int1,
 HOUR(TIME '23:30:33') AS int2,
 HOUR(time2) AS int3,
 HOUR(timestamp1) AS int4,
 HOUR(CAST(time1 AS TIME)) AS int5,
 HOUR(TO_TIMESTAMP(datetime1)) AS int6
FROM T1;              
1
2
3
4
5
6
7

测试结果

DAYOFMONTH

功能描述

返回输入时间参数date或time中所指代的“日”。返回值范围为1～31。

语法

BIGINT DAYOFMONTH(TIMESTAMP time)
BIGINT DAYOFMONTH(DATE date)
1
2

入参

示例

测试数据

测试语句

SELECT DAYOFMONTH(TIMESTAMP '2016-09-15 00:00:00') as int1,
 DAYOFMONTH(DATE '2017-09-22') as int2,
 DAYOFMONTH(tdate) as int3,
 DAYOFMONTH(ts) as int4,
 DAYOFMONTH(CAST(dateStr AS DATE)) as int5,
 DAYOFMONTH(CAST(tsStr AS TIMESTAMP)) as int6
FROM T1; 
1
2
3
4
5
6
7

测试结果

MINUTE

功能描述

返回输入时间参数中time或timestamp中的“分钟”部分。取值范围0～59。

语法

BIGINT MINUTE(TIME time) 
BIGINT MINUTE(TIMESTAMP timestamp)
1
2

入参

示例

测试数据

测试语句

SELECT MINUTE(TIMESTAMP '2016-09-20 23:33:33') as int1,
 MINUTE(TIME '23:30:33') as int2,
 MINUTE(time2) as int3,
 MINUTE(timestamp1) as int4,
 MINUTE(CAST(time1 AS TIME)) as int5,
 MINUTE(CAST(datetime1 AS TIMESTAMP)) as int6
FROM T1;
1
2
3
4
5
6
7

测试结果

SECOND

功能描述

返回输入时间参数中的“秒”部分，范围0～59。

语法

BIGINT SECOND(TIMESTAMP timestamp)
BIGINT SECOND(TIME time)    
1
2

入参

示例

测试数据

测试语句

SELECT SECOND(TIMESTAMP '2016-09-20 23:33:33') as int1,
 SECOND(TIME '23:30:33') as int2,
 SECOND(time2) as int3,
 SECOND(timestamp1) as int4,
 SECOND(CAST(time1 AS TIME)) as int5,
 SECOND(CAST(datetime1 AS TIMESTAMP)) as int6
FROM T1;   
1
2
3
4
5
6
7

测试结果

TIMESTAMPADD

功能描述

返回时间间隔单位计算后的时间。

语法

TIMESTAMP TIMESTAMPADD(interval,INT int_expr,TIMESTAMP datetime_expr)
DATE TIMESTAMPADD(interval,INT int_expr,DATE datetime_expr)       
1
2

入参

interval可取值如下。

示例

测试数据

测试语句

SELECT 
TIMESTAMPADD(HOUR,3,a) AS `result1`
TIMESTAMPADD(DAY,3,b) AS `result2`
FROM T1;
1
2
3
4

测试结果

EXTRACT

功能描述

返回日期/时间的单独部分，例如年、月、日、小时、分钟、周数等。

语法

BIGINT EXTRACT(unit FROM time)
1

入参

time：任意日期表达式。

unit：日期单元，可取值如下：

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

示例

测试语句

EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS OrderYear,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS OrderMonth,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS OrderDay,
EXTRACT(WEEK FROM CURRENT_TIMESTAMP) AS OrderWeek    
1
2
3
4

测试结果

LOCALTIME

功能描述

以数据类型TIME的值返回会话时区中的当前时间。LOCALTIME可当变量直接使用。

语法

TIME LOCALTIME
1

入参

无

示例

测试语句

SELECT LOCALTIME as `result`
FROM T1;      
1
2

测试结果

CURRENT_DATE

功能描述

返回当前系统日期。

语法

CURRENT_DATE  
1

入参

无

示例

测试语句

SELECT CURRENT_DATE as res
FROM T1;            
1
2

测试结果

逻辑函数

=

功能描述

如果A等于B，返回TRUE，否则返回FALSE。

语法

 A = B 
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int3 = int2;           
1
2
3

测试结果

>

功能描述

如果A大于B，返回TRUE，否则返回FALSE。

语法

 A > B
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int3 > int2;
1
2
3

测试结果

>=

功能描述

如果A大于等于B，返回TRUE，否则返回FALSE。

语法

 A >= B
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int3 >= int2;
1
2
3

测试结果

<=

功能描述

如果A小于等于B，返回TRUE，否则返回FALSE。

语法

 A <= B
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int3 <= int2;
1
2
3

测试结果

<

功能描述

如果A小于B，返回TRUE，否则返回FALSE。

语法

 A < B
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int3 < int2;
1
2
3

测试结果

<>

功能描述

如果A不等于B，则返回TRUE，否则返回FALSE。

语法

 A <> B
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int3 <> int2;
1
2
3

测试结果

AND

功能描述

如果A和B均为TRUE，则为TRUE，否则为FALSE。

语法

 A AND B   
1

入参

示例

测试数据

测试语句

SELECT int2 as aa
FROM T1
WHERE int1=255 AND int3=65;
1
2
3

测试结果

BETWEEN AND

功能描述

BETWEEN操作符用于选取介于两个值之间的数据范围内的值。

语法

A BETWEEN B AND C
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int1 BETWEEN int2 AND int3;
1
2
3

测试结果

IS NOT FALSE

功能描述

如果A是TRUE，返回TRUE。如果A是FALSE，返回FALSE。

语法

A IS NOT FALSE
1

入参

示例

测试数据

测试语句

SELECT int2 as aa
FROM T1
WHERE int1=255 IS NOT FALSE;
1
2
3

测试结果

IS NOT TRUE

功能描述

如果A是TRUE，返回FALSE。如果A是FALSE，返回TRUE。

语法

A IS NOT TRUE
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int1=25 IS NOT TRUE;
1
2
3

测试结果

IS NOT NULL

功能描述

如果value为NULL时，则返回FALSE，否则返回TRUE。

语法

value IS NOT NULL
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int2 IS NOT NULL;            
1
2
3

测试结果

IS NOT UNKNOWN

功能描述

当两边无法进行正常的逻辑判断时，即A值既不是TRUE也不是FALSE，返回 FALSE。可正常逻辑判断时，即A值为TRUE或者FALSE，返回 TRUE 。

语法

A IS NOT UNKNOWN
1

入参

A为逻辑比较表达式，例如： 6<8。

正常情况下数值型与数值型作逻辑比较时，A值为TRUE或者FALSE。当其中一个不为数值型数据类型时，就会出现无法比较的情况。

示例

测试数据

测试语句

SELECT int2 as aa
FROM T1
WHERE int1=25 IS NOT UNKNOWN;
1
2
3

测试结果

IS NULL

功能描述

如果value为NULL时，则返回TRUE，否则返回FALSE。

语法

value IS NULL
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int2 IS NULL;       
1
2
3

测试结果

IS TRUE

功能描述

如果A是TRUE时，则返回TRUE。如果A是FALSE时，则返回FALSE。

语法

A IS TRUE
1

入参

示例

测试数据

测试语句

SELECT int2 as aa
FROM T1
WHERE int1=255 IS TRUE;             
1
2
3

测试结果

IS UNKNOWN

功能描述

IS UNKNOWN通过逻辑判断关系，返回结果：

• A（逻辑比较表达式）值既不是TRUE也不是FALSE，即无法进行正常的逻辑判断，返回 TRUE。
• A（逻辑比较表达式）值为TRUE或者FALSE，即可以进行正常逻辑判断，返回 FALSE。

正常情况下数值型与数值型进行逻辑比较时（例如6<>8），A值为TRUE或者FALSE。但是，当其中一个不为数值型数据类型时，就会出现无法比较的情况。IS UNKNOWN用于判断这种情况是否存在

语法

A IS UNKNOWN
1

入参

示例

测试数据

测试语句

SELECT int2 as aa
FROM T1
WHERE int1 > null IS UNKNOWN;
1
2
3

测试结果

LIKE

功能描述

如果匹配，返回TRUE，否则返回FALSE。

语法

A LIKE B
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE VARCHAR2 LIKE 'ss%';
1
2
3

测试结果

NOT

功能描述

如果A是TRUE，返回FALSE。如果A是FALSE，返回TRUE。

语法

NOT
1

入参

示例

测试数据

测试语句

SELECT int2 as aa
FROM T1
WHERE NOT int3=62;
1
2
3

测试结果

NOT BETWEEN AND

功能描述

NOT BETWEEN AND操作符用于选取不存在与两个值之间的数据范围内的值。

语法

A NOT BETWEEN B AND C
1

入参

示例

测试数据

测试语句

SELECT int1 as aa
FROM T1
WHERE int1 NOT BETWEEN int2 AND int3;
1
2
3

测试结果

IN

功能描述

用来查找在参数中的记录。

语法

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)     
1
2
3

入参

示例

测试数据

测试语句

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)     
1
2
3

测试结果

OR

功能描述

如果A和B中至少有一个为TRUE，则为TRUE，否则为FALSE。

语法

 A OR B
1

入参

示例

测试数据

测试语句

SELECT int2 as aa
FROM T1
WHERE int1=255 OR int3=65;
1
2
3

测试结果

IS DISTINCT FROM

功能描述

• A和B的数据类型、值不完全相同则返回TRUE。
• A和B的数据类型、值都相同返回FALSE。
• 将空值视为相同。

语法

A IS DISTINCT FROM B
1

入参

示例

测试数据

测试语句

SELECT 
A IS DISTINCT FROM B as 'result'
FROM T1;
1
2
3

测试结果

IS NOT DISTINCT FROM

功能描述

跟IS DISTINCT FROM相反。

• A和B的数据类型、值不完全相同则返回FALSE。
• A和B的数据类型、值都相同返回TRUE。
• 将空值视为相同。

语法

A IS NOT DISTINCT FROM B
1

入参

示例

测试数据

• A(int)	B(varchar)
  97	97
  null	sss
  null	null

测试语句

SELECT 
A IS NOT DISTINCT FROM B as `result`
FROM T1;      
1
2
3

测试结果

NOT IN

功能描述

用来查找在不在参数中的记录。

语法

SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (value1,value2,...)
1
2
3

入参

示例

测试数据

测试语句

SELECT * 
FROM T1
WHERE LastName NOT IN ('Adams','Carter') 
1
2
3

测试结果

条件函数

IS_DECIMAL

功能描述

str字符串如果可以转换为十进制数值返回TRUE；否则返回FALSE。

语法

BOOLEAN IS_DECIMAL(VARCHAR str)
1

入参

示例

测试数据

测试语句

SELECT 
IS_DECIMAL(a) as boo1,
IS_DECIMAL(b) as boo2,
IS_DECIMAL(c) as boo3,
IS_DECIMAL(d) as boo4,
IS_DECIMAL(e) as boo5,
IS_DECIMAL(f) as boo6,
IS_DECIMAL(g) as boo7
FROM T1
1
2
3
4
5
6
7
8
9

测试结果

IS_DIGIT

功能描述

str中只包含数字则返回true，否则返回false。返回值为BOOLEAN类型。

语法

BOOLEAN IS_DIGIT(VARCHAR str)
1

入参

示例

测试数据

测试语句

SELECT 
IS_DIGIT(e) as boo1,
IS_DIGIT(f) as boo2,
IS_DIGIT(g) as boo3
FROM T1
1
2
3
4
5

测试结果

IS_ALPHA

功能描述

如果str中只包含字母，则返回true，否则返回false。

语法

BOOLEAN IS_ALPHA(VARCHAR str) 
1

入参

示例

测试数据

测试语句

SELECT IS_ALPHA(e) as boo1,IS_ALPHA(f) as boo2,IS_ALPHA(g) as boo3
FROM T1;             
1
2

测试结果

IF

功能描述

以testCondition的布尔值为判断标准，返回对应的参数值：

• true：返回第2个参数valueTrue。
• false：返回第3个参数valueFalseOrNull。

testCondition为null时，判定结果为false。

语法

T if (BOOLEAN testCondition, T valueTrue, T valueFalseOrNull)
1

T代表任意类型的返回值。

入参

示例

测试数据

测试语句

SELECT IF(int1 < int2,str1, str2) as int1
FROM T1
1
2

测试结果

CASE WHEN

功能描述

如果表达式a为TRUE，则返回b；如果表达式a为FALSE、c为TRUE，则返回d；如果表达式a和c都为FALSE，则返回e。

语法

CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
1

入参

CASE WHEN返回常量字符串时，会在字符串后面补全空格。例如，当满足else条件时，返回值ios后面会多几个空格。

case when device_type = 'android'
then 'android' 
else 'ios'
end as os
1
2
3
4

解决方法：

• 利用TRIM函数去除空格，该示例中，涉及os的字段都改为TRIM(os)。
• 利用CAST函数去除空格，将常量字符串转为VARCHAR类型。

示例

测试数据

测试语句

-- 用TRIM函数
SELECT 
   trim(os), --添加trim。
   CHAR_LENGTH(trim(os)) --添加trim。
from(
   SELECT
     case when device_type = 'android'
     then 'android'
     else 'ios' 
end as os 
FROM T1
);

-- 用CAST函数
SELECT
 os, 
CHAR_LENGTH(os) 
from
(SELECT 
 case when device_type = 'android'
 then cast('android' as varchar) --添加cast。
 else cast('ios' as varchar) --添加cast。
end as os 
FROM T1
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

测试结果

COALESCE

功能描述

返回列表中第一个非null的值，返回值类型和参数类型相同。如果列表中所有的值都是null，则返回null。

语法

COALESCE(A,B,...)
1

入参

示例

测试数据

测试语句

SELECT COALESCE(var1,var2) as aa
FROM T1
1
2

测试结果

NULLIF

功能描述

两个参数的值相同，则返回null，值不同则返回第一个参数的值。

语法

NULLIF(A,B)
1

入参

示例

测试数据

测试语句

SELECT NULLIF(var1,var2) as aa
FROM T1
1
2

测试结果

表值函数

STRING_SPLIT

功能描述

根据指定的分隔符将目标字符串拆分为子字符串行，返回子字符串的单列的表。需要注意以下几点：

• 如果目标字符串为NULL，则STRING_SPLIT表值函数返回一个空行。
• 如果目标字符串包含两个或多个连续出现的分隔符时，则返回长度为零的空子字符串。
• 如果目标字符串未包含指定分隔符，则只返回目标字符串。

语法

string_split(string, separator)
1

入参

分隔符separator暂不支持多字符串形式，只支持单个字符串形式。

示例

测试数据

测试语句

select d,v 
from T1, 
lateral table(string_split(d, s)) as T(v);
1
2
3

测试结果

GENERATE_SERIES

功能描述

生成一串连续的数值，分别为 from, from+1, from+2, … , to-1。

语法

GENERATE_SERIES(INT from, INT to)
1

入参

示例

测试数据

测试语句

SELECT s, e, v 
FROM T1, lateral table(GENERATE_SERIES(s, e)) 
as T(v)
1
2
3

测试结果

JSON_TUPLE

功能描述

从JSON字符串中取出各路径字符串所表示的值。

语法

JSON_TUPLE(str, path1, path2 ..., pathN)     
1

入参

示例

测试数据

测试语句

SELECT d, v 
FROM T1, lateral table(JSON_TUPLE(d, 'qwe', s))
AS T(v);   
1
2
3

测试结果

MULTI_KEYVALUE

功能描述

解析str字符串中的key-value对，匹配有split1和split2的key-value对，并返回参数列表里key_name1，key_name2等对应的value值列表。key_name值不存在时，对应的value值是null。

语法

MULTI_KEYVALUE(VARCHAR str, VARCHAR split1, VARCHAR split2, VARCHAR key_name1, VARCHAR key_name2, ...)
1

入参

示例

测试数据

测试语句

SELECT c1, c2 
FROM T1, lateral table(MULTI_KEYVALUE(str, split1, split2, key1, key2)) 
as T(c1, c2)
1
2
3

测试结果

类型转换函数

CAST

功能描述

将A值转换为给定类型。如果转换后的类型和目标表字段类型不匹配时，会出现类似Insert into: Query result and target table 'test_result' field type(s) not match. 的报错。

语法

CAST(A AS type)    
1

入参

A：任意类型数据。

示例

测试数据

测试语句

SELECT CAST(var1 AS INT) as aa
FROM T1;             
1
2

测试结果

聚合函数

AVG

功能描述

返回指定表达式中所有值的平均值。返回值默认为DOUBLE类型，如果您的结果表字段为非DOUBLE类型，您需要使用CAST进行转化。

语法

AVG(A)
1

入参

示例

测试数据

测试语句

SELECT AVG(var1) as aa
FROM T1;     
1
2

测试结果

CONCAT_AGG

功能描述

连接对应字段的字符串，默认连接符\n，连接完成后新生成的字符串。返回值VARCHAR类型。

语法

CONCAT_AGG([linedelimiter,] value ) 
1

入参

示例

测试数据

测试语句

SELECT 
      b,
      concat_agg(c) as var1, 
      concat_agg('-', c) as var2
FROM MyTable
GROUP BY b;         
1
2
3
4
5
6

测试结果

COUNT

功能描述

返回输入列的数量。

语法

COUNT(A)
1

入参

示例

测试数据

测试语句

SELECT COUNT(var1) as aa
FROM T1;     
1
2

测试结果

FIRST_VALUE

功能描述

获取数据流的第1条非null数据。根据order判定FIRST_VALUE所在的行，取order值最小的记录作为FIRST_VALUE。

语法

T FIRST_VALUE( T value )
T FIRST_VALUE( T value, BIGINT order )
1
2

入参

示例

测试数据

测试语句

SELECT c,
 FIRST_VALUE(b) 
OVER (
PARTITION BY c 
ORDER BY PROCTIME() RANGE UNBOUNDED PRECEDING
) AS var1
FROM T1;
1
2
3
4
5
6
7

测试结果

LAST_VALUE

功能描述

获取数据流的最后1条非NULL数据。根据ORDER判定LAST_VALUE所在的行，取ORDER值最大的记录作为LAST_VALUE。

语法

T LAST_VALUE(T value)
T LAST_VALUE(T value，BIGINT order)
1
2

入参

所有输入参数需要为相同的数据类型。

示例

测试数据

测试语句

SELECT c,
 LAST_VALUE(b)
OVER (
PARTITION BY c 
ORDER BY PROCTIME() RANGE UNBOUNDED PRECEDING
) AS var1
FROM T1;
1
2
3
4
5
6
7

测试结果

MAX

功能描述

返回所有输入值的最大值。

语法

MAX(A)
1

入参

示例

测试数据

测试语句

SELECT MAX(var1) as aa
FROM T1;
1
2

测试结果

MIN

功能描述

返回所有输入值的最小值。

语法

MIN(A)      
1

入参

示例

测试数据

测试语句

SELECT MIN(var1) as aa
FROM T1;
1
2

测试结果

SUM

功能描述

返回所有输入值的数值之和。

语法

SUM(A)
1

入参

示例

测试数据

测试语句

SELECT sum(var1) as aa
FROM T1;
1
2

测试结果

VAR_POP

功能描述

返回所有输入值的方差。

语法

T VAR_POP(T value)
1

入参

示例

测试数据

测试语句

SELECT 
VAR_POP(a) as `result`,
c
FROM MyTable
GROUP BY c;
1
2
3
4
5

测试结果

STDDEV_POP

功能描述

返回数值的总体标准差。

语法

T STDDEV_POP(T value)
1

入参

示例

测试数据

测试语句

SELECT c, STDDEV_POP(a) as dou1
FROM MyTable
GROUP BY c;
1
2
3

测试结果

其他函数

UUID

功能描述

返回通用唯一标识字符。

语法

VARCHAR UUID()
1

入参

无

示例

测试语句

SELECT uuid() as `result`
FROM T1
1
2

测试结果

DISTINCT

功能描述

DISTINCT用于SELECT语句中，可以对查询结果进行去重。

语法

SELECT DISTINCT expressions 
FROM tables;
1
2

入参

• DISTINCT必须放到开始位置。和其他函数一起使用时，DISTINCT也必须放到开始位置，例如，concat_agg(DISTINCT ',' ,device_id)。
• expressions是一个或多个expression，可以是具体的column，也可以是function等任何合法表达式。

示例

测试数据

测试语句

CREATE TABLE distinct_tab_source(
 FirstName VARCHAR,
 LastName VARCHAR
)WITH(
 type='random'
) ;

CREATE TABLE distinct_tab_sink(
 FirstName  VARCHAR,
 LastName VARCHAR
)WITH(
 type = 'print'
) ;

INSERT INTO distinct_tab_sink
SELECT DISTINCT FirstName, LastName --按照FirstName和LastName两个列进行去重。
FROM distinct_tab_source;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

测试结果

SUNS,HENGRAN和SUN,SHENGRAN两条记录并没有被去重，说明DISTINCT FirstName, LastName是对两个字段分别处理的，而不是Concat到一起再进行去重。

DISTINCT的等效写法

在SQL中利用GROUP BY语句也可以到达和DISTINCT类似的去重效果。GROUP BY语法如下。

SELECT expressions
FROM tables
GROUP BY expressions
;        
1
2
3
4

通过多路输出的方式编写的和DISTINCT示例等效的SQL，示例如下。

CREATE TABLE distinct_tab_source(
 FirstName VARCHAR,
 LastName VARCHAR
)WITH(
 type='random'
);

CREATE TABLE distinct_tab_sink(
 FirstName VARCHAR,
 LastName VARCHAR
)WITH(
 type = 'print'
);

CREATE TABLE distinct_tab_sink2(
 FirstName VARCHAR,
 LastName VARCHAR
)WITH(
 type = 'print'
);

INSERT INTO distinct_tab_sink
SELECT DISTINCT FirstName, LastName --按照FirstName和LastName两个列进行去重。
FROM distinct_tab_source;

INSERT INTO distinct_tab_sink2
SELECT FirstName, LastName
FROM distinct_tab_source
GROUP BY FirstName, LastName; --按照FirstName和LastName两个列进行去重。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

DISTINCT在聚合函数COUNT中的作用

DISTINCT能使聚合函数COUNT统计去重后的计数。

COUNT(DISTINCT expression)
1

expression目前只支持一个表达式。

COUNT DISTINCT语法示例

CREATE TABLE distinct_tab_source(
 FirstName VARCHAR,
 LastName VARCHAR
)WITH(
 type='random'
);

CREATE TABLE distinct_tab_sink(
 cnt BIGINT,
 distinct_cnt BIGINT
)WITH(
 type = 'print'
) ;

INSERT INTO distinct_tab_sink
    SELECT 
      COUNT(FirstName),  --不去重。
      COUNT(DISTINCT FirstName)  --按照FirstName去重。
    FROM distinct_tab_source;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19