Functions

eKuiper has many built-in functions for performing calculations on data.

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions can be used as expressions only in the following:

• The select list of a SELECT statement (either a sub-query or an outer query).
• A HAVING clause.

Collect() Examples

• Get an array of column a of the current window. Assume the column a is of int type, the result will be like: [{"r1":[32, 45]}]

    SELECT collect(a) as r1 FROM test GROUP BY TumblingWindow(ss, 10)

• Get the whole array of the current window. The result will be like: [{"r1":{"a":32, "b":"hello"}, {"a":45, "b":"world"}}]

    SELECT collect(*) as r1 FROM test GROUP BY TumblingWindow(ss, 10)

• Get the second element’s column ‘a’ value within the current window. The result will be like: [{"r1":32}]

    SELECT collect(*)[1]->a as r1 FROM test GROUP BY TumblingWindow(ss, 10)

Deduplicate() Examples

• Get the whole array of the current window which is deduplicated by column a. The result will be like: [{"r1":{"a":32, "b":"hello"}, {"a":45, "b":"world"}}]

    SELECT deduplicate(a, true) as r1 FROM test GROUP BY TumblingWindow(ss, 10)

• Get the column a value which is not duplicate during the last hour. The result will be like: [{"r1":32}], [{"r1":45}] and [{}] if a duplicate value arrives. Use the omitIfEmpty sink property to filter out those empty results.

     SELECT deduplicate(a, false)->a as r1 FROM demo GROUP BY SlidingWindow(hh, 1)

Mathematical Functions

String Functions

Format_time patterns

A pattern is used to create a format string. Patterns are based on a simple sequence of letters and symbols which is common in many languages like Java etc. The supported symbols in Kuiper are

Examples:

• YYYY-MM-dd T HH:mm:ss -> 2006-01-02 T 15:04:05
• YYYY/MM/dd HH:mm:ssSSS XXX -> 2006/01/02 15:04:05.000 -07:00

Conversion Functions

Cast to datetime

When casting to datetime type, the supported column type and casting rule are:

1. If column is datetime type, just return the value.
2. If column is bigint or float type, the number will be treated as the milliseconds elapsed since January 1, 1970 00:00:00 UTC and converted.
3. If column is string, it will be parsed to datetime with the default format: "2006-01-02T15:04:05.000Z07:00".
4. Other types are not supported.

Hashing Functions

JSON Functions

Please refer to json path functions for how to compose a json path.

Other Functions

Multiple Column Functions

A multiple column function is a function that returns multiple columns. Contrast to normal scalar function, which returns a single column of a single row.

Multiple column function can only be used in the SELECT clause of a query.

Functions to detect changes

Changed_col function

This function is a normal scalar function, so it can be used in any clause including SELECT and WHERE.

Syntax

CHANGED_COL(<ignoreNull>, <expr>)

Arguments

ignoreNull: whether to ignore null values when comparing for changes. If true, the null value won’t emit a change.

expr: An expression to be selected and monitored for the changed status.

Returns

Return the changed value or nil with column name changed_col by default like any other functions. Use as alias to rename the column.

Changed_cols function

This function returns multiple columns, so it is only allowed in the SELECT clause.

Syntax

CHANGED_COLS (<prefix>, <ignoreNull>, <expr> [,...,<exprN>])

Arguments

prefix: The prefix of the selected column name. By default, the selected name will be the same as select the expr directly. For example, CHANGED_COLS("", true, col1) will return col1 as the name. If setting a prefix, the return name will have that prefix. For example, CHANGED_COLS("changed_", true, col1) will return changed_col1 as the name.

ignoreNull: whether to ignore null values when detecting changes. If true, the null value won’t trigger a change.

expr: An expression to be selected and monitored for the changed status. Allow any expression that can be used in select clause. The expression can be a * which will return multiple columns by one expression.

Returns

Return all changed values compared to the previous sink result. So if using in a scalar rule, it will compare to the previous value emit. If using in a window, it will compare to the previous window result.

In the first run, all expressions will be returned because there is no previous result.

In the consequent runs, if nothing changed, it can emit nothing. And if the sink has the default omitEmpty, the sink will not be triggerred.

Notice

