Single-row functions return a single value for every single result row generated by your statement. These functions can appear anywhere where expressions are allowed.
Esper allows static Java library methods as single-row functions, and also features built-in single-row functions. In addition, Esper allows instance method invocations on named streams.
Esper auto-imports the following Java library packages:
java.lang.*
java.math.*
java.text.*
java.util.*
Thus Java static library methods can be used in all expressions as shown in below example:
select symbol, Math.round(volume/1000) from StockTickEvent.win:time(30 sec)
In general, arbitrary Java class names have to be fully qualified (e.g. java.lang.Math) but Esper provides a mechanism for user-controlled imports of classes and packages as outlined in Section 10.4.5, “Class and package imports”.
The below table outlines the built-in single-row functions available.
Table 7.1. Syntax and results of single-row functions
Single-row Function | Result |
---|---|
case value when compare_value then result [when compare_value then result ...] [else result] end | Returns result where the first value equals compare_value. |
case when condition then result [when condition then result ...] [else result] end | Returns the result for the first condition that is true. |
cast(expression, type_name) | Casts the result of an expression to the given type. |
coalesce(expression, expression [, expression ...]) | Returns the first non-null value in the list, or null if there are no non-null values. |
current_timestamp[()] | Returns the current engine time as a long millisecond value. Reserved keyword with optional parenthesis. |
exists(dynamic_property_name) | Returns true if the dynamic property exists for the event, or false if the property does not exist. |
instanceof(expression, type_name [, type_name ...]) | Returns true if the expression returns an object whose type is one of the types listed. |
max(expression, expression [, expression ...]) | Returns the highest numeric value among the 2 or more comma-separated expressions. |
min(expression, expression [, expression ...]) | Returns the lowest numeric value among the 2 or more comma-separated expressions. |
prev(expression, event_property) | Returns a property value of a previous event, relative to the event order within a data window |
prior(integer, event_property) | Returns a property value of a prior event, relative to the natural order of arrival of events |
The case control flow function has two versions. The first version takes a value and a list of compare values to compare against, and returns the result where the first value equals the compare value. The second version takes a list of conditions and returns the result for the first condition that is true.
The return type of a case expression is the compatible aggregated type of all return values.
The example below shows the first version of a case statement. It has a String return type and returns the value 'one'.
select case 1 when 1 then 'one' when 2 then 'two' else 'more' end from ...
The second version of the case function takes a list of conditions. The next example has a Boolean return type and returns the boolean value true.
select case when 1>0 then true else false end from ...
The cast function casts the return type of an expression to a designated type. The function accepts two parameters: The first parameter is the property name or expression that returns the value to be casted. The second parameter is the type to cast to.
Valid parameters for the second (type) parameter are:
Any of the Java built-in types: int, long, byte, short, char, double, float, string, where string is a short notation for java.lang.String. The type name is not case-sensitive. For example:
cast(price, double)
The fully-qualified class name of the class to cast to, for example:
cast(product, org.myproducer.Product)
The cast function is often used to provide a type for dynamic (unchecked) properties. Dynamic properties are properties whose type is not known at compile type. These properties are always of type java.lang.Object.
The cast function as shown in the next statement casts the dynamic "price" property of an "item" in the OrderEvent to a double value.
select cast(item.price?, double) from OrderEvent
The cast function returns a null value if the expression result cannot be casted to the desired type, or if the expression result itself is null.
The cast function adheres to the following type conversion rules:
For all numeric types, the cast function utilitzes java.lang.Number to convert numeric types, if required.
For casts to string or java.lang.String, the function calls toString on the expression result.
For casts to other objects including application objects, the cast function considers a Java class's superclasses as well as all directly or indirectly-implemented interfaces by superclasses .
The result of the coalesce function is the first expression in a list of expressions that returns a non-null value. The return type is the compatible aggregated type of all return values.
This example returns a String-typed result of value 'foo':
select coalesce(null, 'foo') from ...
The current_timestamp function is a reserved keyword and requires no parameters. The result of the current_timestamp function is the long-type millisecond value of the current engine system time.
The function returns the current engine timestamp at the time of expression evaluation. When using external-timer events, the function provides the last value of the externally-supplied time at the time of expression evaluation.
This example selects the current engine time:
select current_timestamp from MyEvent // equivalent to select current_timestamp() from MyEvent
The exists function returns a boolean value indicating whether the dynamic property, provided as a parameter to the function, exists on the event. The exists function accepts a single dynamic property name as its only parameter.
The exists function is for use with dynamic (unchecked) properties. Dynamic properties are properties whose type is not known at compile type. Dynamic properties return a null value if the dynamic property does not exists on an event, or if the dynamic property exists but the value of the dynamic property is null.
The exists function as shown next returns true if the "item" property contains an object that has a "serviceName" property. It returns false if the "item" property is null, or if the "item" property does not contain an object that has a property named "serviceName" :
select exists(item.serviceName?) from OrderEvent
The instanceof function returns a boolean value indicating whether the type of value returned by the expression is one of the given types. The first parameter to the instanceof function is an expression to evaluate. The second and subsequent parameters are Java type names.
The function determines the return type of the expression at runtime by evaluating the expression, and compares the type of object returned by the expression to the defined types. If the type of object returned by the expression matches any of the given types, the function returns true. If the expression returned null or a type that does not match any of the given types, the function returns false.
The instanceof function is often used in conjunction with dynamic (unchecked) properties. Dynamic properties are properties whose type is not known at compile type.
This example uses the instanceof function to select different properties based on the type:
select case when instanceof(item, com.mycompany.Service) then serviceName? when instanceof(item, com.mycompany.Product) then productName? end from OrderEvent
The instanceof function returns false if the expression tested by instanceof returned null.
Valid parameters for the type parameter list are:
Any of the Java built-in types: int, long, byte, short, char, double, float, string, where string is a short notation for java.lang.String. The type name is not case-sensitive. For example, the next function tests if the dynamic "price" property is either of type float or type double:
instanceof(price?, double, float)
The fully-qualified class name of the class to cast to, for example:
instanceof(product, org.myproducer.Product)
The function considers an event class's superclasses as well as all the directly or indirectly-implemented interfaces by superclasses.
The min and max function take two or more parameters that itself can be expressions. The min function returns the lowest numeric value among the 2 or more comma-separated expressions, while the max function returns the highest numeric value. The return type is the compatible aggregated type of all return values.
The next example shows the max function that has a Double return type and returns the value 1.1.
select max(1, 1.1, 2 * 0.5) from ...
The min function returns the lowest value. The statement below uses the function to determine the smaller of two timestamp values.
select symbol, min(ticks.timestamp, news.timestamp) as minT from StockTickEvent.win:time(30 sec) as ticks, NewsEvent.win:time(30 sec) as news where ticks.symbol = news.symbol
The prev function returns the property value of a previous event. The first parameter denotes the i-th previous event in the order established by the data window. The second parameter is a property name for which the function returns the value for the previous event.
This example selects the value of the price property of the 2nd-previous event from the current Trade event.
select prev(2, price) from Trade.win:length(10)
Since the prev function takes the order established by the data window into account, the function works well with sorted windows. In the following example the statement selects the symbol of the 3 Trade events that had the largest, second-largest and third-largest volume.
select prev(0, symbol), prev(1, symbol), prev(2, symbol) from Trade.ext:sort(volume, true, 10)
The i-th previous event parameter can also be an expression returning an Integer-type value. The next statement joins the Trade data window with an RankSelectionEvent event that provides a rank property used to look up a certain position in the sorted Trade data window:
select prev(rank, symbol) from Trade.ext:sort(volume, true, 10), RankSelectionEvent
And the expression count(*) - 1 allows us to select the oldest event in the length window:
select prev(count(*) - 1, price) from Trade.win:length(100)
The prev function returns a null value if the data window does not currently hold the i-th previous event. The example below illustrates this using a time batch window. Here the prev function returns a null value for any events in which the previous event is not in the same batch of events. Note that the prior function as discussed below can be used if a null value is not the desired result.
select prev(1, symbol) from Trade.win:time_batch(1 min)
The combination of prev function and group-by view returns the property value for a previous event in the given group.
Let's look at an example. Assume we want to obtain the price of the previous event of the same symbol as the current event.
The statement that follows solves this problem. It declares a group-by view grouping on the symbol property and a time window of 1 minute. As a result, when the engine encounters a new symbol value that it hasn't seen before, it creates a new time window specifically to hold events for that symbol. Consequently, the previous function returns the previous event within the respective time window for that event's symbol value.
select prev(1, price) as prevPrice from Trade.std:groupby(symbol).win:time(1 min)
In a second example, assume we need to return, for each event, the current top price per symbol. We can use the prev to obtain the highest price from a sorted data window, and use the group-by view to group by symbol:
select prev(0, price) as topPricePerSymbol from Trade.std:groupby(symbol).ext:sort(price, false, 1)
The following restrictions apply to the prev functions and its results:
The function always returns a null value for remove stream (old data) events
The function requires a data window view, or a group-by and data window view, without any additional sub-views. See Chapter 8, EPL Reference: Views for built-in data window views.
The prev function is similar to the prior function. The key differences between the two functions are as follows:
The prev function returns previous events in the order provided by the data window, while the prior function returns prior events in the order of arrival as posted by a stream's declared views.
The prev function requires a data window view while the prior function does not have any view requirements.
The prev function returns the previous event grouped by a criteria by combining the std:groupby view and a data window. The prior function returns prior events posted by the last view regardless of data window grouping.
The prev function returns a null value for remove stream events, i.e. for events leaving a data window. The prior function does not have this restriction.
The prior function returns the property value of a prior event. The first parameter is an integer value that denotes the i-th prior event in the natural order of arrival. The second parameter is a property name for which the function returns the value for the prior event.
This example selects the value of the price property of the 2nd-prior event to the current Trade event.
select prior(2, price) from Trade
The prior function can be used on any event stream or view and does not have any specific view requirements. The function operates on the order of arrival of events by the event stream or view that provides the events.
The next statement uses a time batch window to compute an average volume for 1 minute of Trade events, posting results every minute. The select-clause employs the prior function to select the current average and the average before the current average:
select average, prior(1, average) from TradeAverages.win:time_batch(1 min).stat:uni(volume)
The syntax of the aggregation functions and the results they produce are shown in below table.
Table 7.2. Syntax and results of aggregate functions
Aggregate Function | Result |
---|---|
sum([all|distinct] expression) | Totals the (distinct) values in the expression, returning a value of long, double, float or integer type depending on the expression |
avg([all|distinct] expression) | Average of the (distinct) values in the expression, returning a value of double type |
count([all|distinct] expression) | Number of the (distinct) non-null values in the expression, returning a value of long type |
count(*) | Number of events, returning a value of long type |
max([all|distinct] expression) | Highest (distinct) value in the expression, returning a value of the same type as the expression itself returns |
min([all|distinct] expression) | Lowest (distinct) value in the expression, returning a value of the same type as the expression itself returns |
median([all|distinct] expression) | Median (distinct) value in the expression, returning a value of double type |
stddev([all|distinct] expression) | Standard deviation of the (distinct) values in the expression, returning a value of double type |
avedev([all|distinct] expression) | Mean deviation of the (distinct) values in the expression, returning a value of double type |
Your application may also add its own aggregation function as Section 11.2, “Custom Aggregation Functions” describes.
A user-defined function (UDF) can be invoked anywhere as an expression itself or within an expresson. The function must simply be a public static method that the classloader can resolve at statement creation time. The engine resolves the function reference at statement creation time and verifies parameter types.
User-defined functions can be also be invoked on instances of an event: Please see Section 4.4.4, “Using the Stream Name” to invoke event instance methods on a named stream.
The example below assumes a class MyClass that exposes a public static method myFunction accepting 2 parameters, and returing a numeric type such as double.
select 3 * com.mycompany.MyClass.myFunction(price, volume) as myValue from StockTick.win:time(30 sec)
User-defined functions also take array parameters as this example shows. The section on Section 6.5, “Array Definition Operator” outlines in more detail the types of arrays produced.
select * from RFIDEvent where com.mycompany.rfid.MyChecker.isInZone(zone, {10, 20, 30})
Java class names have to be fully qualified (e.g. java.lang.Math) but Esper provides a mechanism for user-controlled imports of classes and packages as outlined in Section 10.4.5, “Class and package imports”.
User-defined functions can return any value including null, Java objects or arrays. Therefore user-defined functions can serve to transform, convert or map events, or to extract information and assemble further events.
The following statement is a simple pattern that looks for events of type E1 that are followed by events of type E2. It assigns the tags "e1" and "e2" that the function can use to assemble a final event for output:
select MyLib.mapEvents(e1, e2) from pattern [every e1=E1 -> e2=E2]
A function that converts from one event type to another event type is shown in the next example. The first statement declares a stream that consists of MyEvent events. The second statement employs a conversion function to convert MyOtherEvent events to events of type MyEvent:
insert into MyStream select * from MyEvent insert into MyStream select MyLib.convert(other) from MyOtherEvent as other
In the example above, assuming the event classes MyEvent and MyOtherEvent are Java classes and not aliases, the static method should have the following footprint:
public static MyEvent convert(MyOtherEvent otherEvent)