内置函数

Flink Table API和SQL为用户提供了一组用于数据转换的内置函数。本页简要概述了它们。如果尚不支持您需要的函数，则可以实现用户定义的函数。如果您认为该函数足够通用，请打开一个Jira问题并附上详细说明。

标量函数

标量函数将零，一个或多个值作为输入，并返回单个值作为结果。

比较函数

比较函数描述
  1. value1 = value2
如果value1等于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。
  1. value1 <> value2
如果value1不等于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。
  1. value1 > value2
如果value1大于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。
  1. value1 >= value2
如果value1大于或等于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。
  1. value1 < value2
如果value1小于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。
  1. value1 <= value2
如果value1小于或等于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。
  1. value IS NULL
如果value为NULL，则返回TRUE 。
  1. value IS NOT NULL
如果value不为NULL，则返回TRUE 。
  1. value1 IS DISTINCT FROM value2
如果两个值不相等，则返回TRUE。NULL值在此处视为相同。例如，1 IS DISTINCT FROM NULL返回TRUE;NULL IS DISTINCT FROM NULL返回FALSE。
  1. value1 IS NOT DISTINCT FROM value2
如果两个值相等，则返回TRUE。NULL值在此处视为相同。例如，1 IS NOT DISTINCT FROM NULL返回FALSE;NULL IS NOT DISTINCT FROM NULL返回TRUE。
  1. value1 BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3
默认情况下（或使用ASYMMETRIC关键字），如果value1大于或等于value2且小于或等于value3，则返回TRUE 。使用SYMMETRIC关键字，如果value1包含在value2和value3之间，则返回TRUE 。当value2或value3为NULL时，返回FALSE或UNKNOWN。例如，12 BETWEEN 15 AND 12返回FALSE;12 BETWEEN SYMMETRIC 15 AND 12返回TRUE;12 BETWEEN 10 AND NULL返回UNKNOWN;12 BETWEEN NULL AND 10返回FALSE;12 BETWEEN SYMMETRIC NULL AND 12返回UNKNOWN。
  1. value1 NOT BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3
默认情况下（或使用ASYMMETRIC关键字），如果value1小于value2或大于value3，则返回TRUE 。使用SYMMETRIC关键字，如果value1不包含在value2和value3之间，则返回TRUE 。当value2或value3为NULL时，返回TRUE或UNKNOWN。例如，12 NOT BETWEEN 15 AND 12返回TRUE;12 NOT BETWEEN SYMMETRIC 15 AND 12返回FALSE;12 NOT BETWEEN NULL AND 15返回UNKNOWN;12 NOT BETWEEN 15 AND NULL返回TRUE;12 NOT BETWEEN SYMMETRIC 12 AND NULL返回UNKNOWN。
  1. string1 LIKE string2 [ ESCAPE char ]
如果string1匹配模式string2，则返回TRUE ; 如果string1或string2为NULL，则返回UNKNOWN 。如有必要，可以定义转义字符。注意：尚未支持转义字符。
  1. string1 NOT LIKE string2 [ ESCAPE char ]
如果string1与模式string2不匹配，则返回TRUE ; 如果string1或string2为NULL，则返回UNKNOWN 。如有必要，可以定义转义字符。注意：尚未支持转义字符。
  1. string1 SIMILAR TO string2 [ ESCAPE char ]
如果string1匹配SQL正则表达式string2，则返回TRUE ; 如果string1或string2为NULL，则返回UNKNOWN 。如有必要，可以定义转义字符。注意：尚未支持转义字符。
  1. string1 NOT SIMILAR TO string2 [ ESCAPE char ]
如果string1与SQL正则表达式string2不匹配，则返回TRUE ; 如果string1或string2为NULL，则返回UNKNOWN 。如有必要，可以定义转义字符。注意：尚未支持转义字符。
  1. value1 IN (value2 [, value3] )
如果给定列表中存在value1 （value2，value3，…），则返回TRUE 。当（value2，value3，…）。包含NULL，如果可以找到该数据元则返回TRUE，否则返回UNKNOWN。如果value1为NULL，则始终返回UNKNOWN 。例如，4 IN (1, 2, 3)返回FALSE;1 IN (1, 2, NULL)返回TRUE;4 IN (1, 2, NULL)返回UNKNOWN。
  1. value1 NOT IN (value2 [, value3] )
如果给定列表中不存在value1 （value2，value3，…），则返回TRUE 。当（value2，value3，…）。包含NULL，如果可以找到value1则返回FALSE ，否则返回UNKNOWN。如果value1为NULL，则始终返回UNKNOWN 。例如，4 NOT IN (1, 2, 3)返回TRUE;1 NOT IN (1, 2, NULL)返回FALSE;4 NOT IN (1, 2, NULL)返回UNKNOWN。
  1. EXISTS (sub-query)
如果子查询返回至少一行，则返回TRUE 。仅在可以在连接和组 算子操作中重写 算子操作时才支持。注意：对于流式查询， 算子操作将在连接和组 算子操作中重写。计算查询结果所需的状态可能会无限增长，具体取决于不同输入行的数量。请提供具有有效保存间隔的查询配置，以防止过大的状态。有关详细信息，请参阅Streaming Concepts。
  1. value IN (sub-query)
如果value等于子查询返回的行，则返回TRUE 。注意：对于流式查询， 算子操作将在连接和组 算子操作中重写。计算查询结果所需的状态可能会无限增长，具体取决于不同输入行的数量。请提供具有有效保存间隔的查询配置，以防止过大的状态。有关详细信息，请参阅Streaming Concepts。
  1. value NOT IN (sub-query)
如果value不等于子查询返回的每一行，则返回TRUE 。注意：对于流式查询， 算子操作将在连接和组 算子操作中重写。计算查询结果所需的状态可能会无限增长，具体取决于不同输入行的数量。请提供具有有效保存间隔的查询配置，以防止过大的状态。有关详细信息，请参阅Streaming Concepts。
Comparison functionsDescription
  1. ANY1 === ANY2
Returns TRUE if ANY1 is equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY1 !== ANY2
Returns TRUE if ANY1 is not equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY1 > ANY2
Returns TRUE if ANY1 is greater than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY1 >= ANY2
Returns TRUE if ANY1 is greater than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY1 < ANY2
Returns TRUE if ANY1 is less than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY1 <= ANY2
Returns TRUE if ANY1 is less than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY.isNull
Returns TRUE if ANY is NULL.
  1. ANY.isNotNull
Returns TRUE if ANY is not NULL.
  1. STRING1.like(STRING2)
Returns TRUE if STRING1 matches pattern STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL.E.g., "JoKn".like("Jon%") returns TRUE.
  1. STRING.similar(STRING)
Returns TRUE if _STRING1 matches SQL regular expression STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL.E.g., "A".similar("A+") returns TRUE.
  1. ANY1.in(ANY2, ANY3, …)
 Returns TRUE if ANY1 exists in a given list (ANY2, ANY3, …).When (ANY2, ANY3, …). contains NULL, returns TRUE if the element can be found and UNKNOWN otherwise. Always returns UNKNOWN if ANY1 is NULL.E.g., 4.in(1, 2, 3) returns FALSE.
  1. ANY.in(TABLE)
Returns TRUE if ANY is equal to a row returned by sub-query TABLE.Note: For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See Streaming Concepts for details.
  1. ANY1.between(ANY2, ANY3)
Returns TRUE if ANY1 is greater than or equal to ANY2 and less than or equal to ANY3.When either ANY2 or ANY3 is NULL, returns FALSE or UNKNOWN.E.g., 12.between(15, 12) returns FALSE;12.between(10, Null(INT)) returns UNKNOWN;12.between(Null(INT), 10) returns FALSE.
  1. ANY1.notBetween(ANY2, ANY3)
Returns TRUE if ANY1 is less than ANY2 or greater than ANY3.When either ANY2 or ANY3 is NULL, returns TRUE or UNKNOWN.E.g., 12.notBetween(15, 12) returns TRUE;12.notBetween(Null(INT), 15) returns UNKNOWN;12.notBetween(15, Null(INT)) returns TRUE.
Comparison functionsDescription
  1. ANY1 === ANY2
