内置函数 - 《Flink 中文文档 - 帮助手册 - 教程》

标量函数
聚合函数
日期格式说明符

译者：flink.sojb.cn

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

标量函数

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

比较函数

SQL
Java
Scala | 比较函数 | 描述 | | —- | —- | | value1 = value2 | 如果value1等于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。 | | value1 <> value2 | 如果value1不等于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。 | | value1 > value2 | 如果value1大于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。 | | value1 >= value2 | 如果value1大于或等于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。 | | value1 < value2 | 如果value1小于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。 | | value1 <= value2 | 如果value1小于或等于value2，则返回TRUE ; 如果value1或value2为NULL，则返回UNKNOWN 。 | | value IS NULL | 如果value为NULL，则返回TRUE 。 | | value IS NOT NULL | 如果value不为NULL，则返回TRUE 。 | | value1 IS DISTINCT FROM value2 | 如果两个值不相等，则返回TRUE。NULL值在此处视为相同。例如，1 IS DISTINCT FROM NULL返回TRUE; NULL IS DISTINCT FROM NULL返回FALSE。 | | value1 IS NOT DISTINCT FROM value2 | 如果两个值相等，则返回TRUE。NULL值在此处视为相同。例如，1 IS NOT DISTINCT FROM NULL返回FALSE; NULL IS NOT DISTINCT FROM NULL返回TRUE。 | | 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。 | | 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。 | | string1 LIKE string2 [ ESCAPE char ] | 如果string1匹配模式string2，则返回TRUE ; 如果string1或string2为NULL，则返回UNKNOWN 。如有必要，可以定义转义字符。注意：尚未支持转义字符。 | | string1 NOT LIKE string2 [ ESCAPE char ] | 如果string1与模式string2不匹配，则返回TRUE ; 如果string1或string2为NULL，则返回UNKNOWN 。如有必要，可以定义转义字符。注意：尚未支持转义字符。 | | string1 SIMILAR TO string2 [ ESCAPE char ] | 如果string1匹配SQL正则表达式string2，则返回TRUE ; 如果string1或string2为NULL，则返回UNKNOWN 。如有必要，可以定义转义字符。注意：尚未支持转义字符。 | | string1 NOT SIMILAR TO string2 [ ESCAPE char ] | 如果string1与SQL正则表达式string2不匹配，则返回TRUE ; 如果string1或string2为NULL，则返回UNKNOWN 。如有必要，可以定义转义字符。注意：尚未支持转义字符。 | | 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。 | | 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。 | | EXISTS (sub-query) | 如果子查询返回至少一行，则返回TRUE 。仅在可以在连接和组算子操作中重写算子操作时才支持。注意：对于流式查询，算子操作将在连接和组算子操作中重写。计算查询结果所需的状态可能会无限增长，具体取决于不同输入行的数量。请提供具有有效保存间隔的查询配置，以防止过大的状态。有关详细信息，请参阅Streaming Concepts。 | | value IN (sub-query) | 如果value等于子查询返回的行，则返回TRUE 。注意：对于流式查询，算子操作将在连接和组算子操作中重写。计算查询结果所需的状态可能会无限增长，具体取决于不同输入行的数量。请提供具有有效保存间隔的查询配置，以防止过大的状态。有关详细信息，请参阅Streaming Concepts。 | | value NOT IN (sub-query) | 如果value不等于子查询返回的每一行，则返回TRUE 。注意：对于流式查询，算子操作将在连接和组算子操作中重写。计算查询结果所需的状态可能会无限增长，具体取决于不同输入行的数量。请提供具有有效保存间隔的查询配置，以防止过大的状态。有关详细信息，请参阅Streaming Concepts。 |

Comparison functions	Description
`ANY1 === ANY2`	Returns TRUE if ANY1 is equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 !== ANY2`	Returns TRUE if ANY1 is not equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 > ANY2`	Returns TRUE if ANY1 is greater than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 >= ANY2`	Returns TRUE if ANY1 is greater than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 < ANY2`	Returns TRUE if ANY1 is less than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 <= ANY2`	Returns TRUE if ANY1 is less than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY.isNull`	Returns TRUE if ANY is NULL.
`ANY.isNotNull`	Returns TRUE if ANY is not NULL.
`STRING1.like(STRING2)`	Returns TRUE if STRING1 matches pattern STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL.E.g., `"JoKn".like("Jo_n%")` returns TRUE.
`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.
`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.
`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.
`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.
`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 functions	Description
`ANY1 === ANY2`	Returns TRUE if ANY1 is equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 !== ANY2`	Returns TRUE if ANY1 is not equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 > ANY2`	Returns TRUE if ANY1 is greater than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 >= ANY2`	Returns TRUE if ANY1 is greater than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 < ANY2`	Returns TRUE if ANY1 is less than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 <= ANY2`	Returns TRUE if ANY1 is less than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY.isNull`	Returns TRUE if ANY is NULL.
`ANY.isNotNull`	Returns TRUE if ANY is not NULL.
`STRING1.like(STRING2)`	Returns TRUE if STRING1 matches pattern STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL.E.g., `"JoKn".like("Jo_n%")` returns TRUE.
`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.
`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.
`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.
`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.
`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.

逻辑函数

