Chapter 8. EPL Reference: Views

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.

Esper organizes built-in views in namespaces and names. Views that provide sliding or tumbling data windows are in the win namespace. Other most commonly used views are in the std namespace. The ext namespace are views that order events. The stat namespace is used for views that derive statistical data.

Esper distinguishes between data window views and derived-value views. Data windows, or data window views, are views that retain incoming events until an expiry policy indicates to release events.

The next table summarizes data window views:

Table 8.1. Built-in Data Window Views

ViewSyntaxDescription
Length windowwin:length(size)Sliding length window extending the specified number of elements into the past
Length batch windowwin:length_batch(size)Tumbling window that batches events and releases them when a given minimum number of events has been collected
Time windowwin:time(time period)Sliding time window extending the specified time interval into the past
Externally-timed windowwin:ext_timed(timestamp property, time period)Sliding time window, based on the millisecond time value supplied by an event property
Time batch windowwin:time_batch(time period)Tumbling window that batches events and releases them every specified time interval
Time-Length combination batch windowwin:time_length_batch(time period, size)Tumbling multi-policy time and length batch window with flow control options
Time-Accumulating windowwin:time_accum(time period)Sliding time window accumulates events until no more events arrive within a given time interval
Keep-All windowwin:keepall()The keep-all data window view simply retains all events
Sorted windowext:sort(property names, descending, size)Sorts by values of the specified properties and keeps only the top events up to the given size
Time-Order Viewext:time_order(property name, time_period)Orders events that arrive out-of-order, using a timestamp-property supplied by each event to be ordered
Uniquestd:unique(property name(s))Retains only the most recent among events having the same value for the specified property or properties. Acts as a length window of size 1 for each distinct value of the property
Group Bystd:groupby(property name(s))Groups events into sub-views by the value of the specified field or fields, generally used to provide a separate data window per group
Last Eventstd:lastevent()Retains the last event, acts as a length window of size 1

The table below summarizes views that derive information from received events and present the derived information as an insert and remove stream:

Table 8.2. Built-in Derived-Value Views

ViewSyntaxDescription
Sizestd:size()Derives a count of the number of events in a data window, or in an insert stream if used without a data window.
Univariate statisticsstat:uni(property name)Calculates univariate statistics on an event property
Regressionstat:linest(property name, property name)Calculates regression on two event properties
Correlationstat:correl(property name, property name)Calculates the correlation value on two event properties
Weighted averagestat:weighted_avg(property name, property name)Calculates weighted average given a weight field and a field to compute the average for
Multi-dimensional statisticsstat:cube(values to derive, property datapoint, property column, row, page)Groups and calculates statistics by one or more event properties

8.1. A Note on View Parameters

View parameters follow the view namespace name and view name in parenthesis. Where the view parameters include one or more event property names, property names can be placed in single or double quotes, or appear unquoted:

select * from StockTickEvent.stat:groupby(feed).win:length(10)
... equivalent to ...
select * from StockTickEvent.stat:groupby('feed').win:length(10)

Expressions are not allowed as view parameters.

8.1. Window views

8.1.1. Length window (win:length)

This view is a moving (sliding) 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)

8.1.2. Length batch window (win:length_batch)

This window view buffers events (tumbling window) 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)

8.1.3. Time window (win:time)

This view is a moving (sliding) time window extending 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)

8.1.4. Externally-timed window (win:ext_timed)

Similar to the time window, this view is a moving (sliding) time window extending 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:ext_timed(timestamp_property_name, time_period)
win:ext_timed(timestamp_property_name, number_of_seconds)

The key difference comparing the externally-timed window to the regular time window is that the window slides not based on the engine time, but strictly based on the timestamp property value of the events entering the window. The algorithm underlying the view compares the timestamp value of the oldest event with the timestamp value of the newest arriving event on event arrival. If the time interval between the oldest and newest event is larger then the timer period parameter, then the algorithm removes all oldest events tail-first until the difference between the oldest and newest event is within the time interval. The window therefore slides only when events arrive and only considers each event's timestamp property and not engine time.

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)