Returns TRUE if ANY1 is equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY1 !== ANY2
Returns TRUE if ANY1 is not equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY1 > ANY2
Returns TRUE if ANY1 is greater than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY1 >= ANY2
Returns TRUE if ANY1 is greater than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY1 < ANY2
Returns TRUE if ANY1 is less than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY1 <= ANY2
Returns TRUE if ANY1 is less than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
  1. ANY.isNull
Returns TRUE if ANY is NULL.
  1. ANY.isNotNull
Returns TRUE if ANY is not NULL.
  1. STRING1.like(STRING2)
Returns TRUE if STRING1 matches pattern STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL.E.g., "JoKn".like("Jon%") returns TRUE.
  1. STRING1.similar(STRING2)
Returns TRUE if _STRING1 matches SQL regular expression STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL.E.g., "A".similar("A+") returns TRUE.
  1. ANY1.in(ANY2, ANY3, …)
 Returns TRUE if ANY1 exists in a given list (ANY2, ANY3, …).When (ANY2, ANY3, …). contains NULL, returns TRUE if the element can be found and UNKNOWN otherwise. Always returns UNKNOWN if ANY1 is NULL.E.g., 4.in(1, 2, 3) returns FALSE.
  1. ANY.in(TABLE)
Returns TRUE if ANY is equal to a row returned by sub-query TABLE.Note: For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See Streaming Concepts for details.
  1. ANY1.between(ANY2, ANY3)
Returns TRUE if ANY1 is greater than or equal to ANY2 and less than or equal to ANY3.When either ANY2 or ANY3 is NULL, returns FALSE or UNKNOWN.E.g., 12.between(15, 12) returns FALSE;12.between(10, Null(Types.INT)) returns UNKNOWN;12.between(Null(Types.INT), 10) returns FALSE.
  1. ANY1.notBetween(ANY2, ANY3)
Returns TRUE if ANY1 is less than ANY2 or greater than ANY3.When either ANY2 or ANY3 is NULL, returns TRUE or UNKNOWN.E.g., 12.notBetween(15, 12) returns TRUE;12.notBetween(Null(Types.INT), 15) returns UNKNOWN;12.notBetween(15, Null(Types.INT)) returns TRUE.

逻辑函数

逻辑函数描述
  1. boolean1 OR boolean2
如果boolean1为TRUE或boolean2为TRUE，则返回TRUE。支持三值逻辑。例如，TRUE OR UNKNOWN返回TRUE。
  1. boolean1 AND boolean2
如果boolean1和boolean2都为TRUE，则返回TRUE。支持三值逻辑。例如，TRUE AND UNKNOWN返回UNKNOWN。
  1. NOT boolean
如果boolean为FALSE，则返回TRUE ; 否则返回TRUE 。如果boolean为TRUE，则返回FALSE ; 如果布尔值为UNKNOWN，则返回UNKNOWN。
  1. boolean IS FALSE
如果boolean为FALSE，则返回TRUE ; 否则返回TRUE 。如果boolean为TRUE或UNKNOWN，则返回FALSE 。
  1. boolean IS NOT FALSE
如果boolean为TRUE或UNKNOWN，则返回TRUE;否则返回TRUE 。如果boolean为FALSE，则返回FALSE。
  1. boolean IS TRUE
如果boolean为TRUE，则返回TRUE; 如果boolean为FALSE或UNKNOWN，则返回FALSE 。
  1. boolean IS NOT TRUE
如果boolean为FALSE或UNKNOWN，则返回TRUE;否则返回TRUE 。如果boolean为FALSE，则返回FALSE。
  1. boolean IS UNKNOWN
如果boolean是UNKNOWN，则返回TRUE ; 否则返回TRUE 。如果boolean为TRUE或FALSE，则返回FALSE。
  1. boolean IS NOT UNKNOWN
如果boolean为TRUE或FALSE，则返回TRUE;否则返回TRUE 。如果布尔值为UNKNOWN，则返回FALSE 。
Logical functionsDescription
  1. BOOLEAN1 || BOOLEAN2
Returns TRUE if BOOLEAN1 is TRUE or BOOLEAN2 is TRUE. Supports three-valued logic.E.g., true || Null(BOOLEAN) returns TRUE.
  1. BOOLEAN1 && BOOLEAN2
Returns TRUE if BOOLEAN1 and BOOLEAN2 are both TRUE. Supports three-valued logic.E.g., true && Null(BOOLEAN) returns UNKNOWN.
  1. !BOOLEAN
Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE; returns UNKNOWN if BOOLEAN is UNKNOWN.
  1. BOOLEAN.isTrue
Returns TRUE if BOOLEAN is TRUE; returns FALSE if BOOLEAN is FALSE or UNKNOWN.
  1. BOOLEAN.isFalse
Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE or UNKNOWN.
  1. BOOLEAN.isNotTrue
Returns TRUE if BOOLEAN is FALSE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.
  1. BOOLEAN.isNotFalse
Returns TRUE if BOOLEAN is TRUE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.
Logical functionsDescription
  1. BOOLEAN1 || BOOLEAN2
Returns TRUE if BOOLEAN1 is TRUE or BOOLEAN2 is TRUE. Supports three-valued logic.E.g., true || Null(Types.BOOLEAN) returns TRUE.
  1. BOOLEAN1 && BOOLEAN2
Returns TRUE if BOOLEAN1 and BOOLEAN2 are both TRUE. Supports three-valued logic.E.g., true && Null(Types.BOOLEAN) returns UNKNOWN.
  1. !BOOLEAN
Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE; returns UNKNOWN if BOOLEAN is UNKNOWN.
  1. BOOLEAN.isTrue
Returns TRUE if BOOLEAN is TRUE; returns FALSE if BOOLEAN is FALSE or UNKNOWN.
  1. BOOLEAN.isFalse
Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE or UNKNOWN.
  1. BOOLEAN.isNotTrue
Returns TRUE if BOOLEAN is FALSE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.
  1. BOOLEAN.isNotFalse
Returns TRUE if BOOLEAN is TRUE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.

算术函数

算术函数描述
  1. + numeric
返回数字。
  1. - numeric
返回负数字。
  1. numeric1 + numeric2
返回numeric1加上numeric2。
  1. numeric1 - numeric2
返回numeric1减去numeric2。
  1. numeric1  numeric2
返回numeric1乘以numeric2。
  1. numeric1 / numeric2
返回numeric1除以numeric2。
  1. POWER(numeric1, numeric2)
返回numeric1上升到numeric2的幂。
  1. ABS(numeric)
返回numeric的绝对值。
  1. MOD(numeric1, numeric2)
返回numeric1的余数（模数）除以numeric2。仅当numeric1为负数时，结果才为负数。
  1. SQRT(numeric)
返回数字的平方根。
  1. LN(numeric)
返回的自然对数（以e为底）的数字。
  1. LOG10(numeric)
返回数字的基数10对数。
  1. LOG2(numeric)
返回数字的基数2对数。
  1. LOG(numeric2)LOG(numeric1, numeric2)
使用一个参数调用时，返回numeric2的自然对数。当使用两个参数调用时，此函数将numeric2的对数返回到基数numeric1。*注意：目前，numeric2必须大于0且numeric1必须大于1。
  1. EXP(numeric)
返回e提升到数字的幂。
  1. CEIL(numeric)CEILING(numeric)
将数字向上舍入，并返回大于或等于numeric的最小数字。
  1. FLOOR(numeric)
将数字向下舍入，并返回小于或等于numeric的最大数字。
  1. SIN(numeric)
返回数字的正弦值。
  1. COS(numeric)
返回数字的余弦值。
  1. TAN(numeric)
返回数字的正切值。
  1. COT(numeric)
返回数字的余切。
  1. ASIN(numeric)
返回数字的反正弦值。
  1. ACOS(numeric)
返回数字的反余弦值。
  1. ATAN(numeric)
返回数字的反正切。
  1. ATAN2(numeric1, numeric2)
