All Functions

Modulo (%)Modulo (%)

Syntax:number_1 % number_2

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

Multiplication (*)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 (+)Addition and concatenation (+)

Syntax:value_1 + value_2

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

Changing the order of arguments does not affect the result.

Subtraction (-)Subtraction (-)

Syntax:value_1 - value_2

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

Division (/)Division (/)

Syntax:number_1 / number_2

Divides the number number_1 by the number number_2.

ComparisonComparison

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 (^)Power (^)

Syntax:base ^ power

Raises base to the power of power.

CASECASE

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.

IFIF

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. The expressions to be checked are set using logical operators.

ABSABS

Syntax:ABS( number )

Returns the absolute value of number.

ACOSACOS

Syntax:ACOS( number )

Returns the arccosine of number in radians.

AGOAGO

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 sets the offset in units of the unit argument. Set as an integer. It may take negative values. The default value is 1.
The unit argument sets the unit for number. It may take the following values:

• "year";
• "month";
• "week";
• "day" (default value);
• "hour";
• "minute";
• "second".

Can also be used as AGO( measure, date_dimension, number ). In this case, the unit argument takes the "day" value.

See also AT_DATE, LAG.

ALL_CONCATALL_CONCAT

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

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

ANDAND

Syntax:value_1 AND value_2

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

ANYANY

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_MAXARG_MAX

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

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

ARG_MINARG_MIN

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

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

ARR_AVGARR_AVG

Syntax:ARR_AVG( array )

Returns the average of elements in the array.

ARR_MAXARR_MAX

Syntax:ARR_MAX( array )

Returns the greatest of elements in the array.

ARR_MINARR_MIN

Syntax:ARR_MIN( array )

Returns the least of elements in the array.

ARR_PRODUCTARR_PRODUCT

Syntax:ARR_PRODUCT( array )

Returns the product of elements in the array.

ARR_REMOVEARR_REMOVE

Syntax:ARR_REMOVE( array, value )

Removes all array elements equal to value.

ARR_STRARR_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).

See also STR

ARR_SUMARR_SUM

Syntax:ARR_SUM( array )

Returns the sum of elements in the array.

ARRAYARRAY

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

Returns an array containing the passed values.

ASCIIASCII

Syntax:ASCII( string )

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

ASINASIN

Syntax:ASIN( number )

Returns the arcsine of number in radians.

AT_DATEAT_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. It allows to get the measure at the beginning and at the end of a period, or for the specified date.
The date_dimension argument is the dimension along which the offset is made.

You can use the following as the date_expr argument:

• Certain date.
• Function TODAY to obtain the current date.
• Functions to calculate date and time.

See also AGO, LAG.

ATANATAN

Syntax:ATAN( number )

Returns the arctangent of number in radians.

ATAN2ATAN2

Syntax:ATAN2( x, y )

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

AVGAVG

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)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_IFAVG_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)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.

BETWEENBETWEEN

Syntax:value [ NOT ] BETWEEN low AND high

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

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

BOLDBOLD

Syntax:BOLD( text )

Stylizes the passed text in bold font.

BOOLBOOL

Syntax:BOOL( expression )

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

BRBR

Syntax:BR()

Adds a line break.

CAST_ARR_FLOATCAST_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_INTCAST_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_STRCAST_ARR_STR

Syntax:CAST_ARR_STR( array )

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

CEILINGCEILING

Syntax:CEILING( number )

Rounds the value up to the nearest integer.

CHARCHAR

Syntax:CHAR( string )

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

COLORCOLOR

Syntax:COLOR( text, color )

Enables specifying the color for the provided text.

We recommend using the color variables from the Gravity UI palette to specify colors. Such colors are easily discernible with both the light and dark theme.

You can also specify the color in any web format, such as HEX, keyword (e.g., green), RGB, etc. In this case, however, we cannot guarantee that the colors will be discernible.

COMPARECOMPARE

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.

CONCATCONCAT

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)CONTAINS (array)

Syntax:CONTAINS( array, value )

Returns TRUE if array contains value.

CONTAINS (string)CONTAINS (string)

Syntax:CONTAINS( string, substring )

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

CONTAINS_ALLCONTAINS_ALL

Syntax:CONTAINS_ALL( array_1, array_2 )

Returns TRUE if array_1 contains all elements of array_2.

CONTAINS_ANYCONTAINS_ANY

