# Aggregate Functions

 Function Explanation 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.

# Comparison Operators

 Operator Explanation = Equal to != or <> Not equal to < Less than > Greater than <= Less than or equal to >= Greater than or equal to

# Date and Time Functions

 Function Explanation 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.

# Expressions

AND/OR Operators

NOT Operator

IS Operator

 IS TRUE IS NOT TRUE IS FALSE IS NOT FALSE IS NULL IS NOT NULL

IN Operator

 Operator Explanation 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.

# Mathematical Functions

 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)

# Mathematical Operators

 Operator Explanation + Addition - Subtraction * Multiplication / Division

# Special Functions

 Function Explanation 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.
 CASE Returns a value when the first condition is met (like an if-then-else statement).

 Function Explanation 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 Functions

 Function Explanation string || string Concatenate two strings ASCII(string) ASCII code of the first character of the argument INITCAP(string) 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

# Trigonometric Functions

 ACOS(double) ASIN(double) ATAN(double) COS(double) COT(double) SIN(double) TAN(double)