返回坐标（numeric1，numeric2）的反正切。
  1. DEGREES(numeric)
返回弧度数字的度数表示。
  1. RADIANS(numeric)
返回度数数字的弧度表示。
  1. SIGN(numeric)
返回数字的符号。
  1. ROUND(numeric, integer)
返回舍入为数字的整数小数位数。
  1. PI
返回比pi更接近任何其他值的值。
  1. E()
返回比e的任何其他值更接近的值。
  1. RAND()
返回介于0.0（包括）和1.0（不包括）之间的伪随机双精度值。
  1. RAND(integer)
使用初始种子整数返回介于0.0（含）和1.0（不包含）之间的伪随机双精度值。如果两个RAND函数具有相同的初始种子，则它们将返回相同的数字序列。
  1. RANDINTEGER(integer)
返回0（包括）和整数（不包括）之间的伪随机整数值。
  1. RAND_INTEGER(integer1, integer2)
返回0（包括）之间的伪随机整数值和具有初始种子的指定值（不包括）。如果两个RAND_INTEGER函数具有相同的初始种子和绑定，则它们将返回相同的数字序列。
  1. UUID()
根据RFC 4122类型4（伪随机生成的）UUID返回UUID（通用唯一标识符）字符串（例如，“3d3c68f7-f608-473f-b60c-b0c44ad4cc4e”）。使用加密强伪随机数生成器生成UUID。
  1. BIN(integer)
以二进制格式返回整数的字符串表示形式。如果integer为NULL，则返回NULL。例如，BIN(4)返回'100'并BIN(12)返回'1100'。
  1. HEX(numeric)HEX(string)
返回整数数值的字符串表示形式或十六进制格式的字符串_。如果参数为NULL，则返回NULL。例如，数字20导致“14”，数字100导致“64”，字符串“hello，world”导致“68656C6C6F2C776F726C64”。
Arithmetic functionsDescription
  1. + NUMERIC
Returns NUMERIC.
  1. - NUMERIC
Returns negative NUMERIC.
  1. NUMERIC1 + NUMERIC2
Returns NUMERIC1 plus NUMERIC2.
  1. NUMERIC1 - NUMERIC2
Returns NUMERIC1 minus NUMERIC2.
  1. NUMERIC1  NUMERIC2
Returns NUMERIC1 multiplied by NUMERIC2.
  1. NUMERIC1 / NUMERIC2
Returns NUMERIC1 divided by NUMERIC2.
  1. NUMERIC1.power(NUMERIC2)
Returns NUMERIC1 raised to the power of NUMERIC2.
  1. NUMERIC.abs()
Returns the absolute value of NUMERIC.
  1. NUMERIC1 % NUMERIC2
Returns the remainder (modulus) of NUMERIC1 divided by NUMERIC2. The result is negative only if numeric1 is negative.
  1. NUMERIC.sqrt()
Returns the square root of NUMERIC.
  1. NUMERIC.ln()
Returns the natural logarithm (base e) of NUMERIC.
  1. NUMERIC.log10()
Returns the base 10 logarithm of NUMERIC.
  1. NUMERIC.log2()
Returns the base 2 logarithm of NUMERIC.
  1. NUMERIC1.log()NUMERIC1.log(NUMERIC2)
When called without argument, returns the natural logarithm of NUMERIC1. When called with an argument, returns the logarithm of NUMERIC1 to the base NUMERIC2.*Note: Currently, NUMERIC1 must be greater than 0 and NUMERIC2 must be greater than 1.
  1. NUMERIC.exp()
Returns e raised to the power of NUMERIC.
  1. NUMERIC.ceil()
Rounds NUMERIC up, and returns the smallest number that is greater than or equal to NUMERIC.
  1. NUMERIC.floor()
Rounds NUMERIC down, and returns the largest number that is less than or equal to NUMERIC.
  1. NUMERIC.sin()
Returns the sine of NUMERIC.
  1. NUMERIC.cos()
Returns the cosine of NUMERIC.
  1. NUMERIC.tan()
Returns the tangent of NUMERIC.
  1. NUMERIC.cot()
Returns the cotangent of a NUMERIC.
  1. NUMERIC.asin()
Returns the arc sine of NUMERIC.
  1. NUMERIC.acos()
Returns the arc cosine of NUMERIC.
  1. NUMERIC.atan()
Returns the arc tangent of NUMERIC.
  1. atan2(NUMERIC1, NUMERIC2)
Returns the arc tangent of a coordinate (NUMERIC1, NUMERIC2).
  1. NUMERIC.degrees()
Returns the degree representation of a radian NUMERIC.
  1. NUMERIC.radians()
Returns the radian representation of a degree NUMERIC.
  1. NUMERIC.sign()
Returns the signum of NUMERIC.
  1. NUMERIC.round(INT)
Returns a number rounded to INT decimal places for NUMERIC.
  1. pi()
Returns a value that is closer than any other values to pi.
  1. e()
Returns a value that is closer than any other values to e.
  1. rand()
Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).
  1. rand(INTEGER)
Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with an initial seed INTEGER. Two RAND functions will return identical sequences of numbers if they have the same initial seed.
  1. randInteger(INTEGER)
Returns a pseudorandom integer value between 0 (inclusive) and INTEGER (exclusive).
  1. randInteger(INTEGER1, INTEGER2)
Returns a pseudorandom integer value between 0 (inclusive) and INTEGER2 (exclusive) with an initial seed INTEGER1. Two randInteger functions will return identical sequences of numbers if they have same initial seed and bound.
  1. uuid()
Returns an UUID (Universally Unique Identifier) string (e.g., "3d3c68f7-f608-473f-b60c-b0c44ad4cc4e") according to RFC 4122 type 4 (pseudo randomly generated) UUID. The UUID is generated using a cryptographically strong pseudo random number generator.
  1. INTEGER.bin()
Returns a string representation of INTEGER in binary format. Returns NULL if INTEGER is NULL.E.g., 4.bin() returns "100" and 12.bin() returns "1100".
  1. NUMERIC.hex()STRING.hex()
Returns a string representation of an integer NUMERIC value or a STRING in hex format. Returns NULL if the argument is NULL.E.g. a numeric 20 leads to "14", a numeric 100 leads to "64", a string "hello,world" leads to "68656C6C6F2C776F726C64".
Arithmetic functionsDescription
  1. + NUMERIC
Returns NUMERIC.
  1. - NUMERIC
Returns negative NUMERIC.
  1. NUMERIC1 + NUMERIC2
Returns NUMERIC1 plus NUMERIC2.
  1. NUMERIC1 - NUMERIC2
Returns NUMERIC1 minus NUMERIC2.
  1. NUMERIC1  NUMERIC2
Returns NUMERIC1 multiplied by NUMERIC2.
  1. NUMERIC1 / NUMERIC2
Returns NUMERIC1 divided by NUMERIC2.
  1. NUMERIC1.power(NUMERIC2)
Returns NUMERIC1 raised to the power of NUMERIC2.
  1. NUMERIC.abs()
Returns the absolute value of NUMERIC.
  1. NUMERIC1 % NUMERIC2
Returns the remainder (modulus) of NUMERIC1 divided by NUMERIC2. The result is negative only if numeric1 is negative.
  1. NUMERIC.sqrt()
Returns the square root of NUMERIC.
  1. NUMERIC.ln()
Returns the natural logarithm (base e) of NUMERIC.
  1. NUMERIC.log10()
Returns the base 10 logarithm of NUMERIC.
  1. NUMERIC.log2()
Returns the base 2 logarithm of NUMERIC.
  1. NUMERIC1.log()NUMERIC1.log(NUMERIC2)
When called without argument, returns the natural logarithm of NUMERIC1. When called with an argument, returns the logarithm of NUMERIC1 to the base NUMERIC2.*Note: Currently, NUMERIC1 must be greater than 0 and NUMERIC2 must be greater than 1.
  1. NUMERIC.exp()
Returns e raised to the power of NUMERIC.
  1. NUMERIC.ceil()
