www.espertech.comDocumentation
Aggregation methods are methods that work with aggregations to perform lookups into aggregation state.
Aggregation methods are stateless and the use of aggregation methods alone does not cause the runtime to retain any events or other state.
There are four types of aggregations that offer aggregation methods:
window aggregation, see Section 10.2.2.8, “Window Aggregation Function”.
sorted aggregation, see Section 10.2.2.7, “Sorted Aggregation Function”.
countminsketch aggregation, see Section 10.2.3, “Approximation Aggregation Functions”.
Aggregation methods are handy when an aggregation organizes events or other data for further lookup. For example, the sorted aggregation organizes events according to sort criteria
and offers operations for floor, ceiling, higher and lower keys, and many other operations.
Each aggregation makes different aggregation methods available. For example, sorted provides the higherKey aggregation method. The higherKey method is only available for
use with the sorted aggregation and not with the window aggregation.
You may use aggregation methods together with aggregation functions without tables, and you may use aggregation methods with table columns that hold aggregations. Further examples are provided below.
You may also use aggregation methods with enumeration methods, date-time methods and the dot-syntax to access event properties.
The syntax for aggregation methods is the same syntax as for any chained invocation:
input_aggregation.aggregation_method_name( [method_parameter [, method_parameter [,...]]]) .[ [other_method(...) [...]] | property_name]
Following the input_aggregation input aggregation (options outlined below), is the . (dot) operator and the aggregation_method_name aggregation method name.
It follows, in parenthesis, a comma-separated list of method parameter expressions. Additional enumeration or date-time methods can be chained thereafter.
An event property name can follow for those aggregation or enumeration methods returning an event-typed (non-scalar) element.
The examples in this section focus on the sorted aggregation.
You may use aggregation methods with the respective aggregation function.
The following statement outputs the greatest price strictly less than the current order event price, or null if there is no such value, considering the last 10 minutes of order events.
select sorted(price).lowerKey(price) as lowerPrice from OrderEvent#time(10 minutes)
You may use aggregation methods with table columns.
The sample statement below creates a simple un-keyed table that keeps order events sorted by price:
create table OrderPrices(prices sorted(price) @type('OrderEvent'))
The next statement aggregates order events into the table column prices considering the last 10 minutes of order events (the example specifies priority 1 so the runtime always updates the table before other selects):
@Priority(1) into table OrderPrices select sorted(*) as prices from OrderEvent#time(10 minutes)
Now the prices column of table OrderPrices is ready. The runtime keeps it continually updated from the last 10 minutes of order events.
You may access the table column by providing a table name, column name and the aggregation method. For more information please see Section 6.3.3, “Table Column Keyed-Access Expressions”.
The following statement access the table column to output the first (least) and last (greatest) current price for each order event:
select OrderPrices.prices.firstKey() as leastPrice,
OrderPrices.prices.lastKey() as greatestPrice
from OrderEventYou may access the table column by putting the table into the from-clause such as in a subquery or join.
The following statement uses a subquery and outputs the least price as well as the order event itself:
select (select prices.firstKey() from OrderPrices) as leastPrice, * from OrderEvent
Use the dot (.) and the event property name to select an event property of the event, for aggregation methods that return a single event.
The example statement below selects the order id of the least price order:
select OrderPrices.prices.firstEvent().orderId as leastPriceOrderId from OrderEvent
Use the dot (.) and an enumeration method to enumerate events returned by aggregation methods that return one or more events.
The example statement below selects the first 3 events among the order events with prices between one and ten:
select OrderPrices.prices.eventsbetween(0, true, 10, true).take(3) as first3Events from OrderEvent
This table lists methods for key-up and key-down:
Table 13.1. Sorted Aggregation Methods For Key Up/Down
| Method | Result |
|---|---|
| ceilingEvent(key) |
Returns the first event associated with the least key greater than or equal to the given key, or null if there is no such key. Section 13.3.3, “CeilingEvent, FloorEvent, HigherEvent, LowerEvent, GetEvent”. |
| ceilingEvents(key) |
Returns all events associated with the least key greater than or equal to the given key, or null if there is no such key. Section 13.3.4, “CeilingEvents, FloorEvents, HigherEvents, LowerEvents, GetEvents”. |
| ceilingKey(key) |
Returns the least key greater than or equal to the given key, or null if there is no such key. Section 13.3.5, “CeilingKey, FloorKey, HigherKey, LowerKey”. |
| floorEvent(key) |
Returns the first event associated with the greatest key less than or equal to the given key, or null if there is no such key. Section 13.3.3, “CeilingEvent, FloorEvent, HigherEvent, LowerEvent, GetEvent”. |
| floorEvents(key) |
Returns all events associated with the greatest key less than or equal to the given key, or null if there is no such key. Section 13.3.4, “CeilingEvents, FloorEvents, HigherEvents, LowerEvents, GetEvents”. |
| floorKey(key) |
Returns the greatest key less than or equal to the given key, or null if there is no such key. Section 13.3.5, “CeilingKey, FloorKey, HigherKey, LowerKey”. |
| higherEvent(key) |
Returns the first event associated with the least key strictly greater than the given key, or null if there is no such key. Section 13.3.3, “CeilingEvent, FloorEvent, HigherEvent, LowerEvent, GetEvent”. |
| higherEvents(key) |
Returns all events associated with the least key strictly greater than the given key, or null if there is no such key. Section 13.3.4, “CeilingEvents, FloorEvents, HigherEvents, LowerEvents, GetEvents”. |
| higherKey(key) |
Returns the least key strictly greater than the given key, or null if there is no such key. Section 13.3.5, “CeilingKey, FloorKey, HigherKey, LowerKey”. |
| lowerEvent(key) |
Returns the first event associated with the greatest key strictly less than the given key, or null if there is no such key. Section 13.3.3, “CeilingEvent, FloorEvent, HigherEvent, LowerEvent, GetEvent”. |
| lowerEvents(key) |
Returns all events associated with the greatest key strictly less than the given key, or null if there is no such key. Section 13.3.4, “CeilingEvents, FloorEvents, HigherEvents, LowerEvents, GetEvents”. |
| lowerKey(key) |
Returns the greatest key strictly less than the given key, or null if there is no such key. Section 13.3.5, “CeilingKey, FloorKey, HigherKey, LowerKey”. |
This table lists methods for least and greatest:
Table 13.2. Sorted Aggregation Methods For Key First/Last
| Method | Result |
|---|---|
| firstEvent() or minBy() |
Returns the first event associated with the least key, or null if empty. |
| firstEvents() |
Returns all events associated with the least key, or null if empty. |
| firstKey() |
Returns the first (least) key. |
| lastEvent() or maxBy() |
Returns the first event associated with the greatest key, or null if empty. |
| lastEvents() |
Returns all events associated with the greatest key, or null if empty. |
| lastKey() |
Returns the last (greatest) key. |
This table lists methods for getting for specific keys, checking contains and getting counts:
Table 13.3. Sorted Aggregation Methods For Get, Contains, Count
| Method | Result |
|---|---|
| containsKey(key) |
Returns true if the aggregation contains the specified key. |
| countEvents() |
Returns the number of events in the aggregation. |
| countKeys() |
Returns the number of keys in the aggregation. |
| getEvent(key) |
Returns the first event for the specified key, or null if there is no such key. Section 13.3.3, “CeilingEvent, FloorEvent, HigherEvent, LowerEvent, GetEvent”. |
| getEvents(key) |
Returns all events for the specified key, or null if there is no such key. Section 13.3.4, “CeilingEvents, FloorEvents, HigherEvents, LowerEvents, GetEvents”. |
This table lists methods for sub-map, between-values and reference:
Table 13.4. Sorted Aggregation Methods For Submap, Between and Reference
| Method | Result |
|---|---|
| eventsBetween(fromKey, fromInclusive, toKey, toInclusive) |
Returns all events of a portion of this aggregation whose keys range from |
| submap(fromKey, fromInclusive, toKey, toInclusive) |
Returns a submap of type |
| navigableMapReference() |
NOTE: This method returns an object that the runtime may concurrently modify and that is only valid for the period of evaluation of the current event or time.
Returns a |
Use com.espertech.esper.common.client.util.HashableMultiKey when the sorted aggregation has multiple keys.
E.g. for sorted(string, double) use new HashableMultiKey(orderId, price).
The methods accept a single key parameter and return the event or null if an event was not found. If the result is multiple events, the method returns the oldest ceiling/floor/lower/higher/keyed event.
Table 13.5. Sorted Aggregation Methods - Keyed Returning Single Event
| Method | Result |
|---|---|
| ceilingEvent(key) |
Returns the first event associated with the least key greater than or equal to the given key, or null if there is no such key. |
| floorEvent(key) |
Returns the first event associated with the greatest key less than or equal to the given key, or null if there is no such key. |
| higherEvent(key) |
Returns the first event associated with the least key strictly greater than the given key, or null if there is no such key. |
| lowerEvent(key) |
Returns the first event associated with the greatest key strictly less than the given key, or null if there is no such key. |
| getEvent(key) |
Returns the first event for the specified key, or null if there is no such key. |
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns the order event associated with the greatest price strictly less than the given price, or null if there is no such price:
select OrderPrices.prices.lowerEvent(price) as lowerPriceEvent from OrderEvent
The methods accepts a single key parameter and return all events or null if no events are found.
Table 13.6. Sorted Aggregation Methods - Keyed Returning Multiple Events
| Method | Result |
|---|---|
| ceilingEvents(key) |
Returns all events associated with the least key greater than or equal to the given key, or null if there is no such key. |
| floorEvents(key) |
Returns all events associated with the greatest key less than or equal to the given key, or null if there is no such key. |
| higherEvents(key) |
Returns all events associated with the least key strictly greater than the given key, or null if there is no such key. |
| lowerEvents(key) |
Returns all events associated with the greatest key strictly less than the given key, or null if there is no such key. |
| getEvents(key) |
Returns all events for the specified key, or null if there is no such key. |
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns all order events associated with the greatest price strictly less than the given price, or null if there is no such price:
select OrderPrices.prices.lowerEvents(price) as lowerPriceEvents from OrderEvent
The methods accepts a single key parameter and return the key or null if no key is found.
Table 13.7. Sorted Aggregation Methods - Keyed Returning Key
| Method | Result |
|---|---|
| ceilingKey(key) |
Returns the least key greater than or equal to the given key, or null if there is no such key. |
| floorKey(key) |
Returns the greatest key less than or equal to the given key, or null if there is no such key. |
| higherKey(key) |
Returns the least key strictly greater than the given key, or null if there is no such key. |
| lowerKey(key) |
Returns the greatest key strictly less than the given key, or null if there is no such key. |
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns the greatest price strictly less than the given price, or null if there is no such price:
select OrderPrices.prices.lowerKey(price) as lowerPrice from OrderEvent
The methods have no parameter and return the first (least) or last (greatest) event or null if an event was not found. If the result is multiple events, the method returns the oldest first/last (least/greatest) event.
Table 13.8. Sorted Aggregation Methods - Unparameterized Returning Single Event
| Method | Result |
|---|---|
| firstEvent() or minBy() |
Returns the first event associated with the least key, or null if empty. |
| lastEvent() or maxBy() |
Returns the first event associated with the greatest key, or null if empty. |
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns the order event associated with the least price , or null if there is no such price:
select OrderPrices.prices.firstEvent() as leastPriceEvent from OrderEvent
The methods have no parameter and return the first (least) or last (greatest) events or null if no events are found.
Table 13.9. Sorted Aggregation Methods - Unparameterized Returning Multiple Events
| Method | Result |
|---|---|
| firstEvents() |
Returns all events associated with the least key, or null if empty. |
| lastEvents() |
Returns all events associated with the greatest key, or null if empty. |
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns all order events associated with the least price or null if there is no such price:
select OrderPrices.prices.firstEvents(price) as lowerPriceEvents from OrderEvent
The methods have no parameter and return the the first (least) or last (greatest) key or null if no key is found.
Table 13.10. Sorted Aggregation Methods - Unparameterized Returning Key
| Method | Result |
|---|---|
| firstKey() |
Returns the first (least) key. |
| lastKey() |
Returns the last (greatest) key. |
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns the least price or null if there is no such price:
select OrderPrices.prices.firstKey() as leastPrice from OrderEvent
This method accepts a single key parameter and returns a boolean-type value indicating whether the key is found, or null if using tables and the table row is not found.
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns true if a price of zero exists:
select OrderPrices.prices.containsKey(0) as priceZeroFound from OrderEvent
This method has no parameters and returns an int-type count of the number of events, or null if using tables and the table row is not found.
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns the number of events:
select OrderPrices.prices.countEvents() as numEvents from OrderEvent
This method has no parameters and returns an int-type count of the number of keys, or null if using tables and the table row is not found.
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns the number of prices:
select OrderPrices.prices.countKeys() as numPrices from OrderEvent
This method accepts four parameters, namely the from-key, the boolean-typed from-inclusive indicator, the to-key and the boolean-typed to-inclusive indicator.
The method returns all events whose keys range from from-key to to-key.
If from-key and to-key are equal, the returned value is empty unless from-inclusive and to-inclusive are both true.
The from-key must be less or equal to to-key.
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns all events with prices between one (inclusive) and ten (inclusive):
select OrderPrices.prices.eventsBetween(1, true, 10, true) as eventsPricedOneToTen from OrderEvent
This method accepts four parameters, namely the from-key, the boolean-typed from-inclusive indicator, the to-key and the boolean-typed to-inclusive indicator.
The method returns an object of type NavigableMap<Object, Object[]> that is a shallow copy of all keys of a portion of this aggregation whose keys range from fromKey to toKey.
If from-key and to-key are equal, the returned value is empty unless from-inclusive and to-inclusive are both true.
The from-key must be less or equal to to-key.
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns all keys and events as a navigable map with prices between one (inclusive) and ten (inclusive):
select OrderPrices.prices.submap(1, true, 10, true) as keysAndEventsPricedOneToTen from OrderEvent
The map keys are the key objects. Each map value is an array containing the event underlying object of the events for the key.
Modifications on the map returned by the method are not reflected back into the aggregation state. The map returned by the method is in effect a shallow copy.
This method has no parameters and returns an object of type NavigableMap<Object, Collection<EventBean>> that is an unmodifiable view of keys and unmodifiable collections of EventBean instances.
This method returns an object that the runtime may concurrently modify and that is only valid for the period of evaluation of the current event or time.
Assuming the OrderPrices table exists as defined above, this example statement, when an order event arrives, returns all keys and events as a navigable map:
select OrderPrices.prices.navigableMapReference() as prices from OrderEvent
The map keys are the key objects. Each map value is an a collection of EventBean events for the key.
Modifications on the map returned by the method are not allowed. The map returned by the method is in effect a wrapper of the actual aggregation state.
The below table summarizes the built-in aggregation methods for use with the window aggregation:
Table 13.11. Window Aggregation Methods
| Method | Result |
|---|---|
| listReference() |
NOTE: This method returns an object that the runtime may concurrently modify and that is only valid for the period of evaluation of the current event or time.
Returns a |
| countEvents() |
Returns the number of events. |
| first() |
Returns the oldest event or null if the aggregation is empty. |
| last() |
Returns the newest event or null if the aggregation is empty. |
The sample statement below creates a simple un-keyed table that keeps order events:
create table OrdersTable(orders window(*) @type('OrderEvent'))
The next statement aggregates order events into the table column orders considering the last 10 minutes of order events (the example specifies priority 1 so the runtime always updates the table before other selects):
@Priority(1) into table OrdersTable select window(*) as orders from OrderEvent#time(10 minutes)
This method has no parameters and returns an int-type count of the number of events, or null if using tables and the table row is not found.
Assuming the OrdersTable table exists as defined above, this example statement, when an order event arrives, returns the number of events:
select OrdersTable.orders.countEvents() as numEvents from OrderEvent
This method has no parameters and returns an object of type List<EventBean> that is an unmodifiable list of EventBean instances.
This method returns an object that the runtime may concurrently modify and that is only valid for the period of evaluation of the current event or time.
Assuming the OrdersTable table exists as defined above, this example statement, when an order event arrives, returns all events as a list:
select OrderPrices.orders.listReference() as prices from OrderEvent
Modifications on the list returned by the method are not allowed. The list returned by the method is in effect a wrapper of the actual aggregation state.
The method has no parameter and return the first (oldest) or last (newest) event or null if an event was not found.
Assuming the OrdersTable table exists as defined above, this example statement, when an order event arrives, returns the oldest order event, or null if there is no such event:
select OrdersTable.orders.first() as oldestOrder from OrderEvent
You may optionally specify an event property name as a parameter, in which case the method returns the event property value of the oldest/newest event or null if there is no such event.
The aggregation methods for count-min sketch are countMinSketchFrequency and countMinSketchTopk and are already described in Section 10.2.3, “Approximation Aggregation Functions”.
The extension code defines the aggregation methods and their parameters. For more information please see Section 22.5.2, “Aggregation Multi-Function Development”.