Repository: calcite Updated Branches: refs/heads/master 4519ef6e5 -> f6af061ce
[CALCITE-1645] In MATCH_RECOGNIZE clause, support ONE ROW PER MATCH and ALL ROWS PER MATCH (Zhiqiang-He) Close apache/calcite#452 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/e117c10c Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/e117c10c Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/e117c10c Branch: refs/heads/master Commit: e117c10cef192ca4ab10ffec2132b7dbd34319fa Parents: 4519ef6 Author: Zhiqiang-He <[email protected]> Authored: Fri May 19 14:52:34 2017 +0800 Committer: Julian Hyde <[email protected]> Committed: Tue May 23 11:28:41 2017 -0700 ---------------------------------------------------------------------- core/src/main/codegen/templates/Parser.jj | 19 +++- .../java/org/apache/calcite/rel/core/Match.java | 16 ++- .../apache/calcite/rel/core/RelFactories.java | 8 +- .../calcite/rel/logical/LogicalMatch.java | 17 +-- .../calcite/rel/rel2sql/RelToSqlConverter.java | 6 +- .../apache/calcite/sql/SqlMatchRecognize.java | 47 ++++++++- .../calcite/sql/validate/SqlValidatorImpl.java | 30 ++++-- .../calcite/sql2rel/SqlToRelConverter.java | 12 ++- .../rel/rel2sql/RelToSqlConverterTest.java | 103 +++++++++++++++++++ .../calcite/sql/parser/SqlParserTest.java | 96 +++++++++++++---- 10 files changed, 299 insertions(+), 55 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/e117c10c/core/src/main/codegen/templates/Parser.jj ---------------------------------------------------------------------- diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 205e4fe..c84ebe6 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -2440,12 +2440,14 @@ SqlNode OrderItem() : */ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : { - final Span s, s1; + final Span s, s0, s1; SqlNodeList measureList = SqlNodeList.EMPTY; SqlNode pattern; SqlNodeList patternDefList; final SqlNode after; + SqlParserPos pos; final SqlNode var; + final SqlLiteral rowsPerMatch; SqlNodeList subsetList = SqlNodeList.EMPTY; SqlLiteral isStrictStarts = SqlLiteral.createBoolean(false, getPos()); SqlLiteral isStrictEnds = SqlLiteral.createBoolean(false, getPos()); @@ -2457,6 +2459,19 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : measureList = MeasureColumnCommaList(span()) ] ( + <ONE> { s0 = span(); } <ROW> <PER> <MATCH> { + rowsPerMatch = SqlMatchRecognize.RowsPerMatchOption.ONE_ROW.symbol(s0.end(this)); + } + | + <ALL> { s0 = span(); } <ROWS> <PER> <MATCH> { + rowsPerMatch = SqlMatchRecognize.RowsPerMatchOption.ALL_ROWS.symbol(s0.end(this)); + } + | + { + rowsPerMatch = null; + } + ) + ( <AFTER> { s1 = span(); } <MATCH> <SKIP_> ( <TO> @@ -2509,7 +2524,7 @@ SqlMatchRecognize MatchRecognizeOpt(SqlNode tableRef) : <RPAREN> { return new SqlMatchRecognize(s.end(this), tableRef, pattern, isStrictStarts, isStrictEnds, patternDefList, measureList, - after, subsetList); + after, subsetList, rowsPerMatch); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/e117c10c/core/src/main/java/org/apache/calcite/rel/core/Match.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/core/Match.java b/core/src/main/java/org/apache/calcite/rel/core/Match.java index 920a6e2..9412f64 100644 --- a/core/src/main/java/org/apache/calcite/rel/core/Match.java +++ b/core/src/main/java/org/apache/calcite/rel/core/Match.java @@ -59,6 +59,7 @@ public abstract class Match extends SingleRel { protected final RexNode pattern; protected final boolean strictStart; protected final boolean strictEnd; + protected final boolean allRows; protected final RexNode after; protected final ImmutableMap<String, RexNode> patternDefinitions; protected final Set<RexMRAggCall> aggregateCalls; @@ -69,7 +70,8 @@ public abstract class Match extends SingleRel { /** * Creates a Match. - * @param cluster Cluster + * + * @param cluster Cluster * @param traitSet Trait set * @param input Input relational expression * @param pattern Regular expression that defines pattern variables @@ -79,13 +81,14 @@ public abstract class Match extends SingleRel { * @param measures Measure definitions * @param after After match definitions * @param subsets Subsets of pattern variables + * @param allRows Whether all rows per match (false means one row per match) * @param rowType Row type */ protected Match(RelOptCluster cluster, RelTraitSet traitSet, RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, ? extends SortedSet<String>> subsets, - RelDataType rowType) { + boolean allRows, RelDataType rowType) { super(cluster, traitSet, input); this.pattern = Preconditions.checkNotNull(pattern); Preconditions.checkArgument(patternDefinitions.size() > 0); @@ -96,6 +99,7 @@ public abstract class Match extends SingleRel { this.measures = ImmutableMap.copyOf(measures); this.after = Preconditions.checkNotNull(after); this.subsets = copyMap(subsets); + this.allRows = allRows; final AggregateFinder aggregateFinder = new AggregateFinder(); for (RexNode rex : this.patternDefinitions.values()) { @@ -149,6 +153,10 @@ public abstract class Match extends SingleRel { return strictEnd; } + public boolean isAllRows() { + return allRows; + } + public ImmutableMap<String, RexNode> getPatternDefinitions() { return patternDefinitions; } @@ -161,7 +169,7 @@ public abstract class Match extends SingleRel { boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, ? extends SortedSet<String>> subsets, - RelDataType rowType); + boolean allRows, RelDataType rowType); @Override public RelNode copy(RelTraitSet traitSet, List<RelNode> inputs) { if (getInputs().equals(inputs) @@ -170,7 +178,7 @@ public abstract class Match extends SingleRel { } return copy(inputs.get(0), pattern, strictStart, strictEnd, - patternDefinitions, measures, after, subsets, rowType); + patternDefinitions, measures, after, subsets, allRows, rowType); } @Override public RelWriter explainTerms(RelWriter pw) { http://git-wip-us.apache.org/repos/asf/calcite/blob/e117c10c/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java b/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java index 7cefe9f..e829d7a 100644 --- a/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java +++ b/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java @@ -398,7 +398,8 @@ public class RelFactories { RelNode createMatchRecognize(RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RexNode after, Map<String, TreeSet<String>> subsets, RelDataType rowType); + RexNode after, Map<String, TreeSet<String>> subsets, boolean allRows, + RelDataType rowType); } /** @@ -409,9 +410,10 @@ public class RelFactories { public RelNode createMatchRecognize(RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RexNode after, Map<String, TreeSet<String>> subsets, RelDataType rowType) { + RexNode after, Map<String, TreeSet<String>> subsets, boolean allRows, + RelDataType rowType) { return LogicalMatch.create(input, pattern, strictStart, strictEnd, - patternDefinitions, measures, after, subsets, rowType); + patternDefinitions, measures, after, subsets, allRows, rowType); } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/e117c10c/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java b/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java index 2fc751e..2ccf6e1 100644 --- a/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java +++ b/core/src/main/java/org/apache/calcite/rel/logical/LogicalMatch.java @@ -47,15 +47,16 @@ public class LogicalMatch extends Match { * @param measures Measure definitions * @param after After match definitions * @param subsets Subset definitions + * @param allRows Whether all rows per match (false means one row per match) * @param rowType Row type */ - public LogicalMatch(RelOptCluster cluster, RelTraitSet traitSet, + private LogicalMatch(RelOptCluster cluster, RelTraitSet traitSet, RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, ? extends SortedSet<String>> subsets, - RelDataType rowType) { + boolean allRows, RelDataType rowType) { super(cluster, traitSet, input, pattern, strictStart, strictEnd, - patternDefinitions, measures, after, subsets, rowType); + patternDefinitions, measures, after, subsets, allRows, rowType); } /** @@ -64,11 +65,13 @@ public class LogicalMatch extends Match { public static LogicalMatch create(RelNode input, RexNode pattern, boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, - RexNode after, Map<String, TreeSet<String>> subsets, RelDataType rowType) { + RexNode after, Map<String, TreeSet<String>> subsets, boolean allRows, + RelDataType rowType) { final RelOptCluster cluster = input.getCluster(); final RelTraitSet traitSet = cluster.traitSetOf(Convention.NONE); return new LogicalMatch(cluster, traitSet, input, pattern, - strictStart, strictEnd, patternDefinitions, measures, after, subsets, rowType); + strictStart, strictEnd, patternDefinitions, measures, after, subsets, + allRows, rowType); } //~ Methods ------------------------------------------------------ @@ -77,11 +80,11 @@ public class LogicalMatch extends Match { boolean strictStart, boolean strictEnd, Map<String, RexNode> patternDefinitions, Map<String, RexNode> measures, RexNode after, Map<String, ? extends SortedSet<String>> subsets, - RelDataType rowType) { + boolean allRows, RelDataType rowType) { final RelTraitSet traitSet = getCluster().traitSetOf(Convention.NONE); return new LogicalMatch(getCluster(), traitSet, input, pattern, strictStart, strictEnd, patternDefinitions, measures, - after, subsets, rowType); + after, subsets, allRows, rowType); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/e117c10c/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java index 4a07e55..673e8ef 100644 --- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java @@ -380,6 +380,10 @@ public class RelToSqlConverter extends SqlImplementor SqlNode tableRef = x.asQueryOrValues(); + final SqlLiteral rowsPerMatch = e.isAllRows() + ? SqlMatchRecognize.RowsPerMatchOption.ALL_ROWS.symbol(POS) + : SqlMatchRecognize.RowsPerMatchOption.ONE_ROW.symbol(POS); + final SqlNode after; if (e.getAfter() instanceof RexLiteral) { SqlMatchRecognize.AfterOption value = (SqlMatchRecognize.AfterOption) @@ -424,7 +428,7 @@ public class RelToSqlConverter extends SqlImplementor final SqlNode matchRecognize = new SqlMatchRecognize(POS, tableRef, pattern, strictStart, strictEnd, patternDefList, measureList, after, - subsetList); + subsetList, rowsPerMatch); return result(matchRecognize, Expressions.list(Clause.FROM), e, null); } http://git-wip-us.apache.org/repos/asf/calcite/blob/e117c10c/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java b/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java index dfc87f8..d98b58b 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlMatchRecognize.java @@ -40,6 +40,7 @@ public class SqlMatchRecognize extends SqlCall { public static final int OPERAND_MEASURES = 5; public static final int OPERAND_AFTER = 6; public static final int OPERAND_SUBSET = 7; + public static final int OPERAND_ROWS_PER_MATCH = 8; public static final SqlPrefixOperator SKIP_TO_FIRST = new SqlPrefixOperator("SKIP TO FIRST", SqlKind.SKIP_TO_FIRST, 20, null, @@ -59,11 +60,12 @@ public class SqlMatchRecognize extends SqlCall { private SqlNodeList measureList; private SqlNode after; private SqlNodeList subsetList; + private SqlLiteral rowsPerMatch; /** Creates a SqlMatchRecognize. */ public SqlMatchRecognize(SqlParserPos pos, SqlNode tableRef, SqlNode pattern, SqlLiteral strictStart, SqlLiteral strictEnd, SqlNodeList patternDefList, - SqlNodeList measureList, SqlNode after, SqlNodeList subsetList) { + SqlNodeList measureList, SqlNode after, SqlNodeList subsetList, SqlLiteral rowsPerMatch) { super(pos); this.tableRef = Preconditions.checkNotNull(tableRef); this.pattern = Preconditions.checkNotNull(pattern); @@ -74,6 +76,9 @@ public class SqlMatchRecognize extends SqlCall { this.measureList = Preconditions.checkNotNull(measureList); this.after = after; this.subsetList = subsetList; + Preconditions.checkArgument(rowsPerMatch == null + || rowsPerMatch.value instanceof RowsPerMatchOption); + this.rowsPerMatch = rowsPerMatch; } // ~ Methods @@ -127,6 +132,11 @@ public class SqlMatchRecognize extends SqlCall { case OPERAND_SUBSET: subsetList = (SqlNodeList) operand; break; + case OPERAND_ROWS_PER_MATCH: + rowsPerMatch = (SqlLiteral) operand; + Preconditions.checkArgument(rowsPerMatch == null + || rowsPerMatch.value instanceof RowsPerMatchOption); + break; default: throw new AssertionError(i); } @@ -164,6 +174,32 @@ public class SqlMatchRecognize extends SqlCall { return subsetList; } + public SqlLiteral getRowsPerMatch() { + return rowsPerMatch; + } + + /** + * Options for {@code ROWS PER MATCH}. + */ + public enum RowsPerMatchOption { + ONE_ROW("ONE ROW PER MATCH"), + ALL_ROWS("ALL ROWS PER MATCH"); + + private final String sql; + + RowsPerMatchOption(String sql) { + this.sql = sql; + } + + @Override public String toString() { + return sql; + } + + public SqlLiteral symbol(SqlParserPos pos) { + return SqlLiteral.createSymbol(this, pos); + } + } + /** * Options for {@code AFTER MATCH} clause. */ @@ -210,12 +246,12 @@ public class SqlMatchRecognize extends SqlCall { SqlParserPos pos, SqlNode... operands) { assert functionQualifier == null; - assert operands.length == 8; + assert operands.length == 9; return new SqlMatchRecognize(pos, operands[0], operands[1], (SqlLiteral) operands[2], (SqlLiteral) operands[3], (SqlNodeList) operands[4], (SqlNodeList) operands[5], operands[6], - (SqlNodeList) operands[7]); + (SqlNodeList) operands[7], (SqlLiteral) operands[8]); } @Override public <R> void acceptCall( @@ -263,6 +299,11 @@ public class SqlMatchRecognize extends SqlCall { writer.endList(measureFrame); } + if (pattern.rowsPerMatch != null) { + writer.newlineAndIndent(); + pattern.rowsPerMatch.unparse(writer, 0, 0); + } + if (pattern.after != null) { writer.newlineAndIndent(); writer.sep("AFTER MATCH"); http://git-wip-us.apache.org/repos/asf/calcite/blob/e117c10c/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java index a035d34..b8cddb0 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java @@ -4499,6 +4499,12 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { getNamespace(call).unwrap(MatchRecognizeNamespace.class); assert ns.rowType == null; + // rows per match + final SqlLiteral rowsPerMatch = matchRecognize.getRowsPerMatch(); + final boolean allRows = rowsPerMatch != null + && rowsPerMatch.getValue() + == SqlMatchRecognize.RowsPerMatchOption.ALL_ROWS; + // retrieve pattern variables used in pattern and subset SqlNode pattern = matchRecognize.getPattern(); PatternVarVisitor visitor = new PatternVarVisitor(scope); @@ -4539,7 +4545,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { } List<Map.Entry<String, RelDataType>> fields = - validateMeasure(matchRecognize, scope); + validateMeasure(matchRecognize, scope, allRows); final RelDataType rowType = typeFactory.createStructType(fields); if (matchRecognize.getMeasureList().size() == 0) { ns.setType(getNamespace(matchRecognize.getTableRef()).getRowType()); @@ -4549,7 +4555,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { } private List<Map.Entry<String, RelDataType>> validateMeasure(SqlMatchRecognize mr, - MatchRecognizeScope scope) { + MatchRecognizeScope scope, boolean allRows) { final List<String> aliases = new ArrayList<>(); final List<SqlNode> sqlNodes = new ArrayList<>(); final SqlNodeList measures = mr.getMeasureList(); @@ -4561,7 +4567,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { aliases.add(alias); SqlNode expand = expand(measure, scope); - expand = navigationInMeasure(expand); + expand = navigationInMeasure(expand, allRows); setOriginal(expand, measure); inferUnknownTypes(unknownType, scope, expand); @@ -4586,15 +4592,17 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { return fields; } - private SqlNode navigationInMeasure(SqlNode node) { - Set<String> prefix = node.accept(new PatternValidator(true)); + private SqlNode navigationInMeasure(SqlNode node, boolean allRows) { + final Set<String> prefix = node.accept(new PatternValidator(true)); Util.discard(prefix); - List<SqlNode> ops = ((SqlCall) node).getOperandList(); - - SqlOperator defaultOp = SqlStdOperatorTable.FINAL; - if (!isRunningOrFinal(ops.get(0).getKind()) - || ops.get(0).getKind() == SqlKind.RUNNING) { - SqlNode newNode = defaultOp.createCall(SqlParserPos.ZERO, ops.get(0)); + final List<SqlNode> ops = ((SqlCall) node).getOperandList(); + + final SqlOperator defaultOp = + allRows ? SqlStdOperatorTable.RUNNING : SqlStdOperatorTable.FINAL; + final SqlNode op0 = ops.get(0); + if (!isRunningOrFinal(op0.getKind()) + || !allRows && op0.getKind() == SqlKind.RUNNING) { + SqlNode newNode = defaultOp.createCall(SqlParserPos.ZERO, op0); node = SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO, newNode, ops.get(1)); } return node; http://git-wip-us.apache.org/repos/asf/calcite/blob/e117c10c/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index d5fc1c0..0df7230 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -2180,6 +2180,10 @@ public class SqlToRelConverter { definitionNodes.put(alias, rex); } + final SqlLiteral rowsPerMatch = matchRecognize.getRowsPerMatch(); + final boolean allRows = rowsPerMatch != null + && rowsPerMatch.getValue() == SqlMatchRecognize.RowsPerMatchOption.ALL_ROWS; + matchBb.setPatternVarRef(false); final RelFactories.MatchFactory factory = @@ -2189,7 +2193,7 @@ public class SqlToRelConverter { matchRecognize.getStrictStart().booleanValue(), matchRecognize.getStrictEnd().booleanValue(), definitionNodes.build(), measureNodes.build(), after, - subsetMap, rowType); + subsetMap, allRows, rowType); bb.setRoot(rel, false); } @@ -5011,7 +5015,7 @@ public class SqlToRelConverter { * * <blockquote><code>AVG(x)</code></blockquote> * - * becomes + * <p>becomes * * <blockquote><code>SUM(x) / COUNT(x)</code></blockquote> * @@ -5021,12 +5025,12 @@ public class SqlToRelConverter { * * <blockquote><code>MIN(x), MAX(x)</code></blockquote> * - * are converted to + * <p>are converted to * * <blockquote><code>$HistogramMin($Histogram(x)), * $HistogramMax($Histogram(x))</code></blockquote> * - * Common sub-expression elmination will ensure that only one histogram is + * <p>Common sub-expression elimination will ensure that only one histogram is * computed. */ private class HistogramShuttle extends RexShuttle { http://git-wip-us.apache.org/repos/asf/calcite/blob/e117c10c/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java index e48bb03..ccfb46e 100644 --- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java +++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java @@ -659,6 +659,7 @@ public class RelToSqlConverterTest { String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -681,6 +682,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" + $)\n" + "DEFINE " @@ -703,6 +705,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (^ \"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -725,6 +728,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (^ \"STRT\" \"DOWN\" + \"UP\" + $)\n" + "DEFINE " @@ -747,6 +751,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" * \"UP\" ?)\n" + "DEFINE " @@ -769,6 +774,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" {- \"DOWN\" -} \"UP\" ?)\n" + "DEFINE " @@ -792,6 +798,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" { 2 } \"UP\" { 3, })\n" + "DEFINE " @@ -814,6 +821,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" { , 2 } \"UP\" { 3, 5 })\n" + "DEFINE " @@ -836,6 +844,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" {- \"DOWN\" + -} {- \"UP\" * -})\n" + "DEFINE " @@ -859,6 +868,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN " + "(\"A\" \"B\" \"C\" | \"A\" \"C\" \"B\" | \"B\" \"A\" \"C\" " @@ -882,6 +892,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -904,6 +915,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -944,6 +956,7 @@ public class RelToSqlConverterTest { + "WHERE \"customer\".\"city\" = 'San Francisco' " + "AND \"product_class\".\"product_department\" = 'Snacks') " + "MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -967,6 +980,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -989,6 +1003,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1011,6 +1026,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1034,6 +1050,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1065,6 +1082,7 @@ public class RelToSqlConverterTest { + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1096,6 +1114,7 @@ public class RelToSqlConverterTest { + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1127,6 +1146,7 @@ public class RelToSqlConverterTest { + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL (RUNNING LAST(\"DOWN\".\"net_weight\", 0)) AS \"BOTTOM_NW\", " + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"END_NW\"\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1158,6 +1178,7 @@ public class RelToSqlConverterTest { + "FINAL COUNT(\"UP\".\"net_weight\") AS \"UP_CNT\", " + "FINAL COUNT(\"*\".\"net_weight\") AS \"DOWN_CNT\", " + "FINAL (RUNNING COUNT(\"*\".\"net_weight\")) AS \"RUNNING_CNT\"\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1191,6 +1212,7 @@ public class RelToSqlConverterTest { + "FINAL LAST(\"UP\".\"net_weight\", 0) AS \"UP_CNT\", " + "FINAL (SUM(\"DOWN\".\"net_weight\") / COUNT(\"DOWN\".\"net_weight\")) " + "AS \"DOWN_CNT\"\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1222,6 +1244,7 @@ public class RelToSqlConverterTest { + "FINAL FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"UP_CNT\", " + "FINAL SUM(\"DOWN\".\"net_weight\") AS \"DOWN_CNT\"\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN " + "(\"STRT\" \"DOWN\" + \"UP\" +)\n" @@ -1254,6 +1277,7 @@ public class RelToSqlConverterTest { + "FINAL FIRST(\"STRT\".\"net_weight\", 0) AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"UP_CNT\", " + "FINAL SUM(\"DOWN\".\"net_weight\") AS \"DOWN_CNT\"\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN " + "(\"STRT\" \"DOWN\" + \"UP\" +)\n" @@ -1279,6 +1303,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1302,6 +1327,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP PAST LAST ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1325,6 +1351,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO FIRST \"DOWN\"\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1348,6 +1375,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO LAST \"DOWN\"\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1371,6 +1399,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO LAST \"DOWN\"\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "DEFINE " @@ -1395,6 +1424,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT *\n" + "FROM (SELECT *\n" + "FROM \"foodmart\".\"product\") MATCH_RECOGNIZE(\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO LAST \"DOWN\"\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\")\n" @@ -1429,6 +1459,7 @@ public class RelToSqlConverterTest { + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + "FINAL (SUM(\"STDN\".\"net_weight\") / " + "COUNT(\"STDN\".\"net_weight\")) AS \"AVG_STDN\"\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\")\n" @@ -1462,6 +1493,7 @@ public class RelToSqlConverterTest { + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + "FINAL SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\")\n" @@ -1495,6 +1527,7 @@ public class RelToSqlConverterTest { + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + "FINAL SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "ONE ROW PER MATCH\n" + "AFTER MATCH SKIP TO NEXT ROW\n" + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\"), \"STDN2\" = (\"DOWN\", \"STRT\")\n" @@ -1506,6 +1539,76 @@ public class RelToSqlConverterTest { sql(sql).ok(expected); } + @Test public void testMatchRecognizeRowsPerMatch1() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " measures STRT.\"net_weight\" as start_nw," + + " LAST(DOWN.\"net_weight\") as bottom_nw," + + " SUM(STDN.\"net_weight\") as avg_stdn" + + " ONE ROW PER MATCH\n" + + " pattern (strt down+ up+)\n" + + " subset stdn = (strt, down), stdn2 = (strt, down)\n" + + " define\n" + + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n" + + " up as up.\"net_weight\" > prev(up.\"net_weight\")\n" + + " ) mr"; + + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"product\") " + + "MATCH_RECOGNIZE(\n" + + "MEASURES " + + "FINAL \"STRT\".\"net_weight\" AS \"START_NW\", " + + "FINAL LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + + "FINAL SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "ONE ROW PER MATCH\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\"), \"STDN2\" = (\"DOWN\", \"STRT\")\n" + + "DEFINE " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "PREV(\"UP\".\"net_weight\", 1))"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizeRowsPerMatch2() { + final String sql = "select *\n" + + " from \"product\" match_recognize\n" + + " (\n" + + " measures STRT.\"net_weight\" as start_nw," + + " LAST(DOWN.\"net_weight\") as bottom_nw," + + " SUM(STDN.\"net_weight\") as avg_stdn" + + " ALL ROWS PER MATCH\n" + + " pattern (strt down+ up+)\n" + + " subset stdn = (strt, down), stdn2 = (strt, down)\n" + + " define\n" + + " down as down.\"net_weight\" < PREV(down.\"net_weight\"),\n" + + " up as up.\"net_weight\" > prev(up.\"net_weight\")\n" + + " ) mr"; + + final String expected = "SELECT *\n" + + "FROM (SELECT *\n" + + "FROM \"foodmart\".\"product\") " + + "MATCH_RECOGNIZE(\n" + + "MEASURES " + + "RUNNING \"STRT\".\"net_weight\" AS \"START_NW\", " + + "RUNNING LAST(\"DOWN\".\"net_weight\", 0) AS \"BOTTOM_NW\", " + + "RUNNING SUM(\"STDN\".\"net_weight\") AS \"AVG_STDN\"\n" + + "ALL ROWS PER MATCH\n" + + "AFTER MATCH SKIP TO NEXT ROW\n" + + "PATTERN (\"STRT\" \"DOWN\" + \"UP\" +)\n" + + "SUBSET \"STDN\" = (\"DOWN\", \"STRT\"), \"STDN2\" = (\"DOWN\", \"STRT\")\n" + + "DEFINE " + + "\"DOWN\" AS PREV(\"DOWN\".\"net_weight\", 0) < " + + "PREV(\"DOWN\".\"net_weight\", 1), " + + "\"UP\" AS PREV(\"UP\".\"net_weight\", 0) > " + + "PREV(\"UP\".\"net_weight\", 1))"; + sql(sql).ok(expected); + } + /** Fluid interface to run tests. */ private static class Sql { private CalciteAssert.SchemaSpec schemaSpec; http://git-wip-us.apache.org/repos/asf/calcite/blob/e117c10c/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java index b3dafec..bb74daa 100644 --- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java +++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java @@ -7807,27 +7807,83 @@ public class SqlParserTest { @Test public void testMatchRecognizeSubset3() { final String sql = "select *\n" - + " from t match_recognize\n" - + " (\n" - + " measures STRT.ts as start_ts," - + " LAST(DOWN.ts) as bottom_ts," - + " AVG(stdn.price) as stdn_avg" - + " pattern (strt down+ up+)\n" - + " subset stdn = (strt, down), stdn2 = (strt, down)\n" - + " define\n" - + " down as down.price < PREV(down.price),\n" - + " up as up.price > prev(up.price)\n" - + " ) mr"; + + " from t match_recognize\n" + + " (\n" + + " measures STRT.ts as start_ts," + + " LAST(DOWN.ts) as bottom_ts," + + " AVG(stdn.price) as stdn_avg" + + " pattern (strt down+ up+)\n" + + " subset stdn = (strt, down), stdn2 = (strt, down)\n" + + " define\n" + + " down as down.price < PREV(down.price),\n" + + " up as up.price > prev(up.price)\n" + + " ) mr"; + final String expected = "SELECT *\n" + + "FROM `T` MATCH_RECOGNIZE(\n" + + "MEASURES `STRT`.`TS` AS `START_TS`, " + + "LAST(`DOWN`.`TS`, 0) AS `BOTTOM_TS`, " + + "AVG(`STDN`.`PRICE`) AS `STDN_AVG`\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n" + + "SUBSET (`STDN` = (`STRT`, `DOWN`)), (`STDN2` = (`STRT`, `DOWN`))\n" + + "DEFINE `DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), " + + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))" + + ") AS `MR`"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizeRowsPerMatch1() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " measures STRT.ts as start_ts," + + " LAST(DOWN.ts) as bottom_ts," + + " AVG(stdn.price) as stdn_avg" + + " ONE ROW PER MATCH" + + " pattern (strt down+ up+)\n" + + " subset stdn = (strt, down), stdn2 = (strt, down)\n" + + " define\n" + + " down as down.price < PREV(down.price),\n" + + " up as up.price > prev(up.price)\n" + + " ) mr"; + final String expected = "SELECT *\n" + + "FROM `T` MATCH_RECOGNIZE(\n" + + "MEASURES `STRT`.`TS` AS `START_TS`, " + + "LAST(`DOWN`.`TS`, 0) AS `BOTTOM_TS`, " + + "AVG(`STDN`.`PRICE`) AS `STDN_AVG`\n" + + "ONE ROW PER MATCH\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n" + + "SUBSET (`STDN` = (`STRT`, `DOWN`)), (`STDN2` = (`STRT`, `DOWN`))\n" + + "DEFINE `DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), " + + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))" + + ") AS `MR`"; + sql(sql).ok(expected); + } + + @Test public void testMatchRecognizeRowsPerMatch2() { + final String sql = "select *\n" + + " from t match_recognize\n" + + " (\n" + + " measures STRT.ts as start_ts," + + " LAST(DOWN.ts) as bottom_ts," + + " AVG(stdn.price) as stdn_avg" + + " ALL ROWS PER MATCH" + + " pattern (strt down+ up+)\n" + + " subset stdn = (strt, down), stdn2 = (strt, down)\n" + + " define\n" + + " down as down.price < PREV(down.price),\n" + + " up as up.price > prev(up.price)\n" + + " ) mr"; final String expected = "SELECT *\n" - + "FROM `T` MATCH_RECOGNIZE(\n" - + "MEASURES `STRT`.`TS` AS `START_TS`, " - + "LAST(`DOWN`.`TS`, 0) AS `BOTTOM_TS`, " - + "AVG(`STDN`.`PRICE`) AS `STDN_AVG`\n" - + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n" - + "SUBSET (`STDN` = (`STRT`, `DOWN`)), (`STDN2` = (`STRT`, `DOWN`))\n" - + "DEFINE `DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), " - + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))" - + ") AS `MR`"; + + "FROM `T` MATCH_RECOGNIZE(\n" + + "MEASURES `STRT`.`TS` AS `START_TS`, " + + "LAST(`DOWN`.`TS`, 0) AS `BOTTOM_TS`, " + + "AVG(`STDN`.`PRICE`) AS `STDN_AVG`\n" + + "ALL ROWS PER MATCH\n" + + "PATTERN (((`STRT` (`DOWN` +)) (`UP` +)))\n" + + "SUBSET (`STDN` = (`STRT`, `DOWN`)), (`STDN2` = (`STRT`, `DOWN`))\n" + + "DEFINE `DOWN` AS (`DOWN`.`PRICE` < PREV(`DOWN`.`PRICE`, 1)), " + + "`UP` AS (`UP`.`PRICE` > PREV(`UP`.`PRICE`, 1))" + + ") AS `MR`"; sql(sql).ok(expected); }