The multiple column outputs can only be used in the select clause. Even the selected result cannot be access in WHERE or other place. If filter based on the value is needed, use CHANGED_COL or set the result of multiple column outputs as the prior rule in a rule chain.

For multiple column outputs, the alias can only be set generally with the prefix. To set alias for each column separately, try to call the changed function for each column respectively and use as to set alias.

Had_changed function

This function is a scalar function with one or more arguments.

HAD_CHANGED (<ignoreNull>, <expr> [,...,<exprN>])

Arguments

ignoreNull: whether to ignore null values when detecting changes. If true, the null value won’t trigger a change.

expr: An expression to be monitored for the changed status. Allow any expression that can be used in select clause. The expression can be a * to detect changes of all columns easily.

Returns

Return a bool value to indicate the changed status if any of the arguments had changed since the last run. The multiple arguments’ version is a handy way to check HAD_CHANGED(expr1) OR HAD_CHANGED(expr2) … OR HAD_CHANGED(exprN). To detect other relationship, just use separate HAD_CHANGED functions. For example, to check if all expressions are changed HAD_CHANGED(expr1) AND HAD_CHANGED(expr2) … AND HAD_CHANGED(exprN).

Examples

Create a stream demo and have below inputs

```json lines {“ts”:1, “temperature”:23, “humidity”:88} {“ts”:2, “temperature”:23, “humidity”:88} {“ts”:3, “temperature”:23, “humidity”:88} {“ts”:4, “temperature”:25, “humidity”:88} {“ts”:5, “temperature”:25, “humidity”:90} {“ts”:6, “temperature”:25, “humidity”:91} {“ts”:7, “temperature”:25, “humidity”:91} {“ts”:8, “temperature”:25, “humidity”:91}

Rule to get the changed temperature values:
```text
SQL: SELECT CHANGED_COLS("", true, temperature) FROM demo
___________________________________________________
{"temperature":23}
{"temperature":25}

Rule to get the changed temperature and humidity values, and rename the changed value in a unified prefix:

SQL: SELECT CHANGED_COLS("c_", true, temperature, humidity) FROM demo
_________________________________________________________
{"c_ts":1, "c_temperature":23, "c_humidity":88}
{"c_ts":2}
{"c_ts":3}
{"c_ts":4, "c_temperature":25}
{"c_ts":5, "c_humidity":90}
{"c_ts":6, "c_humidity":91}
{"c_ts":7}
{"c_ts":8}

Rule to get the changed values of all columns and do not ignore null:

SQL: SELECT CHANGED_COLS("c_", false, *) FROM demo
_________________________________________________________
{"c_temperature":23,"c_humidity":88}
{"c_temperature":25}
{"c_humidity":90}
{"c_humidity":91}

Rule to get the average value change in a window:

SQL: SELECT CHANGED_COLS("t", true, avg(temperature)) FROM demo GROUP BY CountWindow(2)
_________________________________________________________________
{"tavg":23}
{"tavg":24}
{"tavg":25}

Rule to get the events when temperature or humidity changed:

SQL: SELECT ts, temperature, humidity FROM demo
WHERE HAD_CHANGED(true, temperature, humidity) = true
_________________________________________________________
{"ts":1,temperature":23,"humidity":88}
{"ts":4,temperature":25,"humidity":88}
{"ts":5,temperature":25,"humidity":90}
{"ts":6,temperature":25,"humidity":91}

Rule to get the events when temperature has changed but humidity has NOT changed:

SQL: SELECT ts, temperature, humidity FROM demo 
WHERE HAD_CHANGED(true, temperature) = true AND HAD_CHANGED(true, humidity) = false
_________________________________________________________
{"ts":4,temperature":25,"humidity":88}

Rule to get the changed temperature and humidity value with customized names:

SQL: SELECT CHANGED_COL(true, temperature) AS myTemp, CHANGED_COL(true, humidity) AS myHum FROM demo
_________________________________________________________
{"myTemp":23,"myHum":88}
{"myTemp":25}
{"myHum":90}
{"myHum":91}

Rule to get the changed values when the temperature had changed to value bigger than 24:

SQL: SELECT ts, temperature, humidity FROM demo 
WHERE CHANGED_COL(true, temperature) > 24
_________________________________________________________
{"ts":4,temperature":25,"humidity":88}