SQL
Java
Scala | 逻辑函数 | 描述 | | —- | —- | | boolean1 OR boolean2 | 如果boolean1为TRUE或boolean2为TRUE，则返回TRUE。支持三值逻辑。例如，TRUE OR UNKNOWN返回TRUE。 | | boolean1 AND boolean2 | 如果boolean1和boolean2都为TRUE，则返回TRUE。支持三值逻辑。例如，TRUE AND UNKNOWN返回UNKNOWN。 | | NOT boolean | 如果boolean为FALSE，则返回TRUE ; 否则返回TRUE 。如果boolean为TRUE，则返回FALSE ; 如果布尔值为UNKNOWN，则返回UNKNOWN。 | | boolean IS FALSE | 如果boolean为FALSE，则返回TRUE ; 否则返回TRUE 。如果boolean为TRUE或UNKNOWN，则返回FALSE 。 | | boolean IS NOT FALSE | 如果boolean为TRUE或UNKNOWN，则返回TRUE;否则返回TRUE 。如果boolean为FALSE，则返回FALSE。 | | boolean IS TRUE | 如果boolean为TRUE，则返回TRUE; 如果boolean为FALSE或UNKNOWN，则返回FALSE 。 | | boolean IS NOT TRUE | 如果boolean为FALSE或UNKNOWN，则返回TRUE;否则返回TRUE 。如果boolean为FALSE，则返回FALSE。 | | boolean IS UNKNOWN | 如果boolean是UNKNOWN，则返回TRUE ; 否则返回TRUE 。如果boolean为TRUE或FALSE，则返回FALSE。 | | boolean IS NOT UNKNOWN | 如果boolean为TRUE或FALSE，则返回TRUE;否则返回TRUE 。如果布尔值为UNKNOWN，则返回FALSE 。 |

Logical functions	Description
`BOOLEAN1 \|\| BOOLEAN2`	Returns TRUE if BOOLEAN1 is TRUE or BOOLEAN2 is TRUE. Supports three-valued logic.E.g., `true \|\| Null(BOOLEAN)` returns TRUE.
`BOOLEAN1 && BOOLEAN2`	Returns TRUE if BOOLEAN1 and BOOLEAN2 are both TRUE. Supports three-valued logic.E.g., `true && Null(BOOLEAN)` returns UNKNOWN.
`!BOOLEAN`	Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE; returns UNKNOWN if BOOLEAN is UNKNOWN.
`BOOLEAN.isTrue`	Returns TRUE if BOOLEAN is TRUE; returns FALSE if BOOLEAN is FALSE or UNKNOWN.
`BOOLEAN.isFalse`	Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE or UNKNOWN.
`BOOLEAN.isNotTrue`	Returns TRUE if BOOLEAN is FALSE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.
`BOOLEAN.isNotFalse`	Returns TRUE if BOOLEAN is TRUE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.

Logical functions	Description
`BOOLEAN1 \|\| BOOLEAN2`	Returns TRUE if BOOLEAN1 is TRUE or BOOLEAN2 is TRUE. Supports three-valued logic.E.g., `true \|\| Null(Types.BOOLEAN)` returns TRUE.
`BOOLEAN1 && BOOLEAN2`	Returns TRUE if BOOLEAN1 and BOOLEAN2 are both TRUE. Supports three-valued logic.E.g., `true && Null(Types.BOOLEAN)` returns UNKNOWN.
`!BOOLEAN`	Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE; returns UNKNOWN if BOOLEAN is UNKNOWN.
`BOOLEAN.isTrue`	Returns TRUE if BOOLEAN is TRUE; returns FALSE if BOOLEAN is FALSE or UNKNOWN.
`BOOLEAN.isFalse`	Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE or UNKNOWN.
`BOOLEAN.isNotTrue`	Returns TRUE if BOOLEAN is FALSE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.
`BOOLEAN.isNotFalse`	Returns TRUE if BOOLEAN is TRUE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.

算术函数

SQL
Java
Scala | 算术函数 | 描述 | | —- | —- | | + numeric | 返回数字。 | | - numeric | 返回负数字。 | | numeric1 + numeric2 | 返回numeric1加上numeric2。 | | numeric1 - numeric2 | 返回numeric1减去numeric2。 | | numeric1 * numeric2 | 返回numeric1乘以numeric2。 | | numeric1 / numeric2 | 返回numeric1除以numeric2。 | | POWER(numeric1, numeric2) | 返回numeric1上升到numeric2的幂。 | | ABS(numeric) | 返回numeric的绝对值。 | | MOD(numeric1, numeric2) | 返回numeric1的余数（模数）除以numeric2。仅当numeric1为负数时，结果才为负数。 | | SQRT(numeric) | 返回数字的平方根。 | | LN(numeric) | 返回的自然对数（以e为底）的数字。 | | LOG10(numeric) | 返回数字的基数10对数。 | | LOG2(numeric) | 返回数字的基数2对数。 | | LOG(numeric2) | 使用一个参数调用时，返回numeric2的自然对数。当使用两个参数调用时，此函数将numeric2的对数返回到基数numeric1。注意：目前，numeric2必须大于0且numeric1必须大于1。 | | LOG(numeric1, numeric2) | | | EXP(numeric) | 返回e提升到数字的幂。 | | CEIL(numeric) | 将数字向上舍入，并返回大于或等于numeric的最小数字。 | | CEILING(numeric) | | | FLOOR(numeric) | 将数字向下舍入，并返回小于或等于numeric的最大数字。 | | SIN(numeric) | 返回数字的正弦值。 | | COS(numeric) | 返回数字的余弦值。 | | TAN(numeric) | 返回数字的正切值。 | | COT(numeric) | 返回数字的余切。 | | ASIN(numeric) | 返回数字的反正弦值。 | | ACOS(numeric) | 返回数字的反余弦值。 | | ATAN(numeric) | 返回数字的反正切。 | | ATAN2(numeric1, numeric2) | 返回坐标（numeric1，numeric2）的反正切。 | | DEGREES(numeric) | 返回弧度数字的度数表示。 | | RADIANS(numeric) | 返回度数数字的弧度表示。 | | SIGN(numeric) | 返回数字的符号。 | | ROUND(numeric, integer) | 返回舍入为数字的整数小数位数。 | | PI | 返回比pi更接近任何其他值的值。 | | E() | 返回比e的任何其他值更接近的值。 | | RAND() | 返回介于0.0（包括）和1.0（不包括）之间的伪随机双精度值。 | | RAND(integer) | 使用初始种子整数返回介于0.0（含）和1.0（不包含）之间的伪随机双精度值。如果两个RAND函数具有相同的初始种子，则它们将返回相同的数字序列。 | | RAND_INTEGER(integer) | 返回0（包括）和整数（不包括）之间的伪随机整数值。 | | RAND_INTEGER(integer1, integer2) | 返回0（包括）之间的伪随机整数值和具有初始种子的指定值（不包括）。如果两个RAND_INTEGER函数具有相同的初始种子和绑定，则它们将返回相同的数字序列。 | | UUID() | 根据RFC 4122类型4（伪随机生成的）UUID返回UUID（通用唯一标识符）字符串（例如，“3d3c68f7-f608-473f-b60c-b0c44ad4cc4e”）。使用加密强伪随机数生成器生成UUID。 | | BIN(integer) | 以二进制格式返回整数的字符串表示形式。如果_integer为NULL，则返回NULL。例如，BIN(4)返回’100’并BIN(12)返回’1100’。 | | HEX(numeric) | 返回整数数值的字符串表示形式或十六进制格式的字符串。如果参数为NULL，则返回NULL。例如，数字20导致“14”，数字100导致“64”，字符串“hello，world”导致“68656C6C6F2C776F726C64”。 | | HEX(string) | |

