Details

    • Type: Sub-task
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.11.0
    • Fix Version/s: 1.13.0
    • Component/s: core
    • Labels:

      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.

        Activity

        Hide
        ransom Zhiqiang He added a comment -
        Show
        ransom Zhiqiang He added a comment - https://github.com/apache/calcite/pull/445 please review it. thanks.
        Hide
        julianhyde Julian Hyde added a comment -

        A few minor things:

        • You accidentally changed indentation in a couple of places. Please revert.
        • The error message "Pattern var 'STRT' has been defined" doesn't work for me. Why not re-use "Pattern variable '' {0}

          '' has already been defined"?

        • Can you update reference.md. The SUBSET clause doesn't look right.
        • Deep-copy the map you assign to Match.subsets. Currently the TreeSets are still mutable. Maybe you can use Match.copy or something similar.
        Show
        julianhyde Julian Hyde added a comment - A few minor things: You accidentally changed indentation in a couple of places. Please revert. The error message "Pattern var 'STRT' has been defined" doesn't work for me. Why not re-use "Pattern variable '' {0} '' has already been defined"? Can you update reference.md. The SUBSET clause doesn't look right. Deep-copy the map you assign to Match.subsets. Currently the TreeSets are still mutable. Maybe you can use Match.copy or something similar.
        Hide
        ransom Zhiqiang He added a comment -

        Julian Hyde fixed. please review it . thanks.

        Show
        ransom Zhiqiang He added a comment - Julian Hyde fixed. please review it . thanks.
        Hide
        julianhyde Julian Hyde added a comment -

        The syntax is still wrong. You have

        subsetItem:
              '(' variable (, variable )* ')'
        

        but it should be

        subsetItem:
              variable = '(' variable [, variable ]* ')'
        
        Show
        julianhyde Julian Hyde added a comment - The syntax is still wrong. You have subsetItem: '(' variable (, variable )* ')' but it should be subsetItem: variable = '(' variable [, variable ]* ')'
        Hide
        julianhyde Julian Hyde added a comment -

        Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/7a56eb63. Thanks for the PR, Zhiqiang He!

        I corrected the syntax documentation.

        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/7a56eb63 . Thanks for the PR, Zhiqiang He ! I corrected the syntax documentation.
        Hide
        jcamachorodriguez Jesus Camacho Rodriguez added a comment -

        Resolved in release 1.13.0 (2017-06-26).

        Show
        jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.13.0 (2017-06-26).

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            ransom Zhiqiang He
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development