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 library methods as single-row functions, and also features built-in single-row functions. In addition, Esper allows instance method invocations on named streams.
You may also register your own single-row function name with the engine so that your EPL statements become less cluttered. This is described in detail in Section 15.3, “Single-Row Functions”. Single-row functions that return an object can be chained.
Esper auto-imports the following library namespaces:
System
System.Collections
System.Text
Thus 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 type names have to be fully qualified (e.g. System.Math) but Esper provides a mechanism for user-controlled imports of classes and packages as outlined in Section 13.4.5, “Class and package imports”.
The below table outlines the built-in single-row functions available.
Table 8.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 or all properties of a previous event, relative to the event order within a data window, or according to an optional index parameter (N) the positional Nth-from-last value. |
prevtail(expression, event_property) | Returns a property value or all properties of the first event in a data window relative to the event order within a data window, or according to an optional index parameter (N) the positional Nth-from-first value. |
prevwindow(event_property) | Returns a single property value of all events or all properties of all events in a data window in the order that reflects the sort order of the data window. |
prevcount(event_property) | Returns the count of events (number of data points) in a data window. |
prior(integer, event_property) | Returns a property value of a prior event, relative to the natural order of arrival of events |
typeof(expression) | If expression is a stream name, returns the event type name of the evaluated event, often used with variant streams. If expression is a property name or expression, returns the name of the expression result type. |
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 built-in types: int, long, ulong, byte, sbyte, short, ushort, char, decimal, double, float, string, where string is a short notation for System.String. The type name is not case-sensitive. For example:
cast(price, double)
The fully-qualified type 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 System.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 utilizes an internal cast conversion mecuanism to convert numeric types, if required.
For casts to string or System.String, the function calls System.Convert.ToString on the expression result.
For casts to other objects including application objects, the cast function considers a type'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 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 built-in types: int, long, byte, short, char, double, float, string, where string is a short notation for System.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 or all event properties of a previous event. For data windows that introduce a sort order other then the order of arrival, such as the sorted data window and the time order data window, the function returns the event at the specified position.
The prev function is not an aggregation function and therefore does not return results per group when used with group by. Please consider the last, lastever or nth aggregation functions instead as described in Section 8.2.2, “Data Window Aggregation Functions”. You must use an aggregation function instead of prev when querying a named window.
The first parameter to the prev function is an index parameter and denotes the i-th previous event, in the order established by the data window. If no index is provided, the default index is 1 and the function returns the previous event. The second parameter is a property name or stream name. If specifying a property name, the function returns the value for the previous event property value. If specifying a stream name, the function returns the previous event underlying object.
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)
By using the stream alias in the previous function, the next example selects the trade event itself that is immediately previous to the current Trade event
select prev(1, trade) from Trade.win:length(10) as trade
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(3, volume desc)
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(10, volume desc), RankSelectionEvent unidirectional
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)
An alternative form of the prev function allows the index to not appear or appear after the property name if the index value is a constant and not an expression:
select prev(1, symbol) from Trade // ... equivalent to ... select prev(symbol) from Trade // ... and ... select prev(symbol, 1) from Trade
The combination of the prev function and std:groupwin view returns the property value for a previous event in the given data window group.
The following example returns for each event the current smallest price per symbol:
select symbol, prev(0, price) as topPricePerSymbol from Trade.std:groupwin(symbol).ext:sort(1, price asc)
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 std:groupwin and data window view, without any additional sub-views. See Chapter 11, 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:groupwin 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 prevtail function returns the property value or all event properties of the positional-first event in a data window. For data windows that introduce a sort order other then the order of arrival, such as the sorted data window and the time order data window, the function returns the first event at the specified position.
The prevtail function is not an aggregation function and therefore does not return results per group when used with group by. Please consider the first, firstever or window aggregation functions instead as described in Section 8.2.2, “Data Window Aggregation Functions”. You must use an aggregation function instead of prevtail when querying a named window.
The first parameter is an index parameter and denotes the i-th from-first event in the order established by the data window. If no index is provided the default is zero and the function returns the first event in the data window. The second parameter is a property name or stream name. If specifying a property name, the function returns the value for the previous event property value. If specifying a stream name, the function returns the previous event underlying object.
This example selects the value of the price property of the first (oldest) event held in the length window:
select prevtail(price) from Trade.win:length(10)
By using the stream alias in the prevtail function, the next example selects the trade event itself that is the second event held in the length window:
select prevtail(1, trade) from Trade.win:length(10) as trade
Since the prevtail 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 smallest, second-smallest and third-smallest volume.
select prevtail(0, symbol), prevtail(1, symbol), prevtail(2, symbol) from Trade.ext:sort(3, volume asc)
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 prevtail(rank, symbol) from Trade.ext:sort(10, volume asc), RankSelectionEvent unidirectional
The prev function returns a null value if the data window does not currently holds positional-first or the Nth-from-first event. For batch data windows the value returned is relative to the current batch.
The following example returns the first and second symbol value in the batch:
select prevtail(0, symbol), prevtail(1, symbol) from Trade.win:time_batch(1 min)
An alternative form of the prevtail function allows the index to not appear or appear after the property name if the index value is a constant and not an expression:
select prevtail(1, symbol) from Trade // ... equivalent to ... select prevtail(symbol) from Trade // ... and ... select prevtail(symbol, 1) from Trade
The combination of the prevtail function and std:groupwin view returns the property value for a positional first event in the given data window group.
Let's look at an example. This statement outputs the oldest price per symbol retaining the last 10 prices per symbol:
select symbol, prevtail(0, price) as oldestPrice from Trade.std:groupwin(symbol).win:length(10)
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 std:groupwin and data window view, without any additional sub-views. See Chapter 11, EPL Reference: Views for built-in data window views.
The prevwindow function returns property values or all event properties for all events in a data window. For data windows that introduce a sort order other then the order of arrival, such as the sorted data window and the time order data window, the function returns the event data sorted in that order, otherwise it returns the events sorted by order of arrival with the newest arriving event first.
The prevwindow function is not an aggregation function and therefore does not return results per group when used with group by. Please consider the window aggregation function instead as described in Section 8.2.2, “Data Window Aggregation Functions”. You must use an aggregation function instead of prevwindow when querying a named window.
The single parameter is a property name or stream name. If specifying a property name, the function returns the value of the event property for all events held by the data window. If specifying a stream name, the function returns the event underlying object for all events held by the data window.
This example selects the value of the price property of all events held in the length window:
select prevwindow(price) from Trade.win:length(10)
By using the stream alias in the prevwindow function, the next example selects all trade events held in the length window:
select prevwindow(trade) from Trade.win:length(10) as trade
When used with a data window that introduces a certain sort order, the prevwindow function returns events sorted according to that sort order.
The next statement outputs for every arriving event the current 10 underying trade event objects that have the largest volume:
select prevwindow(trade) from Trade.ext:sort(10, volume desc) as trade
The prevwindow function returns a null value if the data window does not currently hold any events.
The combination of the prevwindow function and std:groupwin view returns the property value(s) for all events in the given data window group.
This example statement outputs all prices per symbol retaining the last 10 prices per symbol:
select symbol, prevwindow(price) from Trade.std:groupwin(symbol).win:length(10)
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 std:groupwin and data window view, without any additional sub-views. See Chapter 11, EPL Reference: Views for built-in data window views.
The prevcount function returns the number of events held in a data window.
The prevcount function is not an aggregation function and therefore does not return results per group when used with group by. Please consider the count(*) aggregation function instead as described in Section 8.2, “Aggregate Functions”. You must use an aggregation function instead of prevcount when querying a named window.
The single parameter is a property name or stream name of the data window to return the count for.
This example selects the number of data points for the price property held in the length window:
select prevcount(price) from Trade.win:length(10)
By using the stream alias in the prevcount function the next example selects the count of trade events held in the length window:
select prevcount(trade) from Trade.win:length(10) as trade
The combination of the prevcount function and std:groupwin view returns the count of events in the given data window group.
This example statement outputs the number of events retaining the last 10 events per symbol:
select symbol, prevcount(price) from Trade.std:groupwin(symbol).win:length(10)
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 std:groupwin and data window view, without any additional sub-views. See Chapter 11, EPL Reference: Views for built-in data window views.
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. The second parameter is a property name or stream name. If specifying a property name, the function returns the property value for the prior event. If specifying a stream name, the function returns the prior event underlying object.
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
By using the stream alias in the prior function, the next example selects the trade event itself that is immediately prior to the current Trade event
select prior(1, trade) from Trade as 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 typeof function, when parameterized by a stream name, returns the event type name of the evaluated event which can be useful with variant streams. When parameterized by an expression or property name, the function returns the type name of the expression result or null if the expression result is null.
In summary, the function determines the return type of the expression at runtime by evaluating the expression and returns the type name of the expression result.
The typeof function is often used in conjunction with variant streams. A variant stream is a predefined stream into which events of multiple disparate event types can be inserted. The typeof function, when passed a stream name alias, returns the name of the event type of each event in the stream.
The following example elaborates on the use of variant streams with typeof. The first statement declares a variant stream SequencePatternStream:
create variant schema SequencePatternStream as *
The next statement inserts all order events and is followed by a statement to insert all product events:
insert into SequencePatternStream select * from OrderEvent;
insert into SequencePatternStream select * from PriceEvent;
This example statement returns the event type name for each event in the variant stream:
select typeof(sps) from SequencePatternStream as sps
The next example statement detects a pattern by utilizing the typeof function to find pairs of order event immediately followed by product event:
select * from SequencePatternStream match_recognize( measures A as a, B as b pattern (A B) define A as typeof(A) = "OrderEvent", B as typeof(B) = "ProductEvent" )
When passing a property name to the typeof function, the function evaluates whether the property type is event type (a fragment event type). If the property type is event type, the function returns the type name of the event in the property value or null if not provided. If the property type is not event type, the function returns the simple class name of the property value.
When passing an expression to the typeof function, the function evaluates the expression and returns the simple class name of the expression result value or null if the expression result value is null.
This example statement returns the simple class name of the value of the dynamic property prop of events in stream MyStream, or a null value if the property is not found for an event or the property value itself is null:
select typeof(prop?) from MyStream
When using subclasses or interface implementations as event classes or when using Map-event type inheritance, the function returns the event type name provided when the class or Map-type event was registered, or if the event type was not registered, the function returns the fully-qualified class name.
Aggregation functions return a single value from a collection of input values. The group by keywords are often used in conjunction with aggregation functions to group the result-set by one or more columns.
The EPL language extends the standard SQL aggregation functions by useful aggregation functions that can track a data window or compute event rates. Your application may also add its own aggregation function as Section 15.5, “Aggregation Functions” describes.
Aggregation values are always computed incrementally: Insert and remove streams result in aggregation value changes. The exceptions are on-demand queries and joins when using the unidirectional keyword. Aggregation functions are optimized to retain the minimal information necessary to compute the aggregated result.
Aggregation functions can also be used with unbound streams when no data window is specified.
The SQL-standard aggregation functions are shown in below table.
Table 8.2. Syntax and results of SQL-standard aggregation functions
Aggregate Function | Result |
---|---|
avedev([all|distinct] expression) | Mean deviation of the (distinct) values in the expression, returning a value of double type. |
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. |
median([all|distinct] expression) | Median (distinct) value in the expression, returning a value of double type. Double Not-a-Number (NaN) values are ignored in the median computation. |
min([all|distinct] expression) | Lowest (distinct) value in the expression, returning a value of the same type as the expression itself returns. |
stddev([all|distinct] expression) | Standard deviation of the (distinct) values in the expression, returning a value of double type. |
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. |
If your application provides double-type values to an aggregation function, avoid using Not-a-Number (NaN) and infinity. Also when using double-type values, round-off errors (or rounding errors) may occur due to double-type precision. Consider rounding your result value to the desired precision.
The first, last and window aggregation functions return event properties of events present in a stream's data window. They are useful when information about current data window contents is required.
When comparing the last aggregation function to the prev function, the differences are as follows. The prev function is not an aggregation function and thereby not sensitive to the presence of group by. The prev function accesses data window contents directly and respects the sort order of the data window. The last aggregation function returns results based on arrival order and tracks data window contents in a separate shared data structure.
When comparing the first aggregation function to the prevtail function, the differences are as follows. The prevtail function is not an aggregation function and thereby not sensitive to the presence of group by. The prevtail function accesses data window contents directly and respects the sort order of the data window. The first aggregation function returns results based on arrival order and tracks data window contents in a separate shared data structure.
When comparing the window aggregation function to the prevwindow function, the differences are as follows. The prevwindow function is not an aggregation function and thereby not sensitive to the presence of group by. The prevwindow function accesses data window contents directly and respects the sort order of the data window. The window aggregation function returns results based on arrival order and tracks data window contents in a separate shared data structure.
When comparing the count aggregation function to the prevcount function, the differences are as follows. The prevcount function is not an aggregation function and thereby not sensitive to the presence of group by.
When comparing the last aggregation function to the nth aggregation function, the differences are as follows. The nth aggregation function does not consider out-of-order deletes (for example with on-delete and sorted windows) and does not revert to the prior expression value when the last event or nth-event was deleted from a data window. The last aggregation function tracks the data window and reflects out-of-order deletes.
From an implementation perspective, the first, last and window aggregation functions share a common data structure for each stream.
The synopsis for the first aggregation function is:
first(*|stream.*|value_expression [, index_expression])
The first aggregation function returns properties of very first event in the data window. When used with group by, it returns properties of the first event in the data window for each group. When specifying an index expression, the function returns properties of the Nth-subsequent event to the first event, all according to order of arrival.
The first parameter to the function is required and defines the event properties or expression result to return. The second parameter is an optional index_expression that must return an integer value used as an index to evaluate the Nth-subsequent event to the first event.
You may specify the wildcard (*) character in which case the function returns the underlying event of the single selected stream. For joins and subqueries you must use the stream wildcard syntax below.
You may specify the stream name and wildcard (*) character in the stream.* syntax. This returns the underlying event for the specified stream.
You may specify a value_expression to evaluate for the first event. The value expression may not select properties from multiple streams.
The index_expression is optional. If no index expression is provided, the function returns the first event. If present, the function evaluates the index expression to determine the value for N, and evaluates the Nth-subsequent event to the first event. A value of zero returns the first event and a value of 1 returns the event subsequent to the first event. You may not specify event properties in the index expression.
The function returns null if there are no events in the data window or when the index is larger then the number of events held in the data window. If used with group by, it returns null if there are no events in the data window for that group or when the index is larger then the number of events held in the data window for that group.
To explain, consider the statement below which selects the underlying event of the first sensor event held by the length window of 2 events.
select first(*) from SensorEvent.win:length(2)
Assume event E1, event E2 and event E3 are of type SensorEvent. When event E1 arrives the statement outputs the underlying event E1. When event E2 arrives the statement again outputs the underlying event E1. When event E3 arrives the statement outputs the underlying event E2, since event E1 has left the data window.
The stream wildcard syntax is useful for joins and subqueries. This example demonstrates a subquery that returns the first SensorEvent when a DoorEvent arrives:
select (select first(se.*) from SensorEvent.win:length(2) as se) from DoorEvent
The following example shows the use of an index expression. The output value for f1 is the temperature property value of the first event in the data window, the value for f2 is the temperature property value of the second event in the data window:
select first(temperature, 0) as f1, first(temperature, 1) as f2 from SensorEvent.win:time(10 sec)
The synopsis for the last aggregation function is:
last(*|stream.*|value_expression [, index_expression])
The last aggregation function returns properties of the very last event in the data window. When used with group by, it returns properties of the last event in the data window for each group. When specifying an index expression, the function returns properties of the Nth-prior event to the last event, all according to order of arrival.
Similar to the first aggregation function described above, you may specify the wildcard (*) character or stream name and wildcard (*) character or a value_expression to evaluate for the last event.
The index_expression is optional. If no index expression is provided, the function returns the last event. If present, the function evaluates the index expression to determine the value for N, and evaluates the Nth-prior event to the last event. A value of zero returns the last event and a value of 1 returns the event prior to the last event. You may not specify event properties in the index expression.
The function returns null if there are no events in the data window or when the index is larger then the number of events held in the data window. If used with group by, it returns null if there are no events in the data window for that group or when the index is larger then the number of events held in the data window for that group.
The next statement selects the underlying event of the first and last sensor event held by the time window of 10 seconds:
select first(*), last(*) from SensorEvent.win:time(10 sec)
The statement shown next selects the last temperature (f1) and the prior-to-last temperature (f1) of sensor events in the last 10 seconds:
select last(temperature, 0) as f1, select last(temperature, 1) as f2 from SensorEvent.win:time(10 sec)
The synopsis for the window aggregation function is:
window(*|stream.*|value_expression)
The window aggregation function returns properties of all events in the data window. When used with group by, it returns properties of all events in the data window for each group.
Similar to the first aggregation function described above, you may specify the wildcard (*) character or stream name and wildcard (*) character or a value_expression to evaluate for all events.
The function returns null if there are no events in the data window. If used with group by, it returns null if there are no events in the data window for that group.
The next statement selects the underlying event of all events held by the time window of 10 seconds:
select window(*) from SensorEvent.win:time(10 sec)
The window aggregation function requires that your stream is bound by a data window. You may not use the window aggregation function on unbound streams with the exception of on-demand queries.
Esper provides the following additional aggregation functions beyond those in the SQL standard:
Table 8.3. Syntax and results of EPL aggregation functions
Aggregate Function | Result |
---|---|
firstever(expression) | The firstever aggregation function returns the very first value ever. If used with group by it returns the first value ever for that group. If used with a data window, the result of the function does not change as data points leave a data window. Use the first or prevtail function to return values relative to a data window. |
lastever(expression) | Returns the last value or last value per group, if used with group by. This sample statement outputs the total price, the first price and the last price per symbol for the last 30 seconds of events and every 5 seconds: select symbol, sum(price), lastever(price), firstever(price) from StockTickEvent.win:time(30 sec) group by symbol output every 5 sec If used with a data window, the result of the function does not change as data points leave a data window (for example when all data points leave the data window). Use the last or prev function to return values relative to a data window. |
leaving() | Returns true when any remove stream data has passed, for use in the having clause to output only when a data window has filled. The leaving aggregation function is useful when you want to trigger output after a data window has a remove stream data point. Use the output after syntax as an alternative to output after a time interval. This sample statement uses leaving() to output after the first data point leaves the data window, ignoring the first datapoint: select symbol, sum(price) from StockTickEvent.win:time(30 sec) having leaving() |
nth(expression, N_index) | Returns the Nth oldest element; If N=0 returns the most recent value. If N=1 returns the value before the most recent value. If N is larger than the events held in the data window for this group, returns null. A maximum N historical values are stored, so it can be safely used to compare recent values in large views without incurring excessive overhead. As compared to the prev row function, this aggregation function works within the current group by group, see Section 3.7.2, “Output for Aggregation and Group-By”. This statement outputs every 2 seconds the groups that have new data and their last price and the previous-to-last price: select symbol, nth(price, 1), last(price) from StockTickEvent group by symbol output last every 2 sec |
rate(number_of_seconds) | Returns an event arrival rate per second over the provided number of seconds, computed based on engine time. Returns null until events fill the number of seconds. Useful with output snapshot to output a current rate. Does not require a data window onto the stream(s). A sample statement to output, every 2 seconds, the arrival rate per second considering the last 10 seconds of events is shown here: select rate(10) from StockTickEvent output snapshot every 2 sec The aggregation function retains an engine timestamp value for each arriving event. |
rate(timestamp_property[, accumulator]) | Returns an event arrival rate over the data window including the last remove stream event. The timestamp_property is the name of a long-type property of the event that provides a timestamp value. The first parameter is a property name or expression providing millisecond timestamp values. The optional second parameter is a property or expression for computing an accumulation rate: If a value is provided as a second parameter then the accumulation rate for that quantity is returned (e.g. turnover in dollars per second). Requires a data window declared onto the stream. Returns null until events start leaving the window. This sample statement outputs event rate for each group (symbol) with fixed sample size of four events (and considering the last event that left). The timestamp event property must be part of the event for this to work. select colour, rate(timestamp) as rate from StockTickEvent.std:groupwin(symbol).win:length(4) group by symbol |
Built-in aggregation functions can be disabled via configuration (see Section 13.4.18.4, “Extended Built-in Aggregation Functions”). A custom aggregation function of the same name as a built-on function may be registered to override the built-in function.
A user-defined function (UDF) is a single-row function that 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.
You may register your own single-row function name for the user-defined function so that your EPL statements are less cluttered. This is described in detail in Section 15.3, “Single-Row Functions”.
User-defined functions can be also be invoked on instances of an event: Please see Section 4.4.5, “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 7.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})
Type names have to be fully qualified (e.g. System.Math) but Esper provides a mechanism for user-controlled imports of classes and packages as outlined in Section 13.4.5, “Class and package imports”.
User-defined functions can return any value including null, native 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]
User-defined functions may also be chained: If a user-defined function returns an object then the object can itself be the target of the next function call and so on.
Assume that there is a calculator function in the MyLib class that returns a class which provides the Search method taking two parameters. The EPL that takes the result of the Calculator property and that calls the Search method on the result and returns its return value is shown below:
select MyLib.Calculator.Search(zonevariable, zone) from RFIDEvent]
A user-defined function should be implemented thread-safe.
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 types, the static method should have the following footprint:
public static MyEvent Convert(MyOtherEvent otherEvent)
For user-defined functions that take no parameters or only constants as parameters the engine automatically caches the return result of the function, and invokes the function only once. This is beneficial to performance if your function indeed returns the same result for the same input parameters.
You may disable caching of return values of user-defined functions via configuration as described in Section 13.4.18.3, “User-Defined Function or Static Method Cache”.
EPL follows CLR standards in terms of widening, performing widening automatically in cases where widening type conversion is allowed without loss of precision, for both boxed and primitive types.
When user-defined functions are overloaded, the function with the best match is selected based on how well the arguments to a function can match up with the parameters, giving preference to the function that requires the least number of widening conversions.
Boxing and unboxing of arrays is not supported in UDF. For example, an array of int? and an array of int are not compatible types.
When using {} array syntax in EPL, the resulting type is always a boxed type: "{1, 2}" is an array of Integer (and not int since it may contain null values), "{1.0, 2d}" is an array of double and "{'A', "B"}" is an array of string, while "{1, "B", 2.0}" is an array of Object (Object[]).