运算符和函数

提示

关于 Apache Calcite 支持的函数的详细信息，请参见官方文档。

1.聚合函数

1.1.AVG

AVG( [ ALL | DISTINCT ] numeric)

返回所有输入值的平均值（算术平均值），使用时数据类型将按以下方式更改：

输入类型	返回类型	最小标度
DECIMAL、BIGINT、INTEGER、SMALLINT、TINYINT	DECIMAL	16
DOUBLE、REAL	DOUBLE

1.2.COUNT

COUNT( [ ALL | DISTINCT ] value [, value ]*)

返回值不为空的输入行数（如果值是复合值，则完全不为空）。

1.3.MAX

MAX( [ ALL | DISTINCT ] value)

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

1.4.MIN

SUM( [ ALL | DISTINCT ] numeric)

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

1.5.SUM

SUM( [ ALL | DISTINCT ] numeric)

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

1.6.ANY_VALUE

ANY_VALUE( [ ALL | DISTINCT ] value)

返回所有输入值中的任意值，这在SQL标准中没有规定。

1.7.EVERY

EVERY(condition)

如果条件的所有值都为TRUE，则返回TRUE。

1.8.SOME

SOME(condition)

如果条件的至少一个值是TRUE，则返回TRUE。

2.JSON函数

2.1.JSON_TYPE

JSON_TYPE(jsonValue)

返回JSON类型的字符串值。

2.2.FORMAT JSON

将值格式化成JSON形式。

2.3.JSON_VALUE

JSON_VALUE(jsonValue, path [ RETURNING type ] [ { ERROR | NULL | DEFAULT expr } ON EMPTY ] [ { ERROR | NULL | DEFAULT expr } ON ERROR ] )

使用JSON路径表达式从JSON值中提取SQL标量。

2.4.JSON_QUERY

JSON_QUERY(jsonValue, path [ RETURNING type ] [ { WITHOUT [ ARRAY ] | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ] } WRAPPER ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ] )

使用JSON路径表达式从JSON值中提取JSON对象或JSON数组。

2.5.JSON_EXISTS

JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ] )

JSON值是否满足JSON路径表达式描述的搜索条件。

2.6.JSON_DEPTH

JSON_DEPTH(jsonValue)

返回表示JSON值深度的整数值。

2.7.JSON_KEYS

JSON_KEYS(jsonValue [, path ])

返回JSON值中的键的字符串表示。

2.8.JSON_PRETTY

JSON_PRETTY(jsonValue)

返回JSON值经过美化的格式化输出形式。

2.9.JSON_LENGTH

JSON_LENGTH(jsonValue [, path ])

返回JSON值的长度的整形值表示。

2.10.JSON_REMOVE

JSON_REMOVE(jsonValue, path [, path ])

使用一系列路径表达式从JSON值中删除数据并返回结果。

2.11.JSON_STORAGE_SIZE

JSON_STORAGE_SIZE(jsonValue)

返回用于存储JSON值二进制表示的字节数。

2.12.JSON_OBJECT

JSON_OBJECT( jsonKeyVal [, jsonKeyVal ]* [ nullBehavior ] )

使用一组键值对构建JSON对象。

2.13.JSON_ARRAY

JSON_ARRAY( [ jsonVal [, jsonVal ]* ] [ nullBehavior ] )

使用一组值构建JSON数组。

4.14.IS JSON VALUE

jsonValue IS JSON [ VALUE ]

JSON值是否为JSON。

4.15.IS JSON OBJECT

jsonValue IS JSON OBJECT

JSON值是否为JSON对象。

4.16.IS JSON ARRAY

jsonValue IS JSON ARRAY

JSON值是否为JSON数组。

4.17.IS JSON SCALAR

jsonValue IS JSON SCALAR

JSON值是否为JSON标量值。

3.正则表达式函数

3.1.POSIX REGEX CASE INSENSITIVE

value 1 POSIX REGEX CASE INSENSITIVE value 2

大小写不敏感的POSIX正则表达式。