Syntax:CONTAINS_ANY( array_1, array_2 )

Returns TRUE if array_1 contains any elements of array_2.

CONTAINS_SUBSEQUENCECONTAINS_SUBSEQUENCE

Syntax:CONTAINS_SUBSEQUENCE( array_1, array_2 )

Returns TRUE if array_2 is a continuous subsequence of array_1. In other words, returns TRUE if and only if array1 = prefix + array2 + suffix.

COSCOS

Syntax:COS( number )

Returns the cosine of number in radians.

COTCOT

Syntax:COT( number )

Returns the cotangent of number in radians.

COUNTCOUNT

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

Returns the number of items in the group.

Can be used with constants, such as COUNT(1) or COUNT(). If the chart does not use other measures and dimensions, the result of such an expression will always be 1. This is because the function does not include any fields, so DataLens accesses no source tables in the query.

COUNT (window)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_IFCOUNT_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)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_ITEMCOUNT_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.

COUNTDCOUNTD

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

Returns the number of unique values in the group.

See also COUNTD_APPROX.

COUNTD_APPROXCOUNTD_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_IFCOUNTD_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.

See also COUNTD_APPROX.

DATEDATE

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_PARSEDATE_PARSE

Syntax:DATE_PARSE( value )

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

DATEADDDATEADD

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".

DATEPARTDATEPART

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.

DATETIMEDATETIME

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.
Numeric values are rendered as time in Unix time format or equal to the number of seconds elapsed since 00:00:00 on January 1, 1970, less the adjustments for leap seconds.

The date and time can be converted to the specified time zone when the timezone option is available. The timezone parameter must be specified in Region/Data_Zone format.

DATETIME_PARSEDATETIME_PARSE

Syntax:DATETIME_PARSE( value )

Converts the value expression to date and time format. Unlike DATETIME, it supports multiple formats. The expression is processed on the ClickHouse® source side. For more information on the supported formats, see the relevant ClickHouse® documentation.

DATETRUNCDATETRUNC

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".

When using a function with three arguments, it is processed on the ClickHouse® side by the toStartOfInterval function. Rounding is done relative to a specific point in time, as detailed in the table in the function description. For example:

DATETRUNC(#2018-07-12 11:07:13#, "month", 4) = #2018-05-01 00:00:00#

For the unit argument set to month, rounding starts from 1900-01-01. There are 1,422 months between 2018-07-12 and 1900-01-01. Rounding this value to the nearest number divisible by 4 (the number argument), we get 1,420 months. Thus, adding 1,420 months to 1900-01-01 gives us 2018-05-01.

DAYDAY

Syntax:DAY( datetime )

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

DAYOFWEEKDAYOFWEEK

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_CASTDB_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:

DEGREESDEGREES

Syntax:DEGREES( radians )

Converts radians to degrees.

DIVDIV

Syntax:DIV( number_1, number_2 )

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

DIV_SAFEDIV_SAFE

Syntax:DIV_SAFE( numerator, denominator [ , fallback_value ] )

Divides numerator by denominator. Returns fallback_value if division by zero occurs. If the number fallback_value is omitted, it is assumed to be NULL.
The result is rounded down to the nearest integer.

ENDSWITHENDSWITH

Syntax:ENDSWITH( string, substring )

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

EXPEXP

Syntax:EXP( number )

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

FDIV_SAFEFDIV_SAFE

Syntax:FDIV_SAFE( numerator, denominator [ , fallback_value ] )

Divides numerator by denominator. Returns fallback_value if division by zero occurs. If the number fallback_value is omitted, it is assumed to be NULL.

FINDFIND

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)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.

FLOATFLOAT

Syntax:FLOAT( expression )

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

FLOORFLOOR

Syntax:FLOOR( number )

Rounds the value down to the nearest integer.

GEOPOINTGEOPOINT

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.

GEOPOLYGONGEOPOLYGON

Syntax:GEOPOLYGON( value )

Converts the value expression to geopolygon format. At input, the function accepts strings in [[[lat_1,lon_1], [lat_2,lon_2], ..., [lat_N-1,lon_N-1], [lat_N,lon_N]]] format.

GET_ITEMGET_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. Returns the last item from the array if index is -1.

GREATESTGREATEST

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

Returns the greatest value.

See also LEAST.

Depending on the specified data type, it returns:

