From: Tatsuo Ishii Date: Sun, 25 Jun 2023 20:48:14 +0900 Subject: [PATCH v1 5/7] Row pattern recognition patch (docs). --- doc/src/sgml/advanced.sgml | 51 ++++++++++++++++++++++++++ doc/src/sgml/func.sgml | 69 ++++++++++++++++++++++++++++++++++++ doc/src/sgml/ref/select.sgml | 18 ++++++++-- 3 files changed, 136 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 755c9f1485..23ee285b40 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -537,6 +537,57 @@ WHERE pos < 3; rank less than 3. + + Window function rpr 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, rpr(price) OVER w FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + 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 5a47ce4343..8069c58ca5 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -21648,6 +21648,22 @@ SELECT count(*) FROM sometable; returns NULL if there is no such row. + + + + + rpr + + rpr ( value anyelement ) + anyelement + + + Perform row pattern recognition using column specified + by value and returns the value of the column if + current row is the first matching row; + returns NULL otherwise. + + @@ -21687,6 +21703,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..c0fc16d773 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,20 @@ 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. + + +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(Sun_Jun_25_21_05_09_2023_126)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v1-0006-Row-pattern-recognition-patch-tests.patch"