Built-in Functions#

function abs#

abs()

Description:

Return the absolute value of expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT ABS(-32);
-- output 32

Supported Types:

  • [bool]

  • [number]

function acos#

acos()

Description:

Return the arc cosine of expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT ACOS(1);
-- output 0

Supported Types:

  • [number]

function add#

add()

Description:

Compute sum of two arguments.

Since: 0.1.0

Example:

select add(1, 2);
-- output 3

Supported Types:

  • [bool, bool]

  • [bool, number]

  • [bool, timestamp]

  • [int16, timestamp]

  • [int32, timestamp]

  • [int64, timestamp]

  • [number, bool]

  • [number, number]

  • [timestamp, bool]

  • [timestamp, int16]

  • [timestamp, int32]

  • [timestamp, int64]

  • [timestamp, timestamp]

function asin#

asin()

Description:

Return the arc sine of expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT ASIN(0.0);
-- output 0.000000

Supported Types:

  • [number]

function at#

at()

Description:

Returns the value of expression from the offset-th row of the ordered partition.

Parameters:

  • offset The number of rows forward from the current row from which to obtain the value.

Example:

value

0

1

2

3

4

 
SELECT at(value, 3) OVER w;
-- output 3

Supported Types:

  • [list<bool>, int64]

  • [list<date>, int64]

  • [list<number>, int64]

  • [list<string>, int64]

  • [list<timestamp>, int64]

function atan#

atan()

Description:

Return the arc tangent of expr If called with one parameter, this function returns the arc tangent of expr. If called with two parameters X and Y, this function returns the arc tangent of Y / X.

Parameters:

  • X

  • Y

Since: 0.1.0

Example:

SELECT ATAN(-0.0);  
-- output -0.000000

SELECT ATAN(0, -0);
-- output 3.141593

Supported Types:

  • [bool, bool]

  • [bool, number]

  • [number]

  • [number, bool]

  • [number, number]

function atan2#

atan2()

Description:

Return the arc tangent of Y / X…

Parameters:

  • X

  • Y

Since: 0.1.0

Example:

SELECT ATAN2(0, -0);
-- output 3.141593

Supported Types:

  • [bool, bool]

  • [bool, number]

  • [number, bool]

  • [number, number]

function avg#

avg()

Description:

Compute average of values.

Parameters:

  • value Specify value column to aggregate on.

Since: 0.1.0

Example:

value

0

1

2

3

4

 
SELECT avg(value) OVER w;
-- output 2

Supported Types:

  • [list<number>]

function avg_cate#

avg_cate()

Description:

Compute average of values grouped by category key and output string. Each group is represented as ‘K:V’ and separated by comma in outputs and are sorted by key in ascend order.

Parameters:

  • value Specify value column to aggregate on.

  • catagory Specify catagory column to group by.

Example:

value

catagory

0

x

1

y

2

x

3

y

4

x

 
SELECT avg_cate(value, catagory) OVER w;
-- output "x:2,y:2"

Supported Types:

  • [list<number>, list<date>]

  • [list<number>, list<int16>]

  • [list<number>, list<int32>]

  • [list<number>, list<int64>]

  • [list<number>, list<string>]

  • [list<number>, list<timestamp>]

function avg_cate_where#

avg_cate_where()

Description:

Compute average of values matching specified condition grouped by category key and output string. Each group is represented as ‘K:V’ and separated by comma in outputs and are sorted by key in ascend order.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

  • catagory Specify catagory column to group by.

Example:

value

condition

catagory

0

true

x

1

false

y

2

false

x

3

true

y

4

true

x

 
SELECT avg_cate_where(value, condition, catagory) OVER w;
-- output "x:2,y:3"

Supported Types:

  • [list<number>, list<bool>, list<date>]

  • [list<number>, list<bool>, list<int16>]

  • [list<number>, list<bool>, list<int32>]

  • [list<number>, list<bool>, list<int64>]

  • [list<number>, list<bool>, list<string>]

  • [list<number>, list<bool>, list<timestamp>]

function avg_where#

avg_where()

Description:

Compute average of values match specified condition.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

Since: 0.1.0

Example:

value

0

1

2

3

4

 
SELECT avg_where(value, value > 2) OVER w;
-- output 3.5

Supported Types:

  • [list<number>, list<bool>]

function bool#

bool()

Description:

Cast string expression to bool.

Since: 0.1.0

Example:

select bool("true");
-- output true

Supported Types:

  • [string]

function ceil#

ceil()

Description:

Return the smallest integer value not less than the expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT CEIL(1.23);
-- output 2

Supported Types:

  • [bool]

  • [number]

function ceiling#

ceiling()

Description:

Return the smallest integer value not less than the expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT CEIL(1.23);
-- output 2

Supported Types:

  • [bool]

  • [number]

function concat#

concat()

Description:

This function returns a string resulting from the joining of two or more string values in an end-to-end manner. (To add a separating value during joining, see concat_ws.)

Since: 0.1.0

Example:

select concat("1", 2, 3, 4, 5.6, 7.8, Timestamp(1590115420000L));
-- output "12345.67.82020-05-22 10:43:40"

Supported Types:

  • […]

function concat_ws#

concat_ws()

Description:

Returns a string resulting from the joining of two or more string value in an end-to-end manner. It separates those concatenated string values with the delimiter specified in the first function argument.

Since: 0.1.0

Example:

select concat("-", "1", 2, 3, 4, 5.6, 7.8, Timestamp(1590115420000L));
-- output "1-2-3-4-5.6-7.8-2020-05-22 10:43:40"