The externally-timed data window expects to receive event in timestamp order. The view is not useful for ordering events in time order, please us the time-order view instead.

8.1.5. Time batch window (win:time_batch)

This window view buffers events (tumbling window) 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)

8.1.6. Time-Length combination batch window (win:time_length_batch)

This data window view is a combination of time and length batch (tumbling) windows. Similar to the time and length batch windows, this view batches events and releases the batched events when either one of the following conditions occurs, whichever occurs first: the data window has collected a given number of events, or a given time interval has passed.

The view parameters take 2 forms. The first form accepts a time period or number of seconds, and a parameter for the number of events:

win:time_length_batch(time_period or number_of_seconds, number of events)

The next example shows a time-length combination batch window that batches up to 100 events or all events arriving within a 1-second time interval, whichever condition occurs first:

 select * from MyEvent.win:time_length_batch(1 sec, 100)

In this example, if 100 events arrive into the window before a 1-second time interval passes, the view posts the batch of 100 events. If less then 100 events arrive within a 1-second interval, the view posts all events that arrived within the 1-second interval at the end of the interval.

By default, if there are no events arriving in the current interval (insert stream), and no events remain from the prior batch (remove stream), then the view does not post results to listeners. This view allows overriding this default behavior via flow control keywords.

The synopsis of the view with flow control parameters is:

win:time_length_batch(time_period or number_of_seconds, number of events, 
    "flow control keyword [, keyword...]")

The FORCE_UPDATE flow control keyword instructs the view to post an empty result set to listeners if there is no data to post for an interval. The view begins posting no later then after one time interval passed after the first event arrives.

The START_EAGER flow control keyword instructs the view to post empty result sets even before the first event arrives, starting a time interval at statement creation time. As when using FORCE_UPDATE, the view also posts an empty result set to listeners if there is no data to post for an interval, however it starts doing so at time of statement creation rather then at the time of arrival of the first event.

Taking the two flow control keywords in one sample statement, this example presents a view that waits for 10 seconds or reacts when the 5th event arrives, whichever comes first. It posts empty result sets after one interval after the statement is created, and keeps posting an empty result set as no events arrive during intervals:

 select * from MyEvent.win:time_length_batch(10 sec, 5, "FORCE_UPDATE, START_EAGER")

8.1.7. Time-Accumulating window (win:time_accum)

This data window view is a specialized moving (sliding) time window that differs from the regular time window in that it accumulates events until no more events arrive within a given time interval, and only then releases the accumulated events as a remove stream.

The view accepts a single parameter: the time period or number of seconds specifying the length of the time interval during which no events must arrive until the view releases accumulated events. The synopsis is as follows:

win:time_accum(time_period or number_of_seconds)

The next example shows a time-accumulating window that accumulates events, and then releases events if within the time interval no more events arrive:

 select * from MyEvent.win:time_accum(10 sec)

This example accumulates events, until when for a period of 10 seconds no more MyEvent events arrive, at which time it posts all accumulated MyEvent events.

Your application may only be interested in the batches of events as events leave the data window. This can be done simply by selecting the remove stream of this data window, populated by the engine as accumulated events leave the data window all-at-once when no events arrive during the time interval following the time the last event arrived:

 select rstream * from MyEvent.win:time_accum(10 sec)

If there are no events arriving, then the view does not post results to listeners.

8.1.8. Keep-All window (win:keepall)

This keep-all data window view simply retains all events. The view does not remove events from the data window, unless used with a named window and the on delete clause.

The view accepts no parameters. The synopsis is as follows:

win:keepall()

The next example shows a keep-all window that accumulates all events received into the window:

 select * from MyEvent.win:keepall()

Note that since the view does not release events, care must be taken to prevent retained events from using all available memory.

8.2. Standard view set

8.2.1. Unique (std:unique)

The unique view is a view that includes only the most recent among events having the same value for the specified field or list of fields.

The synopsis is:

std:unique(property_name [, property_name ...])

The view acts as a length window of size 1 for each distinct value of the event property, or combination of event property values. It thus posts as old events the prior event of the same property value(s), if any.

