[
https://issues.apache.org/jira/browse/CALCITE-1641?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15876924#comment-15876924
]
Julian Hyde commented on CALCITE-1641:
--------------------------------------
Very nice work! The way you have structured the code looks right.
I know you intend to add test cases later, but I would add a few tests to
SqlParserTest and SqlValidatorTest fairly soon. Be sure to include negative
tests (e.g. references to invalid columns).
There is a chance that the operators you have added to SqlStdOperatorTable
(e.g. CLASSIFIER, "|") will be available in regular SQL, and we don't want
that. Please add tests to make sure.
Double-check your spelling and grammar. E.g. "the alternation operator in a
pattern expression whtin a match_recognize clasue" should be "The alternation
operator in a pattern expression within a match_recognize clause." Also
SqlKind.RUNNINIG.
Also add a section to reference.md for the syntax you have implemented. Mark it
"not fully implemented" but having it in the doc will stimulate discussion and
review.
Once those tests and doc are written we will have high confidence that this
works, and we can commit as an experimental/incomplete feature. This will be
better for you, because you won't be working on a branch.
> Base functions support for MATCH_RECOGNIZE
> ------------------------------------------
>
> Key: CALCITE-1641
> URL: https://issues.apache.org/jira/browse/CALCITE-1641
> Project: Calcite
> Issue Type: Sub-task
> Components: core
> Affects Versions: 1.11.0
> Reporter: Zhiqiang He
> Assignee: Zhiqiang He
> Labels: features
>
> MATCH_RECOGNIZE syntax like
> this:https://docs.oracle.com/database/121/DWHSG/pattern.htm#DWHSG8980
> Only pattern and define is supported in first step.
> h1. PATTERN: Defining the Row Pattern to Be Matched
> The PATTERN keyword specifies the pattern to be recognized in the ordered
> sequence of rows in a partition. Each variable name in a pattern corresponds
> to a Boolean condition, which is specified later using the DEFINE component
> of the syntax.
> The PATTERN clause is used to specify a regular expression. It is outside the
> scope of this material to explain regular expression concepts and details. If
> you are not familiar with regular expressions, you are encouraged to
> familiarize yourself with the topic using other sources.
> The regular expression in a PATTERN clause is enclosed in parentheses.
> PATTERN may use the following operators:
> * Concatenation
> Concatenation is used to list two or more items in a pattern to be matched in
> that order. Items are concatenated when there is no operator sign between two
> successive items. For example: PATTERN (A B C).
> * Quantifiers
> Quantifiers define the number of iterations accepted for a match. Quantifiers
> are postfix operators with the following choices:
> ** \* — 0 or more iterations
> ** + — 1 or more iterations
> ** ? — 0 or 1 iterations
> ** {n} — n iterations (n > 0)
> ** {n,} — n or more iterations (n >= 0)
> ** {n,m} — between n and m (inclusive) iterations (0 <= n <= m, 0 < m)
> ** {,m} — between 0 and m (inclusive) iterations (m > 0)
> ** reluctant quantifiers — indicated by an additional question mark following
> a quantifier (*?, +?, ??, {n,}?, { n, m }?, {,m}?). See "Reluctant Versus
> Greedy Quantifier" for the difference between reluctant and non-reluctant
> quantifiers.
> The following are examples of using quantifier operators:
> ** A* matches 0 or more iterations of A
> ** A{3,6} matches 3 to 6 iterations of A
> ** A{,4} matches 0 to 4 iterations of A
> * Alternation
> Alternation matches a single regular expression from a list of several
> possible regular expressions. The alternation list is created by placing a
> vertical bar (|) between each regular expression. Alternatives are preferred
> in the order they are specified. As an example, PATTERN (A | B | C) attempts
> to match A first. If A is not matched, it attempts to match B. If B is not
> matched, it attempts to match C.
> * Grouping
> Grouping treats a portion of the regular expression as a single unit,
> enabling you to apply regular expression operators such as quantifiers to
> that group. Grouping is created with parentheses. As an example, PATTERN ((A
> B){3} C) attempts to match the group (A B) three times and then seeks one
> occurrence of C.
> * PERMUTE
> See "How to Express All Permutations" for more information.
> * Exclusion
> Parts of the pattern to be excluded from the output of ALL ROWS PER MATCH are
> enclosed between {- and -}. See "How to Exclude Portions of the Pattern from
> the Output".
> * Anchors
> Anchors work in terms of positions rather than rows. They match a position
> either at the start or end of a partition.
> ** ^ matches the position before the first row in the partition.
> ** $ matches the position after the last row in the partition.
> As an example, PATTERN (^A+$) will match only if all rows in a partition
> satisfy the condition for A. The resulting match spans the entire partition.
> * Empty pattern (), matches an empty set of rows
> This section contains the following topics:
> Reluctant Versus Greedy Quantifier
> Operator Precedence
> h2. Reluctant Versus Greedy Quantifier
> Pattern quantifiers are referred to as greedy; they will attempt to match as
> many instances of the regular expression on which they are applied as
> possible. The exception is pattern quantifiers that have a question mark ? as
> a suffix, and those are referred to as reluctant. They will attempt to match
> as few instances as possible of the regular expression on which they are
> applied.
> The difference between greedy and reluctant quantifiers appended to a single
> pattern variable is illustrated as follows: A* tries to map as many rows as
> possible to A, whereas A*? tries to map as few rows as possible to A. For
> example:
> {code:sql}
> PATTERN (X Y* Z)
> {code}
> The pattern consists of three variable names, X, Y, and Z, with Y quantified
> with *. This means a pattern match will be recognized and reported when the
> following condition is met by consecutive incoming input rows:
> ** A row satisfies the condition that defines variable X followed by zero or
> more rows that satisfy the condition that defines the variable Y followed by
> a row that satisfies the condition that defines the variable Z.
> During the pattern matching process, after a row was mapped to X and 0 or
> more rows were mapped to Y, if the following row can be mapped to both
> variables Y and Z (which satisfies the defining condition of both Y and Z),
> then, because the quantifier * for Y is greedy, the row is preferentially
> mapped to Y instead of Z. Due to this greedy property, Y gets preference over
> Z and a greater number of rows to Y are mapped. If the pattern expression was
> PATTERN (X Y*? Z), which uses a reluctant quantifier *? over Y, then Z gets
> preference over Y.
> h2. Operator Precedence
> The precedence of the elements in a regular expression, in decreasing order,
> is as follows:
> * row_pattern_primary
> These elements include primary pattern variables (pattern variables not
> created with the SUBSET clause described in "SUBSET: Defining Union Row
> Pattern Variables"), anchors, PERMUTE, parenthetic expressions, exclusion
> syntax, and empty pattern
> * Quantifier
> A row_pattern_primary may have zero or one quantifier.
> * Concatenation
> * Alternation
> Precedence of alternation is illustrated by PATTERN(A B | C D), which is
> equivalent to PATTERN ((A B) | (C D)). It is not, however, equivalent to
> PATTERN (A (B | C) D).
> Precedence of quantifiers is illustrated by PATTERN (A B *), which is
> equivalent to PATTERN (A (B*)). It is not, however, PATTERN ((A B)*).
> A quantifier may not immediately follow another quantifier. For example,
> PATTERN(A**) is prohibited.
> It is permitted for a primary pattern variable to occur more than once in a
> pattern, for example, PATTERN (X Y X).
> h1. **DEFINE**: Defining Primary Pattern Variables
> DEFINE is a mandatory clause, used to specify the conditions that define
> primary pattern variables. In the example:
> {code:sql}
> DEFINE UP AS UP.Price > PREV(UP.Price),
> DOWN AS DOWN.Price < PREV(DOWN.Price)
> {code}
> UP is defined by the condition UP.Price > PREV (UP.Price), and DOWN is
> defined by the condition DOWN.Price < PREV (DOWN.Price). (PREV is a row
> pattern navigation operation which evaluates an expression in the previous
> row; see "Row Pattern Navigation Operations" regarding the complete set of
> row pattern navigation operations.)
> A pattern variable does not require a definition; if there is no definition,
> any row can be mapped to the pattern variable.
> A union row pattern variable (see discussion of SUBSET in "SUBSET: Defining
> Union Row Pattern Variables") cannot be defined by DEFINE, but can be
> referenced in the definition of a pattern variable.
> The definition of a pattern variable can reference another pattern variable,
> which is illustrated in Example 20-6.
> Example 20-6 Defining Pattern Variables
> {code:sql}
> SELECT *
> FROM Ticker MATCH_RECOGNIZE (
> PARTITION BY Symbol
> FROM Ticker
> MATCH_RECOGNIZE (
> PARTITION BY Symbol
> ORDER BY tstamp
> MEASURES FIRST (A.tstamp) AS A_Firstday,
> LAST (D.tstamp) AS D_Lastday,
> AVG (B.Price) AS B_Avgprice,
> AVG (D.Price) AS D_Avgprice
> PATTERN (A B+ C+ D)
> SUBSET BC = (B,C)
> DEFINE A AS Price > 100,
> B AS B.Price > A.Price,
> C AS C.Price < AVG (B.Price),
> D AS D.Price > MAX (BC.Price)
> ) M
> {code}
> In this example:
> The definition of A implicitly references the universal row pattern variable
> (because of the unqualified column reference Price).
> The definition of B references the pattern variable A.
> The definition of C references the pattern variable B.
> The definition of D references the union row pattern variable BC.
> The conditions are evaluated on successive rows of a partition in a trial
> match, with the current row being tentatively mapped to a pattern variable as
> permitted by the pattern. To be successfully mapped, the condition must
> evaluate to true.
> In the previous example:
> {code:sql}
> A AS Price > 100
> {code}
> Price refers to the Price in the current row, because the last row mapped to
> any primary row pattern variable is the current row, which is tentatively
> mapped to A. Alternatively, in this example, using A.Price would have led to
> the same results.
> {code:sql}
> B AS B.Price > A.Price
> {code}
> B.Price refers to the Price in the current row (because B is being defined),
> whereas A.Price refers to the last row mapped to A. In view of the pattern,
> the only row mapped to A is the first row to be mapped.
> {code:sql}
> C AS C.Price < AVG(B.Price)
> {code}
> Here C.Price refers to the Price in the current row, because C is being
> defined. The aggregate AVG (that is, insert Price) is computed as the average
> of all rows that are already mapped to B.
> {code:sql}
> D AS D.Price > MAX(BC.Price)
> {code}
> The pattern variable D is similar to pattern variable C, though it
> illustrates the use of a union row pattern variable in the Boolean condition.
> In this case, MAX(BC.Price) returns the maximum price value of the rows
> matched to variable B or variable C. The semantics of Boolean conditions are
> discussed in more detail in "Expressions in MEASURES and DEFINE".
> h1. MATCH_RECOGNIZE syntax :
> {code:sql}
> table_reference ::=
> {only (query_table_expression) | query_table_expression
> }[flashback_query_clause]
> [pivot_clause|unpivot_clause|row_pattern_recognition_clause] [t_alias]
> row_pattern_recognition_clause ::=
> MATCH_RECOGNIZE (
> PATTERN (row_pattern)
> DEFINE row_pattern_definition_list
> )
> row_pattern ::=
> row_pattern_term
> | row_pattern "|" row_pattern_term
> row_pattern_term ::=
> row_pattern_factor
> | row_pattern_term row_pattern_factor
> row_pattern_factor ::=
> row_pattern_primary [row_pattern_quantifier]
> row_pattern_quantifier ::=
> *[?]
> |+[?]
> |?[?]
> |"{"[unsigned_integer ],[unsigned_integer]"}"[?]
> |"{"unsigned_integer "}"
> row_pattern_primary ::=
> variable_name
> |$
> |^
> |([row_pattern])
> |"{-" row_pattern"-}"
> | row_pattern_permute
> row_pattern_permute ::=
> PERMUTE (row_pattern [, row_pattern] ...)
> row_pattern_subset_clause ::=
> SUBSET row_pattern_subset_item [, row_pattern_subset_item] ...
> row_pattern_subset_item ::=
> variable_name = (variable_name[ , variable_name]...)
> row_pattern_definition_list ::=
> row_pattern_definition[, row_pattern_definition]...
> row_pattern_definition ::=
> variable_name AS condition
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