Arithmetic functions	Description
`+ NUMERIC`	Returns NUMERIC.
`- NUMERIC`	Returns negative NUMERIC.
`NUMERIC1 + NUMERIC2`	Returns NUMERIC1 plus NUMERIC2.
`NUMERIC1 - NUMERIC2`	Returns NUMERIC1 minus NUMERIC2.
`NUMERIC1 * NUMERIC2`	Returns NUMERIC1 multiplied by NUMERIC2.
`NUMERIC1 / NUMERIC2`	Returns NUMERIC1 divided by NUMERIC2.
`NUMERIC1.power(NUMERIC2)`	Returns NUMERIC1 raised to the power of NUMERIC2.
`NUMERIC.abs()`	Returns the absolute value of NUMERIC.
`NUMERIC1 % NUMERIC2`	Returns the remainder (modulus) of NUMERIC1 divided by NUMERIC2. The result is negative only if numeric1 is negative.
`NUMERIC.sqrt()`	Returns the square root of NUMERIC.
`NUMERIC.ln()`	Returns the natural logarithm (base e) of NUMERIC.
`NUMERIC.log10()`	Returns the base 10 logarithm of NUMERIC.
`NUMERIC.log2()`	Returns the base 2 logarithm of NUMERIC.
`NUMERIC1.log()`	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.
`NUMERIC1.log(NUMERIC2)`
`NUMERIC.exp()`	Returns e raised to the power of NUMERIC.
`NUMERIC.ceil()`	Rounds NUMERIC up, and returns the smallest number that is greater than or equal to NUMERIC.
`NUMERIC.floor()`	Rounds NUMERIC down, and returns the largest number that is less than or equal to NUMERIC.
`NUMERIC.sin()`	Returns the sine of NUMERIC.
`NUMERIC.cos()`	Returns the cosine of NUMERIC.
`NUMERIC.tan()`	Returns the tangent of NUMERIC.
`NUMERIC.cot()`	Returns the cotangent of a NUMERIC.
`NUMERIC.asin()`	Returns the arc sine of NUMERIC.
`NUMERIC.acos()`	Returns the arc cosine of NUMERIC.
`NUMERIC.atan()`	Returns the arc tangent of NUMERIC.
`atan2(NUMERIC1, NUMERIC2)`	Returns the arc tangent of a coordinate (NUMERIC1, NUMERIC2).
`NUMERIC.degrees()`	Returns the degree representation of a radian NUMERIC.
`NUMERIC.radians()`	Returns the radian representation of a degree NUMERIC.
`NUMERIC.sign()`	Returns the signum of NUMERIC.
`NUMERIC.round(INT)`	Returns a number rounded to INT decimal places for NUMERIC.
`pi()`	Returns a value that is closer than any other values to pi.
`e()`	Returns a value that is closer than any other values to e.
`rand()`	Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).
`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.
`randInteger(INTEGER)`	Returns a pseudorandom integer value between 0 (inclusive) and INTEGER (exclusive).
`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.
`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.
`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”.
`NUMERIC.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”.
`STRING.hex()`

Arithmetic functions	Description
`+ NUMERIC`	Returns NUMERIC.
`- NUMERIC`	Returns negative NUMERIC.
`NUMERIC1 + NUMERIC2`	Returns NUMERIC1 plus NUMERIC2.
`NUMERIC1 - NUMERIC2`	Returns NUMERIC1 minus NUMERIC2.
`NUMERIC1 * NUMERIC2`	Returns NUMERIC1 multiplied by NUMERIC2.
`NUMERIC1 / NUMERIC2`	Returns NUMERIC1 divided by NUMERIC2.
`NUMERIC1.power(NUMERIC2)`	Returns NUMERIC1 raised to the power of NUMERIC2.
`NUMERIC.abs()`	Returns the absolute value of NUMERIC.
`NUMERIC1 % NUMERIC2`	Returns the remainder (modulus) of NUMERIC1 divided by NUMERIC2. The result is negative only if numeric1 is negative.
`NUMERIC.sqrt()`	Returns the square root of NUMERIC.
`NUMERIC.ln()`	Returns the natural logarithm (base e) of NUMERIC.
`NUMERIC.log10()`	Returns the base 10 logarithm of NUMERIC.
`NUMERIC.log2()`	Returns the base 2 logarithm of NUMERIC.
`NUMERIC1.log()`	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.
`NUMERIC1.log(NUMERIC2)`
`NUMERIC.exp()`	Returns e raised to the power of NUMERIC.
`NUMERIC.ceil()`	Rounds NUMERIC up, and returns the smallest number that is greater than or equal to NUMERIC.
`NUMERIC.floor()`	Rounds NUMERIC down, and returns the largest number that is less than or equal to NUMERIC.
`NUMERIC.sin()`	Returns the sine of NUMERIC.
`NUMERIC.cos()`	Returns the cosine of NUMERIC.
`NUMERIC.tan()`	Returns the tangent of NUMERIC.
`NUMERIC.cot()`	Returns the cotangent of a NUMERIC.
`NUMERIC.asin()`	Returns the arc sine of NUMERIC.
`NUMERIC.acos()`	Returns the arc cosine of NUMERIC.
`NUMERIC.atan()`	Returns the arc tangent of NUMERIC.
`atan2(NUMERIC1, NUMERIC2)`	Returns the arc tangent of a coordinate (NUMERIC1, NUMERIC2).
`NUMERIC.degrees()`	Returns the degree representation of a radian NUMERIC.
`NUMERIC.radians()`	Returns the radian representation of a degree NUMERIC.
`NUMERIC.sign()`	Returns the signum of NUMERIC.
`NUMERIC.round(INT)`	Returns a number rounded to INT decimal places for NUMERIC.
`pi()`	Returns a value that is closer than any other values to pi.
`e()`	Returns a value that is closer than any other values to e.
`rand()`	Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).
`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.
`randInteger(INTEGER)`	Returns a pseudorandom integer value between 0 (inclusive) and INTEGER (exclusive).
`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.
`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.
`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”.
`NUMERIC.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”.
`STRING.hex()`