3.2.POSIX REGEX CASE SENSITIVE

value 1 POSIX REGEX CASE SENSITIVE value 2

大小写敏感的POSIX正则表达式。

3.3.REGEXP_REPLACE

REGEXP_REPLACE(string, regexp, rep [, pos [, occurrence [, matchType]]])

将 string 中从 pos 开始（如果省略，则默认为 1）匹配 regexp 的所有子字符串替换为 rep，occurrence 指定要搜索匹配的哪个匹配项（如果省略，则默认为 1），matchType 指定如何执行匹配。

REGEXP_REPLACE(string, regexp)

将 string 中所有与 regexp 匹配的子字符串替换为空字符串，并返回修改后的值。

4.数值函数

4.1.MOD

MOD(numeric1, numeric2)

返回numeric1除以numeric2的余数（模数），只有当numeric1为负时，结果才为负。

4.2.EXP

EXP(numeric)

返回e的numeric次幂。

4.3.POWER

POWER(numeric1, numeric2)

返回numeric1的numeric2次幂。

4.4.LN

LN(numeric)

返回numeric的自然对数（以e为底）。

4.5.LOG10

LOG10(numeric)

返回以10为底的numeric对数。

4.6.ABS

ABS(numeric)

返回numeric的绝对值。

4.7.RAND

RAND([seed])

生成一个介于0和1之间（包括0和1）的随机双精度数，也可以使用seed初始化随机数生成器。

4.8.RAND_INTEGER

RAND_INTEGER([seed, ] numeric)

生成一个介于0和numeric之间的随机整数，也可以使用seed初始化随机数生成器。

4.9.ACOS

ACOS(numeric)

返回numeric的余弦值。

4.10.ASIN

ASIN(numeric)

返回numeric的正弦值。

4.11.ATAN

ATAN(numeric)

返回numeric的反正切值。

4.12.ATAN2

ATAN2(numeric, numeric)

返回numeric1和numeric2的反正切值。

4.13.SQRT

SQRT(numeric)

返回numeric的平方根。

4.14.CBRT

CBRT(numeric)

返回numeric的立方根。

4.15.COS

COS(numeric)

返回numeric的余弦值。

4.16.COSH

COSH(numeric)

返回numeric的双曲余弦值。

4.17.COT

COT(numeric)

返回numeric的余切值。

4.18.DEGREES

DEGREES(numeric)

将numeric从弧度转换为度数。

4.19.RADIANS

RADIANS(numeric)

将numeric从度数转换为弧度。

4.20.ROUND

ROUND(numeric1 [, integer2])

返回numeric1四舍五入到integer2位小数后的值，如果省略integer2，则返回最接近的整数。

4.21.SIGN

SIGN(numeric)

返回numeric的符号。

4.22.SIN

SIN(numeric)

返回numeric的正弦值。

4.23.SINH

SINH(numeric)

返回numeric的双曲正弦值。

4.24.TAN

TAN(numeric)

返回numeric的正切值。

4.25.TANH

TANH(numeric)

返回numeric的双曲正切值。

4.26.TRUNCATE

TRUNCATE(numeric1 [, integer2])

返回numeric1截断到integer2位小数后的值，如果省略integer2，则返回最接近的整数。

4.27.PI

PI()

返回一个比其他任何值都更接近Pi的值。

5.字符串函数

5.1.UPPER

UPPER(string)

返回string的大写形式。

5.2.LOWER

LOWER(string)

返回string的小写形式。

5.3.INITCAP

INITCAP(string)

