From: Tatsuo Ishii Date: Fri, 22 Sep 2023 13:53:36 +0900 Subject: [PATCH v7 5/7] Row pattern recognition patch (docs). --- doc/src/sgml/advanced.sgml | 52 ++++++++++++++++++++++++++++++++++ doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++++++++++++++ doc/src/sgml/ref/select.sgml | 38 +++++++++++++++++++++++-- 3 files changed, 142 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 755c9f1485..f39ec8f2d5 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -537,6 +537,58 @@ WHERE pos < 3; rank less than 3. + + Row pattern common syntax can be used with row pattern common syntax to + perform row pattern recognition in a query. Row pattern common syntax + includes two sub clauses. DEFINE defines definition + variables along with an expression. The expression must be a logical + expression, which means it must + return TRUE, FALSE + or NULL. Moreover if the expression comprises a column + reference, it must be the argument of rpr. An example + of DEFINE is as follows. + + +DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) + + + Note that PREV returns price column in the previous + row if it's called in a context of row pattern recognition. So in the + second line means the definition variable "UP" is TRUE + when price column in the current row is greater than the price column in + the previous row. Likewise, "DOWN" is TRUE when when + price column in the current row is lower than the price column in the + previous row. + + + Once DEFINE exists, PATTERN can be + used. PATTERN defines a sequence of rows that satisfies + certain conditions. For example following PATTERN + defines that a row starts with the condition "LOWPRICE", then one or more + rows satisfy "UP" and finally one or more rows satisfy "DOWN". If a + sequence of rows found, rpr returns the column at the starting row. + Example of a SELECT using the DEFINE + and PATTERN clause is as follows. + + +SELECT company, tdate, price, max(price) OVER w FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + + + When a query involves multiple window functions, it is possible to write out each one with a separate OVER clause, but this is diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 24ad87f910..9c99dda4ae 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -21780,6 +21780,7 @@ SELECT count(*) FROM sometable; returns NULL if there is no such row. + @@ -21819,6 +21820,59 @@ SELECT count(*) FROM sometable; Other frame specifications can be used to obtain other effects. + + Row pattern recognition navigation functions are listed in + . These functions + can be used to describe DEFINE clause of Row pattern recognition. + + + + Row Pattern Navigation Functions + + + + + Function + + + Description + + + + + + + + + prev + + prev ( value anyelement ) + anyelement + + + Returns the column value at the previous row; + returns NULL if there is no previous row in the window frame. + + + + + + + next + + next ( value anyelement ) + anyelement + + + Returns the column value at the next row; + returns NULL if there is no next row in the window frame. + + + + + +
+ The SQL standard defines a RESPECT NULLS or diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 0ee0cc7e64..056768b330 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -966,8 +966,8 @@ WINDOW window_name AS ( frame_clause can be one of -{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ] -{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ] +{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ] [row_pattern_common_syntax] +{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ] [row_pattern_common_syntax] where frame_start @@ -1074,6 +1074,40 @@ EXCLUDE NO OTHERS a given peer group will be in the frame or excluded from it. + + The + optional row_pattern_common_syntax + defines the row pattern recognition condition for + this + window. row_pattern_common_syntax + includes following subclauses. AFTER MATCH SKIP PAST LAST + ROW or AFTER MATCH SKIP TO NEXT ROW controls + how to proceed to next row position after a match + found. With AFTER MATCH SKIP PAST LAST ROW (the + default) next row position is next to the last row of previous match. On + the other hand, with AFTER MATCH SKIP TO NEXT ROW next + row position is always next to the last row of previous + match. DEFINE defines definition variables along with a + boolean expression. PATTERN defines a sequence of rows + that satisfies certain conditions using variables defined + in DEFINE clause. If the variable is not defined in + the DEFINE clause, it is implicitly assumed + following is defined in the DEFINE clause. + + +variable_name AS TRUE + + + Note that the maximu number of variables defined + in DEFINE clause is 26. + + +[ AFTER MATCH SKIP PAST LAST ROW | AFTER MATCH SKIP TO NEXT ROW ] +PATTERN pattern_variable_name[+] [, ...] +DEFINE definition_varible_name AS expression [, ...] + + + The purpose of a WINDOW clause is to specify the behavior of window functions appearing in the query's -- 2.25.1 ----Next_Part(Fri_Sep_22_14_16_40_2023_530)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v7-0006-Row-pattern-recognition-patch-tests.patch"