字符串函数

SQL
Java
Scala | 字符串函数 | 描述 | | —- | —- | | string1 || string2 | 返回string1和string2的串联。 | | CHAR_LENGTH(string) | 返回string中的字符数。 | | CHARACTER_LENGTH(string) | | | UPPER(string) | 以大写形式返回字符串。 | | LOWER(string) | 以小写形式返回字符串。 | | POSITION(string1 IN string2) | 返回的第一次出现的位置（从1开始）字符串1在字符串2 ; 如果在string2中找不到string1，则返回0 。 | | TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2) | 返回一个字符串，用于从string2中删除前导和/或尾随字符string1。默认情况下，两侧的空格都被删除。 | | LTRIM(string) | 返回一个字符串，从去除左空格的字符串。例如，LTRIM(' This is a test String.')返回“This is a test String。”。 | | RTRIM(string) | 返回一个字符串，从去除右方的空格字符串。例如，RTRIM('This is a test String. ')返回“This is a test String。”。 | | REPEAT(string, integer) | 返回一个重复基本字符串 整数倍的字符串。例如，REPEAT('This is a test String.', 2)返回“这是一个测试字符串。这是一个测试字符串。”。 | | 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)返回“这是一个新字符串” | | SUBSTRING(string FROM integer1 [ FOR integer2 ]) | 返回从位置integer1开始的字符串的子字符串，其长度为integer2（默认情况下为结尾）。 | | INITCAP(string) | 返回一种新形式的字符串，每个单词的第一个字符转换为大写，其余字符转换为小写。这里的单词意味着一系列字母数字字符。 | | CONCAT(string1, string2,...) | 返回连接string1，string2，…的字符串。如果任何参数为NULL，则返回NULL。例如，CONCAT('AA', 'BB', 'CC')返回“AABBCC”。 | | CONCAT_WS(string1, string2, string3,...) | 返回一个字符串，用于将string2，string3，…与分隔符string1连接起来。在要连接的字符串之间添加分隔符。返回NULL如果string1为NULL。与之相比CONCAT()，CONCAT_WS()自动跳过NULL参数。例如，CONCAT_WS('~', 'AA', NULL, 'BB', '', 'CC')返回“AA~BB ~~ CC”。 | | LPAD(string1, integer, string2) | 返回从string1左侧填充string2到整数字符长度的新字符串。如果string1的长度小于整数，则返回string1缩短为整数字符。例如，LPAD('hi',4,'??')返回“?? hi”; LPAD('hi',1,'??')返回“h”。 | | RPAD(string1, integer, string2) | 返回从string1右侧填充string2到整数字符长度的新字符串。如果string1的长度小于整数，则返回string1缩短为整数字符。例如，RPAD('hi',4,'??')返回“hi ??”，RPAD('hi',1,'??')返回“h”。 | | FROM_BASE64(string) | 返回string的base64解码结果; 如果string为NULL，则返回NULL。例如，FROM_BASE64('aGVsbG8gd29ybGQ=')返回“hello world”。 | | TO_BASE64(string) | 从string返回base64编码的结果; 如果string为NULL，则返回NULL。例如，TO_BASE64('hello world')返回“aGVsbG8gd29ybGQ =”。 |

