From a6136179e9cc69db0d4eef033c8f74dc5794f936 Mon Sep 17 00:00:00 2001 From: Tatsuo Ishii Date: Tue, 18 Nov 2025 11:22:15 +0900 Subject: [PATCH v35 6/8] Row pattern recognition patch (docs). --- doc/src/sgml/advanced.sgml | 81 ++++++++++++++++++++++++++++++ doc/src/sgml/func/func-window.sgml | 53 +++++++++++++++++++ doc/src/sgml/ref/select.sgml | 49 ++++++++++++++++-- 3 files changed, 180 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 451bcb202ec..7514f2a3848 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -540,6 +540,87 @@ WHERE pos < 3; two rows for each department). + + Row pattern common syntax can be used to perform row pattern recognition + in a query. The row pattern common syntax includes two sub + clauses: DEFINE + and PATTERN. DEFINE defines + definition variables along with an expression. The expression must be a + logical expression, which means it must + return TRUE, FALSE + or NULL. The expression may comprise column references + and functions. Window functions, aggregate functions and subqueries are + not allowed. 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 the price column in the + previous row if it's called in a context of row pattern recognition. Thus + in the second line the definition variable "UP" is TRUE + when the price column in the current row is greater than the price column + in the previous row. Likewise, "DOWN" is TRUE when the + 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 + conditions defined in the DEFINE clause. For example + following PATTERN defines a sequence of rows starting + with the a row satisfying "LOWPRICE", then one or more rows satisfying + "UP" and finally one or more rows satisfying "DOWN". Note that "+" means + one or more matches. Also you can use "*", which means zero or more + matches. If a sequence of rows which satisfies the PATTERN is found, in + the starting row all columns or functions are shown in the target + list. Note that aggregations only look into the matched rows, rather than + the whole frame. On the second or subsequent rows all window functions are + shown as NULL. Aggregates are NULL or 0 depending on its aggregation + definition. A count() aggregate shows 0. For rows that do not match on the + PATTERN, columns are shown AS NULL too. Example of + a SELECT using the DEFINE + and PATTERN clause is as follows. + + +SELECT company, tdate, price, + first_value(price) OVER w, + max(price) OVER w, + count(price) OVER w +FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + 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) +); + + + company | tdate | price | first_value | max | count +----------+------------+-------+-------------+-----+------- + company1 | 2023-07-01 | 100 | 100 | 200 | 4 + company1 | 2023-07-02 | 200 | | | 0 + company1 | 2023-07-03 | 150 | | | 0 + company1 | 2023-07-04 | 140 | | | 0 + company1 | 2023-07-05 | 150 | | | 0 + company1 | 2023-07-06 | 90 | 90 | 130 | 4 + company1 | 2023-07-07 | 110 | | | 0 + company1 | 2023-07-08 | 130 | | | 0 + company1 | 2023-07-09 | 120 | | | 0 + company1 | 2023-07-10 | 130 | | | 0 +(10 rows) + + + 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/func-window.sgml b/doc/src/sgml/func/func-window.sgml index bcf755c9ebc..ae36e0f3135 100644 --- a/doc/src/sgml/func/func-window.sgml +++ b/doc/src/sgml/func/func-window.sgml @@ -278,6 +278,59 @@ nth_value. + + 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 FROM FIRST or FROM LAST diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index ca5dd14d627..428bd4f7372 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -937,6 +937,7 @@ WINDOW window_name AS ( expression [, ...] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ frame_clause ] +[ row_pattern_common_syntax ] @@ -979,8 +980,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 @@ -1087,9 +1088,51 @@ 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 | AFTER MATCH SKIP TO NEXT ROW ] +PATTERN pattern_variable_name[+] [, ...] +DEFINE definition_varible_name AS expression [, ...] + + 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. + + + + The SQL standard defines more subclauses: MEASURES + and SUBSET. They are not currently supported + in PostgreSQL. Also in the standard there are + more variations in AFTER MATCH clause. + + The purpose of a WINDOW clause is to specify the - behavior of window functions appearing in the query's + behavior of window functions appearing in the + query's SELECT list or ORDER BY clause. These functions -- 2.43.0