• The greatest number.
• The last string in alphabetical order.
• The latest date.
• TRUE when selecting between TRUE and FALSE for Boolean type.

HOURHOUR

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.

ICONTAINSICONTAINS

Syntax:ICONTAINS( string, substring )

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

IENDSWITHIENDSWITH

Syntax:IENDSWITH( string, substring )

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

IFNULLIFNULL

Syntax:IFNULL( check_value, alt_value )

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

IMAGEIMAGE

Syntax:IMAGE( src [ , width [ , height [ , alt ] ] ] )

Enables inserting an image located at the src address to the table. The width and height values are provided in pixels. If one of the dimensions is NULL, it will be calculated automatically in proportion to the other. If both dimensions are NULL, the image will be inserted with the original width and height. In case there are issues when uploading the image, the function will display the alt text.

ININ

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.

INTINT

Syntax:INT( expression )

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

IS FALSEIS FALSE

Syntax:value IS [ NOT ] FALSE

Checks whether the value value is false (FALSE).

The value IS NOT FALSE option returns the opposite value.

ISNULLISNULL

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.

ISTARTSWITHISTARTSWITH

Syntax:ISTARTSWITH( string, substring )

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

IS TRUEIS 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.

ITALICITALIC

Syntax:ITALIC( text )

Stylizes the passed text in cursive font.

LAG (window)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)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.

LEASTLEAST

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

Returns the smallest value.

See also GREATEST.

Depending on the specified data type, it returns:

• The smallest number.
• The first string in alphabetical order.
• The earliest date.
• FALSE when selecting between TRUE and FALSE for Boolean type.

LEFTLEFT

Syntax:LEFT( string, number )

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

LEN (array)LEN (array)

Syntax:LEN( value )

Returns the number of the value items.

LENLEN

Syntax:LEN( value )

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

LIKELIKE

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.

When comparing values, the function is case-sensitive. You can use LIKE along with UPPER or LOWER for case-insensitive comparison.

LNLN

Syntax:LN( number )

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

LOGLOG

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.

LOG10LOG10

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.

LOWERLOWER

Syntax:LOWER( string )

Returns the string string in lowercase.

LTRIMLTRIM

Syntax:LTRIM( string )

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

MARKUPMARKUP

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)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:

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

See also AVG, RAVG.

MAXMAX

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)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)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:

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

See also COUNT, RCOUNT.

MEDIANMEDIAN

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

Returns the median value. For an even number of items, it returns the greatest of the neighboring items in the central position.

MINMIN

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)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.

MINUTEMINUTE

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)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:

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

See also MAX, RMAX.

MMIN (window)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:

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

See also MIN, RMIN.

MONTHMONTH

Syntax:MONTH( datetime )

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

MSUM (window)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:

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

See also SUM, RSUM.

Negation (-)Negation (-)

Syntax:-value

Returns the number value with the opposite sign.

NOTNOT

Syntax:NOT value

Inverts a Boolean value.

NOWNOW

Syntax:NOW()

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

OROR

Syntax:value_1 OR value_2

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

PIPI

Syntax:PI()

Returns PI. The accuracy depends on the data source.

POWERPOWER

Syntax:POWER( base, power )

Raises base to the power of power.

QUANTILEQUANTILE

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_APPROXQUANTILE_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).

QUARTERQUARTER

Syntax:QUARTER( datetime )

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

RADIANSRADIANS

Syntax:RADIANS( degrees )

Converts degrees degrees to radians.

RANK (window)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.

See also RANK_DENSE, RANK_UNIQUE, RANK_PERCENTILE.

RANK_DENSE (window)RANK_DENSE (window)

Syntax:
RANK_DENSE( value [ , direction ] )
or
RANK_DENSE( 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 2, (rank without gaps).

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)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.

See also RANK, RANK_DENSE, RANK_UNIQUE.

RANK_UNIQUE (window)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)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:

By default "asc" is used.

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

See also AVG, MAVG.

RCOUNT (window)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:

By default "asc" is used.

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

See also COUNT, MCOUNT.

REGEXP_EXTRACTREGEXP_EXTRACT

Syntax:REGEXP_EXTRACT( string, pattern )

Returns the substring string that matches the regular expression pattern.

REGEXP_EXTRACT_ALLREGEXP_EXTRACT_ALL

Syntax:REGEXP_EXTRACT_ALL( string, pattern )

Returns all string substrings matching the pattern regex. For regexes with subgroups, it only works for the first subgroup.