String functions	Description
`STRING1 + STRING2`	Returns the concatenation of STRING1 and STRING2.
`STRING.charLength()`	Returns the number of characters in STRING.
`STRING.upperCase()`	Returns STRING in uppercase.
`STRING.lowerCase()`	Returns STRING in lowercase.
`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.
`STRING1.trim(LEADING, STRING2)`	Returns a string that removes leading and/or trailing characters STRING2 from STRING1. By default, whitespaces at both sides are removed.
`STRING1.trim(TRAILING, STRING2)`
`STRING1.trim(BOTH, STRING2)`
`STRING1.trim(BOTH)`
`STRING1.trim()`
`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.”.
`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.”.
`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.”.
`STRING1.overlay(STRING2, INT1)`	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”.
`STRING1.overlay(STRING2, INT1, INT2)`
`STRING.substring(INT1)`	Returns a substring of STRING starting from position INT1 with length INT2 (to the end by default).
`STRING.substring(INT1, INT2)`
`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.
`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”.
`concat_ws(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()`, `concat_ws()` automatically skips NULL arguments.E.g., `concat_ws('~', 'AA', Null(STRING), 'BB', '', 'CC')` returns “AA~CC”.
`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”.
`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”.
`STRING.fromBase64()`	Returns the base64-decoded result from STRING; returns NULL if STRING is NULL.E.g., `'aGVsbG8gd29ybGQ='.fromBase64()` returns “hello world”.
`STRING.toBase64()`	Returns the base64-encoded result from STRING; returns NULL if STRING is NULL.E.g., `'hello world'.toBase64()` returns “aGVsbG8gd29ybGQ=”.

String functions	Description
`STRING1 + STRING2`	Returns the concatenation of STRING1 and STRING2.
`STRING.charLength()`	Returns the number of characters in STRING.
`STRING.upperCase()`	Returns STRING in uppercase.
`STRING.lowerCase()`	Returns STRING in lowercase.
`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.
`STRING.trim(leading = true, trailing = true, character = " ")`	Returns a string that removes leading and/or trailing characters from STRING.
`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.”.
`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.”.
`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.”.
`STRING1.overlay(STRING2, INT1)`	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”.
`STRING1.overlay(STRING2, INT1, INT2)`
`STRING.substring(INT1)`	Returns a substring of STRING starting from position INT1 with length INT2 (to the end by default).
`STRING.substring(INT1, INT2)`
`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.
`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”.
`concat_ws(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()`, `concat_ws()` automatically skips NULL arguments.E.g., `concat_ws("~", "AA", Null(Types.STRING), "BB", "", "CC")` returns “AA~CC”.
`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”.
`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”.
`STRING.fromBase64()`	Returns the base64-decoded result from STRING; returns null If STRING is NULL.E.g., `"aGVsbG8gd29ybGQ=".fromBase64()` returns “hello world”.
`STRING.toBase64()`	Returns the base64-encoded result from STRING; returns NULL if STRING is NULL.E.g., `"hello world".toBase64()` returns “aGVsbG8gd29ybGQ=”.

时间函数

