COUNT(*) :: BIGINT

Calculates the number of input rows.

COUNT(any) :: BIGINT

Calculates the number of input rows in which the input value is not null.

COUNT(DISTINCT *) :: BIGINT

Calculates the number of input rows in which the input value is unique.

COUNT(DISTINCT any) :: BIGINT

Calculates the number of input rows in which the input value is unique and not null.

SUM(TINYINT | SMALLINT | INT) :: BIGINT, SUM(BIGINT | DECIMAL) :: DECIMAL, SUM(REAL) :: REAL, SUM(DOUBLE) :: DOUBLE

Calculates the sum of the non-null input values.

SUM(DISTINCT TINYINT | SMALLINT | INT) :: BIGINT, SUM(DISTINCT BIGINT | DECIMAL) :: DECIMAL, SUM(DISTINCT REAL) :: REAL, SUM(DISTINCT DOUBLE) :: DOUBLE

Calculates the sum of the unique, non-null input values.

AVG(DECIMAL) :: DECIMAL, AVG(DOUBLE) :: DOUBLE

Calculates the mean of all the non-null input values.

AVG(DISTINCT DECIMAL) :: DECIMAL, AVG(DISTINCT DOUBLE) :: DOUBLE

Calculates the mean of all the unique, non-null input values.

MIN(any) :: same type as the input

Calculates the minimum of the non-null input values. Applicable also to OBJECT type, if the underlying value is java.lang.Comparable.

=

Equal to

!= or <>

Not equal to

<

Less than

>

Greater than

<=

Less than or equal to

>=

Greater than or equal to

EXTRACT(element, date)

Extracts element from date.

Supported elements: MILLENIUM, CENTURY, DECADE, YEAR, ISOYEAR, QUARTER, MONTH, WEEK, DAY, DOW, ISODOW, DOY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, EPOCH

Supported date types: DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE

TO_TIMESTAMP_TZ(BIGINT)

Converts BIGINT values as seconds to TIMESTAMP_WITH_TIMEZONE.

TO_EPOCH_MILLIS(TIMESTAMP_WITH_TIMEZONE)

Converts TIMESTAMP_WITH_TIMEZONE values to an EPOCH value in milliseconds.

IS TRUE

IS NOT TRUE

IS FALSE

IS NOT FALSE

IS NULL

IS NOT NULL

IN

Evaluates to TRUE if the value on the left of the operator is in the list of values on the right.

NOT IN

Evaluates to TRUE if the value on the left of the operator is not in the list of values on the right.

ABS(number)

CEIL(number)

DEGREES(double)

EXP(number)

FLOOR(number)

LN(number)

LOG10(number)

RADIANS(double)

RAND

RAND(number)

ROUND(number)

ROUND(number, s integer)

TRUNCATE(number)

TRUNCATE(number, s integer)

+

Addition

-

Subtraction

*

Multiplication

/

Division

CAST(value AS type)

Convert the value to the given type

NULLIF(expression1, expression2)

Returns NULL if the expressions are equal. The data type of the NULL value returned is the same as the first expression.

COALESCE( expression1 [, expression2 [, expression3 ] …​] )

Evaluates each expression at a time, starting from the first. If the value of any expression is not NULL, its value is returned. If all the expressions are NULL, NULL is returned. Arguments to the right of the first non-null argument are not evaluated.

BETWEEN

Evaluates to TRUE if the value on the left of the operator is in a given range between the first value on the right and the second value on the right. (a >= b AND a <= c)

BETWEEN SYMMETRIC

Evaluates to TRUE if the value on the left of the operator is in a given range between the first value on the right and the second value on the right or the second value on the right and the first value on the right. (a >= b AND a <= c) OR (a >= c AND a <= b).

NOT BETWEEN

Evaluates to TRUE if the value on the left of the operator is not in a given range between the first value on the right and the second value on the right.

NOT BETWEEN SYMMETRIC

Evaluates to TRUE if the value on the left of the operator is not in a given range between the first value on the right and the second value on the right or the second value on the right and the first value on the right.

string || string

Concatenate two strings

ASCII(string)

ASCII code of the first character of the argument

INITCAP(string)

Convert the first letter of each word to upper case, and the rest to lower case

LENGTH(string)

Length of the string

LIKE

Return TRUE if the value string follows the pattern

LIKE …​ ESCAPE

Return TRUE if the value string follows the pattern, escaping a special character in the pattern

LOWER(string)

Convert the string to lower case

LTRIM(string)

Equivalent to TRIM(LEADING ' ' FROM string)

RTRIM(string)

Equivalent to TRIM(TRAILING ' ' FROM string)

SUBSTRING(string FROM integer)

Extract substring starting with the given position

SUBSTRING(string FROM integer FOR integer)

Extract substring starting with the given position for the given length

TRIM([LEADING|TRAILING|BOTH] [characters FROM] string)

Remove characters (a space by default) from the start/end/both ends of the string

TRIM(characters FROM string)

Equivalent to TRIM(BOTH characters FROM string)

TRIM(string)

Equivalent to TRIM(BOTH ' ' FROM string)

UPPER(string)

Convert the string to upper case

ACOS(double)

ASIN(double)

ATAN(double)

COS(double)

COT(double)

SIN(double)

TAN(double)