## Built-in Functions

### function abs

```cpp
abs()
```

**Description**:

Return the absolute value of expr. 

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


Example:

```sql

SELECT ABS(-32);
-- output 32
```


**Supported Types**:

* [`bool`]
* [`number`] 

### function acos

```cpp
acos()
```

**Description**:

Return the arc cosine of expr. 

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


Example:

```sql

SELECT ACOS(1);
-- output 0
```


**Supported Types**:

* [`number`] 

### function add

```cpp
add()
```

**Description**:

Compute sum of two arguments. 

**Since**:
0.1.0


Example:

```sql

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

```cpp
asin()
```

**Description**:

Return the arc sine of expr. 

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


Example:

```sql

SELECT ASIN(0.0);
-- output 0.000000
```


**Supported Types**:

* [`number`] 

### function at

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


```sql

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

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

```sql

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

```cpp
atan2()
```

**Description**:

Return the arc tangent of Y / X.. 

**Parameters**: 

  * **X** 
  * **Y** 


**Since**:
0.1.0


Example:

```sql

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


**Supported Types**:

* [`bool`, `bool`]
* [`bool`, `number`]
* [`number`, `bool`]
* [`number`, `number`] 

### function avg

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


```sql

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


**Supported Types**:

* [`list<number>`] 

### function avg_cate

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


```sql

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

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


```sql

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

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


```sql

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


**Supported Types**:

* [`list<number>`, `list<bool>`] 

### function bool

```cpp
bool()
```

**Description**:

Cast string expression to bool. 

**Since**:
0.1.0


Example:

```sql

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


**Supported Types**:

* [`string`] 

### function ceil

```cpp
ceil()
```

**Description**:

Return the smallest integer value not less than the expr. 

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


Example:

```sql

SELECT CEIL(1.23);
-- output 2
```


**Supported Types**:

* [`bool`]
* [`number`] 

### function ceiling

```cpp
ceiling()
```

**Description**:

Return the smallest integer value not less than the expr. 

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


Example:

```sql

SELECT CEIL(1.23);
-- output 2
```


**Supported Types**:

* [`bool`]
* [`number`] 

### function char

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

```sql

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


**Supported Types**:

* [`int32`] 

### function char_length

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

```sql

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


**Supported Types**:

* [`string`] 

### function character_length

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

```sql

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


**Supported Types**:

* [`string`] 

### function concat

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

```sql

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

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

```sql

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

```cpp
cos()
```

**Description**:

Return the cosine of expr. 

**Parameters**: 

  * **expr** It is a single argument in radians.


**Since**:
0.1.0


Example:

```sql

SELECT COS(0);
-- output 1.000000
```



