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.

CASE

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

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

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, 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_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_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_REMOVE

Syntax:ARR_REMOVE( array, value )

Removes all array elements equal to value.

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

See also STR

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

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

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

BR

Syntax:BR()

Adds a line break.

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.

COLOR

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.

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.

CONTAINS_ALL

Syntax:CONTAINS_ALL( array_1, array_2 )

Returns TRUE if array_1 contains all elements of array_2.

CONTAINS_ANY

Syntax:CONTAINS_ANY( array_1, array_2 )

Returns TRUE if array_1 contains any elements of array_2.

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

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.

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)

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.

See also COUNTD_APPROX.

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.

See also COUNTD_APPROX.

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

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

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.

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 Date Date
ClickHouse Date Date32
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.

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

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.

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

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 TRUE1.0, FALSE0.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. 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_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.

GREATEST

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.

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.

IMAGE

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.

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 TRUE1, FALSE0.

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.

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.

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

Syntax:LEN( value )

Returns the number of the value items.

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.

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

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.

See also AVG, RAVG.

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.

See also COUNT, RCOUNT.

MEDIAN

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.

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.

See also MAX, RMAX.

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.

See also MIN, RMIN.

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.

See also SUM, RSUM.

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.

See also RANK_DENSE, RANK_UNIQUE, RANK_PERCENTILE.

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)

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)

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.

See also AVG, MAVG.

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.

See also COUNT, MCOUNT.

REGEXP_EXTRACT

Syntax:REGEXP_EXTRACT( string, pattern )

Returns the substring string that matches the regular expression pattern.

REGEXP_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_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.

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.

See also MAX, MMAX.

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.

See also MIN, MMIN.

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.

See also SUM, MSUM.

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.

SIZE

Syntax:SIZE( text, size )

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

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

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

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

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. The numbering starts with one.

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

SUM

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)

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

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.

TAN

Syntax:TAN( number )

Returns the tangent of number in radians.

TODAY

Syntax:TODAY()

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

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

TREE

Syntax:TREE( array )

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

TRIM

Syntax:TRIM( string )

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

UNNEST

Syntax:UNNEST( array )

Expands the array array expression to a set of rows.

UPPER

Syntax:UPPER( string )

Returns the string string in uppercase.

URL

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

UTF8

Syntax:UTF8( string, old_encoding )

Converts the string string encoding to UTF8.

VAR

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.

VARP

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.

WEEK

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.

YEAR

Syntax:YEAR( datetime )

Returns the year number in the specified date datetime.

ZN

Syntax:ZN( expression )

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