|
|
This section contains information on the following subjects:
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.
EPL allows static Java library methods as single-row functions, and also features built-in single-row functions.
EPL auto-imports the following Java library packages:
Thus Java static library methods can be used in all expressions as shown in below example:
SELECT symbol, Math.round(volume/1000)
FROM StockTickEvent RETAIN 30 SECONDS
Other arbitrary Java classes may also be used, however their names must be fully qualified or configured to be imported. For more information, see User-Defined functions.
The table below outlines the built-in single-row functions available.
The MIN and MAX functions take two or more expression parameters. The min function returns the lowest numeric value among these 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 AS ticks, NewsEvent AS news RETAIN 30 SECONDS
WHERE ticks.symbol = news.symbol
The MIN and MAX functions are also available as aggregate functions. See Aggregate functions for a description of this usage.
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 type result with a value of foo.
SELECT COALESCE(NULL, 'foo')
FROM …
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 PREV function returns the property value of a previous event. The first parameter denotes the ith previous event in the order established by the data window. The second parameter is a property name for which the function returns the value for the previous event.
This example selects the value of the price property of the second previous event from the current Trade event.
SELECT PREV(2, price)
FROM Trade RETAIN 10 EVENTS
Because 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 three Trade events that had the largest, second-largest and third-largest volume.
SELECT PREV(0, symbol), PREV(1, symbol), PREV(2, symbol)
FROM Trade RETAIN 10 EVENTS WITH LARGEST volume
The ith previous event parameter can also be an expression returning an Integer type value. The next statement joins the Trade data window with a 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, RankSelectionEvent RETAIN 10 EVENTS WITH LARGEST volume
The PREV function returns a NULL value if the data window does not currently hold the ith 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. The PRIOR function as discussed below can be used if a null value is not the desired result.
SELECT PREV(1, symbol)
FROM Trade RETAIN BATCH OF 1 MINUTE
The combination of the PREV function and the PARTITION BY clause returns the property value for a previous event in the given group.
For example, assume we want to obtain the price of the previous event of the same symbol as the current event.
The statement that follows solves this problem. It partitions the window on the symbol property over a time window of one minute. As a result, when the engine encounters a new symbol value that it hasn't seen before, it creates a new window specifically to hold events for that symbol. Consequently, the PREV function returns the previous event within the respective time window for that event's symbol value.
SELECT PREV(1, price) AS prevPrice
FROM Trade RETAIN 1 MIN PARTITION BY symbol
The following restrictions apply to the PREV functions and its results:
The PRIOR function returns the property value of a prior event. The first parameter is an integer value that denotes the ith prior event in the natural order of arrival. The second parameter is a property name for which the function returns the value for the prior event.
This example selects the value of the price property of the second prior event to the current Trade event.
SELECT PRIOR(2, price)
FROM Trade RETAIN ALL
The PRIOR function can be used on any event stream or view and does not require a stream to be constrained by a RETAIN clause as with the PREV function. The function operates based on the order of arrival of events in the event stream 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 AVG(volume) AS avgVolume, PRIOR(1, avgVolume)
FROM TradeAverages RETAIN BATCH OF 1 MINUTE
The PRIOR function is similar to the PREV function. The key differences between the two functions are as follows:
PREV function returns previous events in the order provided by the window, while the PRIOR function returns prior events in the order of arrival in the stream. PREV function requires a RETAIN clause while the PRIOR function does not. PREV function returns the previous event taking into account any grouping. The PRIOR function returns prior events regardless of any grouping. 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 aggregate functions are SUM, AVG, COUNT, MAX, MIN, MEDIAN, STDDEV, AVEDEV. You can use aggregate functions to calculate and summarize data from event properties. For example, to find out the total price for all stock tick events in the last 30 seconds:
SELECT SUM(price)
FROM StockTickEvent RETAIN 30 SECONDS
Here is the syntax for aggregate functions:
aggregate_function( [ALL | DISTINCT]expression)
You can apply aggregate functions to all events in an event stream window or other view, or to one or more groups of events. From each set of events to which an aggregate function is applied, EPL generates a single value.
The expression is usually an event property name. However it can also be a constant, function, or any combination of event property names, constants, and functions connected by arithmetic operators.
For example, to find out the average price for all stock tick events in the last 30 seconds if the price was doubled:
SELECT AVG(price * 2)
FROM StockTickEvent RETAIN 30 SECONDS
You can use the optional keyword DISTINCT with all aggregate functions to eliminate duplicate values before the aggregate function is applied. The optional keyword ALL which performs the operation on all events is the default.
The MIN and MAX aggregate functions are also available as single row functions. See The MIN and MAX Functions for a description of this usage.
The syntax of the aggregation functions and the results they produce are shown in table below.
You can use aggregation functions in a SELECT clause and in a HAVING clause. You cannot use aggregate functions in a WHERE clause, but you can use the WHERE clause to restrict the events to which the aggregate is applied. The next query computes the average and sum of the price of stock tick events for the symbol ACME only, for the last 10 stock tick events regardless of their symbol.
SELECT 'ACME stats' AS title, AVG(price) AS avgPrice, SUM(price) AS sumPrice
FROM StockTickEvent RETAIN 10 EVENTS
WHERE symbol='ACME'
In the preceding example the length window of 10 elements is not affected by the WHERE clause, in other words, all events enter and leave the length window regardless of their symbol. If we only care about the last 10 ACME events, we need to add a MATCHING clause as shown below.
SELECT 'ACME stats' AS title, AVG(price) AS avgPrice, SUM(price) AS sumPrice
FROM (SELECT * FROM StockTickEvent WHERE symbol='ACME')
RETAIN 10 EVENT
You can use aggregate functions with any type of event property or expression, with the following restriction:
EPL ignores any null values returned by the event property or expression on which the aggregate function is operating, except for the COUNT(*) function, which counts null values as well. All aggregate functions return null if the data set contains no events, or if all events in the data set contain only null values for the aggregated expression.
A user-defined function can be invoked anywhere as an expression itself or within an expression. The function must simply be a public static method that the class loader can resolve at statement creation time. The engine resolves the function reference at statement creation time and verifies parameter types.
The example below assumes a class MyClass that exposes a public static method myFunction accepting two parameters, and returning a numeric type such as double.
SELECT 3 * MyClass.myFunction(price, volume) as myValue
FROM StockTick RETAIN 30 SECONDS
User-defined functions also take array parameters as this example shows. Array Definition Operator outlines in more detail the types of arrays produced.
SELECT *
FROM RFIDEvent RETAIN 10 MINUTES
WHERE com.mycompany.rfid.MyChecker.isInZone(zone, {10, 20, 30})
|