Rounds NUMERIC up, and returns the smallest number that is greater than or equal to NUMERIC.
  1. NUMERIC.floor()
Rounds NUMERIC down, and returns the largest number that is less than or equal to NUMERIC.
  1. NUMERIC.sin()
Returns the sine of NUMERIC.
  1. NUMERIC.cos()
Returns the cosine of NUMERIC.
  1. NUMERIC.tan()
Returns the tangent of NUMERIC.
  1. NUMERIC.cot()
Returns the cotangent of a NUMERIC.
  1. NUMERIC.asin()
Returns the arc sine of NUMERIC.
  1. NUMERIC.acos()
Returns the arc cosine of NUMERIC.
  1. NUMERIC.atan()
Returns the arc tangent of NUMERIC.
  1. atan2(NUMERIC1, NUMERIC2)
Returns the arc tangent of a coordinate (NUMERIC1, NUMERIC2).
  1. NUMERIC.degrees()
Returns the degree representation of a radian NUMERIC.
  1. NUMERIC.radians()
Returns the radian representation of a degree NUMERIC.
  1. NUMERIC.sign()
Returns the signum of NUMERIC.
  1. NUMERIC.round(INT)
Returns a number rounded to INT decimal places for NUMERIC.
  1. pi()
Returns a value that is closer than any other values to pi.
  1. e()
Returns a value that is closer than any other values to e.
  1. rand()
Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).
  1. rand(INTEGER)
Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with an initial seed INTEGER. Two RAND functions will return identical sequences of numbers if they have the same initial seed.
  1. randInteger(INTEGER)
Returns a pseudorandom integer value between 0 (inclusive) and INTEGER (exclusive).
  1. randInteger(INTEGER1, INTEGER2)
Returns a pseudorandom integer value between 0 (inclusive) and INTEGER2 (exclusive) with an initial seed INTEGER1. Two randInteger functions will return identical sequences of numbers if they have same initial seed and bound.
  1. uuid()
Returns an UUID (Universally Unique Identifier) string (e.g., "3d3c68f7-f608-473f-b60c-b0c44ad4cc4e") according to RFC 4122 type 4 (pseudo randomly generated) UUID. The UUID is generated using a cryptographically strong pseudo random number generator.
  1. INTEGER.bin()
Returns a string representation of INTEGER in binary format. Returns NULL if INTEGER is NULL.E.g., 4.bin() returns "100" and 12.bin() returns "1100".
  1. NUMERIC.hex()STRING.hex()
Returns a string representation of an integer NUMERIC value or a STRING in hex format. Returns NULL if the argument is NULL.E.g. a numeric 20 leads to "14", a numeric 100 leads to "64", a string "hello,world" leads to "68656C6C6F2C776F726C64".

字符串函数

字符串函数描述
  1. string1 || string2
返回string1和string2的串联。
  1. CHARLENGTH(string)CHARACTER_LENGTH(string)
返回string中的字符数。
  1. UPPER(string)
以大写形式返回字符串。
  1. LOWER(string)
以小写形式返回字符串。
  1. POSITION(string1 IN string2)
返回的第一次出现的位置（从1开始）字符串1在字符串2 ; 如果在string2中找不到string1，则返回0 。
  1. TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2)
返回一个字符串，用于从string2中删除前导和/或尾随字符string1。默认情况下，两侧的空格都被删除。
  1. LTRIM(string)
返回一个字符串，从去除左空格的字符串。例如，LTRIM(' This is a test String.')返回“This is a test String。”。
  1. RTRIM(string)
返回一个字符串，从去除右方的空格字符串。例如，RTRIM('This is a test String. ')返回“This is a test String。”。
  1. REPEAT(string, integer)
返回一个重复基本字符串_整数倍的字符串。例如，REPEAT('This is a test String.', 2)返回“这是一个测试字符串。这是一个测试字符串。”。
  1. OVERLAY(string1 PLACING string2 FROM integer1 [ FOR integer2 ])
返回替换字符串整数2（字符串2通过默认的长度）的字符字符串1与字符串2从位置整数1。例如，OVERLAY('This is an old string' PLACING ' new' FROM 10 FOR 5)返回“这是一个新字符串”
  1. SUBSTRING(string FROM integer1 [ FOR integer2 ])
返回从位置integer1开始的字符串的子字符串，其长度为integer2（默认情况下为结尾）。
  1. INITCAP(string)
返回一种新形式的字符串，每个单词的第一个字符转换为大写，其余字符转换为小写。这里的单词意味着一系列字母数字字符。
  1. CONCAT(string1, string2,…)
返回连接string1，string2，…的字符串。如果任何参数为NULL，则返回NULL。例如，CONCAT('AA', 'BB', 'CC')返回“AABBCC”。
  1. CONCATWS(string1, string2, string3,…)
返回一个字符串，用于将string2，string3，…与分隔符string1连接起来。在要连接的字符串之间添加分隔符。返回NULL如果string1为NULL。与之相比CONCAT()，CONCAT_WS()自动跳过NULL参数。例如，CONCAT_WS('~', 'AA', NULL, 'BB', '', 'CC')返回“AA~BB ~~ CC”。
  1. LPAD(string1, integer, string2)
返回从string1左侧填充string2到整数字符长度的新字符串。如果string1的长度小于整数，则返回string1缩短为整数字符。例如，LPAD('hi',4,'??')返回“?? hi”; LPAD('hi',1,'??')返回“h”。
  1. RPAD(string1, integer, string2)
返回从string1右侧填充string2到整数字符长度的新字符串。如果string1的长度小于整数，则返回string1缩短为整数字符。例如，RPAD('hi',4,'??')返回“hi ??”，RPAD('hi',1,'??')返回“h”。
  1. FROM_BASE64(string)
返回string的base64解码结果; 如果string为NULL，则返回NULL。例如，FROM_BASE64('aGVsbG8gd29ybGQ=')返回“hello world”。
  1. TO_BASE64(string)
从string返回base64编码的结果; 如果string_为NULL，则返回NULL。例如，TO_BASE64('hello world')返回“aGVsbG8gd29ybGQ =”。
String functionsDescription
  1. STRING1 + STRING2
Returns the concatenation of STRING1 and STRING2.
  1. STRING.charLength()
Returns the number of characters in STRING.
  1. STRING.upperCase()
Returns STRING in uppercase.
  1. STRING.lowerCase()
Returns STRING in lowercase.
  1. STRING1.position(STRING2)
Returns the position (start from 1) of the first occurrence of STRING1 in STRING2; returns 0 if STRING1 cannot be found in STRING2.
  1. STRING1.trim(LEADING, STRING2)STRING1.trim(TRAILING, STRING2)STRING1.trim(BOTH, STRING2)STRING1.trim(BOTH)STRING1.trim()
Returns a string that removes leading and/or trailing characters STRING2 from STRING1. By default, whitespaces at both sides are removed.
  1. STRING.ltrim()
Returns a string that removes the left whitespaces from STRING.E.g., ' This is a test String.'.ltrim() returns "This is a test String.".
  1. STRING.rtrim()
Returns a string that removes the right whitespaces from STRING.E.g., 'This is a test String. '.rtrim() returns "This is a test String.".
  1. STRING.repeat(INT)
Returns a string that repeats the base STRING__INT times.E.g., 'This is a test String.'.repeat(2) returns "This is a test String.This is a test String.".
  1. STRING1.overlay(STRING2, INT1)STRING1.overlay(STRING2, INT1, INT2)