Supported Types:

  • [bool, …]

  • [date, …]

  • [number, …]

  • [string, …]

  • [timestamp, …]

function cos#

cos()

Description:

Return the cosine of expr.

Parameters:

  • expr It is a single argument in radians.

Since: 0.1.0

Example:

SELECT COS(0);
-- output 1.000000

  • The value returned by cos() is always in the range: -1 to 1.

Supported Types:

  • [number]

function cot#

cot()

Description:

Return the cotangent of expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT COT(1);  
-- output 0.6420926159343306

Supported Types:

  • [number]

function count#

count()

Description:

Compute number of values.

Parameters:

  • value Specify value column to aggregate on.

Since: 0.1.0

Example:

value

0

1

2

3

4

 
SELECT count(value) OVER w;
-- output 5

Supported Types:

  • [list<bool>]

  • [list<date>]

  • [list<number>]

  • [list<row>]

  • [list<string>]

  • [list<timestamp>]

function count_cate#

count_cate()

Description:

Compute count of values grouped by category key and output string. Each group is represented as ‘K:V’ and separated by comma in outputs and are sorted by key in ascend order.

Parameters:

  • value Specify value column to aggregate on.

  • catagory Specify catagory column to group by.

Example:

value

catagory

0

x

1

y

2

x

3

y

4

x

 
SELECT count_cate(value, catagory) OVER w;
-- output "x:3,y:2"

Supported Types:

  • [list<number>, list<date>]

  • [list<number>, list<int16>]

  • [list<number>, list<int32>]

  • [list<number>, list<int64>]

  • [list<number>, list<string>]

  • [list<number>, list<timestamp>]

function count_cate_where#

count_cate_where()

Description:

Compute count of values matching specified condition grouped by category key and output string. Each group is represented as ‘K:V’ and separated by comma in outputs and are sorted by key in ascend order.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

  • catagory Specify catagory column to group by.

Example:

value

condition

catagory

0

true

x

1

false

y

2

false

x

3

true

y

4

true

x

 
SELECT count_cate_where(value, condition, catagory) OVER w;
-- output "x:2,y:1"

Supported Types:

  • [list<number>, list<bool>, list<date>]

  • [list<number>, list<bool>, list<int16>]

  • [list<number>, list<bool>, list<int32>]

  • [list<number>, list<bool>, list<int64>]

  • [list<number>, list<bool>, list<string>]

  • [list<number>, list<bool>, list<timestamp>]

function count_where#

count_where()

Description:

Compute number of values match specified condition.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

Since: 0.1.0

Example:

value

0

1

2

3

4

 
SELECT count_where(value, value > 2) OVER w;
-- output 2

Supported Types:

  • [list<date>, list<bool>]

  • [list<number>, list<bool>]

  • [list<string>, list<bool>]

  • [list<timestamp>, list<bool>]

function date#

date()

Description:

Cast timestamp or string expression to date.

Since: 0.1.0

Example:

select date(timestamp(1590115420000));
-- output 2020-05-22
select date("2020-05-22");
-- output 2020-05-22

Supported Types:

  • [string]

  • [timestamp]

function date_format#

date_format()

Description:

Formats the datetime value according to the format string.

Example:

select date_format(timestamp(1590115420000),"%Y-%m-%d %H:%M:%S");
--output "2020-05-22 10:43:40"

Supported Types:

  • [date, string]

  • [timestamp, string]

function day#

day()

Description:

Return the day of the month for a timestamp or date.

Since: 0.1.0