The below example creates a view that retains only the last event per symbol.

select * from StockTickEvent.std:unique(symbol)

The next example creates a view that retains the last event per symbol and feed.

select * from StockTickEvent.std:unique(symbol, feed)

8.2.2. Group-By (std:groupby)

This view groups events into sub-views by the value of the specified field or combination of fields. 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 [, 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)

8.2.3. Size (std:size)

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 EPL 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 EPL 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()

8.2.4. Last Event (std:lastevent)

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()

8.3. Statistics views

8.3.1. Univariate statistics (stat:uni)

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.3. Univariate statistics derived properties

Property NameDescription
datapointsNumber of values, equivalent to count(*) for the stream
totalSum of values
averageAverage of values
varianceVariance
stdevSample standard deviation (square root of variance)
stdevpaPopulation 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)

8.3.2. Regression (stat:linest)

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)

Table 8.4. Regression derived properties

Property NameDescription
slopeSlope
YInterceptY Intercept

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)

8.3.3. Correlation (stat:correl)

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.5. Correlation derived properties

Property NameDescription
correlationCorrelation 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)

8.3.4. Weighted average (stat:weighted_avg)

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)

Table 8.6. Weighted average derived properties

Property NameDescription
averageWeighted average

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)

8.3.5. Multi-dimensional statistics (stat:cube)

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.7. Multi-dim derived properties

Property NameDescription
cubeThe cube following the com.espertech.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({‘datapoints’, ‘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)

8.4. Extension View Set

8.4.1. Sorted Window View (ext:sort)

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.

ext: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)

8.4.2. Time-Order View (ext:time_order)

This view orders events that arrive out-of-order, using a timestamp-property supplied by each event to be ordered, and by comparing the event timestamp to engine system time.

The syntax for this view is as follows.

ext:time_order(timestamp_property_name, time_period or number_of_seconds)

The first parameter to the view is the name of the property on the event that supplies the timestamp value. The timestamp is expected to be a long-typed millisecond value that denotes an event's time of consideration by the view. This is typically the time of arrival. The second parameter is a number of seconds or the time period specifying the time interval that an arriving event should maximally be held, in order to consider older events arriving at a later time.

Since the view compares an event's timestamp property to engine time, the view requires that the event timestamp values and current engine time are both following the same clock. Therefore, to the extend that the clocks that originated both timestamps differ, the view may produce inaccurate results.

As an example, the next statement uses the arrival_time property of MyTimestampedEvent events to order and release events by arrival time:

insert into ArrivalTimeOrderedStream
select rstream * from MyTimestampedEvent.ext:time_order(arrival_time, 10 sec)

In the example above, the arrival_time property holds a long-typed timestamp value in milliseconds. On arrival of an event, the engine compares the timestamp value of each event to the tail-time of the window. The tail-time of the window is, in this example, 10 seconds before engine time (continuously sliding). If the timestamp value indicates that the event is older then the tail-time of the time window, the event is released immediately in the remove stream. If the timestamp value indicates that the event is newer then the tail-time of the window, the view retains the event until engine time moves such that the event timestamp is older then tail-time.

The examples thus holds each arriving event in memory anywhere from zero seconds to 10 seconds, to allow for older events (considering arrival time timestamp) to arrive. In other words, the view holds an event with an arrival time equal to engine time for 10 seconds. The view holds an event with an arrival time that is 2 seconds older then engine time for 8 seconds. The view holds an event with an arrival time that is 10 or more seconds older then engine time for zero seconds, and releases such (old) events immediately into the remove stream.

The insert stream of this sliding window consists of all arriving events. The remove stream of the view is ordered by timestamp value: The event that has the oldest timestamp value is released first, followed by the next newer events. Note the statement above uses the rstream keyword to select ordered events only and uses the insert into clause to makes such ordered stream available for subsequent statements to use.

It is up to your application to populate the timestamp property into your events for consideration by the view. The view also works well if you use externally-provided time via timer events.


© 2007 EsperTech Inc. All Rights Reserved