Returns a string that replaces INT2 (STRING2's length by default) characters of STRING1 with STRING2 from position INT1.E.g., 'xxxxxtest'.overlay('xxxx', 6) returns "xxxxxxxxx"; 'xxxxxtest'.overlay('xxxx', 6, 2) returns "xxxxxxxxxst".
  1. STRING.substring(INT1)STRING.substring(INT1, INT2)
Returns a substring of STRING starting from position INT1 with length INT2 (to the end by default).
  1. STRING.initCap()
Returns a new form of STRING with the first character of each word converted to uppercase and the rest characters to lowercase. Here a word means a sequences of alphanumeric characters.
  1. concat(STRING1, STRING2, …)
Returns a string that concatenates STRING1, STRING2, …. Returns NULL if any argument is NULL.E.g., concat('AA', 'BB', 'CC') returns "AABBCC".
  1. concatws(STRING1, STRING2, STRING3, …)
Returns a string that concatenates _STRING2, STRING3, … with a separator STRING1. The separator is added between the strings to be concatenated. Returns NULL If STRING1 is NULL. Compared with concat(), concatws() automatically skips NULL arguments.E.g., concat_ws('~', 'AA', Null(STRING), 'BB', '', 'CC') returns "AA~BB~~CC".
  1. STRING1.lpad(INT, STRING2)
Returns a new string from _STRING1 left-padded with STRING2 to a length of INT characters. If the length of STRING1 is shorter than INT, returns STRING1 shortened to INT characters.E.g., 'hi'.lpad(4, '??') returns "??hi"; 'hi'.lpad(1, '??') returns "h".
  1. STRING1.rpad(INT, STRING2)
Returns a new string from STRING1 right-padded with STRING2 to a length of INT characters. If the length of STRING1 is shorter than INT, returns STRING1 shortened to INT characters.E.g., 'hi'.rpad(4, '??') returns "hi??"; 'hi'.rpad(1, '??') returns "h".
  1. STRING.fromBase64()
Returns the base64-decoded result from STRING; returns NULL if STRING is NULL.E.g., 'aGVsbG8gd29ybGQ='.fromBase64() returns "hello world".
  1. STRING.toBase64()
Returns the base64-encoded result from STRING; returns NULL if STRING is NULL.E.g., 'hello world'.toBase64() returns "aGVsbG8gd29ybGQ=".
String functionsDescription
  1. STRING1 + STRING2
Returns the concatenation of STRING1 and STRING2.
  1. STRING.charLength()
Returns the number of characters in STRING.
  1. STRING.upperCase()
Returns STRING in uppercase.
  1. STRING.lowerCase()
Returns STRING in lowercase.
  1. STRING1.position(STRING2)
Returns the position (start from 1) of the first occurrence of STRING1 in STRING2; returns 0 if STRING1 cannot be found in STRING2.
  1. STRING.trim(  leading = true,  trailing = true,  character = " ")
Returns a string that removes leading and/or trailing characters from STRING.
  1. STRING.ltrim()
Returns a string that removes the left whitespaces from STRING.E.g., " This is a test String.".ltrim() returns "This is a test String.".
  1. STRING.rtrim()
Returns a string that removes the right whitespaces from STRING.E.g., "This is a test String. ".rtrim() returns "This is a test String.".
  1. STRING.repeat(INT)
Returns a string that repeats the base STRING__INT times.E.g., "This is a test String.".repeat(2) returns "This is a test String.This is a test String.".
  1. STRING1.overlay(STRING2, INT1)STRING1.overlay(STRING2, INT1, INT2)
Returns a string that replaces INT2 (STRING2's length by default) characters of STRING1 with STRING2 from position INT1.E.g., "xxxxxtest".overlay("xxxx", 6) returns "xxxxxxxxx"; "xxxxxtest".overlay("xxxx", 6, 2) returns "xxxxxxxxxst".
  1. STRING.substring(INT1)STRING.substring(INT1, INT2)
Returns a substring of STRING starting from position INT1 with length INT2 (to the end by default).
  1. STRING.initCap()
Returns a new form of STRING with the first character of each word converted to uppercase and the rest characters to lowercase. Here a word means a sequences of alphanumeric characters.
  1. concat(STRING1, STRING2, …)
Returns a string that concatenates STRING1, STRING2, …. Returns NULL if any argument is NULL.E.g., concat("AA", "BB", "CC") returns "AABBCC".
  1. concatws(STRING1, STRING2, STRING3, …)
Returns a string that concatenates _STRING2, STRING3, … with a separator STRING1. The separator is added between the strings to be concatenated. Returns NULL If STRING1 is NULL. Compared with concat(), concatws() automatically skips NULL arguments.E.g., concat_ws("~", "AA", Null(Types.STRING), "BB", "", "CC") returns "AA~BB~~CC".
  1. STRING1.lpad(INT, STRING2)
Returns a new string from _STRING1 left-padded with STRING2 to a length of INT characters. If the length of STRING1 is shorter than INT, returns STRING1 shortened to INT characters.E.g., "hi".lpad(4, "??") returns "??hi"; "hi".lpad(1, "??") returns "h".
  1. STRING1.rpad(INT, STRING2)
Returns a new string from STRING1 right-padded with STRING2 to a length of INT characters. If the length of STRING1 is shorter than INT, returns STRING1 shortened to INT characters.E.g., "hi".rpad(4, "??") returns "hi??"; "hi".rpad(1, "??") returns "h".
  1. STRING.fromBase64()
Returns the base64-decoded result from STRING; returns null If STRING is NULL.E.g., "aGVsbG8gd29ybGQ=".fromBase64() returns "hello world".
  1. STRING.toBase64()
Returns the base64-encoded result from STRING; returns NULL if STRING is NULL.E.g., "hello world".toBase64() returns "aGVsbG8gd29ybGQ=".

时间函数

时间函数描述
  1. DATE string
返回以“yyyy-MM-dd”形式从字符串解析的SQL日期。
  1. TIME string
返回以“HH：mm：ss”形式从字符串解析的SQL时间。
  1. TIMESTAMP string
返回以字符串形式解析的SQL时间戳，格式为“yyyy-MM-dd HH：mm：ss [.SSS]”。
  1. INTERVAL string range
对于SQL间隔为毫秒，以“dd hh：mm：ss.fff”形式解析间隔字符串，对于SQL间隔月，解析“yyyy-mm”。的间隔范围可以是DAY，MINUTE，DAY TO HOUR，或DAY TO SECOND的毫秒时间间隔; YEAR或YEAR TO MONTH间隔数月。例如INTERVAL '10 00:00:00.004' DAY TO SECOND，INTERVAL '10' DAY或者INTERVAL '2-10' YEAR TO MONTH返回的时间间隔。
  1. CURRENTDATE
返回UTC时区中的当前SQL日期。
  1. CURRENT_TIME
返回UTC时区中的当前SQL时间。
  1. CURRENT_TIMESTAMP
返回UTC时区中的当前SQL时间戳。
  1. LOCALTIME
返回本地时区的当前SQL时间。
  1. LOCALTIMESTAMP
返回本地时区的当前SQL时间戳。
  1. EXTRACT(timeintervalunit FROM temporal)
返回从temporal的timeintervalitit部分提取的long值。例如，EXTRACT(DAY FROM DATE '2006-06-05')返回5。
  1. YEAR(date)
返回SQL日期日期的年份。相当于EXTRACT（从日期开始）。例如，YEAR(DATE '1994-09-27')返回1994年。
  1. QUARTER(date)
从SQL返回日期一年的季度（1和4之间的整数）日期。相当于EXTRACT(QUARTER FROM date)。例如，QUARTER(DATE '1994-09-27')返回3。
  1. MONTH(date)
从SQL日期日期返回一年中的月份（1到12之间的整数）。相当于EXTRACT(MONTH FROM date)。例如，MONTH(DATE '1994-09-27')返回9。
  1. WEEK(date)
从SQL date 日期返回一年中的一周（1到53之间的整数）。相当于EXTRACT(WEEK FROM date)。例如，WEEK(DATE '1994-09-27')返回39。
  1. DAYOFYEAR(date)
返回一年的从SQL日期当天（1和366之间的整数）日期。相当于EXTRACT(DOY FROM date)。例如，DAYOFYEAR(DATE '1994-09-27')返回270。
  1. DAYOFMONTH(date)
从SQL日期日期返回一个月中的某一天（1到31之间的整数）。相当于EXTRACT(DAY FROM date)。例如，DAYOFMONTH(DATE '1994-09-27')返回27。
  1. DAYOFWEEK(date)
从SQL date date返回一周中的星期几（1到7之间的整数;星期日= 1）EXTRACT(DOW FROM date)。等效于。例如，DAYOFWEEK(DATE '1994-09-27')返回3。
  1. HOUR(timestamp)
从SQL时间戳记时间戳返回一天中的小时（0到23之间的整数）。相当于EXTRACT(HOUR FROM timestamp)。例如，HOUR(TIMESTAMP '1994-09-27 13:14:15')返回13。
  1. MINUTE(timestamp)
从SQL时间戳记时间戳返回一小时的分钟（0到59之间的整数）。相当于EXTRACT(MINUTE FROM timestamp)。例如，MINUTE(TIMESTAMP '1994-09-27 13:14:15')返回14。
  1. SECOND(timestamp)
从SQL时间戳返回第二分钟（0到59之间的整数）。相当于EXTRACT(SECOND FROM timestamp)。例如，SECOND(TIMESTAMP '1994-09-27 13:14:15')返回15。
  1. FLOOR(timepoint TO timeintervalunit)
返回将时间点向下舍入到时间单位timeintervalunit的值。例如，FLOOR(TIME '12:44:31' TO MINUTE)返回12:44:00。
  1. CEIL(timepoint TO timeintervalunit)
返回一个将时间点舍入到时间单位timeintervalunit的值。例如，CEIL(TIME '12:44:31' TO MINUTE)返回12:45:00。
  1. (timepoint1, temporal1) OVERLAPS (timepoint2, temporal2)
如果由（timepoint1，temporal1）和（timepoint2，temporal2）定义的两个时间间隔重叠，则返回TRUE 。时间值可以是时间点或时间间隔。例如，(TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR)返回TRUE; (TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:15:00', INTERVAL '3' HOUR)返回FALSE。
  1. DATE_FORMAT(timestamp, string)
返回使用指定格式字符串格式化时间戳的字符串。格式规范在Date Format Specifier表中给出。
  1. TIMESTAMPADD(unit, interval, timevalue)
返回一个新的时间值，它将（带符号）整数时间间隔添加到timevalue。为单位的时间间隔_是由单元参数，它应为以下值中的一个给定的：SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER，或YEAR。例如，TIMESTAMPADD(WEEK, 1, DATE '2003-01-02')退货2003-01-09。
Temporal functionsDescription
  1. STRING.toDate()
Returns a SQL date parsed from STRING in form of "yyyy-MM-dd".
  1. STRING.toTime()
Returns a SQL time parsed from STRING in form of "HH:mm:ss".
  1. STRING.toTimestamp()
Returns a SQL timestamp parsed from STRING in form of "yyyy-MM-dd HH:mm:ss[.SSS]".
  1. NUMERIC.yearNUMERIC.years
Creates an interval of months for NUMERIC years.
  1. NUMERIC.monthNUMERIC.months
Creates an interval of NUMERIC months.
  1. NUMERIC.dayNUMERIC.days
Creates an interval of milliseconds for NUMERIC days.
  1. NUMERIC.hourNUMERIC.hours
Creates an interval of milliseconds for NUMERIC hours.
  1. NUMERIC.minuteNUMERIC.minutes
Creates an interval of milliseconds for NUMERIC minutes.
  1. NUMERIC.secondNUMERIC.seconds
Creates an interval of milliseconds for NUMERIC seconds.
  1. NUMERIC.milliNUMERIC.millis
Creates an interval of NUMERIC milliseconds.
  1. currentDate()
Returns the current SQL date in the UTC time zone.
  1. currentTime()
Returns the current SQL time in the UTC time zone.
  1. currentTimestamp()
Returns the current SQL timestamp in the UTC time zone.
  1. localTime()
Returns the current SQL time in local time zone.
  1. localTimestamp()
Returns the current SQL timestamp in local time zone.
  1. TEMPORAL.extract(TIMEINTERVALUNIT)
Returns a long value extracted from the TIMEINTERVALUNIT part of temporal.E.g., '2006-06-05'.toDate.extract(DAY) returns 5; '2006-06-05'.toDate.extract(QUARTER) returns 2.
  1. TIMEPOINT.floor(TIMEINTERVALUNIT)
Returns a value that rounds TIMEPOINT down to the time unit TIMEINTERVALUNIT.E.g., '12:44:31'.toDate.floor(MINUTE) returns 12:44:00.
  1. TIMEPOINT.ceil(TIMEINTERVALUNIT)
Returns a value that rounds TIMEPOINT up to the time unit TIMEINTERVALUNIT.E.g., '12:44:31'.toTime.floor(MINUTE) returns 12:45:00.
  1. temporalOverlaps(TIMEPOINT1, TEMPORAL1, TIMEPOINT2, TEMPORAL2)
Returns TRUE if two time intervals defined by (TIMEPOINT1, TEMPORAL1) and (TIMEPOINT2, TEMPORAL2) overlap. The temporal values could be either a time point or a time interval.E.g., temporalOverlaps('2:55:00'.toTime, 1.hour, '3:30:00'.toTime, 2.hour) returns TRUE.
  1. dateFormat(TIMESTAMP, STRING)
Returns a string that formats TIMESTAMP with a specified format STRING. The format specification is given in the Date Format Specifier table.E.g., dateFormat(ts, '%Y, %d %M') results in strings formatted as "2017, 05 May".
Temporal functionsDescription
  1. STRING.toDate
Returns a SQL date parsed from STRING in form of "yyyy-MM-dd".
  1. STRING.toTime
Returns a SQL time parsed from STRING in form of "HH:mm:ss".
  1. STRING.toTimestamp
Returns a SQL timestamp parsed from STRING in form of "yyyy-MM-dd HH:mm:ss[.SSS]".
  1. NUMERIC.yearNUMERIC.years
Creates an interval of months for NUMERIC years.
  1. NUMERIC.monthNUMERIC.months
Creates an interval of NUMERIC months.
  1. NUMERIC.dayNUMERIC.days
Creates an interval of milliseconds for NUMERIC days.
  1. NUMERIC.hourNUMERIC.hours
Creates an interval of milliseconds for NUMERIC hours.
  1. NUMERIC.minuteNUMERIC.minutes
Creates an interval of milliseconds for NUMERIC minutes.
  1. NUMERIC.secondNUMERIC.seconds
Creates an interval of milliseconds for NUMERIC seconds.
  1. NUMERIC.milliNUMERIC.millis
Creates an interval of NUMERIC milliseconds.
  1. currentDate()
Returns the current SQL date in the UTC time zone.
  1. currentTime()
Returns the current SQL time in the UTC time zone.
  1. currentTimestamp()
Returns the current SQL timestamp in the UTC time zone.
  1. localTime()
Returns the current SQL time in local time zone.
  1. localTimestamp()
Returns the current SQL timestamp in local time zone.
  1. TEMPORAL.extract(TIMEINTERVALUNIT)
Returns a long value extracted from the TIMEINTERVALUNIT part of temporal.E.g., "2006-06-05".toDate.extract(TimeIntervalUnit.DAY) returns 5; "2006-06-05".toDate.extract(QUARTER) returns 2.
  1. TIMEPOINT.floor(TIMEINTERVALUNIT)
Returns a value that rounds TIMEPOINT down to the time unit TIMEINTERVALUNIT.E.g., "12:44:31".toDate.floor(TimeIntervalUnit.MINUTE) returns 12:44:00.
  1. TIMEPOINT.ceil(TIMEINTERVALUNIT)
Returns a value that rounds TIMEPOINT up to the time unit TIMEINTERVALUNIT.E.g., "12:44:31".toTime.floor(TimeIntervalUnit.MINUTE) returns 12:45:00.
  1. temporalOverlaps(TIMEPOINT1, TEMPORAL1, TIMEPOINT2, TEMPORAL2)
Returns TRUE if two time intervals defined by (TIMEPOINT1, TEMPORAL1) and (TIMEPOINT2, TEMPORAL2) overlap. The temporal values could be either a time point or a time interval.E.g., temporalOverlaps("2:55:00".toTime, 1.hour, "3:30:00".toTime, 2.hour) returns TRUE.
  1. dateFormat(TIMESTAMP, STRING)
Returns a string that formats TIMESTAMP with a specified format STRING. The format specification is given in the Date Format Specifier table.E.g., dateFormat('ts, "%Y, %d %M") results in strings formatted as "2017, 05 May".

条件函数

条件函数描述
  1. CASE valueWHEN value11 [, value1_2 ] THEN result1[ WHEN value2_1 [, value2_2 ] THEN result2 ]*[ ELSE resultZ ]END
当第一个时间值包含在（valueX1，valueX_2，…）中时返回resultX。如果没有值匹配，则返回resultZ（如果已提供），否则返回NULL。
  1. CASEWHEN condition1 THEN result1[ WHEN condition2 THEN result2 ][ ELSE resultZ ]END
满足第一个conditionX时返回resultX。如果没有满足条件，则返回resultZ（如果已提供），否则返回NULL。
  1. NULLIF(value1, value2)
如果value1等于value2，则返回NULL ; 否则返回value1。例如，NULLIF(5, 5)返回NULL; NULLIF(5, 0)返回5。
  1. COALESCE(value1, value2 [, value3 ] )
从value1，value2，…返回非NULL的第一个值。例如，COALESCE(NULL, 5)返回5。
Conditional functionsDescription
  1. BOOLEAN.?(VALUE1, VALUE2)
Returns VALUE1 if BOOLEAN evaluates to TRUE; returns VALUE2 otherwise.E.g., (42 > 5).?('A', 'B') returns "A".
Conditional functionsDescription
  1. BOOLEAN.?(VALUE1, VALUE2)
Returns VALUE1 if BOOLEAN evaluates to TRUE; returns VALUE2 otherwise.E.g., (42 > 5).?("A", "B") returns "A".

类型转换函数

类型转换函数描述
  1. CAST(value AS type)
返回要转换为类型类型的新值。请在此处查看支持的类型。例如，CAST('42' AS INT)返回42。
Type conversion functionsDescription
  1. ANY.cast(TYPE)
Returns a new ANY being cast to type TYPE. See the supported types here.E.g., '42'.cast(INT) returns 42.
Type conversion functionsDescription
  1. ANY.cast(TYPE)
Returns a new ANY being cast to type TYPE. See the supported types here.E.g., "42".cast(Types.INT) returns 42.

收集函数

收集函数描述
  1. CARDINALITY(array)
返回数组中的数据元数。
  1. array ‘[’ integer ‘]’
在返回位置处的数据元整数在阵列。索引从1开始。
  1. ELEMENT(array)
返回数组的唯一数据元（其基数应为1）; 如果数组为空，则返回NULL 。如果数组有多个数据元，则抛出异常。
  1. CARDINALITY(map)
返回map中的条目数。
  1. map ‘[’ value ‘]’
返回键指定的值，值的Map。
Collection functionsDescription
  1. ARRAY.cardinality()
Returns the number of elements in ARRAY.
  1. ARRAY.at(INT)
Returns the element at position INT in ARRAY. The index starts from 1.
  1. ARRAY.element()
Returns the sole element of ARRAY (whose cardinality should be one); returns NULL if ARRAY is empty. Throws an exception if ARRAY has more than one element.
  1. MAP.cardinality()
Returns the number of entries in MAP.
  1. MAP.at(ANY)
Returns the value specified by key ANY in MAP.
Collection functionsDescription
  1. ARRAY.cardinality()
Returns the number of elements in ARRAY.
  1. ARRAY.at(INT)
Returns the element at position INT in ARRAY. The index starts from 1.
  1. ARRAY.element()
Returns the sole element of ARRAY (whose cardinality should be one); returns NULL if ARRAY is empty. Throws an exception if ARRAY has more than one element.
  1. MAP.cardinality()
Returns the number of entries in MAP.
  1. MAP.at(ANY)
Returns the value specified by key ANY in MAP.

Value构建函数

Value建构函数描述
  1. ROW(value1, [, value2])(value1, [, value2])
返回从值列表（value1，value2， …）创建的行。
  1. ARRAY ‘[’ value1 [, value2 ] ‘]’
返回从值列表（value1，value2，…）创建的数组。
  1. MAP ‘[’ value1, value2 [, value3, value4 ] ‘]’
返回从键值对列表（（value1，value2），（value3，value4），…）创建的映射。
Value constructor functionsDescription
  1. row(ANY1, ANY2, …)
Returns a row created from a list of object values (ANY1, ANY2, …). Row is composite type that can be access via value access functions.
  1. array(ANY1, ANY2, …)
Returns an array created from a list of object values (ANY1, ANY2, …).
  1. map(ANY1, ANY2, ANY3, ANY4, …)
Returns a map created from a list of key-value pairs ((ANY1, ANY2), (ANY3, ANY4), …).
  1. NUMERIC.rows
Creates a NUMERIC interval of rows (commonly used in window creation).
Value constructor functionsDescription
  1. row(ANY1, ANY2, …)
Returns a row created from a list of object values (ANY1, ANY2, …). Row is composite type that can be access via value access functions.
  1. array(ANY1, ANY2, …)
Returns an array created from a list of object values (ANY1, ANY2, …).
  1. map(ANY1, ANY2, ANY3, ANY4, …)
Returns a map created from a list of key-value pairs ((ANY1, ANY2), (ANY3, ANY4), …).
  1. NUMERIC.rows
Creates a NUMERIC interval of rows (commonly used in window creation).

Value访问函数

Value访问函数描述
  1. tableName.compositeType.field
按名称从Flink复合类型（例如，Tuple，POJO）返回字段的值。
  1. tableName.compositeType.*
返回Flink复合类型（例如，Tuple，POJO）的平面表示，该复合类型将其每个直接子类型转换为单独的字段。在大多数情况下，平面表示的字段与原始字段的命名方式类似，但使用美元分隔符（例如mypojo$mytuple$f0）。
Value access functionsDescription
  1. COMPOSITE.get(STRING)COMPOSITE.get(INT)
Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name or index.E.g., pojo.get('myField') or tuple.get(0).
  1. ANY.flatten()
Returns a flat representation of a Flink composite type (e.g., Tuple, POJO) that converts each of its direct subtype into a separate field.In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g., mypojo$mytuple$f0).
Value access functionsDescription
  1. COMPOSITE.get(STRING)COMPOSITE.get(INT)
Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name or index.E.g., 'pojo.get("myField") or 'tuple.get(0).
  1. ANY.flatten()
Returns a flat representation of a Flink composite type (e.g., Tuple, POJO) that converts each of its direct subtype into a separate field.In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g., mypojo$mytuple$f0).

分组函数

分组函数描述
  1. GROUP_ID()
返回唯一标识分组键组合的整数。
  1. GROUPING(expression1 [, expression2] )GROUPING_ID(expression1 [, expression2] )
返回给定分组表达式的位向量。
Grouping functionsDescription
Grouping functionsDescription

散列函数

散列函数描述
  1. MD5(string)
将字符串的MD5哈希值作为32个十六进制数字的字符串返回; 如果string为NULL，则返回NULL。
  1. SHA1(string)
以40个十六进制数字的字符串形式返回字符串的SHA-1哈希值; 如果string为NULL，则返回NULL。
  1. SHA224(string)
返回字符串的SHA-224哈希值，作为56个十六进制数字的字符串; 如果string为NULL，则返回NULL。
  1. SHA256(string)
以64个十六进制数字的字符串形式返回字符串的SHA-256哈希值; 如果string为NULL，则返回NULL。
  1. SHA384(string)
返回字符串的SHA-384哈希值，作为96个十六进制数字的字符串; 如果string为NULL，则返回NULL。
  1. SHA512(string)
返回字符串的SHA-512哈希值，作为128个十六进制数字的字符串; 如果string为NULL，则返回NULL。
  1. SHA2(string, hashLength)
使用SHA-2系列散列函数（SHA-224，SHA-256，SHA-384或SHA-512）返回散列。第一个参数字符串是要散列的字符串，第二个参数hashLength是结果的位长度（224,256,384或512）。如果string或hashLength为NULL，则返回NULL。
Hash functionsDescription
  1. STRING.md5()
Returns the MD5 hash of STRING as a string of 32 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha1()
Returns the SHA-1 hash of STRING as a string of 40 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha224()
Returns the SHA-224 hash of STRING as a string of 56 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha256()
Returns the SHA-256 hash of STRING as a string of 64 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha384()
Returns the SHA-384 hash of STRING as a string of 96 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha512()
Returns the SHA-512 hash of STRING as a string of 128 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha2(INT)
Returns the SHA-2 family (SHA-224, SHA-256, SHA-384, or SHA-512) hashed value specified by INT (which could be 224, 256, 384, or 512) for STRING. Returns NULL if STRING or INT is NULL.
Hash functionsDescription
  1. STRING.md5()
Returns the MD5 hash of STRING as a string of 32 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha1()
Returns the SHA-1 hash of STRING as a string of 40 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha224()
Returns the SHA-224 hash of STRING as a string of 56 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha256()
Returns the SHA-256 hash of STRING as a string of 64 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha384()
Returns the SHA-384 hash of STRING as a string of 96 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha512()
Returns the SHA-512 hash of STRING as a string of 128 hexadecimal digits; returns NULL if STRING is NULL.
  1. STRING.sha2(INT)
Returns the SHA-2 family (SHA-224, SHA-256, SHA-384, or SHA-512) hashed value specified by INT (which could be 224, 256, 384, or 512) for STRING. Returns NULL if STRING or INT is NULL.

辅助函数

辅助函数描述
Auxiliary functionsDescription
  1. ANY.as(NAME1, NAME2, …)
Specifies a name for ANY (a field). Additional names can be specified if the expression expands to multiple fields.
Auxiliary functionsDescription
  1. ANY.as(NAME1, NAME2, …)
Specifies a name for ANY (a field). Additional names can be specified if the expression expands to multiple fields.

聚合函数

聚合函数将所有行的表达式作为输入，并返回单个聚合值作为结果。

聚合函数描述
  1. COUNT([ ALL ] expression | DISTINCT expression1 [, expression2])
默认情况下或使用ALL，返回表达式不为NULL 的输入行数。将DISTINCT用于每个值的一个唯一实例。
  1. COUNT()COUNT(1)
返回输入行数。
  1. AVG([ ALL | DISTINCT ] expression)
默认情况下或使用关键字ALL，返回所有输入行的表达式的平均值（算术平均值）。将DISTINCT用于每个值的一个唯一实例。
  1. SUM([ ALL | DISTINCT ] expression)
默认情况下或使用关键字ALL，返回所有输入行的表达式总和。将DISTINCT用于每个值的一个唯一实例。
  1. MAX([ ALL | DISTINCT ] expression)
默认情况下或使用关键字ALL，返回所有输入行的表达式的最大值。将DISTINCT用于每个值的一个唯一实例。
  1. MIN([ ALL | DISTINCT ] expression)
默认情况下或使用关键字ALL，返回所有输入行中表达式的最小值。将DISTINCT用于每个值的一个唯一实例。
  1. STDDEVPOP([ ALL | DISTINCT ] expression)
默认情况下或使用关键字ALL，返回所有输入行中表达式的总体标准差。将DISTINCT用于每个值的一个唯一实例。
  1. STDDEV_SAMP([ ALL | DISTINCT ] expression)
默认情况下或使用关键字ALL，返回所有输入行中表达式的样本标准差。将DISTINCT用于每个值的一个唯一实例。
  1. VAR_POP([ ALL | DISTINCT ] expression)
默认情况下或使用关键字ALL，返回所有输入行中表达式的总体方差（总体标准差的平方）。将DISTINCT用于每个值的一个唯一实例。
  1. VAR_SAMP([ ALL | DISTINCT ] expression)
默认情况下或使用关键字ALL，返回所有输入行中表达式的样本方差（样本标准差的平方）。将DISTINCT用于每个值的一个唯一实例。
  1. COLLECT([ ALL | DISTINCT ] expression)
默认情况下或使用关键字ALL，在所有输入行中返回多表达式_。NULL值将被忽略。将DISTINCT用于每个值的一个唯一实例。
Aggregate functionsDescription
  1. FIELD.count
Returns the number of input rows for which FIELD is not NULL.
  1. FIELD.avg
Returns the average (arithmetic mean) of FIELD across all input rows.
  1. FIELD.sum
Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns NULL.
  1. FIELD.sum0
Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns 0.
  1. FIELD.max
Returns the maximum value of numeric field FIELD across all input rows.
  1. FIELD.min
Returns the minimum value of numeric field FIELD across all input rows.
  1. FIELD.stddevPop
Returns the population standard deviation of numeric field FIELD across all input rows.
  1. FIELD.stddevSamp
Returns the sample standard deviation of numeric field FIELD across all input rows.
  1. FIELD.varPop
Returns the population variance (square of the population standard deviation) of numeric field FIELD across all input rows.
  1. FIELD.varSamp
Returns the sample variance (square of the sample standard deviation) of numeric field FIELD across all input rows.
  1. FIELD.collect
Returns a multiset of FIELD across all input rows.
Aggregate functionsDescription
  1. FIELD.count
Returns the number of input rows for which FIELD is not NULL.
  1. FIELD.avg
Returns the average (arithmetic mean) of FIELD across all input rows.
  1. FIELD.sum
Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns NULL.
  1. FIELD.sum0
Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns 0.
  1. FIELD.max
Returns the maximum value of numeric field FIELD across all input rows.
  1. FIELD.min
Returns the minimum value of numeric field FIELD across all input rows.
  1. FIELD.stddevPop
Returns the population standard deviation of numeric field FIELD across all input rows.
  1. FIELD.stddevSamp
Returns the sample standard deviation of numeric field FIELD across all input rows.
  1. FIELD.varPop
Returns the population variance (square of the population standard deviation) of numeric field FIELD across all input rows.
  1. FIELD.varSamp
Returns the sample variance (square of the sample standard deviation) of numeric field FIELD across all input rows.
  1. FIELD.collect
Returns a multiset of FIELD across all input rows.

日期格式说明符

下表列出了日期格式函数的说明符。

符描述
  1. %a
缩写的工作日名称（Sun.. Sat）
  1. %b
缩写的月份名称（Jan.. Dec）
  1. %c
月，数字（1.. 12）
  1. %D
这个月的一天，英语后缀（0th，1st，2nd，3rd，…）
  1. %d
每月的某天，数字（01.. 31）
  1. %e
每月的某天，数字（1.. 31）
  1. %f
第二个分数（打印6位数：000000… 999000;解析时为1 - 9位数：0.. 999999999）（时间戳被截断为毫秒。）
  1. %H
小时（00.. 23）
  1. %h
小时（01.. 12）
  1. %I
小时（01.. 12）
  1. %i
分钟，数字（00.. 59）
  1. %j
一年中的某一天（001.. 366）
  1. %k
小时（0.. 23）
  1. %l
小时（1.. 12）
  1. %M
月份名称（January.. December）
  1. %m
月，数字（01.. 12）
  1. %p
AM 要么 PM
  1. %r
时间，12小时（hh:mm:ss其次是AM或PM）
  1. %S
秒（00… 59）
  1. %s
秒（00… 59）
  1. %T
时间，24小时（hh:mm:ss）
  1. %U
周（00.. 53），周日是一周的第一天
  1. %u
周（00.. 53），周一是一周的第一天
  1. %V
周（01.. 53），周日是一周的第一天; 用于%X
  1. %v
周（01.. 53），周一是一周的第一天; 用于%x
  1. %W
平日名称（Sunday.. Saturday）
  1. %w
星期几（0.. 6），星期日是一周的第一天
  1. %X
星期日是星期的第一天的星期，数字，四位数; 用于%V
  1. %x
一周的年份，星期一是一周的第一天，数字，四位数; 用于%v
  1. %Y
年份，数字，四位数
  1. %y
年份，数字（两位数） 
  1. %%
文字%字符
  1. %x
x，对于x上面未列出的任何内容