* The value returned by [cos()](/reference/sql/functions_and_operators/Files/udfs_8h.md#function-cos) is always in the range: -1 to 1.

**Supported Types**:

* [`number`] 

### function cot

```cpp
cot()
```

**Description**:

Return the cotangent of expr. 

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


Example:

```sql

SELECT COT(1);  
-- output 0.6420926159343306
```


**Supported Types**:

* [`number`] 

### function count

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


```sql

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


**Supported Types**:

* [`list<bool>`]
* [`list<date>`]
* [`list<number>`]
* [`list<row>`]
* [`list<string>`]
* [`list<timestamp>`] 

### function count_cate

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


```sql

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

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


```sql

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

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


```sql

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

```cpp
date()
```

**Description**:

Cast timestamp or string expression to date. 

**Since**:
0.1.0


Example:

```sql

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


**Supported Types**:

* [`string`]
* [`timestamp`] 

### function date_format

```cpp
date_format()
```

**Description**:

Formats the datetime value according to the format string. 

Example:

```sql

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

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

```sql

SELECT degrees(3.141592653589793);
-- output  180.0
```


**Supported Types**:

* [`double`] 

### function distinct_count

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


```sql

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


**Supported Types**:

* [`list<bool>`]
* [`list<date>`]
* [`list<number>`]
* [`list<string>`]
* [`list<timestamp>`] 

### function double

```cpp
double()
```

**Description**:

Cast string expression to double. 

**Since**:
0.1.0


Example:

```sql

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


**Supported Types**:

* [`string`] 

### function exp

```cpp
exp()
```

**Description**:

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

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


```sql

SELECT EXP(0);  
-- output 1
```


**Supported Types**:

* [`number`] 

### function first_value

```cpp
first_value()
```

**Description**:

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



```
    @since 0.1.0
```

 **Supported Types**: 

### function float

```cpp
float()
```

**Description**:

Cast string expression to float. 

**Since**:
0.1.0


Example:

```sql

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


**Supported Types**:

* [`string`] 

### function floor

```cpp
floor()
```

**Description**:

Return the largest integer value not less than the expr. 

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


Example:

```sql

SELECT FLOOR(1.23);
-- output 1
```


**Supported Types**:

* [`bool`]
* [`number`] 

### function fz_join

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

```sql

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


**Supported Types**:

* [`list<string>`, `string`] 

### function fz_split

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

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

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

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

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

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

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

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

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

```sql

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

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

```sql

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

```cpp
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 <escape character> must <= 1
    * if <escape character> 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:

```sql

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


**Supported Types**:

* [`string`] 

### function int32

```cpp
int32()
```

**Description**:

Cast string expression to int32. 

**Since**:
0.1.0


Example:

```sql

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


**Supported Types**:

* [`string`] 

### function int64

```cpp
int64()
```

**Description**:

Cast string expression to int64. 

**Since**:
0.1.0


Example:

```sql

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


**Supported Types**:

* [`string`] 

### function is_null

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

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

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


```sql

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

```cpp
lcase()
```

**Description**:

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

**Since**:
0.5.0


Example:

```sql

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


**Supported Types**:

* [`string`] 

### function like_match

```cpp
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 <escape character> must <= 1
    * if <escape character> 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:

```sql

SELECT LN(1);  
-- output 0.000000
```


**Supported Types**:

* [`bool`]
* [`number`] 

### function log

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

```sql

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

```cpp
log10()
```

**Description**:

Return the base-10 logarithm of expr. 

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


Example:

```sql

SELECT LOG10(100);  
-- output 2
```


**Supported Types**:

* [`bool`]
* [`number`] 

### function log2

```cpp
log2()
```

**Description**:

Return the base-2 logarithm of expr. 

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


Example:

```sql

SELECT LOG2(65536);  
-- output 16
```


**Supported Types**:

* [`bool`]
* [`number`] 

### function lower

```cpp
lower()
```

**Description**:

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

**Since**:
0.5.0


Example:

```sql

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


**Supported Types**:

* [`string`] 

### function make_tuple

```cpp
make_tuple()
```

**Description**:


**Supported Types**:

* [...] 

### function max

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


```sql

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


**Supported Types**:

* [`list<date>`]
* [`list<number>`]
* [`list<string>`]
* [`list<timestamp>`] 

### function max_cate

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


```sql

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

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


```sql

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

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


```sql

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


**Supported Types**:

* [`list<number>`, `list<bool>`] 

### function maximum

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

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


```sql

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


**Supported Types**:

* [`list<date>`]
* [`list<number>`]
* [`list<string>`]
* [`list<timestamp>`] 

### function min_cate

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


```sql

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

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


```sql

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

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


```sql

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


**Supported Types**:

* [`list<number>`, `list<bool>`] 

### function minimum

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

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

```sql

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

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

```sql

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

```cpp
pow()
```

**Description**:

Return the value of expr1 to the power of expr2. 

**Parameters**: 

  * **expr1** 
  * **expr2** 


**Since**:
0.1.0


Example:

```sql

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


**Supported Types**:

* [`bool`, `bool`]
* [`bool`, `number`]
* [`number`, `bool`]
* [`number`, `number`] 

### function power

```cpp
power()
```

**Description**:

Return the value of expr1 to the power of expr2. 

**Parameters**: 

  * **expr1** 
  * **expr2** 


**Since**:
0.1.0


Example:

```sql

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


**Supported Types**:

* [`bool`, `bool`]
* [`bool`, `number`]
* [`number`, `bool`]
* [`number`, `number`] 

### function radians

```cpp
radians()
```

**Description**:

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

**Since**:
0.6.0


Example:

```sql

SELECT RADIANS(90);
--output 1.570796326794896619231
```


**Supported Types**:

* [`double`] 

### function replace

```cpp
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 `search`s removed from final string

Example:

```sql

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


**Supported Types**:

* [`string`, `string`]
* [`string`, `string`, `string`] 

### function reverse

```cpp
reverse()
```

**Description**:

Returns the reversed given string. 

**Since**:
0.4.0


Example:

```sql

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


**Supported Types**:

* [`string`] 

### function round

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

```sql

SELECT ROUND(1.23);
-- output 1
```


**Supported Types**:

* [`bool`]
* [`number`] 

### function second

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

```sql

SELECT SIN(0);
-- output 0.000000
```



* The value returned by [sin()](/reference/sql/functions_and_operators/Files/udfs_8h.md#function-sin) is always in the range: -1 to 1.

**Supported Types**:

* [`number`] 

### function sqrt

```cpp
sqrt()
```

**Description**:

Return square root of expr. 

**Parameters**: 

  * **expr** It is a single argument in radians.


**Since**:
0.1.0


Example:

```sql

SELECT SQRT(100);
-- output 10.000000
```


**Supported Types**:

* [`number`] 

### function strcmp

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

```sql

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


**Supported Types**:

* [`string`, `string`] 

### function string

```cpp
string()
```

**Description**:

Return string converted from numeric expression. 

**Since**:
0.1.0


Example:

```sql

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

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


**Supported Types**:

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

### function substr

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

```sql

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

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

```sql

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

```cpp
sum()
```

**Description**:

Compute sum of values. 

**Parameters**: 

  * **value** Specify value column to aggregate on.



Example:


| value     |
|  -------- |
| 0     |
| 1     |
| 2     |
| 3     |
| 4    |


```sql

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

**Supported Types**:

* [`list<number>`]
* [`list<timestamp>`] 

### function sum_cate

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


```sql

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

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


```sql

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

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


```sql

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


**Supported Types**:

* [`list<number>`, `list<bool>`] 

### function tan

```cpp
tan()
```

**Description**:

Return the tangent of expr. 

**Parameters**: 

  * **expr** It is a single argument in radians.


**Since**:
0.1.0


Example:

```sql

SELECT TAN(0);
-- output 0.000000
```


**Supported Types**:

* [`number`] 

### function timestamp

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

```sql

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

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


```sql

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

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


```sql

    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

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


```sql

    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

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


```sql

    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

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


```sql

    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

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


```sql

    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

```cpp
truncate()
```

**Description**:

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

**Parameters**: 

  * **expr** 


**Since**:
0.1.0


Example:

```sql

SELECT TRUNCATE(1.23);
-- output 1.0
```


**Supported Types**:

* [`bool`]
* [`number`] 

### function ucase

```cpp
ucase()
```

**Description**:

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

**Since**:
0.4.0


Example:

```sql

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


**Supported Types**:

* [`string`] 

### function upper

```cpp
upper()
```

**Description**:

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

**Since**:
0.4.0


Example:

```sql

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


**Supported Types**:

* [`string`] 

### function week

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