All Functions

Modulo (%)

Syntax:number_1 % number_2

Returns the remainder from dividing the first number number_1 by the second number number_2.

Multiplication (*)

Syntax:value_1 * value_2

If both arguments are numbers, it returns the result by multiplying value_1 by value_2.

If one of the arguments is String and the other is Integer, it returns the string repeated the specified number of times.

Addition and concatenation (+)

Syntax:value_1 + value_2

Behaves differently depending on the argument types. Possible options are listed in the table:

Type of `value_1`	Type of `value_2`	Return value
`Fractional number \| Integer`	`Fractional number \| Integer`	The sum of the numbers `value_1` and `value_2`.
`Date`	`Fractional number \| Integer`	The date that is `value_2` days greater than `value_1` (rounded down to an integer number of days).
`Datetime`	`Fractional number \| Integer`	The date with time, `value_2` days greater than `value_1`. If `value_2` contains a fractional part, it is converted hours (`1/24`), minutes (`1/1440`), and seconds (`1/86400`).
`String`	`String`	The merging (concatenation) of strings `value_1` and `value_2`.
`Array of fractional numbers \| Array of integers \| Array of strings`	`Array of fractional numbers \| Array of integers \| Array of strings`	The merging (concatenation) of arrays `value_1` and `value_2`.

Changing the order of arguments does not affect the result.

Subtraction (-)

Syntax:value_1 - value_2

Behaves differently depending on the argument types. Possible options are listed in the table:

Type of `value_1`	Type of `value_2`	Return value
`Fractional number \| Integer`	`Fractional number \| Integer`	The difference between the numbers `value_1` and `value_2`.
`Date`	`Fractional number \| Integer`	The date that is `value_2` days smaller than `value_1` (rounded down to an integer number of days).
`Datetime`	`Fractional number \| Integer`	The date with time, `value_2` days smaller than `value_1`. If `value_2` contains a fractional part, it is converted to hours (`1/24`), minutes (`1/1440`), and seconds (`1/86400`).
`Date`	`Date`	The difference between two dates in days.
`Any`	`Any`	The difference between two dates in days: the integer part — the number of whole days, the fractional part — the number of hours, minutes and seconds expressed as a fraction of the whole day (1 hour is '1/24').
`Datetime`	`Datetime`	The difference between two dates in days: the integer part — the number of whole days, the fractional part — the number of hours, minutes and seconds expressed as a fraction of the whole day (1 hour is '1/24').

Division (/)

Syntax:number_1 / number_2

Divides the number number_1 by the number number_2.

Comparison

Syntax:
value_1 = value_2
or
value_1 != value_2
or
value_1 < value_2
or
value_1 <= value_2
or
value_1 > value_2
or
value_1 >= value_2

Compares the value value_1 with the value value_2.

Power (^)

Syntax:base ^ power

Raises base to the power of power.

Syntax:
CASE expression WHEN value_1 THEN result_1 [ WHEN value_2 THEN result_2 ... ] ELSE default_result END
or
CASE( expression, value_1, result_1, [ value_2, result_2, ... ] default_result )

Compares expression to value_1, value_2, ... consecutively and returns the corresponding result for the first match. If no match is found, it returns default_result.

IF

Syntax:
IF condition_1 THEN result_1 [ ELSEIF condition_2 THEN result_2 ... ] ELSE default_result END
or
IF( condition_1, result_1, [ condition_2, result_2, ... ] default_result )

Checks conditional expressions condition_1, result_1, ... and returns the matching result for the first condition found to be TRUE. IF all conditional expressions are FALSE, it returns default_result.

ABS

Syntax:ABS( number )

Returns the absolute value of number.

ACOS

Syntax:ACOS( number )

Returns the arccosine of number in radians.

AGO

Syntax:
AGO( measure, date_dimension [ , unit [ , number ] ] )
or
AGO( measure, date_dimension [ , unit [ , number ] ] [ BEFORE FILTER BY ... ] [ IGNORE DIMENSIONS ... ] )

Re-evaluate measure for a date/time with a given offset. The date_dimension argument is the dimension along which the offset is made. The number argument is an integer. It can be negative. The unit argument takes the following values:

"year";
"month";
"day";
"hour";
"minute";
"second".

Can also be used as AGO( measure, date_dimension, number ). In this case, the third argument is interpreted as the number of days.