SQL
Java
Scala | 时间函数 | 描述 | | —- | —- | | DATE string | 返回以“yyyy-MM-dd”形式从字符串解析的SQL日期。 | | TIME string | 返回以“HH：mm：ss”形式从字符串解析的SQL时间。 | | TIMESTAMP string | 返回以字符串形式解析的SQL时间戳，格式为“yyyy-MM-dd HH：mm：ss [.SSS]”。 | | 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返回的时间间隔。 | | CURRENT_DATE | 返回UTC时区中的当前SQL日期。 | | CURRENT_TIME | 返回UTC时区中的当前SQL时间。 | | CURRENT_TIMESTAMP | 返回UTC时区中的当前SQL时间戳。 | | LOCALTIME | 返回本地时区的当前SQL时间。 | | LOCALTIMESTAMP | 返回本地时区的当前SQL时间戳。 | | EXTRACT(timeintervalunit FROM temporal) | 返回从temporal的timeintervalitit部分提取的long值。例如，EXTRACT(DAY FROM DATE '2006-06-05')返回5。 | | YEAR(date) | 返回SQL日期日期的年份。相当于EXTRACT（从日期开始）。例如，YEAR(DATE '1994-09-27')返回1994年。 | | QUARTER(date) | 从SQL返回日期一年的季度（1和4之间的整数）日期。相当于EXTRACT(QUARTER FROM date)。例如，QUARTER(DATE '1994-09-27')返回3。 | | MONTH(date) | 从SQL日期日期返回一年中的月份（1到12之间的整数）。相当于EXTRACT(MONTH FROM date)。例如，MONTH(DATE '1994-09-27')返回9。 | | WEEK(date) | 从SQL date 日期返回一年中的一周（1到53之间的整数）。相当于EXTRACT(WEEK FROM date)。例如，WEEK(DATE '1994-09-27')返回39。 | | DAYOFYEAR(date) | 返回一年的从SQL日期当天（1和366之间的整数）日期。相当于EXTRACT(DOY FROM date)。例如，DAYOFYEAR(DATE '1994-09-27')返回270。 | | DAYOFMONTH(date) | 从SQL日期日期返回一个月中的某一天（1到31之间的整数）。相当于EXTRACT(DAY FROM date)。例如，DAYOFMONTH(DATE '1994-09-27')返回27。 | | DAYOFWEEK(date) | 从SQL date date返回一周中的星期几（1到7之间的整数;星期日= 1）EXTRACT(DOW FROM date)。等效于。例如，DAYOFWEEK(DATE '1994-09-27')返回3。 | | HOUR(timestamp) | 从SQL时间戳记时间戳返回一天中的小时（0到23之间的整数）。相当于EXTRACT(HOUR FROM timestamp)。例如，HOUR(TIMESTAMP '1994-09-27 13:14:15')返回13。 | | MINUTE(timestamp) | 从SQL时间戳记时间戳返回一小时的分钟（0到59之间的整数）。相当于EXTRACT(MINUTE FROM timestamp)。例如，MINUTE(TIMESTAMP '1994-09-27 13:14:15')返回14。 | | SECOND(timestamp) | 从SQL时间戳返回第二分钟（0到59之间的整数）。相当于EXTRACT(SECOND FROM timestamp)。例如，SECOND(TIMESTAMP '1994-09-27 13:14:15')返回15。 | | FLOOR(timepoint TO timeintervalunit) | 返回将时间点向下舍入到时间单位timeintervalunit的值。例如，FLOOR(TIME '12:44:31' TO MINUTE)返回12:44:00。 | | CEIL(timepoint TO timeintervalunit) | 返回一个将时间点舍入到时间单位_timeintervalunit的值。例如，CEIL(TIME '12:44:31' TO MINUTE)返回12:45:00。 | | (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。 | | DATE_FORMAT(timestamp, string) | 返回使用指定格式字符串格式化时间戳的字符串。格式规范在[Date Format Specifier表中给出](docs_1.7-SNAPSHOT#date-format-specifiers)。 | | TIMESTAMPADD(unit, interval, timevalue) | 返回一个新的时间值，它将（带符号）整数时间间隔添加到timevalue。为单位的时间间隔是由单元参数，它应为以下值中的一个给定的：SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER，或YEAR。例如，TIMESTAMPADD(WEEK, 1, DATE '2003-01-02')退货2003-01-09。 |

Temporal functions	Description
`STRING.toDate()`	Returns a SQL date parsed from STRING in form of “yyyy-MM-dd”.
`STRING.toTime()`	Returns a SQL time parsed from STRING in form of “HH:mm:ss”.
`STRING.toTimestamp()`	Returns a SQL timestamp parsed from STRING in form of “yyyy-MM-dd HH:mm:ss[.SSS]”.
`NUMERIC.year`	Creates an interval of months for NUMERIC years.
`NUMERIC.years`
`NUMERIC.month`	Creates an interval of NUMERIC months.
`NUMERIC.months`
`NUMERIC.day`	Creates an interval of milliseconds for NUMERIC days.
`NUMERIC.days`
`NUMERIC.hour`	Creates an interval of milliseconds for NUMERIC hours.
`NUMERIC.hours`
`NUMERIC.minute`	Creates an interval of milliseconds for NUMERIC minutes.
`NUMERIC.minutes`
`NUMERIC.second`	Creates an interval of milliseconds for NUMERIC seconds.
`NUMERIC.seconds`
`NUMERIC.milli`	Creates an interval of NUMERIC milliseconds.
`NUMERIC.millis`
`currentDate()`	Returns the current SQL date in the UTC time zone.
`currentTime()`	Returns the current SQL time in the UTC time zone.
`currentTimestamp()`	Returns the current SQL timestamp in the UTC time zone.
`localTime()`	Returns the current SQL time in local time zone.
`localTimestamp()`	Returns the current SQL timestamp in local time zone.
`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.
`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.
`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.
`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.
`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 functions	Description
`STRING.toDate`	Returns a SQL date parsed from STRING in form of “yyyy-MM-dd”.
`STRING.toTime`	Returns a SQL time parsed from STRING in form of “HH:mm:ss”.
`STRING.toTimestamp`	Returns a SQL timestamp parsed from STRING in form of “yyyy-MM-dd HH:mm:ss[.SSS]”.
`NUMERIC.year`	Creates an interval of months for NUMERIC years.
`NUMERIC.years`
`NUMERIC.month`	Creates an interval of NUMERIC months.
`NUMERIC.months`
`NUMERIC.day`	Creates an interval of milliseconds for NUMERIC days.
`NUMERIC.days`
`NUMERIC.hour`	Creates an interval of milliseconds for NUMERIC hours.
`NUMERIC.hours`
`NUMERIC.minute`	Creates an interval of milliseconds for NUMERIC minutes.
`NUMERIC.minutes`
`NUMERIC.second`	Creates an interval of milliseconds for NUMERIC seconds.
`NUMERIC.seconds`
`NUMERIC.milli`	Creates an interval of NUMERIC milliseconds.
`NUMERIC.millis`
`currentDate()`	Returns the current SQL date in the UTC time zone.
`currentTime()`	Returns the current SQL time in the UTC time zone.
`currentTimestamp()`	Returns the current SQL timestamp in the UTC time zone.
`localTime()`	Returns the current SQL time in local time zone.
`localTimestamp()`	Returns the current SQL timestamp in local time zone.
`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.
`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.
`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.
`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.
`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”.

条件函数

SQL
Java
Scala | 条件函数 | 描述 | | —- | —- | |
THEN result1
[ WHEN value2_1 [, value2_2 ]

CASE valueWHEN value1_1 [, value1_2 ] THEN result2 ]*[ ELSE resultZ ]END | 当第一个时间值包含在（valueX_1，valueX_2，…）中时返回resultX。如果没有值匹配，则返回resultZ（如果已提供），否则返回NULL。 | |

CASEWHEN condition1 THEN result1[ WHEN condition2 THEN result2 ]*[ ELSE resultZ ]END | 满足第一个conditionX时返回resultX。如果没有满足条件，则返回resultZ（如果已提供），否则返回NULL。 | | NULLIF(value1, value2) | 如果value1等于value2，则返回NULL ; 否则返回value1。例如，NULLIF(5, 5)返回NULL; NULLIF(5, 0)返回5。 | | COALESCE(value1, value2 [, value3 ]* ) | 从value1，value2，…返回非NULL的第一个值。例如，COALESCE(NULL, 5)返回5。 |

Conditional functions	Description
`BOOLEAN.?(VALUE1, VALUE2)`	Returns VALUE1 if BOOLEAN evaluates to TRUE; returns VALUE2 otherwise.E.g., `(42 > 5).?('A', 'B')` returns “A”.

Conditional functions	Description
`BOOLEAN.?(VALUE1, VALUE2)`	Returns VALUE1 if BOOLEAN evaluates to TRUE; returns VALUE2 otherwise.E.g., `(42 > 5).?("A", "B")` returns “A”.

类型转换函数

SQL
Java
Scala | 类型转换函数 | 描述 | | —- | —- | | CAST(value AS type) | 返回要转换为类型类型的新值。请在此处查看支持的类型。例如，CAST('42' AS INT)返回42。 |

Type conversion functions	Description
`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 functions	Description
`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.

收集函数

SQL
Java
Scala | 收集函数 | 描述 | | —- | —- | | CARDINALITY(array) | 返回数组中的数据元数。 | | array ‘[’ integer ‘]’ | 在返回位置处的数据元整数在阵列。索引从1开始。 | | ELEMENT(array) | 返回数组的唯一数据元（其基数应为1）; 如果数组为空，则返回NULL 。如果数组有多个数据元，则抛出异常。 | | CARDINALITY(map) | 返回map中的条目数。 | | map ‘[’ value ‘]’ | 返回键指定的值，值的Map。 |

Collection functions	Description
`ARRAY.cardinality()`	Returns the number of elements in ARRAY.
`ARRAY.at(INT)`	Returns the element at position INT in ARRAY. The index starts from 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.
`MAP.cardinality()`	Returns the number of entries in MAP.
`MAP.at(ANY)`	Returns the value specified by key ANY in MAP.

Collection functions	Description
`ARRAY.cardinality()`	Returns the number of elements in ARRAY.
`ARRAY.at(INT)`	Returns the element at position INT in ARRAY. The index starts from 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.
`MAP.cardinality()`	Returns the number of entries in MAP.
`MAP.at(ANY)`	Returns the value specified by key ANY in MAP.

Value构建函数

SQL
Java
Scala | Value建构函数 | 描述 | | —- | —- | | ROW(value1, [, value2]*) | 返回从值列表（value1，value2， …）创建的行。 | | (value1, [, value2]*) | | | ARRAY ‘[’ value1 [, value2 ]* ‘]’ | 返回从值列表（value1，value2，…）创建的数组。 | | MAP ‘[’ value1, value2 [, value3, value4 ]* ‘]’ | 返回从键值对列表（（value1，value2），（value3，value4），…）创建的映射。 |

Value constructor functions	Description
`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.
`array(ANY1, ANY2, ...)`	Returns an array created from a list of object values (ANY1, ANY2, …).
`map(ANY1, ANY2, ANY3, ANY4, ...)`	Returns a map created from a list of key-value pairs ((ANY1, ANY2), (ANY3, ANY4), …).
`NUMERIC.rows`	Creates a NUMERIC interval of rows (commonly used in window creation).

Value constructor functions	Description
`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.
`array(ANY1, ANY2, ...)`	Returns an array created from a list of object values (ANY1, ANY2, …).
`map(ANY1, ANY2, ANY3, ANY4, ...)`	Returns a map created from a list of key-value pairs ((ANY1, ANY2), (ANY3, ANY4), …).
`NUMERIC.rows`	Creates a NUMERIC interval of rows (commonly used in window creation).

Value访问函数

SQL
Java
Scala | Value访问函数 | 描述 | | —- | —- | | tableName.compositeType.field | 按名称从Flink复合类型（例如，Tuple，POJO）返回字段的值。 | | tableName.compositeType.* | 返回Flink复合类型（例如，Tuple，POJO）的平面表示，该复合类型将其每个直接子类型转换为单独的字段。在大多数情况下，平面表示的字段与原始字段的命名方式类似，但使用美元分隔符（例如mypojo$mytuple$f0）。 |

Value access functions	Description
`COMPOSITE.get(STRING)`	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)`.
`COMPOSITE.get(INT)`
`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 functions	Description
`COMPOSITE.get(STRING)`	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)`.
`COMPOSITE.get(INT)`
`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`).

分组函数

SQL
Java
Scala | 分组函数 | 描述 | | —- | —- | | GROUP_ID() | 返回唯一标识分组键组合的整数。 | | GROUPING(expression1 [, expression2]* ) | 返回给定分组表达式的位向量。 | | GROUPING_ID(expression1 [, expression2]* ) | |

Grouping functions	Description

Grouping functions	Description

散列函数

SQL
Java
Scala | 散列函数 | 描述 | | —- | —- | | MD5(string) | 将字符串的MD5哈希值作为32个十六进制数字的字符串返回; 如果string为NULL，则返回NULL。 | | SHA1(string) | 以40个十六进制数字的字符串形式返回字符串的SHA-1哈希值; 如果string为NULL，则返回NULL。 | | SHA224(string) | 返回字符串的SHA-224哈希值，作为56个十六进制数字的字符串; 如果string为NULL，则返回NULL。 | | SHA256(string) | 以64个十六进制数字的字符串形式返回字符串的SHA-256哈希值; 如果string为NULL，则返回NULL。 | | SHA384(string) | 返回字符串的SHA-384哈希值，作为96个十六进制数字的字符串; 如果string为NULL，则返回NULL。 | | SHA512(string) | 返回字符串的SHA-512哈希值，作为128个十六进制数字的字符串; 如果string为NULL，则返回NULL。 | | SHA2(string, hashLength) | 使用SHA-2系列散列函数（SHA-224，SHA-256，SHA-384或SHA-512）返回散列。第一个参数字符串是要散列的字符串，第二个参数hashLength是结果的位长度（224,256,384或512）。如果string或hashLength为NULL，则返回NULL。 |

Hash functions	Description
`STRING.md5()`	Returns the MD5 hash of STRING as a string of 32 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha1()`	Returns the SHA-1 hash of STRING as a string of 40 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha224()`	Returns the SHA-224 hash of STRING as a string of 56 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha256()`	Returns the SHA-256 hash of STRING as a string of 64 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha384()`	Returns the SHA-384 hash of STRING as a string of 96 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha512()`	Returns the SHA-512 hash of STRING as a string of 128 hexadecimal digits; returns NULL if STRING is NULL.
`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 functions	Description
`STRING.md5()`	Returns the MD5 hash of STRING as a string of 32 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha1()`	Returns the SHA-1 hash of STRING as a string of 40 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha224()`	Returns the SHA-224 hash of STRING as a string of 56 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha256()`	Returns the SHA-256 hash of STRING as a string of 64 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha384()`	Returns the SHA-384 hash of STRING as a string of 96 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha512()`	Returns the SHA-512 hash of STRING as a string of 128 hexadecimal digits; returns NULL if STRING is NULL.
`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.

辅助函数

SQL
Java
Scala | 辅助函数 | 描述 | | —- | —- |

Auxiliary functions	Description
`ANY.as(NAME1, NAME2, ...)`	Specifies a name for ANY (a field). Additional names can be specified if the expression expands to multiple fields.

Auxiliary functions	Description
`ANY.as(NAME1, NAME2, ...)`	Specifies a name for ANY (a field). Additional names can be specified if the expression expands to multiple fields.

聚合函数

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

SQL
Java
Scala | 聚合函数 | 描述 | | —- | —- | | COUNT([ ALL ] expression | DISTINCT expression1 [, expression2]*) | 默认情况下或使用ALL，返回表达式不为NULL 的输入行数。将DISTINCT用于每个值的一个唯一实例。 | | COUNT(*) | 返回输入行数。 | | COUNT(1) | | | AVG([ ALL | DISTINCT ] expression) | 默认情况下或使用关键字ALL，返回所有输入行的表达式的平均值（算术平均值）。将DISTINCT用于每个值的一个唯一实例。 | | SUM([ ALL | DISTINCT ] expression) | 默认情况下或使用关键字ALL，返回所有输入行的表达式总和。将DISTINCT用于每个值的一个唯一实例。 | | MAX([ ALL | DISTINCT ] expression) | 默认情况下或使用关键字ALL，返回所有输入行的表达式的最大值。将DISTINCT用于每个值的一个唯一实例。 | | MIN([ ALL | DISTINCT ] expression) | 默认情况下或使用关键字ALL，返回所有输入行中表达式的最小值。将DISTINCT用于每个值的一个唯一实例。 | | STDDEV_POP([ ALL | DISTINCT ] expression) | 默认情况下或使用关键字ALL，返回所有输入行中表达式的总体标准差。将DISTINCT用于每个值的一个唯一实例。 | | STDDEV_SAMP([ ALL | DISTINCT ] expression) | 默认情况下或使用关键字ALL，返回所有输入行中表达式的样本标准差。将DISTINCT用于每个值的一个唯一实例。 | | VAR_POP([ ALL | DISTINCT ] expression) | 默认情况下或使用关键字ALL，返回所有输入行中表达式的总体方差（总体标准差的平方）。将DISTINCT用于每个值的一个唯一实例。 | | VAR_SAMP([ ALL | DISTINCT ] expression) | 默认情况下或使用关键字ALL，返回所有输入行中表达式的样本方差（样本标准差的平方）。将DISTINCT用于每个值的一个唯一实例。 | | COLLECT([ ALL | DISTINCT ] expression) | 默认情况下或使用关键字ALL，在所有输入行中返回多表达式。NULL值将被忽略。将DISTINCT用于每个值的一个唯一实例。 |

Aggregate functions	Description
`FIELD.count`	Returns the number of input rows for which FIELD is not NULL.
`FIELD.avg`	Returns the average (arithmetic mean) of FIELD across all input rows.
`FIELD.sum`	Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns NULL.
`FIELD.sum0`	Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns 0.
`FIELD.max`	Returns the maximum value of numeric field FIELD across all input rows.
`FIELD.min`	Returns the minimum value of numeric field FIELD across all input rows.
`FIELD.stddevPop`	Returns the population standard deviation of numeric field FIELD across all input rows.
`FIELD.stddevSamp`	Returns the sample standard deviation of numeric field FIELD across all input rows.
`FIELD.varPop`	Returns the population variance (square of the population standard deviation) of numeric field FIELD across all input rows.
`FIELD.varSamp`	Returns the sample variance (square of the sample standard deviation) of numeric field FIELD across all input rows.
`FIELD.collect`	Returns a multiset of FIELD across all input rows.

Aggregate functions	Description
`FIELD.count`	Returns the number of input rows for which FIELD is not NULL.
`FIELD.avg`	Returns the average (arithmetic mean) of FIELD across all input rows.
`FIELD.sum`	Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns NULL.
`FIELD.sum0`	Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns 0.
`FIELD.max`	Returns the maximum value of numeric field FIELD across all input rows.
`FIELD.min`	Returns the minimum value of numeric field FIELD across all input rows.
`FIELD.stddevPop`	Returns the population standard deviation of numeric field FIELD across all input rows.
`FIELD.stddevSamp`	Returns the sample standard deviation of numeric field FIELD across all input rows.
`FIELD.varPop`	Returns the population variance (square of the population standard deviation) of numeric field FIELD across all input rows.
`FIELD.varSamp`	Returns the sample variance (square of the sample standard deviation) of numeric field FIELD across all input rows.
`FIELD.collect`	Returns a multiset of FIELD across all input rows.

日期格式说明符

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

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