REGEXP_EXTRACT_NTHREGEXP_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_MATCHREGEXP_MATCH

Syntax:REGEXP_MATCH( string, pattern )

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

REGEXP_REPLACEREGEXP_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)REPLACE (array)

Syntax:REPLACE( array, old, new )

Replaces each array element equal to old with new.

REPLACE (string)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.

RIGHTRIGHT

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)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:

By default "asc" is used.

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

See also MAX, MMAX.

RMIN (window)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:

By default "asc" is used.

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

See also MIN, MMIN.

ROUNDROUND

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)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:

By default "asc" is used.

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

See also SUM, MSUM.

RTRIMRTRIM

Syntax:RTRIM( string )

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

SECONDSECOND

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.

SIGNSIGN

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.

SINSIN

Syntax:SIN( number )

Returns the sine of number in radians.

SIZESIZE

Syntax:SIZE( text, size )

Enables specifying the size (in pixels) for the provided text.

SLICESLICE

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.

SPACESPACE

Syntax:SPACE( value )

Returns a string with the specified number of spaces.

SPLITSPLIT

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

It splits orig_string into a sequence of substrings using the delimiter character as separator and returns the substring whose number is equal to the part_index parameter. By default, the delimiting character is comma. If part_index is negative, the substring to return is counted from the end of orig_string. If the number of substrings is less than the part_index absolute value, the function returns an empty string. If part_index was not provided, the function returns an array of the substrings (only for ClickHouse, PostgreSQL sources).

SQRTSQRT

Syntax:SQRT( number )

Returns the square root of the specified number.

SQUARESQUARE

Syntax:SQUARE( number )

Returns the number number raised to the power of 2.

STARTSWITH (array)STARTSWITH (array)

Syntax:STARTSWITH( array_1, array_2 )

Returns TRUE if array_1 starts with array_2.

STARTSWITH (string)STARTSWITH (string)

Syntax:STARTSWITH( string, substring )

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

STDEVSTDEV

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.

STDEVPSTDEVP

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. The function shows how far data points are from their average. In other words, standard deviation shows to what extent values in a dataset deviate from their average.

STRSTR

Syntax:STR( expression )

Converts the expression expression to string type.

SUBSTRSUBSTR

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

Returns the substring string starting from the index from_index. The numbering starts with one.

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

SUMSUM

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

Returns the sum of all expression values. Applicable to numeric data types only.

SUM (window)SUM (window)

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

Returns the sum of all expression values. Applicable to numeric data types only.

SUM_IFSUM_IF

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

Returns the sum of all the expression values that meet the condition condition. Applicable to numeric data types only.

SUM_IF (window)SUM_IF (window)

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

Returns the sum of all the expression values that meet the condition condition. Applicable to numeric data types only.

TANTAN

Syntax:TAN( number )

Returns the tangent of number in radians.

TODAYTODAY

Syntax:TODAY()

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

TOP_CONCATTOP_CONCAT

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

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

TREETREE

Syntax:TREE( array )

Converts the array expression to Tree of strings format. Can be used to create tree hierarchies.

TRIMTRIM

Syntax:TRIM( string )

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

UNNESTUNNEST

Syntax:UNNEST( array )

Expands the array array expression to a set of rows.

UPPERUPPER

Syntax:UPPER( string )

Returns the string string in uppercase.

URLURL

Syntax:URL( address, text )

Wraps text into a hyperlink to URL address. When you click on the link, the page opens in a new browser tab.

USER_INFOUSER_INFO

Syntax:USER_INFO( user_id, user_info_type )

Returns the marked up text by user_id to display username or email depending on the user_info_type value:

• email: Returns email.
• name: Returns name.

If the user has not been found, the function will return the original string from the source.

UTF8UTF8

Syntax:UTF8( string, old_encoding )

Converts the string string encoding to UTF8.

VARVAR

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

Returns the statistical variance of all values in an expression based on a selection from the population.

VARPVARP

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

Returns the statistical variance of all values in an expression across the entire population.

WEEKWEEK

Syntax:WEEK( value )

The number of the week according to ISO 8601. The first week is the week that contains the first Thursday of the year or January 4th.

YEARYEAR

Syntax:YEAR( datetime )

Returns the year number in the specified date datetime.

ZNZN

Syntax:ZN( expression )

Returns expression if it's not NULL. Otherwise returns 0.