Major Description
SUBSET: Defining Union Row Pattern Variables
At times, it is helpful to create a grouping of multiple pattern variables that can be referred to with a variable name of its own. These groupings are called union row pattern variables, and you create them with the SUBSET clause. The union row pattern variable created by SUBSET can be used in the MEASURES and DEFINE clauses. The SUBSET clause is optional. It is used to declare union row pattern variables. For example, here is a query using SUBSET to calculate an average based on all rows that are mapped to the union of STRT and DOWN variables, where STRT is the starting point for a pattern, and DOWN is the downward (left) leg of a V shape.
Example 20-5 illustrates creating a union row pattern variable.
*Example 20-5 Defining Union Row Pattern Variables*
SELECT * FROM Ticker MATCH_RECOGNIZE( PARTITION BY symbol ORDER BY tstamp MEASURES FIRST(STRT.tstamp) AS strt_time, LAST(DOWN.tstamp) AS bottom, AVG(STDN.Price) AS stdn_avgprice ONE ROW PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+) SUBSET STDN= (STRT, DOWN) DEFINE UP AS UP.Price > PREV(UP.Price), DOWN AS DOWN.Price < PREV (DOWN.Price) ); SYMBOL STRT_TIME BOTTOM STDN_AVGPRICE ------ --------- --------- ------------- ACME 05-APR-11 06-APR-11 18.5 ACME 10-APR-11 12-APR-11 19.6666667 ACME 14-APR-11 16-APR-11 17
This example declares a single union row pattern variable, STDN, and defines it as the union of the rows mapped to STRT and the rows mapped to DOWN. There can be multiple union row pattern variables in a query. For example:
PATTERN (W+ X+ Y+ Z+)
SUBSET XY = (X, Y),
WZ = (W, Z)
The right-hand side of a SUBSET item is a comma-separated list of distinct primary row pattern variables within parentheses. This defines the union row pattern variable (on the left-hand side) as the union of the primary row pattern variables (on the right-hand side).
Note that the list of pattern variables on the right-hand side may not include any union row pattern variables (there are no unions of unions).
For every match, there is one implicit union row pattern variable called the universal row pattern variable. The universal row pattern variable is the union of all primary row pattern variables. For instance, if your pattern has primary pattern variable A, B, and C, then the universal row pattern variable is equivalent to a SUBSET clause with the argument (A, B, C). Thus, every row of a match is mapped to the universal row pattern variable. Any unqualified column reference within the MEASURES or DEFINE clauses is implicitly qualified by the universal row pattern variable. Note that there is no keyword to explicitly specify the universal row pattern variable.