返回一个字符串，其将``string`中每个单词转换器的第一个字母大写，其余字母小写。单词是由非字母数字字符分隔的字母数字字符序列。

5.4.TO_BASE64

TO_BASE64(string)

返回string的Base64编码。

5.5.FROM_BASE64

FROM_BASE64(string)

返回string的Base64解码。

5.6.MD5

MD5(string)

计算string的MD5 128位校验和，并将其作为十六进制字符串返回。

5.7.SHA1

SHA1(string)

计算string的SHA-1哈希值，并将其作为十六进制字符串返回。

5.8.SUBSTRING

SUBSTRING(string FROM integer)

返回从给定点开始的字符串的子字符串。

SUBSTRING(string FROM integer FOR integer)

返回字符串从给定点开始指定长度的子字符串。

SUBSTRING(binary FROM integer)

返回二进制字符串从给定点开始的子字符串。

SUBSTRING(binary FROM integer FOR integer)

返回二进制字符串从给定点开始指定长度的子字符串。

5.9.LEFT

LEFT(string, length)

返回字符串string的前length个字符。

5.10.RIGHT

RIGHT(string, length)

返回字符串string的后length个字符。

5.11.REPLACE

REPLACE(char, search_string [, replace_string])

返回char中所有出现的search_string替换为replace_string的字符串。

5.12.TRANSLATE

TRANSLATE(expr, fromString, toString)

返回expr，其中fromString中每个字符的所有出现都被toString中的相应字符替换，expr中不在fromString中的字符不会被替换。

5.13.CHR

CHR(integer)

返回UTF-8代码为整数的字符。

5.14.CHAR_LENGTH

CHAR_LENGTH(string)

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

5.15.CHARACTER_LENGTH

CHARACTER_LENGTH(string)

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

5.16.||

string || string

返回两个字符串的连接。

5.17.CONCAT

CONCAT(string, string)

连接两个字符串，仅当两个字符串参数都为null时才返回null，否则将null视为空字符串。

CONCAT(string [, string ]*)

连接一个或多个字符串，如果任何参数为null，则返回null。

CONCAT(string [, string ]*)

连接一个或多个字符串，null被视为空字符串。

5.18.OVERLAY

OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ])

将string1的子字符串替换为string2。

OVERLAY(binary1 PLACING binary2 FROM integer [ FOR integer2 ])

将binary1的子字符串替换为binary2。

5.19.POSITION

POSITION(substring IN string)

返回substring在string中第一次出现的位置。

POSITION(substring IN string FROM integer)

返回substring在string中从integer位置开始第一次出现的位置(非标准SQL)。

POSITION(binary1 IN binary2)

返回binary1在binary2中第一次出现的位置。

POSITION(binary1 IN binary2 FROM integer)

返回binary1在binary2中从integer位置开始第一次出现的位置(非标准SQL)。

5.20.ASCII

ASCII(string)

返回字符串第一个字符的ASCII码，如果第一个字符是非ASCII字符，则返回其Unicode码值，如果字符串为空，则返回0。

5.21.REPEAT

REPEAT(string, integer)

返回字符串string重复integer次的结果，如果integer小于 1 则返回空字符串。

5.22.SPACE

SPACE(integer)

返回由integer个空格组成的字符串，如果integer小于 1 则返回空字符串。

5.23.STRCMP

STRCMP(string1, string2)

返回两个字符串的比较结果，如果string1小于string2则返回-1，如果string1等于string2则返回0，如果string1大于string2则返回1。

5.24.SOUNDEX

SOUNDEX(string)

返回字符串的语音表示，如果字符串使用多字节编码（如UTF-8）进行编码，则返回原始字符串。

5.25.DIFFERENCE

DIFFERENCE(string1, string2)

返回两个字符串相似性的度量，即它们的SOUNDEX值共有的字符位置的数量：如果SOUNDEX的值相同，则返回4，如果SOUNDEX的值完全不同，则返回0。

5.26.REVERSE

REVERSE(string)

返回字符串的逆序。

5.27.TRIM

TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2)

从string2的开始/结束/两端删除仅包含string1中字符的最长字符串。

5.28.LTRIM

LTRIM(string)

返回从string的开始删除所有空格的字符串。

5.29.RTRIM

RTRIM(string)

返回从string的末尾删除所有空格的字符串。

5.30.SUBSTR

SUBSTR(string, position [, substringLength ])

返回string的一部分，从position开始，长度为substringLength，SUBSTR使用输入字符集定义的字符计算长度。

5.31.LENGTH

LENGTH(string)

返回字符串string的长度。

5.32.OCTET_LENGTH

OCTET_LENGTH(binary)

返回二进制字符串binary的字节数。

5.33.LIKE

string1 LIKE string2 [ ESCAPE string3 ]

如果string1与string2匹配，则返回true，否则返回false。

5.34.SIMILAR TO

string1 SIMILAR TO string2 [ ESCAPE string3 ]

string1是否与正则表达式string2匹配。

6.日期/时间函数

6.1.EXTRACT

EXTRACT(timeUnit FROM datetime)

从日期时间值表达式中提取并返回指定时间单位的值。

6.2.FLOOR

FLOOR(datetime TO timeUnit)

返回日期时间值表达式datetime的向下取整值，向下取整到指定的时间单位。

6.3.CEIL

CEIL(datetime TO timeUnit)

返回日期时间值表达式datetime的向上取整值，向上取整到指定的时间单位。

6.4.TIMESTAMPDIFF

TIMESTAMPDIFF(timeUnit, datetime, datetime2)

返回两个日期时间值表达式之间的差值，以指定的时间单位表示，相当于（datetime2 - datetime）时间单位。

6.5.LAST_DAY

LAST_DAY(date)

返回数据类型为date的月份最后一天的日期，例如，对于DATE'2020-02-10'和TIMESTAMP'2020-02-10 10:10:10'，它都返回DATE'2020-02-29'。

6.6.DAYNAME

DAYNAME(datetime)

根据日期时间值返回一周中某一天的名称。

6.7.MONTHNAME

MONTHNAME(datetime)

返回日期时间中月份的名称（根据连接的区域设置），例如对于DATE“2020-02-10”和TIMESTAMP“2020-02-10 10:10:10”，它都返回二月。

6.8.DAYOFMONTH

DAYOFMONTH(date)

等价于EXTRACT(DAY FROM date)，返回一个 1 到 31 之间的整型值。

6.9.DAYOFWEEK

DAYOFWEEK(date)

等价于EXTRACT(DOW FROM date)，返回一个 1 到 7 之间的整型值。

6.10.DAYOFYEAR

DAYOFYEAR(date)

等价于EXTRACT(DOY FROM date)，返回一个 1 到 366 之间的整型值。

6.11.YEAR

YEAR(date)

等价于EXTRACT(YEAR FROM date)，返回一个整型值。

6.12.QUARTER

QUARTER(date)

等价于EXTRACT(QUARTER FROM date)，返回一个 1 到 4 之间的整型值。

6.13.MONTH

MONTH(date)

等价于EXTRACT(MONTH FROM date)，返回一个 1 到 12 之间的整型值。

6.14.WEEK

WEEK(date)

等价于EXTRACT(WEEK FROM date)，返回一个 0 到 53 之间的整型值。

6.15.HOUR

HOUR(date)

等价于EXTRACT(HOUR FROM datetime)，返回一个 0 到 23 之间的整型值。

6.16.MINUTE

MINUTE(date)

等价于EXTRACT(MINUTE FROM datetime)，返回一个 0 到 59 之间的整型值。

6.17.SECOND

SECOND(date)

等价于EXTRACT(SECOND FROM datetime)，返回一个 0 到 59 之间的整型值。

6.18.TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(integer)

返回从1970-01-01 00:00:00开始到integer秒数的时间戳。

6.19.TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(integer)

返回从1970-01-01 00:00:00开始到integer毫秒数的时间戳。

6.20.TIMESTAMP_MICROS

TIMESTAMP_MICROS(integer)

返回从1970-01-01 00:00:00开始到integer微秒数的时间戳。

6.21.UNIX_SECONDS

UNIX_SECONDS(timestamp)

返回从1970-01-01 00:00:00开始到timestamp的秒数。

6.22.UNIX_MILLIS

UNIX_MILLIS(timestamp)

返回从1970-01-01 00:00:00开始到timestamp的毫秒数。

6.23.UNIX_MICROS

UNIX_MICROS(timestamp)

返回从1970-01-01 00:00:00开始到timestamp的微秒数。

6.24.UNIX_DATE

UNIX_DATE(date)

返回从1970-01-01 00:00:00开始到date的日数。

5.25.DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(integer)

返回从1970-01-01 00:00:00开始到integer日数的日期。

6.26.DATE

DATE(timestamp)

从时间戳中提取日期。

DATE(timestampLtz)

从TIMESTAMP Ltz（一个瞬间，BigQuery的TIMESTAMP类型）中提取日期，假设为UTC。

DATE(timestampLtz, timeZone)

从带时区的TIMESTAMP Ltz（一个瞬间，BigQuery的TIMESTAMP类型）中提取日期。

DATE(string)

等价于CAST(string AS DATE)。

DATE(year, month, day)

返回对应年、月和日的日期值（均为整形值）。

6.27.CURRENT_TIME

CURRENT_TIME

返回当前时区的当前时间，数据类型为带时区的时间戳。

6.28.CURRENT_TIMESTAMP

CURRENT_TIMESTAMP

返回当前时区的当前日期和时间，数据类型为带时区的时间戳。

6.29.CURRENT_DATE

CURRENT_DATE

返回当前时区的当前日期，数据类型为日期类型。

6.30.LOCALTIME

LOCALTIME

返回当前时区的当前日期和时间，数据类型为时间类型。

LOCALTIME(precision)

返回当前时区的当前日期和时间，数据类型为时间类型，精度为precision。

6.31.LOCALTIMESTAMP

LOCALTIMESTAMP

返回当前时区的当前日期和时间，数据类型为时间戳。

LOCALTIMESTAMP(precision)

返回当前时区的当前日期和时间，数据类型为时间戳，精度为precision。

7.其他函数

7.1.CAST

CAST(value AS type)

将值转换为指定类型，整数类型之间的转换将截断为0。

7.2.COALESCE

COALESCE(value, value [, value ]*)

如果第一个值为null，则提供一个值。例如COALESCE(NULL, 5)返回5。

7.3.GREATEST

GREATEST(expr [, expr ]*)

返回表达式中的最大值。

7.4.NULLIF

NULLIF(value, value)

如果两个值相等，则返回null，否则返回第一个值，例如NULLIF(5, 5) 返回 NULL，NULLIF(5, 0) 返回 5。

7.5.NVL

NVL(value1, value2)

如果value1为null，则返回value2，否则返回value1。

7.6.CASE

CASE value
WHEN value1 [, value11 ]* THEN result1
[ WHEN valueN [, valueN1 ]* THEN resultN ]*
[ ELSE resultZ ]
END

简单场景：

CASE
WHEN condition1 THEN result1
[ WHEN conditionN THEN resultN ]*
[ ELSE resultZ ]
END

7.7.DECODE

DECODE(value, value1, result1 [, valueN, resultN ]* [, default ])

逐一将值与每个值N值进行比较，如果value等于valueN，则返回相应的resultN，否则返回默认值，如果未指定默认值，则返回NULL。

7.8.LEAST

LEAST(expr [, expr ]* )

返回表达式中的最小值。

7.9.COMPRESS

COMPRESS(string)

使用zlib压缩对字符串进行压缩，并将结果作为二进制字符串返回。

7.10.TYPEOF

TYPEOF value

返回值的类型。

7.11.RAND_UUID

RAND_UUID

生成一个随机的UUID。

7.12.SYSTEM_RANGE

SYSTEM_RANGE(start, end[, increment]

从表中返回一个范围，并可选择增量。

8.序列函数

8.1.NEXTVAL

NEXTVAL('seq_name')

推进序列并返回新值。

8.2.SETVAL

SETVAL('seq_name', bigint)

设置序列的当前值。

8.3.CURRVAL

CURRVAL('seq_name')

返回序列的当前值。

18624049226