This chapter outlines the views that are built into Esper. All views can be arbitrarily combined as many of the examples below show. The section on Chapter 3, Processing Model provides additional information on the relationship of views, filtering and aggregation.
This view is a moving length window extending the specified number of elements into the past. The view takes a single numeric parameter that defines the window size:
win:length(size)
The below example calculates univariate statistics on price for the last 5 stock ticks for symbol IBM.
select * from StockTickEvent(symbol='IBM').win:length(5).stat:uni('price')
The next example keeps a length window of 10 events of stock trade events, with a separate window for each symbol. The statistics on price is calculated only for the last 10 events for each symbol.
select * from StockTickEvent.std:groupby('symbol').win:length(10).stat:uni('price')
This window view buffers events and releases them when a given minimum number of events has been collected. The view takes the number of events to batch as a parameter:
win:length_batch(size)
The next statement buffers events until a minimum of 5 events have collected. Listeners to updates posted by this view receive updated information only when 5 or more events have collected.
select * from StockTickEvent.win:length_batch(5)
This view is a moving time window extending from the specified time interval into the past based on the system time. This view takes a time period (see Section 4.2.1, “Specifying Time Periods”) or a number of seconds as a parameter:
win:time(time period)
win:time(number of seconds)
For the IBM stock tick events in the last 1 second, calculate statistics on price.
select * from StockTickEvent(symbol='IBM').win:time(1 sec).stat:uni('price')
The same statement rewritten to use a parameter supplying number-of-seconds is:
select * from StockTickEvent(symbol='IBM').win:time(1).stat:uni('price')
The following time windows are equivalent specifications:
win:time(2 minutes 5 seconds) win:time(125 sec) win:time(125)
Similar to the time window, this view is a moving time window extending from the specified time interval into the past, but based on the millisecond time value supplied by an event property. The view takes two parameters: the name of the event property to return the long-typed timestamp value, and a time period or a number of seconds:
win:time(timestamp_property_name, time_period)
win:time(timestamp_property_name, number_of_seconds)
This view holds stock tick events of the last 10 seconds based on the timestamp property in StockTickEvent.
select * from StockTickEvent.win:ext_timed('timestamp', 10 seconds)
This window view buffers events and releases them every specified time interval in one update. The view takes a time period or a number of seconds as a parameter.
win:time_batch(time_period)
win:time_batch(number_of_seconds)
The below example batches events into a 5 second window releasing new batches every 5 seconds. Listeners to updates posted by this view receive updated information only every 5 seconds.
select * from StockTickEvent.win:time_batch(5 sec)
The unique view is a view that includes only the most recent among events having the same value for the specified field:
std:unique(event_property_name)
The view acts as a length window of size 1 for each distinct value of the event property. It thus posts as old events the prior event of the same property value, if any.
The below example creates a view that retains only the last event per symbol.
select * from StockTickEvent.std:unique('symbol')
This view groups events into sub-views by the value of the specified field. The view takes a single property name to supply the group-by values, or a list of property names as the synopsis shows:
std:groupby(property_name)
std:groupby({property_name [, property_name ...] })
This example calculates statistics on price separately for each symbol.
select * from StockTickEvent.std:groupby('symbol').stat:uni('price')
The group-by view can also take multiple fields to group by. This example calculates statistics on price for each symbol and feed.
select * from StockTickEvent.std:groupby({'symbol', 'feed'}).stat:uni('price')
The order in which the group-by view appears within sub-views of a stream controls the data the engine derives from events for each group. The next 2 statements demonstrate this using a length window.
This example keeps a length window of 10 events of stock trade events, with a separate length window for each symbol. The engine calculates statistics on price for the last 10 events for each symbol. During runtime, the engine actually allocates a separate length window for each new symbol arriving.
select * from StockTickEvent.std:groupby('symbol').win:length(10).stat:uni('price')
By putting the group-by view in position after the length window, we can change the semantics of the query. The query now returns the statistics on price per symbol for only the last 10 events across all symbols. Here the engine allocates only one length window for all events.
select * from StockTickEvent.win:length(10).std:groupby('symbol').stat:uni('price')
We have learned that by placing the group-by view before other views, these other views become part of the grouped set of views. The engine dynamically allocates a new view instance for each subview, every time it encounters a new group key such as a new value for symbol. Therefore, in std:groupby('symbol').win:length(10) the engine allocates a new length window for each distinct symbol. However in win:length(10).std:groupby('symbol') the engine maintains a single length window.
Multiple group-by views can also be used in the same statement. The statement below groups by symbol and feed. As the statement declares the time window after the group-by view for symbols, the engine allocates a new time window per symbol however reports statistics on price per symbol and feed. The query results are statistics on price per symbol and feed for the last 1 minute of events per symbol (and not per feed).
select * from StockTickEvent.std:groupby('symbol').win:time(1 minute) .std:groupby('feed').stat:uni('price')
Last, we consider the permutation where the time window is declared after the group-by. Here, the query results are statistics on price per symbol and feed for the last 1 minute of events per symbol and feed.
select * from StockTickEvent.std:groupby({'symbol', 'feed'}) .win:time(1 minute).stat:uni('price')
This view simply posts the number of events received from a stream or view. The synopsis is simply:
std:size()
The view posts a single long-typed property named size. The view posts the prior size as old data, and the current size as new data to update listeners of the view. Via the iterator method of the statement the size value can also be polled (read).
When combined with a data window view, the size view reports the current and prior number of events in the data window. This example reports the number of tick events within the last 1 minute:
select size from StockTickEvent.win:time(1 min).std:size()
The size view is also useful in conjunction with a group-by view to count the number of events per group. The EQL below returns the number of events per symbol.
select size from StockTickEvent.std:groupby('symbol').std:size()
When used without a data window, the view simply counts the number of events:
select size from StockTickEvent.std:size()
All views can be used with pattern statements as well. The next EQL snippet shows a pattern where we look for tick events followed by trade events for the same symbol. The size view counts the number of occurances of the pattern.
select size from pattern[every s=StockTickEvent -> TradeEvent(symbol=s.symbol)].std:size()
This view exposes the last element of its parent view:
std:lastevent()
The view acts as a length window of size 1. It thus posts as old events the prior event in the stream, if any.
This example statement retains statistics calculated on stock tick price for the symbol IBM.
select * from StockTickEvent(symbol='IBM').stat:uni('price').std:lastevent()
This view calculates univariate statistics on an event property. The view takes a single event property name as a parameter. The event property must be of numeric type:
stat:uni(event_property_name)
Table 8.1. Univariate statistics derived properties
Property Name | Description |
---|---|
count | Number of values |
sum | Sum of values |
average | Average of values |
variance | Variance |
stdev | Sample standard deviation (square root of variance) |
stdevpa | Population standard deviation |
The below example selects the standard deviation on price for stock tick events for the last 10 events.
select stdev from StockTickEvent.win:length(10).stat:uni('price')
This view calculates regression on two event properties. The view takes two event property names as parameters. The event properties must be of numeric type:
stat:linest(event_property_name_1, event_property_name_2)
Calculate slope and y-intercept on price and offer for all events in the last 10 seconds.
select slope, YIntercept from StockTickEvent.win:time(10 seconds).stat:linest('price', 'offer')
This view calculates the correlation value on two event properties. The view takes two event property names as parameters. The event properties must be of numeric type:
stat:correl(event_property_name_1, event_property_name_2)
Table 8.3. Correlation derived properties
Property Name | Description |
---|---|
correlation | Correlation between two event properties |
Calculate correlation on price and offer over all stock tick events for IBM.
select correlation from StockTickEvent(symbol='IBM').stat:correl('price', 'offer')
This view returns the weighted average given a weight field and a field to compute the average for. The view takes two event property names as parameters. The event properties must be of numeric type:
stat:weighted_avg(event_property_name_field, event_property_name_weight)
A statement that derives the volume-weighted average price for the last 3 seconds:
select average from StockTickEvent(symbol='IBM').win:time(3 seconds).stat:weighted_avg('price', 'volume')
This view works similar to the std:groupby views in that it groups information by one or more event properties. The view accepts 3 or more parameters: The first parameter to the view defines the univariate statistics values to derive. The second parameter is the property name to derive data from. The remaining parameters supply the event property names to use to derive dimensions.
stat:cube(values_to_derive, property_name_datapoint, property_name_column)
stat:cube(values_to_derive, property_name_datapoint, property_name_column, property_name_row)
stat:cube(values_to_derive, property_name_datapoint, property_name_column, property_name_row, property_name_page)
Table 8.5. Multi-dim derived properties
Property Name | Description |
---|---|
cube | The cube following the net.esper.view.stat.olap.Cube interface |
The example below derives the count, average and standard deviation latency of service measurement events per customer.
select cube from ServiceMeasurement.stat:cube({‘count’, ‘average’, ‘stdev’}, 'latency', 'customer')
This example derives the average latency of service measurement events per customer, service and error status for events in the last 30 seconds.
select * from ServiceMeasurement.win:length(30000).stat:cube({‘average’}, 'latency', 'customer', 'service', 'status')
This view sorts by values of the specified event properties and keeps only the top events up to the given size.
The syntax to sort on a single event property is as follows.
std:sort(property_name, is_descending, size)
To sort on a multiple event properties the syntax is as follows.
sort( { property_name, is_descending [ , property_name, is_descending ...] }, size)
The view below sorts on price descending keeping the lowest 10 prices and reporting statistics on price.
select * from StockTickEvent.ext:sort('price', false, 10).stat:uni('price')
The following example sorts events first by price in descending order, and then by symbol name in ascending (alphabetical) order, keeping only the 10 events with the highest price (with ties resolved by alphabetical order of symbol).
select * from StockTickEvent.ext:sort({'price', true, 'symbol', false}, 10)