{/* Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. */}
:::note For more information on functions supported by Apache Calcite, see the product documentation. :::
AVG( [ ALL | DISTINCT ] numeric)
Returns the average (arithmetic mean) of numeric across all input values. When used, the type of data will be changed in the following way:
| Input type | Result type | Minimum scale |
|---|---|---|
DECIMAL, BIGINT, INTEGER, SMALLINT, TINYINT | DECIMAL | 16 |
DOUBLE, REAL | DOUBLE |
COUNT( [ ALL | DISTINCT ] value [, value ]*)
Returns the number of input rows for which value is not null (wholly not null if value is composite).
MAX( [ ALL | DISTINCT ] value)
Returns the maximum value across all input values.
MIN( [ ALL | DISTINCT ] value)
Returns the minimum value across all input values.
SUM( [ ALL | DISTINCT ] numeric)
Returns the sum of numeric across all input values.
ANY_VALUE( [ ALL | DISTINCT ] value)
Returns one of the values of value across all input values; this is NOT specified in the SQL standard.
EVERY(condition)
Returns TRUE if all of the values of condition are TRUE.
SOME(condition)
Returns TRUE if one or more of the values of condition is TRUE.
GROUPING(column_reference [,column_reference])
Returns a bit vector of the given grouping expressions.
JSON_TYPE(jsonValue)
Returns a string value indicating the type of jsonValue.
Indicates that the value is formatted as JSON.
JSON_VALUE(jsonValue, path [ RETURNING type ] [ { ERROR | NULL | DEFAULT expr } ON EMPTY ] [ { ERROR | NULL | DEFAULT expr } ON ERROR ] )
Extract an SQL scalar from a jsonValue using JSON path expression path.
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 ] )
Extract a JSON object or JSON array from jsonValue using the path JSON path expression.
JSON_TYPE(jsonValue)
Returns a string value indicating the type of jsonValue.
JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ] )
Whether a jsonValue satisfies a search criterion described using JSON path expression path.
JSON_DEPTH(jsonValue)
Returns an integer value indicating the depth of jsonValue.
JSON_KEYS(jsonValue [, path ])
Returns a string indicating the keys of a JSON jsonValue.
JSON_PRETTY(jsonValue)
Returns a pretty-printing of jsonValue.
JSON_LENGTH(jsonValue [, path ])
Returns a integer indicating the length of jsonValue.
JSON_REMOVE(jsonValue, path [, path ])
Removes data from jsonValue using a series of path expressions and returns the result.
JSON_STORAGE_SIZE(jsonValue)
Returns the number of bytes used to store the binary representation of jsonValue.
JSON_OBJECT( jsonKeyVal [, jsonKeyVal ]* [ nullBehavior ] )
Construct JSON object using a series of key-value pairs.
JSON_ARRAY( [ jsonVal [, jsonVal ]* ] [ nullBehavior ] )
Construct a JSON array using a series of values.
jsonValue IS JSON [ VALUE ]
Whether jsonValue is a JSON value.
jsonValue IS JSON OBJECT
Whether jsonValue is a JSON object.
jsonValue IS JSON ARRAY
Whether jsonValue is a JSON array.
jsonValue IS JSON SCALAR
Whether jsonValue is a JSON scalar value.
value 1 POSIX REGEX CASE INSENSITIVE value 2
Case-sensitive POSIX regular expression.
value 1 POSIX REGEX CASE SENSITIVE value 2
Case-sensitive POSIX regular expression.
REGEXP_REPLACE(string, regexp, rep [, pos [, occurrence [, matchType]]])
Replaces all substrings of string that match regexp with rep at the starting pos in expr (if omitted, the default is 1), occurrence specifies which occurrence of a match to search for (if omitted, the default is 1), matchType specifies how to perform matching
REGEXP_REPLACE(string, regexp)
Replaces all substrings of value that match regexp with an empty string and returns modified value.
MOD(numeric1, numeric2)
Returns the remainder (modulus) of numeric1 divided by numeric2. The result is negative only if numeric1 is negative.
EXP(numeric)
Returns e raised to the power of numeric.
POWER(numeric1, numeric2)
Returns numeric1 raised to the power of numeric2.
LN(numeric)
Returns the natural logarithm (base e) of numeric.
LOG10(numeric)
Returns the base 10 logarithm of numeric.
ABS(numeric)
Returns the absolute value of numeric.
RAND([seed])
Generates a random double between 0 and 1 inclusive, optionally initializing the random number generator with seed.
RAND_INTEGER([seed, ] numeric)
Generates a random integer between 0 and numeric - 1 inclusive, optionally initializing the random number generator with seed.
ACOS(numeric)
Returns the arc cosine of numeric.
ASIN(numeric)
Returns the arc sine of numeric.
ATAN(numeric)
Returns the arc tangent of numeric.
ATAN2(numeric, numeric)
Returns the arc tangent of the numeric coordinates.
SQRT(numeric)
Returns the square root of numeric.
CBRT(numeric)
Returns the cube root of numeric.
COS(numeric)
Returns the cosine of numeric.
COSH(numeric)
Returns the hyperbolic cosine of numeric.
COT(numeric)
Returns the cotangent of numeric.
DEGREES(numeric)
Converts numeric from radians to degrees.
RADIANS(numeric)
Converts numeric from degrees to radians.
ROUND(numeric1 [, integer2])
Rounds numeric1 to optionally integer2 (if not specified 0) places right to the decimal point.
SIGN(numeric)
Returns the signum of numeric.
SIN(numeric)
Returns the sine of numeric.
SINH(numeric)
Returns the hyperbolic sine of numeric.
TAN(numeric)
Returns the tangent of numeric.
TANH(numeric)
Returns the hyperbolic tangent of numeric.
TRUNCATE(numeric1 [, integer2])
Truncates numeric1 to optionally integer2 (if not specified 0) places right to the decimal point.
PI()
Returns a value that is closer than any other value to Pi.
UPPER(string)
Returns a character string converted to upper case.
LOWER(string)
Returns a character string converted to lower case.
INITCAP(string)
Returns string with the first letter of each word converter to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.
TO_BASE64(string)
Converts the string to base-64 encoded form and returns an encoded string
FROM_BASE64(string)
Returns the decoded result of a base-64 string as a string.
MD5(string)
Calculates an MD5 128-bit checksum of string and returns it as a hex string.
SHA1(string)
Calculates a SHA-1 hash value of string and returns it as a hex string.
SUBSTRING(string FROM integer)
Returns a substring of a character string starting at a given point.
SUBSTRING(string FROM integer FOR integer)
Returns a substring of a character string starting at a given point with a given length.
SUBSTRING(binary FROM integer)
Returns a substring of binary starting at a given point.
SUBSTRING(binary FROM integer FOR integer)
Returns a substring of binary starting at a given point with a given length.
LEFT(string, length)
Returns the leftmost length characters from the string.
RIGHT(string, length)
Returns the rightmost length characters from the string.
REPLACE(char, search_string [, replace_string])
Replaces search_string with replace_string.
TRANSLATE(expr, fromString, toString)
Returns expr with all occurrences of each character in fromString replaced by its corresponding character in toString. Characters in expr that are not in fromString are not replaced.
CHR(integer)
Returns the character whose UTF-8 code is integer.
CHAR_LENGTH(string)
Returns the number of characters in a character string.
CHARACTER_LENGTH(string)
Returns the number of characters in a character string.
string || string
Concatenates two character strings.
CONCAT(string, string)
Concatenates two strings, returns null only when both string arguments are null, otherwise treats null as empty string.
CONCAT(string [, string ]*)
Concatenates one or more strings, returns null if any of the arguments is null.
CONCAT(string [, string ]*)
Concatenates one or more strings, null is treated as empty string.
OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ])
Replaces a substring of string1 with string2.
OVERLAY(binary1 PLACING binary2 FROM integer [ FOR integer2 ])
Replaces a substring of binary1 with binary2.
POSITION(substring IN string)
Returns the position of the first occurrence of substring in string.
POSITION(substring IN string FROM integer)
Returns the position of the first occurrence of substring in string starting at a given point (not standard SQL).
POSITION(binary1 IN binary2)
Returns the position of the first occurrence of binary1 in binary2.
POSITION(binary1 IN binary2 FROM integer)
Returns the position of the first occurrence of binary1 in binary2 starting at a given point (not standard SQL).
ASCII(string)
Returns the ASCII code of the first character of string; if the first character is a non-ASCII character, returns its Unicode code point; returns 0 if string is empty.
REPEAT(string, integer)
Returns a string consisting of string repeated of integer times; returns an empty string if integer is less than 1.
SPACE(integer)
Returns a string with an integer number of spaces; returns an empty string if integer is less than 1.
STRCMP(string, string)
Returns 0 if both of the strings are same and returns -1 when the first argument is smaller than the second and 1 when the second one is smaller than the first one.
SOUNDEX(string)
DIFFERENCE(string, string)
Returns a measure of the similarity of two strings, namely the number of character positions that their SOUNDEX values have in common: 4 if the SOUNDEX values are same and 0 if the SOUNDEX values are totally different.
REVERSE(string)
Returns string with the order of the characters reversed.
TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2)
Removes the longest string containing only the characters in string1 from the start/end/both ends of string1.
LTRIM(string)
Returns string with all blanks removed from the start.
RTRIM(string)
Returns string with all blanks removed from the end.
SUBSTR(string, position [, substringLength ])
Returns a portion of string, beginning at character position, substringLength characters long. SUBSTR calculates lengths using characters as defined by the input character set.
LENGTH(string)
Equivalent to CHAR_LENGTH(string).
OCTET_LENGTH(binary)
Returns the number of bytes in binary.
string1 LIKE string2 [ ESCAPE string3 ]
Whether string1 matches pattern string2.
string1 SIMILAR TO string2 [ ESCAPE string3 ]
Whether string1 matches regular expression string2.
EXTRACT(timeUnit FROM datetime)
Extracts and returns the value of a specified datetime field from a datetime value expression.
FLOOR(datetime TO timeUnit)
Rounds datetime down to timeUnit.
CEIL(datetime TO timeUnit)
Rounds datetime up to timeUnit.
TIMESTAMPDIFF(timeUnit, datetime, datetime2)
Returns the (signed) number of timeUnit intervals between datetime and datetime2. Equivalent to (datetime2 - datetime) timeUnit.
LAST_DAY(date)
Returns the date of the last day of the month in a value of datatype DATE; For example, it returns DATE‘2020-02-29’ for both DATE‘2020-02-10’ and TIMESTAMP‘2020-02-10 10:10:10’.
DAYNAME(datetime)
Returns the name of the day of the week based on the datetime value.
MONTHNAME(date)
Returns the name, in the connection's locale, of the month in datetime; for example, it returns ‘二月’ for both DATE ‘2020-02-10’ and TIMESTAMP ‘2020-02-10 10:10:10’.
DAYOFMONTH(date)
Equivalent to EXTRACT(DAY FROM date). Returns an integer between 1 and 31.
DAYOFWEEK(date)
Equivalent to EXTRACT(DOW FROM date). Returns an integer between 1 and 7.
DAYOFYEAR(date)
Equivalent to EXTRACT(DOY FROM date). Returns an integer between 1 and 366.
YEAR(date)
Equivalent to EXTRACT(YEAR FROM date). Returns an integer.
QUARTER(date)
Equivalent to EXTRACT(QUARTER FROM date). Returns an integer between 1 and 4.
MONTH(date)
Equivalent to EXTRACT(MONTH FROM date). Returns an integer between 1 and 12.
WEEK(date)
Equivalent to EXTRACT(WEEK FROM date). Returns an integer between 1 and 53.
HOUR(date)
Equivalent to EXTRACT(HOUR FROM date). Returns an integer between 0 and 23.
MINUTE(date)
Equivalent to EXTRACT(MINUTE FROM date). Returns an integer between 0 and 59.
SECOND(date)
Equivalent to EXTRACT(SECOND FROM date). Returns an integer between 0 and 59.
TIMESTAMP_SECONDS(integer)
Returns the TIMESTAMP that is integer seconds after 1970-01-01 00:00:00.
TIMESTAMP_MILLIS(integer)
Returns the TIMESTAMP that is integer milliseconds after 1970-01-01 00:00:00.
TIMESTAMP_MICROS(integer)
Returns the TIMESTAMP that is integer microseconds after 1970-01-01 00:00:00.
UNIX_SECONDS(timestamp)
Returns the number of seconds since 1970-01-01 00:00:00.
UNIX_MILLIS(timestamp)
Returns the number of milliseconds since 1970-01-01 00:00:00.
UNIX_MICROS(timestamp)
Returns the number of microseconds since 1970-01-01 00:00:00.
UNIX_DATE(date)
Returns the number of days since 1970-01-01
DATE_FROM_UNIX_DATE(integer)
Returns the DATE that is integer days after 1970-01-01.
DATE(timestamp)
Extracts the DATE from a timestamp.
DATE(timestampLtz)
Extracts the DATE from timestampLtz (an instant; BigQuery's TIMESTAMP type), assuming UTC.
DATE(timestampLtz, timeZone)
Extracts the DATE from timestampLtz (an instant; BigQuery's TIMESTAMP type) in timeZone.
DATE(string)
Equivalent to CAST(string AS DATE).
DATE(year, month, day)
Returns a DATE value for year, month, and day (all of type INTEGER).
CURRENT_TIMESTAMP
Returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH LOCAL TIME ZONE.
CURRENT_DATE
Returns the current date in the session time zone, in a value of datatype DATE.
LOCALTIME
Returns the current date and time in the session time zone in a value of datatype TIME.
LOCALTIME(precision)
Returns the current date and time in the session time zone in a value of datatype TIME, with precision digits of precision.
LOCALTIMESTAMP
Returns the current date and time in the session time zone in a value of datatype TIMESTAMP.
LOCALTIMESTAMP(precision)
Returns the current date and time in the session time zone in a value of datatype TIMESTAMP, with precision digits of precision.
CAST(value AS type)
Converts a value to a given type. Casts between integer types truncate towards 0.
COALESCE(value, value [, value ]*)
Provides a value if the first value is null. For example, COALESCE(NULL, 5) returns 5.
GREATEST(expr [, expr ]*)
Returns the greatest of the expressions.
NULLIF(value, value)
Returns NULL if the values are the same. For example, NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5.
NVL(value1, value2)
Returns value1 if value1 is not null, otherwise value2.
CASE value WHEN value1 [, value11 ]* THEN result1 [ WHEN valueN [, valueN1 ]* THEN resultN ]* [ ELSE resultZ ] END
Simple case.
CASE WHEN condition1 THEN result1 [ WHEN conditionN THEN resultN ]* [ ELSE resultZ ] END
Searched case.
DECODE(value, value1, result1 [, valueN, resultN ]* [, default ])
Compares value to each valueN value one by one; if value is equal to a valueN, returns the corresponding resultN, else returns default, or NULL if default is not specified.
LEAST(expr [, expr ]* )
Returns the least of the expressions.
COMPRESS(string)
Compresses a string using zlib compression and returns the result as a binary string.
TYPEOF value
Returns the type of the specified value.
RAND_UUID
Generates a random UUID.
SYSTEM_RANGE(start, end[, increment])
Returns a range from the table, with an optional increment.
CURRENT_USER
Returns the name of the current database user. When security is disabled, returns the system user name instead.