ALL_CONCAT

Syntax:
ALL_CONCAT( expression [ , separator ] )
or
ALL_CONCAT( expression [ , separator ] [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns a string that contains all grouped values of expression delimited by separator (if separator is not specified, a comma is used).

AND

Syntax:value_1 AND value_2

Performs a Boolean join of two expressions with the AND condition.

ANY

Syntax:
ANY( value )
or
ANY( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns one of the values of value from the group. This is a nondeterministic aggregation — the result may vary for the same data over multiple queries.

ARG_MAX

Syntax:
ARG_MAX( value, comp )
or
ARG_MAX( value, comp [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns value for the maximum value of comp in the group. If multiple values of value match the maximum value of comp, then the first one encountered is returned. This makes the function non-deterministic.

ARG_MIN

Syntax:
ARG_MIN( value, comp )
or
ARG_MIN( value, comp [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns value for the minimum value of comp in the group. If multiple values of value match the minimum value of comp, then the first one encountered is returned. This makes the function non-deterministic.

ARR_AVG

Syntax:ARR_AVG( array )

Returns the average of elements in the array.

ARR_MAX

Syntax:ARR_MAX( array )

Returns the greatest of elements in the array.

ARR_MIN

Syntax:ARR_MIN( array )

Returns the least of elements in the array.

ARR_PRODUCT

Syntax:ARR_PRODUCT( array )

Returns the product of elements in the array.

ARR_STR

Syntax:ARR_STR( array [ , delimiter [ , null_str ] ] )

Concatenates elements of the array array using delimiter as a delimiter (comma by default) and null_str as a NULL string (NULL items are skipped by default).

ARR_SUM

Syntax:ARR_SUM( array )

Returns the sum of elements in the array.

ARRAY

Syntax:ARRAY( value_1, value_2, value_3 [ , ... ] )

Returns an array containing the passed values.

ASCII

Syntax:ASCII( string )

Returns the numeric representation of the first character of the string.

ASIN

Syntax:ASIN( number )

Returns the arcsine of number in radians.

AT_DATE

Syntax:
AT_DATE( measure, date_dimension, date_expr )
or
AT_DATE( measure, date_dimension, date_expr [ BEFORE FILTER BY ... ] [ IGNORE DIMENSIONS ... ] )

Re-evaluate measure for a date/time specified by date_expr. The date_dimension argument is the dimension along which the offset is made.

ATAN

Syntax:ATAN( number )

Returns the arctangent of number in radians.

ATAN2

Syntax:ATAN2( x, y )

Returns the arctangent in radians for the specified coordinates x and y.

AVG

Syntax:
AVG( value )
or
AVG( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the average of all values. Applicable to numeric data types as well as Date.

AVG (window)

Syntax:
AVG( value TOTAL | WITHIN ... | AMONG ... )
or
AVG( value TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Returns the average of all values. Applicable to numeric data types.

AVG_IF

Syntax:
AVG_IF( expression, condition )
or
AVG_IF( expression, condition [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the average of all values that meet the condition condition. If the values don't exist, it returns NULL. Applicable to numeric data types only.

AVG_IF (window)

Syntax:
AVG_IF( expression, condition TOTAL | WITHIN ... | AMONG ... )
or
AVG_IF( expression, condition TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Returns the average of all values that meet the condition condition. If the values don't exist, it returns NULL. Applicable to numeric data types only.

BETWEEN

Syntax:value [ NOT ] BETWEEN low AND high

Returns TRUE if value is in the range from low to high.

The option value NOT BETWEEN low AND high returns the opposite value.

BOLD

Syntax:BOLD( text )

Stylizes the passed text in bold font.

BOOL

Syntax:BOOL( expression )

Converts the expression expression to Boolean type according to the following rules:

Type	`FALSE`	`TRUE`
`Fractional number \| Integer`	`0`, `0.0`	All others
`String`	Empty string (`""`)	All others
`Boolean`	`FALSE`	`TRUE`
`Date \| Datetime`	-	`TRUE`

CAST_ARR_FLOAT

Syntax:CAST_ARR_FLOAT( array )

Converts array to an array of fractional numbers. The conversion rules are the same as for FLOAT.

CAST_ARR_INT

Syntax:CAST_ARR_INT( array )

Converts array to an array of integers. The conversion rules are the same as for INT.

CAST_ARR_STR

Syntax:CAST_ARR_STR( array )

Converts array to an array of strings. The conversion rules are the same as for STR.

CEILING

Syntax:CEILING( number )

Rounds the value up to the nearest integer.

CHAR

Syntax:CHAR( string )

Converts the numeric representation of an ASCII character to a value.

COMPARE

Syntax:COMPARE( left, right, epsilon )

Returns:

0 if left and right differs by not more than epsilon.
-1 if left is less than right by more than epsilon.
1 if left is greater than right by more than epsilon.

CONCAT

Syntax:CONCAT( arg_1, arg_2, arg_3 [ , ... ] )

Merges any number of strings. When non-string types are used, they're converted to strings and then merged.

CONTAINS (array)

Syntax:CONTAINS( array, value )

Returns TRUE if array contains value.

CONTAINS (string)

Syntax:CONTAINS( string, substring )

Returns TRUE if string contains substring. For case-insensitive searches, see ICONTAINS.

COS

Syntax:COS( number )

Returns the cosine of number in radians.

COT

Syntax:COT( number )

Returns the cotangent of number in radians.

COUNT

Syntax:
COUNT( [ value ] )
or
COUNT( [ value ] [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the number of items in the group.

COUNT (window)

Syntax:
COUNT( [ value ] TOTAL | WITHIN ... | AMONG ... )
or
COUNT( [ value ] TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Returns the number of items in the specified window.

COUNT_IF

Syntax:
COUNT_IF( condition )
or
COUNT_IF( condition [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the number of items in the group meeting the condition condition.

COUNT_IF (window)

Syntax:
COUNT_IF( expression, condition TOTAL | WITHIN ... | AMONG ... )
or
COUNT_IF( expression, condition TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Returns the number of items in the specified window meeting the expression condition.

COUNT_ITEM

Syntax:COUNT_ITEM( array, value )

Returns the number of elements in the array array equal to value. The type of value must match the type of the array elements.

COUNTD

Syntax:
COUNTD( value )
or
COUNTD( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the number of unique values in the group.

COUNTD_APPROX

Syntax:
COUNTD_APPROX( value )
or
COUNTD_APPROX( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the approximate number of unique values in the group. Faster than COUNTD, but doesn't guarantee accuracy.

COUNTD_IF

Syntax:
COUNTD_IF( expression, condition )
or
COUNTD_IF( expression, condition [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the number of unique values in the group that meet the condition condition.

DATE

Syntax:DATE( expression [ , timezone ] )

Converts the expression expression to date format.

The date must be in the format YYYY-MM-DD.

If expression is a number, then the timezone option can be used to convert the date to the specified time zone.

DATE_PARSE

Syntax:DATE_PARSE( value )

Converts the value expression to date format. Unlike DATE, it supports multiple formats.

DATEADD

Syntax:DATEADD( datetime [ , unit [ , number ] ] )

Returns the date obtained by adding unit in the amount of number to the specified date datetime.

The number argument is an integer. It can be negative. The unit argument takes the following values:

"year";
"month";
"day";
"hour";
"minute";
"second".

DATEPART

Syntax:DATEPART( datetime, unit [ , firstday ] )

Returns a part of the date as an integer.

Depending on the argument, unit returns the following values:

"year" — the year number (see YEAR);
"quarter" — the number of the quarter (from 1 to 4) of the year (see QUARTER);
"month" — the number of the month in the year (see MONTH);
"week" — the number of the week in the year according to ISO 8601 (see WEEK);
"dayofweek", "dow" — the number of the day of the week (see DAYOFWEEK);
"day" — the number of the day in the month (see DAY);
"hour" — the number of the hour in the day (see HOUR);
"minute" — the number of the minute in the hour (see MINUTE);
"second" — the number of the second in the minute (see SECOND).

If you select "dayofweek", you can use the additional parameter firstday to specify the first day of the week (Monday by default). Learn more about this parameter in the DAYOFWEEK function description.

DATETIME

Syntax:DATETIME( expression [ , timezone ] )

Converts the expression expression to date and time format. When converting Date to DateTime, the time is set to '00:00:00'. The date must be in the format YYYY-MM-DDThh:mm:ss or YYYY-MM-DD hh:mm:ss.

The date and time can be converted to the specified time zone when the timezone option is available.

DATETIME_PARSE

Syntax:DATETIME_PARSE( value )

Converts the value expression to date and time format. Unlike DATETIME, it supports multiple formats.

DATETRUNC

Syntax:DATETRUNC( datetime, unit [ , number ] )

Rounds datetime down to the given unit. If optional number is given, then the value is rounded down to a number multiple of unit (omitting number is the same as number = 1).

Supported units:

"second";
"minute";
"hour";
"day" (acts as the day of the year if number is specified);
"week";
"month";
"quarter";
"year".

DAY

Syntax:DAY( datetime )

Returns the number of the day in the month of the specified date datetime.

DAYOFWEEK

Syntax:DAYOFWEEK( datetime [ , firstday ] )

Returns the day of the week according to ISO 8601.

Monday — 1.
Sunday — 7.

If the additional parameter firstday is specified, this day is considered the first day of the week. Valid values:

"Monday", "Mon" — Monday;
"Tuesday". "Tue" — Tuesday;
"Wednesday", "Wed" — Wednesday;
"Thursday", "Thu" — Thursday;
"Friday", "Fri" — Friday;
"Saturday", "Sat" — Saturday;
"Sunday", "Sun" — Sunday.

DB_CAST

Syntax:DB_CAST( expression, native_type [ , param_1 [ , param_2 ] ] )

Converts the expression expression to database's native type native_type.

The following type casts are supported:

Data source	Data type	Native data type	Parameters for native type	Comment
`ClickHouse`	`Fractional number`	`Float32`
`ClickHouse`	`Fractional number`	`Float64`
`ClickHouse`	`Fractional number`	`Decimal`	`Integer`, `Integer`
`ClickHouse`	`Integer`	`Int8`
`ClickHouse`	`Integer`	`Int16`
`ClickHouse`	`Integer`	`Int32`
`ClickHouse`	`Integer`	`Int64`
`ClickHouse`	`Integer`	`UInt8`
`ClickHouse`	`Integer`	`UInt16`
`ClickHouse`	`Integer`	`UInt32`
`ClickHouse`	`Integer`	`UInt64`
`ClickHouse`	`String`	`String`
`PostgreSQL`	`Array of fractional numbers`	`double precision[]`
`PostgreSQL`	`Array of fractional numbers`	`real[]`
`PostgreSQL`	`Array of fractional numbers`	`numeric[]`	`Integer`, `Integer`
`PostgreSQL`	`Array of integers`	`smallint[]`
`PostgreSQL`	`Array of integers`	`integer[]`
`PostgreSQL`	`Array of integers`	`bigint[]`
`PostgreSQL`	`Array of strings`	`text[]`
`PostgreSQL`	`Array of strings`	`character varying[]`
`PostgreSQL`	`Array of strings`	`varchar[]`
`PostgreSQL`	`Fractional number`	`double precision`
`PostgreSQL`	`Fractional number`	`real`
`PostgreSQL`	`Fractional number`	`numeric`	`Integer`, `Integer`
`PostgreSQL`	`Integer`	`smallint`
`PostgreSQL`	`Integer`	`integer`
`PostgreSQL`	`Integer`	`bigint`
`PostgreSQL`	`String`	`text`
`PostgreSQL`	`String`	`character`	`Integer`	Alias: `char`
`PostgreSQL`	`String`	`character varying`	`Integer`	Alias: `varchar`
`PostgreSQL`	`String`	`char`	`Integer`	Alias for `character`
`PostgreSQL`	`String`	`varchar`	`Integer`	Alias for `character varying`

DEGREES

Syntax:DEGREES( radians )

Converts radians to degrees.

DIV

Syntax:DIV( number_1, number_2 )

Divides number_1 by number_2. The result is rounded down to the nearest integer.

ENDSWITH

Syntax:ENDSWITH( string, substring )

Returns TRUE if string ends in substring. For case-insensitive searches, see IENDSWITH.

EXP

Syntax:EXP( number )

Returns the result of raising the number 'e' to the power of number.

FIND

Syntax:FIND( string, substring [ , start_index ] )

Returns the index of the position of the first character of the substring substring in the string string.

If the start_index option is specified, the search starts from the specified position.

FIRST (window)

Syntax:
FIRST( value )
or
FIRST( value [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the value of value from the first row in the window. See also LAST.

FLOAT

Syntax:FLOAT( expression )

Converts the expression expression to fractional number format according to the following rules:

Type	Value
`Fractional number \| Integer`	Original value.
`Date \| Datetime`	Unix time corresponding to the date and time. If the value contains time zone data, it's used in the calculation. If the time zone is unknown, the time is set in UTC.
`String`	A number from a decimal string.
`Boolean`	`TRUE` — `1.0`, `FALSE` — `0.0`.

FLOOR

Syntax:FLOOR( number )

Rounds the value down to the nearest integer.

GEOPOINT

Syntax:GEOPOINT( value_1 [ , value_2 ] )

Generates a Geopoint type value. For the input, it accepts a string, a "geopoint" type value, or coordinates — latitude value_1 and longitude value_2. If a single string is input, it must contain a list of two numbers (latitude and longitude) in JSON syntax.

GEOPOLYGON

Syntax:GEOPOLYGON( value )

Converts the value expression to geopolygon format.

GET_ITEM

Syntax:GET_ITEM( array, index )

Returns the element with the index index from the array array. Index must be any integer. Indexes in an array begin with one.

GREATEST

Syntax:GREATEST( value_1, value_2, value_3 [ , ... ] )

Returns the greatest value.

HOUR

Syntax:HOUR( datetime )

Returns the number of the hour in the day of the specified date and time datetime. When the date is specified without time, it returns 0.

ICONTAINS

Syntax:ICONTAINS( string, substring )

Case-insensitive version of CONTAINS. Returns TRUE if string contains substring.

IENDSWITH

Syntax:IENDSWITH( string, substring )

Case-insensitive version of ENDSWITH. Returns TRUE if string ends in substring.

IFNULL

Syntax:IFNULL( check_value, alt_value )

Returns check_value if it's not NULL. Otherwise returns alt_value.

IN

Syntax:item [ NOT ] IN (<list>)

Checks whether the value matches at least one of the values listed in IN(...).

The option item NOT IN (<list>) returns the opposite value.

INT

Syntax:INT( expression )

Converts the expression expression to integer format according to the following rules:

Type	Value
`Integer`	Original value.
`Fractional number`	Integer part of the number (rounded down).
`Date \| Datetime`	Unix time corresponding to the date and time. If the value contains time zone data, it's used in the calculation. If the time zone is unknown, the time is set in UTC.
`String`	A number from a decimal string.
`Boolean`	`TRUE` — `1`, `FALSE` — `0`.

IS FALSE

Syntax:value IS [ NOT ] FALSE

Checks whether the value value is false (FALSE).

The value IS NOT FALSE option returns the opposite value.

ISNULL

Syntax:
ISNULL( expression )
or
expression IS [ NOT ] NULL

Returns TRUE if expression is NULL, otherwise returns FALSE.

expression IS NOT NULL returns the opposite result.

ISTARTSWITH

Syntax:ISTARTSWITH( string, substring )

Case-insensitive version of STARTSWITH. Returns TRUE if string starts with substring.

IS TRUE

Syntax:value IS [ NOT ] TRUE

Checks whether the value of value is true (TRUE).

The value IS NOT TRUE option returns the opposite value.

ITALIC

Syntax:ITALIC( text )

Stylizes the passed text in cursive font.

LAG (window)

Syntax:
LAG( value [ , offset [ , default ] ] )
or
LAG( value [ , offset [ , default ] ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns value re-evaluated against the row that is offset from the current row by offset within the specified window:

Positive offset seeks among preceding rows.
Negative offset seeks among following rows.

By default offset is 1.

If there is no available value (offset reaches before the first row or after the last one), then default is returned. If default is not specified, then NULL is used.

See also AGO for a non-window function alternative.

LAST (window)

Syntax:
LAST( value )
or
LAST( value [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the value of value from the last row in the window. See also FIRST.

LEAST

Syntax:LEAST( value_1, value_2, value_3 [ , ... ] )

Returns the smallest value.

LEFT

Syntax:LEFT( string, number )

Returns a string that contains the number of characters specified in number from the beginning of the string string.

LEN

Syntax:LEN( value )

Returns the number of characters in the string or items in array value.

LIKE

Syntax:string_1 [ NOT ] LIKE string_2

Matches the string string_1 to the template string_2 and returns TRUE on match. You can specify the value in string_2 or use the % character to match a string of any length.

The string_1 NOT LIKE option returns the opposite value.

LN

Syntax:LN( number )

Returns the natural logarithm of the number number. Returns 'NULL' if the number is less than or equal to 0.

LOG

Syntax:LOG( value, base )

Returns the logarithm of value to base base. Returns 'NULL' if the number value is less than or equal to 0.

LOG10

Syntax:LOG10( number )

Returns the logarithm of the number number to base 10. Returns 'NULL' if the number is less than or equal to 0.

LOWER

Syntax:LOWER( string )

Returns the string string in lowercase.

LTRIM

Syntax:LTRIM( string )

Returns the string string without spaces at the beginning of the string.

MARKUP

Syntax:MARKUP( arg_1, arg_2, arg_3 [ , ... ] )

Merges marked up text pieces. Can also be used for converting strings to marked up text.

MAVG (window)

Syntax:
MAVG( value, rows_1 [ , rows_2 ] )
or
MAVG( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the moving average of values in a fixed-size window defined by the sort order and arguments:

`rows_1`	`rows_2`	Window
positive	-	The current row and `rows_1` preceding rows.
negative	-	The current row and -`rows_1` following rows.
any sign	any sign	`rows_1` preceding rows, the current row and `rows_2` following rows.

Window functions with a similar behavior: MSUM, MCOUNT, MMIN, MMAX.

MAX

Syntax:
MAX( value )
or
MAX( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the maximum value.

If value:

number — Returns the largest number.
date — Returns the latest date.
string — Returns the last value in the alphabetic order.

MAX (window)

Syntax:
MAX( value TOTAL | WITHIN ... | AMONG ... )
or
MAX( value TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Returns the maximum value.

If value:

number — Returns the largest number.
date — Returns the latest date.
string — Returns the last value in the alphabetic order.

MCOUNT (window)

Syntax:
MCOUNT( value, rows_1 [ , rows_2 ] )
or
MCOUNT( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the moving count of (non-NULL) values in a fixed-size window defined by the sort order and arguments:

`rows_1`	`rows_2`	Window
positive	-	The current row and `rows_1` preceding rows.
negative	-	The current row and -`rows_1` following rows.
any sign	any sign	`rows_1` preceding rows, the current row and `rows_2` following rows.

Window functions with a similar behavior: MSUM, MMIN, MMAX, MAVG.

MEDIAN

Syntax:
MEDIAN( value )
or
MEDIAN( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the median value.

MIN

Syntax:
MIN( value )
or
MIN( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the minimum value.

If value:

number — Returns the smallest number.
date — Returns the earliest date.
string — Returns the first value in the alphabetic order.

MIN (window)

Syntax:
MIN( value TOTAL | WITHIN ... | AMONG ... )
or
MIN( value TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Returns the minimum value.

If value:

number — Returns the smallest number.
date — Returns the earliest date.
string — Returns the first value in the alphabetic order.

MINUTE

Syntax:MINUTE( datetime )

Returns the number of the minute in the hour of the specified date datetime. When the date is specified without time, it returns 0.

MMAX (window)

Syntax:
MMAX( value, rows_1 [ , rows_2 ] )
or
MMAX( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the moving maximum of values in a fixed-size window defined by the sort order and arguments:

`rows_1`	`rows_2`	Window
positive	-	The current row and `rows_1` preceding rows.
negative	-	The current row and -`rows_1` following rows.
any sign	any sign	`rows_1` preceding rows, the current row and `rows_2` following rows.

Window functions with a similar behavior: MSUM, MCOUNT, MMIN, MAVG.

MMIN (window)

Syntax:
MMIN( value, rows_1 [ , rows_2 ] )
or
MMIN( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the moving minimum of values in a fixed-size window defined by the sort order and arguments:

`rows_1`	`rows_2`	Window
positive	-	The current row and `rows_1` preceding rows.
negative	-	The current row and -`rows_1` following rows.
any sign	any sign	`rows_1` preceding rows, the current row and `rows_2` following rows.

Window functions with a similar behavior: MSUM, MCOUNT, MMAX, MAVG.

MONTH

Syntax:MONTH( datetime )

Returns the number of the month in the year of the specified date datetime.

MSUM (window)

Syntax:
MSUM( value, rows_1 [ , rows_2 ] )
or
MSUM( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the moving sum of values in a fixed-size window defined by the sort order and arguments:

`rows_1`	`rows_2`	Window
positive	-	The current row and `rows_1` preceding rows.
negative	-	The current row and -`rows_1` following rows.
any sign	any sign	`rows_1` preceding rows, the current row and `rows_2` following rows.

Window functions with a similar behavior: MCOUNT, MMIN, MMAX, MAVG.

Negation (-)

Syntax:-value

Returns the number value with the opposite sign.

NOT

Syntax:NOT value

Inverts a Boolean value.

NOW

Syntax:NOW()

Returns the current date and time, depending on the data source and connection type.

OR

Syntax:value_1 OR value_2

Performs a Boolean join of two expressions with the OR condition.

PI

Syntax:PI()

Returns PI. The accuracy depends on the data source.

POWER

Syntax:POWER( base, power )

Raises base to the power of power.

QUANTILE

Syntax:
QUANTILE( value, quant )
or
QUANTILE( value, quant [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the precise quant-level quantile (quant should be in range from 0 to 1).

QUANTILE_APPROX

Syntax:
QUANTILE_APPROX( value, quant )
or
QUANTILE_APPROX( value, quant [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the approximate quant-level quantile (quant should be in range from 0 to 1).

QUARTER

Syntax:QUARTER( datetime )

Returns the number of the quarter (from 1 to 4) of the year of the specified date datetime.

RADIANS

Syntax:RADIANS( degrees )

Converts degrees degrees to radians.

RANK (window)

Syntax:
RANK( value [ , direction ] )
or
RANK( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ BEFORE FILTER BY ... ] )

Returns the rank of the current row if ordered by the given argument. Rows corresponding to the same value used for sorting have the same rank. If the first two rows both have rank of 1, then the next row (if it features a different value) will have rank 3, so, in effect, it is rank with gaps.

If direction is "desc" or omitted, then ranking is done from greatest to least, if "asc", then from least to greatest.

RANK_DENSE (window)

Syntax:
RANK_DENSE( value [ , direction ] )
or
RANK_DENSE( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ BEFORE FILTER BY ... ] )

If direction is "desc" or omitted, then ranking is done from greatest to least, if "asc", then from least to greatest.

See also RANK, RANK_UNIQUE, RANK_PERCENTILE.

RANK_PERCENTILE (window)

Syntax:
RANK_PERCENTILE( value [ , direction ] )
or
RANK_PERCENTILE( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ BEFORE FILTER BY ... ] )

Returns the relative rank (from 0 to 1) of the current row if ordered by the given argument. Calculated as (RANK(...) - 1) / (row count) .

If direction is "desc" or omitted, then ranking is done from greatest to least, if "asc", then from least to greatest.

RANK_UNIQUE (window)

Syntax:
RANK_UNIQUE( value [ , direction ] )
or
RANK_UNIQUE( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ BEFORE FILTER BY ... ] )

Returns the rank of the current row if ordered by the given argument. Rows corresponding to the same value have different rank values. This means that rank values are sequential and different for all rows, always increasing by 1 for the next row.

If direction is "desc" or omitted, then ranking is done from greatest to least, if "asc", then from least to greatest.

See also RANK, RANK_DENSE, RANK_PERCENTILE.

RAVG (window)

Syntax:
RAVG( value [ , direction ] )
or
RAVG( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the average of all values in a growing (or shrinking) window defined by the sort order and the value of direction:

`direction`	Window
`"asc"`	Starts from the first row and ends at the current row.
`"desc"`	Starts from the current row and ends at the last row.

By default "asc" is used.

Window functions with a similar behavior: RSUM, RCOUNT, RMIN, RMAX.

RCOUNT (window)

Syntax:
RCOUNT( value [ , direction ] )
or
RCOUNT( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the count of all values in a growing (or shrinking) window defined by the sort order and the value of direction:

`direction`	Window
`"asc"`	Starts from the first row and ends at the current row.
`"desc"`	Starts from the current row and ends at the last row.

By default "asc" is used.

Window functions with a similar behavior: RSUM, RMIN, RMAX, RAVG.

REGEXP_EXTRACT

Syntax:REGEXP_EXTRACT( string, pattern )

Returns the substring string that matches the regular expression pattern pattern.

REGEXP_EXTRACT_NTH

Syntax:REGEXP_EXTRACT_NTH( string, pattern, match_index )

Returns a substring string that matches the regular expression pattern pattern starting from the specified index.

REGEXP_MATCH

Syntax:REGEXP_MATCH( string, pattern )

Returns 'TRUE' if the string string has a substring that matches the regular expression pattern pattern.

REGEXP_REPLACE

Syntax:REGEXP_REPLACE( string, pattern, replace_with )

Searches for a substring in the string string using the regular expression pattern pattern and replaces it with the string replace_with.

If the substring is not found, the string is not changed.

REPLACE (array)

Syntax:REPLACE( array, old, new )

Replaces each array element equal to old with new.

REPLACE (string)

Syntax:REPLACE( string, substring, replace_with )

Searches for the substring substring in the string string and replaces it with the string replace_with.

If the substring is not found, the string is not changed.

RIGHT

Syntax:RIGHT( string, number )

Returns a string that contains the number of characters specified in number from the end of the string string.

RMAX (window)

Syntax:
RMAX( value [ , direction ] )
or
RMAX( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the maximum of all values in a growing (or shrinking) window defined by the sort order and the value of direction:

`direction`	Window
`"asc"`	Starts from the first row and ends at the current row.
`"desc"`	Starts from the current row and ends at the last row.

By default "asc" is used.

Window functions with a similar behavior: RSUM, RCOUNT, RMIN, RAVG.

RMIN (window)

Syntax:
RMIN( value [ , direction ] )
or
RMIN( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the minimum of all values in a growing (or shrinking) window defined by the sort order and the value of direction:

`direction`	Window
`"asc"`	Starts from the first row and ends at the current row.
`"desc"`	Starts from the current row and ends at the last row.

By default "asc" is used.

Window functions with a similar behavior: RSUM, RCOUNT, RMAX, RAVG.

ROUND

Syntax:ROUND( number [ , precision ] )

Rounds the number number to the number of decimal digits specified in precision. If the number precision is omitted, number is rounded to the nearest integer.

RSUM (window)

Syntax:
RSUM( value [ , direction ] )
or
RSUM( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Returns the sum of all values in a growing (or shrinking) window defined by the sort order and the value of direction:

`direction`	Window
`"asc"`	Starts from the first row and ends at the current row.
`"desc"`	Starts from the current row and ends at the last row.

By default "asc" is used.

Window functions with a similar behavior: RCOUNT, RMIN, RMAX, RAVG.

RTRIM

Syntax:RTRIM( string )

Returns the string string without spaces at the end of the string.

SECOND

Syntax:SECOND( datetime )

Returns the number of the second in the minute of the specified date datetime. When the date is specified without time, it returns 0.

SIGN

Syntax:SIGN( number )

Returns the sign of the number number:

-1 if the number is negative. 0 if the number is zero.
1 if the number is positive.

SIN

Syntax:SIN( number )

Returns the sine of number in radians.

SLICE

Syntax:SLICE( array, offset, length )

Returns the part of array array of length length starting from index offset. Indexes in an array begin with one.

SPACE

Syntax:SPACE( value )

Returns a string with the specified number of spaces.

SPLIT

Syntax:SPLIT( orig_string [ , delimiter [ , part_index ] ] )

Returns a substring from orig_string using the delimiter delimiter character to divide the string into a sequence of part_index parts. Delimiter is a comma by default. If part_index is not passed, an array is returned (only for ClickHouse, PostgreSQL sources)

SQRT

Syntax:SQRT( number )

Returns the square root of the specified number.

SQUARE

Syntax:SQUARE( number )

Returns the number number raised to the power of 2.

STARTSWITH (array)

Syntax:STARTSWITH( array_1, array_2 )

Returns TRUE if array_1 starts with array_2.

STARTSWITH (string)

Syntax:STARTSWITH( string, substring )

Returns TRUE if string starts with substring. For case-insensitive searches, see ISTARTSWITH.

STDEV

Syntax:
STDEV( value )
or
STDEV( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the statistical standard deviation of all values in the expression based on a selection from the population.

STDEVP

Syntax:
STDEVP( value )
or
STDEVP( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Returns the statistical standard deviation of all values in the expression based on the biased population.

STR

Syntax:STR( expression )

Converts the expression expression to string type.

SUBSTR

Syntax:SUBSTR( string, from_index [ , length ] )

Returns the substring string starting from the index from_index.

If an additional argument length is specified, a substring of the specified length is returned.

Heat map

Overview