4 EPL Reference: Functions

4.1.1 The MIN and MAX Functions

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 Section 4.2, "Aggregate functions" for a description of this usage.

4.1.2 The COALESCE Function

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 …

4.1.3 The CASE Control Flow Function

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 …

4.1.4 The PREV Function

The PREV function returns the property value of a previous event. The first parameter denotes the i^th 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 i^th 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 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. 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

SELECT PREV(1, price) AS prevPrice 
  FROM Trade RETAIN 1 MIN PARTITION BY symbol

4.1.5 The PRIOR Function

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 length batch window to compute an average volume for every 3 Trade events, posting results every 3 events. The SELECT clause employs the PRIOR function to select the current average and the average before the current average:

SELECT AVG(volume) AS avgVolume, AVG(PRIOR(3, volume))
  FROM Trade RETAIN BATCH OF 3 EVENTS

4.1.6 The INSTANCEOF Function

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 Java type names.

The INSTANCEOF 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.

The following 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 are:

• Any of the Java built-in types: int, long, byte, short, char, double, float, string, where string is a short notation for java.lang.String. The type name is not case-sensitive. For example, the following 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 test, for example:

  INSTANCEOF(product, org.myproducer.Product)
  

Valid parameters for the type parameter list are:

The INSTANCEOF function considers an event class's superclasses as well as all the directly or indirectly-implemented interfaces by superclasses.

4.1.7 The CAST Function

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 and the second parameter is the type to cast to.

Valid parameters for the second (type) parameter are:

• Any of the Java built-in types: int, long, byte, short, char, double, float, string, where string is a short notation for java.lang.String. The type name is not case-sensitive. For example:

  cast(price, double)
  

• The fully-qualified class 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 java.lang.Object.

The following example shows how to use the CAST function to cast the price dynamic 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 java.lang.Number to convert numeric types, if required.

• For casts to string or java.lang.String, the CAST function calls toString on the expression result.

• For casts to other objects, including application objects, the CAST function considers a Java class's superclasses as well as all directly or indirectly-implemented interfaces by superclasses .

4.1.8 The EXISTS Function

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.

Use the EXISTS function 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 exist on an event, or if the dynamic property exists but the value of the dynamic property is null.

The following example of using the EXISTS function 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