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 value evaluated at the row that is offset rows before the current row within the partition. Offset is evaluated with respect to the current row.

Parameters:

• offset The number of rows forwarded from the current row, must not negative

Example:

c1	c2
0	1
1	1
2	2
3	2
4	2

SELECT at(c1, 1) as co OVER w from t1 window (order by c1 partition by c2);
-- output
-- | co |
-- |----|
-- |NULL|
-- |0   |
-- |NULL|
-- |2   |
-- |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:

• catagory Specify catagory column to group by.

• value Specify value column to aggregate on.

• condition Specify condition column.

Example:

value	condition	catagory
0	true	x
1	false	y
2	false	x
3	true	y
4	true	x

SELECT avg_cate_where(catagory, value, condition) 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 char

char()

Description:

Returns the ASCII character having the binary equivalent to expr. If n >= 256 the result is equivalent to char(n % 256).

Since: 0.6.0

Example:

SELECT char(65);
--output "A"

Supported Types:

• [int32]

function char_length

char_length()

Description:

Returns the length of the string. It is measured in characters and multibyte character string is not supported.

Since: 0.6.0

Example:

SELECT CHAR_LENGTH('Spark SQL ');
--output 10

Supported Types:

• [string]

function character_length

character_length()

Description:

Returns the length of the string. It is measured in characters and multibyte character string is not supported.

Since: 0.6.0

Example:

SELECT CHAR_LENGTH('Spark SQL ');
--output 10

Supported Types:

• [string]

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:

• catagory Specify catagory column to group by.

• value Specify value column to aggregate on.

• condition Specify condition column.

Example:

value	condition	catagory
0	true	x
1	false	y
2	false	x
3	true	y
4	true	x

SELECT count_cate_where(catagory, value, condition) 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<row>, 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: ```sql

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

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

**Supported Types**:

* [`date`]
* [`int64`]
* [`timestamp`] 

### function dayofmonth

```cpp
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: ```sql

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

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

**Supported Types**:

* [`date`]
* [`int64`]
* [`timestamp`] 

### function dayofweek

```cpp
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: ```sql

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

**Supported Types**:

* [`date`]
* [`int64`]
* [`timestamp`] 

### function dayofyear

```cpp
dayofyear()

Description:

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

Since: 0.1.0

Example: ```sql

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 degrees

```cpp
degrees()

Description:

Convert radians to degrees.

Parameters:

• expr

Since: 0.5.0

Example:

SELECT degrees(3.141592653589793);
-- output  180.0

Supported Types:

• [double]

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: ```sql

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

**Supported Types**:

* [`int64`]
* [`timestamp`] 

### function identity

```cpp
identity()

Description:

Return value.

Since: 0.1.0

Example: ```sql

select identity(1); – output 1

**Supported Types**:

* [`bool`]
* [`date`]
* [`number`]
* [`string`]
* [`timestamp`] 

### function if_null

```cpp
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 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 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: ```sql

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

```cpp
inc()

Description:

Return expression + 1.

Since: 0.1.0

Example: ```sql

select inc(1); – output 2

**Supported Types**:

* [`number`] 

### function int16

```cpp
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 value evaluated at the row that is offset rows before the current row within the partition. Offset is evaluated with respect to the current row.

Parameters:

• offset The number of rows forwarded from the current row, must not negative

Example:

c1	c2
0	1
1	1
2	2
3	2
4	2

SELECT at(c1, 1) as co OVER w from t1 window (order by c1 partition by c2);
-- output
-- | co |
-- |----|
-- |NULL|
-- |0   |
-- |NULL|
-- |2   |
-- |3   |

Supported Types:

• [list<bool>, int64]

• [list<date>, int64]

• [list<number>, int64]

• [list<string>, int64]

• [list<timestamp>, int64]

function lcase

lcase()

Description:

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

Since: 0.5.0

Example:

SELECT LCASE('SQl') as str1;
--output "sql"

Supported Types:

• [string]

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: ```sql

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

```cpp
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 lower

lower()

Description:

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

Since: 0.5.0

Example:

SELECT LCASE('SQl') as str1;
--output "sql"

Supported Types:

• [string]

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:

• catagory Specify catagory column to group by.

• value Specify value column to aggregate on.

• condition Specify condition column.

Example:

value	condition	catagory
0	true	x
1	false	y
2	false	x
3	true	y
4	true	x

SELECT max_cate_where(catagory, value, condition) 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:

• catagory Specify catagory column to group by.

• value Specify value column to aggregate on.

• condition Specify condition column.

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(catagory, value, condition) 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: ```sql

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

**Supported Types**:

* [`int64`]
* [`timestamp`] 

### function month

```cpp
month()

Description:

Return the month part of a timestamp or date.

Since: 0.1.0

Example: ```sql

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

**Supported Types**:

* [`date`]
* [`int64`]
* [`timestamp`] 

### function nvl

```cpp
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 radians

radians()

Description:

Returns the argument X, converted from degrees to radians. (Note that π radians equals 180 degrees.)

Since: 0.6.0

Example:

SELECT RADIANS(90);
--output 1.570796326794896619231

Supported Types:

• [double]

function replace

replace()

Description:

replace(str, search[, replace]) - Replaces all occurrences of search with replace

Since: 0.5.2

if replace is not given or is empty string, matched searchs removed from final string

Example:

select replace("ABCabc", "abc", "ABC")
-- output "ABCABC"

Supported Types:

• [string, string]

• [string, string, string]

function reverse

reverse()

Description:

Returns the reversed given string.

Since: 0.4.0

Example:

SELECT REVERSE('abc') as str1;
--output "cba"

Supported Types:

• [string]

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: ```sql

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

**Supported Types**:

* [`int64`]
* [`timestamp`] 

### function sin

```cpp
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:

• catagory Specify catagory column to group by.

• value Specify value column to aggregate on.

• condition Specify condition column.

Example:

value	condition	catagory
0	true	x
1	false	y
2	false	x
3	true	y
4	true	x

SELECT sum_cate_where(catagory, value, condition) 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:

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

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

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

• catagory Specify catagory column to group by.

• value Specify value column to aggregate on.

• condition Specify condition column.

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

• catagory Specify catagory column to group by.

• value Specify value column to aggregate on.

• condition Specify condition column.

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

• catagory Specify catagory column to group by.

• value Specify value column to aggregate on.

• condition Specify condition column.

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

• catagory Specify catagory column to group by.

• value Specify value column to aggregate on.

• condition Specify condition column.

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

• catagory Specify catagory column to group by.

• value Specify value column to aggregate on.

• condition Specify condition column.

• 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: ```sql

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

**Supported Types**:

* [`date`]
* [`int64`]
* [`timestamp`] 

### function weekofyear

```cpp
weekofyear()

Description:

Return the week of year for a timestamp or date.

Since: 0.1.0

Example: ```sql

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

**Supported Types**:

* [`date`]
* [`int64`]
* [`timestamp`] 

### function year

```cpp
year()

Description:

Return the year part of a timestamp or date.

Since: 0.1.0

Example: ```sql

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

**Supported Types**:

* [`date`]
* [`int64`]
* [`timestamp`]