Note: This function equals the [day()](/reference/sql/functions_and_operators/Files/udfs_8h.md#function-day) function.

Example:

select dayofmonth(timestamp(1590115420000));
-- output 22

select day(timestamp(1590115420000));
-- output 22

Supported Types:

  • [date]

  • [int64]

  • [timestamp]

function dayofmonth#

dayofmonth()

Description:

Return the day of the month for a timestamp or date.

Since: 0.1.0

Note: This function equals the [day()](/reference/sql/functions_and_operators/Files/udfs_8h.md#function-day) function.

Example:

select dayofmonth(timestamp(1590115420000));
-- output 22

select day(timestamp(1590115420000));
-- output 22

Supported Types:

  • [date]

  • [int64]

  • [timestamp]

function dayofweek#

dayofweek()

Description:

Return the day of week for a timestamp or date.

Since: 0.4.0

Note: This function equals the [week()](/reference/sql/functions_and_operators/Files/udfs_8h.md#function-week) function.

Example:

select dayofweek(timestamp(1590115420000));
-- output 6

Supported Types:

  • [date]

  • [int64]

  • [timestamp]

function dayofyear#

dayofyear()

Description:

Return the day of year for a timestamp or date. Returns 0 given an invalid date.

Since: 0.1.0

Example:

select dayofyear(timestamp(1590115420000));
-- output 143

select dayofyear(1590115420000);
-- output 143

select dayofyear(date("2020-05-22"));
-- output 143

select dayofyear(date("2020-05-32"));
-- output 0

Supported Types:

  • [date]

  • [int64]

  • [timestamp]

function distinct_count#

distinct_count()

Description:

Compute number of distinct values.

Parameters:

  • value Specify value column to aggregate on.

Since: 0.1.0

Example:

value

0

0

2

2

4

 
SELECT distinct_count(value) OVER w;
-- output 3

Supported Types:

  • [list<bool>]

  • [list<date>]

  • [list<number>]

  • [list<string>]

  • [list<timestamp>]

function double#

double()

Description:

Cast string expression to double.

Since: 0.1.0

Example:

select double("1.23");
-- output 1.23

Supported Types:

  • [string]

function exp#

exp()

Description:

Return the value of e (the base of natural logarithms) raised to the power of expr.

Parameters:

  • expr

Since: 0.1.0

SELECT EXP(0);  
-- output 1

Supported Types:

  • [number]

function first_value#

first_value()

Description:

Returns the value of expr from the first row of the window frame.

    @since 0.1.0

Supported Types:

function float#

float()

Description:

Cast string expression to float.

Since: 0.1.0

Example:

select float("1.23");
-- output 1.23

Supported Types:

  • [string]

function floor#

floor()

Description:

Return the largest integer value not less than the expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT FLOOR(1.23);
-- output 1

Supported Types:

  • [bool]

  • [number]

function fz_join#

fz_join()

Description:

Used by feature zero, for each string value from specified column of window, join by delimeter. Null values are skipped.

Since: 0.1.0

Example:

select fz_join(fz_split("k1:v1,k2:v2", ","), " ");
--  "k1:v1 k2:v2"

Supported Types:

  • [list<string>, string]

function fz_split#

fz_split()

Description:

Used by feature zero, split string to list by delimeter. Null values are skipped.

Since: 0.1.0

Supported Types:

  • [string, string]

function fz_split_by_key#

fz_split_by_key()

Description:

Used by feature zero, split string by delimeter and then split each segment as kv pair, then add each key to output list. Null and illegal segments are skipped.

Since: 0.1.0

Supported Types:

  • [string, string, string]

function fz_split_by_value#

fz_split_by_value()

Description:

Used by feature zero, split string by delimeter and then split each segment as kv pair, then add each value to output list. Null and illegal segments are skipped.

Since: 0.1.0

Supported Types:

  • [string, string, string]

function fz_top1_ratio#

fz_top1_ratio()

Description:

Compute the top1 key’s ratio.

    @since 0.1.0

Supported Types:

  • [list<date>]

  • [list<number>]

  • [list<string>]

  • [list<timestamp>]

function fz_topn_frequency#

fz_topn_frequency()

Description:

Return the topN keys sorted by their frequency.

    @since 0.1.0

Supported Types:

  • [list<date>, list<int32>]

  • [list<number>, list<int32>]

  • [list<string>, list<int32>]

  • [list<timestamp>, list<int32>]

function fz_window_split#

fz_window_split()

Description:

Used by feature zero, for each string value from specified column of window, split by delimeter and add segment to output list. Null values are skipped.

Since: 0.1.0

Supported Types:

  • [list<string>, list<string>]

function fz_window_split_by_key#

fz_window_split_by_key()

Description:

Used by feature zero, for each string value from specified column of window, split by delimeter and then split each segment as kv pair, then add each key to output list. Null and illegal segments are skipped.

Since: 0.1.0

Supported Types:

  • [list<string>, list<string>, list<string>]

function fz_window_split_by_value#

fz_window_split_by_value()

Description:

Used by feature zero, for each string value from specified column of window, split by delimeter and then split each segment as kv pair, then add each value to output list. Null and illegal segments are skipped.

Since: 0.1.0

Supported Types:

  • [list<string>, list<string>, list<string>]

function hour#

hour()

Description:

Return the hour for a timestamp.

Since: 0.1.0

Example:

select hour(timestamp(1590115420000));
-- output 10

Supported Types:

  • [int64]

  • [timestamp]

function identity#

identity()

Description:

Return value.

Since: 0.1.0

Example:

select identity(1);
-- output 1

Supported Types:

  • [bool]

  • [date]

  • [number]

  • [string]

  • [timestamp]

function if_null#

if_null()

Description:

If input is not null, return input value; else return default value.

Parameters:

  • input Input value

  • default Default value if input is null

Since: 0.1.0

Example:

SELECT if_null("hello", "default"), if_null(NULL, "default");
-- output ["hello", "default"]

Supported Types:

  • [bool, bool]

  • [date, date]

  • [double, double]

  • [float, float]

  • [int16, int16]

  • [int32, int32]

  • [int64, int64]

  • [string, string]

  • [timestamp, timestamp]

function ifnull#

ifnull()

Description:

If input is not null, return input value; else return default value.

Parameters:

  • input Input value

  • default Default value if input is null

Since: 0.1.0

Example:

SELECT ifnull("hello", "default"), ifnull(NULL, "default");
-- output ["hello", "default"]

Supported Types:

  • [bool, bool]

  • [date, date]

  • [double, double]

  • [float, float]

  • [int16, int16]

  • [int32, int32]

  • [int64, int64]

  • [string, string]

  • [timestamp, timestamp]

function ilike_match#

ilike_match()

Description:

pattern match same as ILIKE predicate

Parameters:

  • target string to match

  • pattern the glob match pattern

  • escape escape character

Since: 0.4.0

Rules:

  1. Special characters:

    • underscore(_): exact one character

    • precent(%): zero or more characters.

  2. Escape character:

    • backslash() is the default escape character

    • length of must <= 1

    • if is empty, escape feautre is disabled

  3. case insensitive

  4. backslash: sql string literal use backslash() for escape sequences, write ‘’ as backslash itself

  5. if one or more of target, pattern and escape are null values, then the result is null Example:

select ilike_match('Mike', 'mi_e', '\\')
-- output: true

select ilike_match('Mike', 'mi\\_e', '\\')
-- output: false

select ilike_match('Mi_e', 'mi\\_e', '\\')
-- output: true

select ilike_match('Mi\\ke', 'mi\\_e', '')
-- output: true

select ilike_match('Mi\\ke', 'mi\\_e', string(null))
-- output: null

Supported Types:

  • [string, string]

  • [string, string, string]

function inc#

inc()

Description:

Return expression + 1.

Since: 0.1.0

Example:

select inc(1);
-- output 2

Supported Types:

  • [number]

function int16#

int16()

Description:

Cast string expression to int16.

Since: 0.1.0

Example:

select int16("123");
-- output 123

Supported Types:

  • [string]

function int32#

int32()

Description:

Cast string expression to int32.

Since: 0.1.0

Example:

select int32("12345");
-- output 12345

Supported Types:

  • [string]

function int64#

int64()

Description:

Cast string expression to int64.

Since: 0.1.0

Example:

select int64("1590115420000");
-- output 1590115420000

Supported Types:

  • [string]

function is_null#

is_null()

Description:

Check if input value is null, return bool.

Parameters:

  • input Input value

Since: 0.1.0

Supported Types:

  • [bool]

  • [date]

  • [number]

  • [string]

  • [timestamp]

function isnull#

isnull()

Description:

Check if input value is null, return bool.

Parameters:

  • input Input value

Since: 0.1.0

Supported Types:

  • [bool]

  • [date]

  • [number]

  • [string]

  • [timestamp]

function lag#

lag()

Description:

Returns the value of expression from the offset-th row of the ordered partition.

Parameters:

  • offset The number of rows forward from the current row from which to obtain the value.

Example:

value

0

1

2

3

4

 
SELECT lag(value, 3) OVER w;
-- output 3

Supported Types:

  • [list<bool>, int64]

  • [list<date>, int64]

  • [list<number>, int64]

  • [list<string>, int64]

  • [list<timestamp>, int64]

function like_match#

like_match()

Description:

pattern match same as LIKE predicate

Parameters:

  • target string to match

  • pattern the glob match pattern

  • escape escape character

Since: 0.4.0

Rules:

  1. Special characters:

    • underscore(_): exact one character

    • precent(%): zero or more characters.

  2. Escape character:

    • backslash() is the default escape character

    • length of must <= 1

    • if is empty, escape feature is disabled

  3. case sensitive

  4. backslash: sql string literal use backslash() for escape sequences, write ‘’ as backslash itself

  5. if one or more of target, pattern and escape are null values, then the result is null Example:

select like_match('Mike', 'Mi_e', '\\')
-- output: true

select like_match('Mike', 'Mi\\_e', '\\')
-- output: false

select like_match('Mi_e', 'Mi\\_e', '\\')
-- output: true

select like_match('Mi\\ke', 'Mi\\_e', '')
-- output: true

select like_match('Mi\\ke', 'Mi\\_e', string(null))
-- output: null

Supported Types:

  • [string, string]

  • [string, string, string]

function ln#

ln()

Description:

Return the natural logarithm of expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT LN(1);  
-- output 0.000000

Supported Types:

  • [bool]

  • [number]

function log#

log()

Description:

log(base, expr) If called with one parameter, this function returns the natural logarithm of expr. If called with two parameters, this function returns the logarithm of expr to the base.

Parameters:

  • base

  • expr

Since: 0.1.0

Example:

SELECT LOG(1);  
-- output 0.000000

SELECT LOG(10,100);
-- output 2

Supported Types:

  • [bool]

  • [bool, bool]

  • [bool, date]

  • [bool, number]

  • [bool, string]

  • [bool, timestamp]

  • [number]

  • [number, bool]

  • [number, date]

  • [number, number]

  • [number, string]

  • [number, timestamp]

function log10#

log10()

Description:

Return the base-10 logarithm of expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT LOG10(100);  
-- output 2

Supported Types:

  • [bool]

  • [number]

function log2#

log2()

Description:

Return the base-2 logarithm of expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT LOG2(65536);  
-- output 16

Supported Types:

  • [bool]

  • [number]

function make_tuple#

make_tuple()

Description:

Supported Types:

  • […]

function max#

max()

Description:

Compute maximum of values.

Parameters:

  • value Specify value column to aggregate on.

Since: 0.1.0

Example:

value

0

1

2

3

4

 
SELECT max(value) OVER w;
-- output 4

Supported Types:

  • [list<date>]

  • [list<number>]

  • [list<string>]

  • [list<timestamp>]

function max_cate#

max_cate()

Description:

Compute maximum of values grouped by category key and output string. Each group is represented as ‘K:V’ and separated by comma in outputs and are sorted by key in ascend order.

Parameters:

  • value Specify value column to aggregate on.

  • catagory Specify catagory column to group by.

Example:

value

catagory

0

x

1

y

2

x

3

y

4

x

 
SELECT max_cate(value, catagory) OVER w;
-- output "x:4,y:3"

Supported Types:

  • [list<number>, list<date>]

  • [list<number>, list<int16>]

  • [list<number>, list<int32>]

  • [list<number>, list<int64>]

  • [list<number>, list<string>]

  • [list<number>, list<timestamp>]

function max_cate_where#

max_cate_where()

Description:

Compute maximum of values matching specified condition grouped by category key and output string. Each group is represented as ‘K:V’ and separated by comma in outputs and are sorted by key in ascend order.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

  • catagory Specify catagory column to group by.

Example:

value

condition

catagory

0

true

x

1

false

y

2

false

x

3

true

y

4

true

x

 
SELECT max_cate_where(value, condition, catagory) OVER w;
-- output "x:4,y:3"

Supported Types:

  • [list<number>, list<bool>, list<date>]

  • [list<number>, list<bool>, list<int16>]

  • [list<number>, list<bool>, list<int32>]

  • [list<number>, list<bool>, list<int64>]

  • [list<number>, list<bool>, list<string>]

  • [list<number>, list<bool>, list<timestamp>]

function max_where#

max_where()

Description:

Compute maximum of values match specified condition.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

Since: 0.1.0

Example:

value

0

1

2

3

4

 
SELECT max_where(value, value <= 2) OVER w;
-- output 2

Supported Types:

  • [list<number>, list<bool>]

function maximum#

maximum()

Description:

Compute maximum of two arguments.

Since: 0.1.0

Supported Types:

  • [bool, bool]

  • [date, date]

  • [double, double]

  • [float, float]

  • [int16, int16]

  • [int32, int32]

  • [int64, int64]

  • [string, string]

  • [timestamp, timestamp]

function min#

min()

Description:

Compute minimum of values.

Parameters:

  • value Specify value column to aggregate on.

Since: 0.1.0

Example:

value

0

1

2

3

4

 
SELECT min(value) OVER w;
-- output 0

Supported Types:

  • [list<date>]

  • [list<number>]

  • [list<string>]

  • [list<timestamp>]

function min_cate#

min_cate()

Description:

Compute minimum of values grouped by category key and output string. Each group is represented as ‘K:V’ and separated by comma in outputs and are sorted by key in ascend order.

Parameters:

  • value Specify value column to aggregate on.

  • catagory Specify catagory column to group by.

Example:

value

catagory

0

x

1

y

2

x

3

y

4

x

 
SELECT min_cate(value, catagory) OVER w;
-- output "x:0,y:1"

Supported Types:

  • [list<number>, list<date>]

  • [list<number>, list<int16>]

  • [list<number>, list<int32>]

  • [list<number>, list<int64>]

  • [list<number>, list<string>]

  • [list<number>, list<timestamp>]

function min_cate_where#

min_cate_where()

Description:

Compute minimum of values matching specified condition grouped by category key and output string. Each group is represented as ‘K:V’ and separated by comma in outputs and are sorted by key in ascend order.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

  • catagory Specify catagory column to group by.

Example:

value

condition

catagory

0

true

x

1

false

y

2

false

x

1

true

y

4

true

x

3

true

y

 
SELECT min_cate_where(value, condition, catagory) OVER w;
-- output "x:0,y:1"

Supported Types:

  • [list<number>, list<bool>, list<date>]

  • [list<number>, list<bool>, list<int16>]

  • [list<number>, list<bool>, list<int32>]

  • [list<number>, list<bool>, list<int64>]

  • [list<number>, list<bool>, list<string>]

  • [list<number>, list<bool>, list<timestamp>]

function min_where#

min_where()

Description:

Compute minimum of values match specified condition.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

Since: 0.1.0

Example:

value

0

1

2

3

4

 
SELECT min_where(value, value > 2) OVER w;
-- output 3

Supported Types:

  • [list<number>, list<bool>]

function minimum#

minimum()

Description:

Compute minimum of two arguments.

Since: 0.1.0

Supported Types:

  • [bool, bool]

  • [date, date]

  • [double, double]

  • [float, float]

  • [int16, int16]

  • [int32, int32]

  • [int64, int64]

  • [string, string]

  • [timestamp, timestamp]

function minute#

minute()

Description:

Return the minute for a timestamp.

Since: 0.1.0

Example:

select minute(timestamp(1590115420000));
-- output 43

Supported Types:

  • [int64]

  • [timestamp]

function month#

month()

Description:

Return the month part of a timestamp or date.

Since: 0.1.0

Example:

select month(timestamp(1590115420000));
-- output 5

Supported Types:

  • [date]

  • [int64]

  • [timestamp]

function nvl#

nvl()

Description:

If input is not null, return input value; else return default value.

Parameters:

  • input Input value

  • default Default value if input is null

Since: 0.1.0

Example:

SELECT if_null("hello", "default"), if_null(NULL, "default");
-- output ["hello", "default"]

Supported Types:

  • [bool, bool]

  • [date, date]

  • [double, double]

  • [float, float]

  • [int16, int16]

  • [int32, int32]

  • [int64, int64]

  • [string, string]

  • [timestamp, timestamp]

function nvl2#

nvl2()

Description:

nvl2(expr1, expr2, expr3) - Returns expr2 if expr1 is not null, or expr3 otherwise.

Parameters:

  • expr1 Condition expression

  • expr2 Return value if expr1 is not null

  • expr3 Return value if expr1 is null

Since: 0.2.3

Example:

SELECT nvl2(NULL, 2, 1);
-- output 1

Supported Types:

  • [bool, bool, bool]

  • [bool, date, date]

  • [bool, double, double]

  • [bool, float, float]

  • [bool, int16, int16]

  • [bool, int32, int32]

  • [bool, int64, int64]

  • [bool, string, string]

  • [bool, timestamp, timestamp]

  • [date, bool, bool]

  • [date, date, date]

  • [date, double, double]

  • [date, float, float]

  • [date, int16, int16]

  • [date, int32, int32]

  • [date, int64, int64]

  • [date, string, string]

  • [date, timestamp, timestamp]

  • [number, bool, bool]

  • [number, date, date]

  • [number, double, double]

  • [number, float, float]

  • [number, int16, int16]

  • [number, int32, int32]

  • [number, int64, int64]

  • [number, string, string]

  • [number, timestamp, timestamp]

  • [string, bool, bool]

  • [string, date, date]

  • [string, double, double]

  • [string, float, float]

  • [string, int16, int16]

  • [string, int32, int32]

  • [string, int64, int64]

  • [string, string, string]

  • [string, timestamp, timestamp]

  • [timestamp, bool, bool]

  • [timestamp, date, date]

  • [timestamp, double, double]

  • [timestamp, float, float]

  • [timestamp, int16, int16]

  • [timestamp, int32, int32]

  • [timestamp, int64, int64]

  • [timestamp, string, string]

  • [timestamp, timestamp, timestamp]

function pow#

pow()

Description:

Return the value of expr1 to the power of expr2.

Parameters:

  • expr1

  • expr2

Since: 0.1.0

Example:

SELECT POW(2, 10);
-- output 1024.000000

Supported Types:

  • [bool, bool]

  • [bool, number]

  • [number, bool]

  • [number, number]

function power#

power()

Description:

Return the value of expr1 to the power of expr2.

Parameters:

  • expr1

  • expr2

Since: 0.1.0

Example:

SELECT POW(2, 10);
-- output 1024.000000

Supported Types:

  • [bool, bool]

  • [bool, number]

  • [number, bool]

  • [number, number]

function round#

round()

Description:

Return the nearest integer value to expr (in floating-point format), rounding halfway cases away from zero, regardless of the current rounding mode.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT ROUND(1.23);
-- output 1

Supported Types:

  • [bool]

  • [number]

function second#

second()

Description:

Return the second for a timestamp.

Since: 0.1.0

Example:

select second(timestamp(1590115420000));
-- output 40

Supported Types:

  • [int64]

  • [timestamp]

function sin#

sin()

Description:

Return the sine of expr.

Parameters:

  • expr It is a single argument in radians.

Since: 0.1.0

Example:

SELECT SIN(0);
-- output 0.000000

  • The value returned by sin() is always in the range: -1 to 1.

Supported Types:

  • [number]

function sqrt#

sqrt()

Description:

Return square root of expr.

Parameters:

  • expr It is a single argument in radians.

Since: 0.1.0

Example:

SELECT SQRT(100);
-- output 10.000000

Supported Types:

  • [number]

function strcmp#

strcmp()

Description:

Returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.

Since: 0.1.0

Example:

select strcmp("text", "text1");
-- output -1
select strcmp("text1", "text");
-- output 1
select strcmp("text", "text");
-- output 0

Supported Types:

  • [string, string]

function string#

string()

Description:

Return string converted from numeric expression.

Since: 0.1.0

Example:

select string(123);
-- output "123"

select string(1.23);
-- output "1.23"

Supported Types:

  • [bool]

  • [date]

  • [number]

  • [timestamp]

function substr#

substr()

Description:

Return a substring from string str starting at position pos.

Parameters:

  • str

  • pos define the begining of the substring.

Since: 0.1.0

Note: This function equals the [substr()](/reference/sql/functions_and_operators/Files/udfs_8h.md#function-substr) function.

Example:

select substr("hello world", 2);
-- output "llo world"

select substring("hello world", 2);
-- output "llo world"

  • If pos is positive, the begining of the substring is pos charactors from the start of string.

  • If pos is negative, the beginning of the substring is pos characters from the end of the string, rather than the beginning.

Supported Types:

  • [string, int32]

  • [string, int32, int32]

function substring#

substring()

Description:

Return a substring from string str starting at position pos.

Parameters:

  • str

  • pos define the begining of the substring.

Since: 0.1.0

Note: This function equals the [substr()](/reference/sql/functions_and_operators/Files/udfs_8h.md#function-substr) function.

Example:

select substr("hello world", 2);
-- output "llo world"

select substring("hello world", 2);
-- output "llo world"

  • If pos is positive, the begining of the substring is pos charactors from the start of string.

  • If pos is negative, the beginning of the substring is pos characters from the end of the string, rather than the beginning.

Supported Types:

  • [string, int32]

  • [string, int32, int32]

function sum#

sum()

Description:

Compute sum of values.

Parameters:

  • value Specify value column to aggregate on.

Example:

value

0

1

2

3

4

 
SELECT sum(value) OVER w;
-- output 10

Supported Types:

  • [list<number>]

  • [list<timestamp>]

function sum_cate#

sum_cate()

Description:

Compute sum of values grouped by category key and output string. Each group is represented as ‘K:V’ and separated by comma in outputs and are sorted by key in ascend order.

Parameters:

  • value Specify value column to aggregate on.

  • catagory Specify catagory column to group by.

Example:

value

catagory

0

x

1

y

2

x

3

y

4

x

 
SELECT sum_cate(value, catagory) OVER w;
-- output "x:6,y:4"

Supported Types:

  • [list<number>, list<date>]

  • [list<number>, list<int16>]

  • [list<number>, list<int32>]

  • [list<number>, list<int64>]

  • [list<number>, list<string>]

  • [list<number>, list<timestamp>]

function sum_cate_where#

sum_cate_where()

Description:

Compute sum of values matching specified condition grouped by category key and output string. Each group is represented as ‘K:V’ and separated by comma in outputs and are sorted by key in ascend order.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

  • catagory Specify catagory column to group by.

Example:

value

condition

catagory

0

true

x

1

false

y

2

false

x

3

true

y

4

true

x

 
SELECT sum_cate_where(value, condition, catagory) OVER w;
-- output "x:4,y:3"

Supported Types:

  • [list<number>, list<bool>, list<date>]

  • [list<number>, list<bool>, list<int16>]

  • [list<number>, list<bool>, list<int32>]

  • [list<number>, list<bool>, list<int64>]

  • [list<number>, list<bool>, list<string>]

  • [list<number>, list<bool>, list<timestamp>]

function sum_where#

sum_where()

Description:

Compute sum of values match specified condition.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

Since: 0.1.0

Example:

value

0

1

2

3

4

 
SELECT sum_where(value, value > 2) OVER w;
-- output 7

Supported Types:

  • [list<number>, list<bool>]

function tan#

tan()

Description:

Return the tangent of expr.

Parameters:

  • expr It is a single argument in radians.

Since: 0.1.0

Example:

SELECT TAN(0);
-- output 0.000000

Supported Types:

  • [number]

function timestamp#

timestamp()

Description:

Cast int64, date or string expression to timestamp.

Since: 0.1.0

Supported string style:

  • yyyy-mm-dd

  • yyyymmdd

  • yyyy-mm-dd hh:mm:ss Example:

Tip

We can use string() to make timestamp type values more readable.

select string(timestamp(1590115420000));
-- output 2020-05-22 10:43:40

select string(timestamp(date("2020-05-22")));
-- output 2020-05-22 00:00:00

select string(timestamp("2020-05-22 10:43:40"));
-- output 2020-05-22 10:43:40

Supported Types:

  • [date]

  • [string]

function top#

top()

Description:

Compute top k of values and output string separated by comma. The outputs are sorted in desc order.

Parameters:

  • value Specify value column to aggregate on.

  • k Fetch top n keys.

Since: 0.1.0

Example:

value

0

1

2

3

4

 
SELECT top(value, 3) OVER w;
-- output "2,3,4"

Supported Types:

  • [list<date>, list<int32>]

  • [list<date>, list<int64>]

  • [list<number>, list<int32>]

  • [list<number>, list<int64>]

  • [list<string>, list<int32>]

  • [list<string>, list<int64>]

  • [list<timestamp>, list<int32>]

  • [list<timestamp>, list<int64>]

function top_n_key_avg_cate_where#

top_n_key_avg_cate_where()

Description:

Compute average of values matching specified condition grouped by category key. Output string for top N keys in descend order. Each group is represented as ‘K:V’ and separated by comma.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

  • catagory Specify catagory column to group by.

  • n Fetch top n keys.

Example:

value

condition

catagory

0

true

x

1

false

y

2

false

x

3

true

y

4

true

x

5

true

z

6

false

z

 
    SELECT top_n_key_avg_cate_where(value, condition, catagory, 2)
OVER w;
    -- output "z:5,y:3"

Supported Types:

  • [list<number>, list<bool>, list<date>, list<int32>]

  • [list<number>, list<bool>, list<date>, list<int64>]

  • [list<number>, list<bool>, list<int16>, list<int32>]

  • [list<number>, list<bool>, list<int16>, list<int64>]

  • [list<number>, list<bool>, list<int32>, list<int32>]

  • [list<number>, list<bool>, list<int32>, list<int64>]

  • [list<number>, list<bool>, list<int64>, list<int32>]

  • [list<number>, list<bool>, list<int64>, list<int64>]

  • [list<number>, list<bool>, list<string>, list<int32>]

  • [list<number>, list<bool>, list<string>, list<int64>]

  • [list<number>, list<bool>, list<timestamp>, list<int32>]

  • [list<number>, list<bool>, list<timestamp>, list<int64>]

function top_n_key_count_cate_where#

top_n_key_count_cate_where()

Description:

Compute count of values matching specified condition grouped by category key. Output string for top N keys in descend order. Each group is represented as ‘K:V’ and separated by comma.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

  • catagory Specify catagory column to group by.

  • n Fetch top n keys.

Example:

value

condition

catagory

0

true

x

1

true

y

2

false

x

3

true

y

4

false

x

5

true

z

6

true

z

 
    SELECT top_n_key_count_cate_where(value, condition, catagory, 2)
OVER w;
    -- output "z:2,y:2"

Supported Types:

  • [list<number>, list<bool>, list<date>, list<int32>]

  • [list<number>, list<bool>, list<date>, list<int64>]

  • [list<number>, list<bool>, list<int16>, list<int32>]

  • [list<number>, list<bool>, list<int16>, list<int64>]

  • [list<number>, list<bool>, list<int32>, list<int32>]

  • [list<number>, list<bool>, list<int32>, list<int64>]

  • [list<number>, list<bool>, list<int64>, list<int32>]

  • [list<number>, list<bool>, list<int64>, list<int64>]

  • [list<number>, list<bool>, list<string>, list<int32>]

  • [list<number>, list<bool>, list<string>, list<int64>]

  • [list<number>, list<bool>, list<timestamp>, list<int32>]

  • [list<number>, list<bool>, list<timestamp>, list<int64>]

function top_n_key_max_cate_where#

top_n_key_max_cate_where()

Description:

Compute maximum of values matching specified condition grouped by category key. Output string for top N keys in descend order. Each group is represented as ‘K:V’ and separated by comma.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

  • catagory Specify catagory column to group by.

  • n Fetch top n keys.

Example:

value

condition

catagory

0

true

x

1

false

y

2

false

x

3

true

y

4

true

x

5

true

z

6

false

z

 
    SELECT top_n_key_max_cate_where(value, condition, catagory, 2)
OVER w;
    -- output "z:5,y:3"

Supported Types:

  • [list<number>, list<bool>, list<date>, list<int32>]

  • [list<number>, list<bool>, list<date>, list<int64>]

  • [list<number>, list<bool>, list<int16>, list<int32>]

  • [list<number>, list<bool>, list<int16>, list<int64>]

  • [list<number>, list<bool>, list<int32>, list<int32>]

  • [list<number>, list<bool>, list<int32>, list<int64>]

  • [list<number>, list<bool>, list<int64>, list<int32>]

  • [list<number>, list<bool>, list<int64>, list<int64>]

  • [list<number>, list<bool>, list<string>, list<int32>]

  • [list<number>, list<bool>, list<string>, list<int64>]

  • [list<number>, list<bool>, list<timestamp>, list<int32>]

  • [list<number>, list<bool>, list<timestamp>, list<int64>]

function top_n_key_min_cate_where#

top_n_key_min_cate_where()

Description:

Compute minimum of values matching specified condition grouped by category key. Output string for top N keys in descend order. Each group is represented as ‘K:V’ and separated by comma.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

  • catagory Specify catagory column to group by.

  • n Fetch top n keys.

Example:

value

condition

catagory

0

true

x

1

true

y

2

false

x

3

true

y

4

false

x

5

true

z

6

true

z

 
    SELECT top_n_key_min_cate_where(value, condition, catagory, 2)
OVER w;
    -- output "z:5,y:1"

Supported Types:

  • [list<number>, list<bool>, list<date>, list<int32>]

  • [list<number>, list<bool>, list<date>, list<int64>]

  • [list<number>, list<bool>, list<int16>, list<int32>]

  • [list<number>, list<bool>, list<int16>, list<int64>]

  • [list<number>, list<bool>, list<int32>, list<int32>]

  • [list<number>, list<bool>, list<int32>, list<int64>]

  • [list<number>, list<bool>, list<int64>, list<int32>]

  • [list<number>, list<bool>, list<int64>, list<int64>]

  • [list<number>, list<bool>, list<string>, list<int32>]

  • [list<number>, list<bool>, list<string>, list<int64>]

  • [list<number>, list<bool>, list<timestamp>, list<int32>]

  • [list<number>, list<bool>, list<timestamp>, list<int64>]

function top_n_key_sum_cate_where#

top_n_key_sum_cate_where()

Description:

Compute sum of values matching specified condition grouped by category key. Output string for top N keys in descend order. Each group is represented as ‘K:V’ and separated by comma.

Parameters:

  • value Specify value column to aggregate on.

  • condition Specify condition column.

  • catagory Specify catagory column to group by.

  • n Fetch top n keys.

Example:

value

condition

catagory

0

true

x

1

true

y

2

false

x

3

true

y

4

false

x

5

true

z

6

true

z

 
    SELECT top_n_key_sum_cate_where(value, condition, catagory, 2)
OVER w;
    -- output "z:11,y:4"

Supported Types:

  • [list<number>, list<bool>, list<date>, list<int32>]

  • [list<number>, list<bool>, list<date>, list<int64>]

  • [list<number>, list<bool>, list<int16>, list<int32>]

  • [list<number>, list<bool>, list<int16>, list<int64>]

  • [list<number>, list<bool>, list<int32>, list<int32>]

  • [list<number>, list<bool>, list<int32>, list<int64>]

  • [list<number>, list<bool>, list<int64>, list<int32>]

  • [list<number>, list<bool>, list<int64>, list<int64>]

  • [list<number>, list<bool>, list<string>, list<int32>]

  • [list<number>, list<bool>, list<string>, list<int64>]

  • [list<number>, list<bool>, list<timestamp>, list<int32>]

  • [list<number>, list<bool>, list<timestamp>, list<int64>]

function truncate#

truncate()

Description:

Return the nearest integer that is not greater in magnitude than the expr.

Parameters:

  • expr

Since: 0.1.0

Example:

SELECT TRUNCATE(1.23);
-- output 1.0

Supported Types:

  • [bool]

  • [number]

function ucase#

ucase()

Description:

Convert all the characters to uppercase. Note that characters values > 127 are simply returned.

Since: 0.4.0

Example:

SELECT UCASE('Sql') as str1;
--output "SQL"

Supported Types:

  • [string]

function upper#

upper()

Description:

Convert all the characters to uppercase. Note that characters values > 127 are simply returned.

Since: 0.4.0

Example:

SELECT UCASE('Sql') as str1;
--output "SQL"

Supported Types:

  • [string]

function week#

week()

Description:

Return the week of year for a timestamp or date.

Since: 0.1.0

Example:

select weekofyear(timestamp(1590115420000));
-- output 21
select week(timestamp(1590115420000));
-- output 21

Supported Types:

  • [date]

  • [int64]

  • [timestamp]

function weekofyear#

weekofyear()

Description:

Return the week of year for a timestamp or date.

Since: 0.1.0

Example:

select weekofyear(timestamp(1590115420000));
-- output 21
select week(timestamp(1590115420000));
-- output 21

Supported Types:

  • [date]

  • [int64]

  • [timestamp]

function year#

year()

Description:

Return the year part of a timestamp or date.

Since: 0.1.0

Example:

select year(timestamp(1590115420000));
-- output 2020

Supported Types:

  • [date]

  • [int64]

  • [timestamp]