From: Tatsuo Ishii <ishii@postgresql.org>
Subject: [PATCH v11 5/7] Row pattern recognition patch (docs).
Date: Wed, 8 Nov 2023 15:57:06 +0900
---
doc/src/sgml/advanced.sgml | 80 ++++++++++++++++++++++++++++++++++++
doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++
doc/src/sgml/ref/select.sgml | 38 ++++++++++++++++-
3 files changed, 170 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgmlindex 755c9f1485..cf18dd887e 100644--- a/doc/src/sgml/advanced.sgml+++ b/doc/src/sgml/advanced.sgml@@ -537,6 +537,86 @@ WHERE pos < 3;
<literal>rank</literal> less than 3.
</para>
+ <para>+ Row pattern common syntax can be used to perform row pattern recognition+ in a query. Row pattern common syntax includes two sub+ clauses: <literal>DEFINE</literal>+ and <literal>PATTERN</literal>. <literal>DEFINE</literal> defines+ definition variables along with an expression. The expression must be a+ logical expression, which means it must+ return <literal>TRUE</literal>, <literal>FALSE</literal>+ or <literal>NULL</literal>. The expression may comprise column references+ and functions. Window functions, aggregate functions and subqueries are+ not allowed. An example of <literal>DEFINE</literal> is as follows.++<programlisting>+DEFINE+ LOWPRICE AS price <= 100,+ UP AS price > PREV(price),+ DOWN AS price < PREV(price)+</programlisting>++ Note that <function>PREV</function> returns the price column in the+ previous row if it's called in a context of row pattern recognition. So in+ the second line the definition variable "UP" is <literal>TRUE</literal>+ when the price column in the current row is greater than the price column+ in the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when+ the price column in the current row is lower than the price column in the+ previous row.+ </para>+ <para>+ Once <literal>DEFINE</literal> exists, <literal>PATTERN</literal> can be+ used. <literal>PATTERN</literal> defines a sequence of rows that satisfies+ certain conditions. For example following <literal>PATTERN</literal>+ defines that a row starts with the condition "LOWPRICE", then one or more+ rows satisfy "UP" and finally one or more rows satisfy "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 of the sequence of rows all window functions and+ aggregates are shown in the target list. Note that aggregations only look+ into the matched rows, rather than whole frame. In the second or+ subsequent rows all window functions and aggregates are NULL. For rows+ that do not match the PATTERN, all window functions and aggregates are+ shown AS NULL too, except count which shows 0. This is because the+ unmatched rows are in an empty frame. Example of+ a <literal>SELECT</literal> using the <literal>DEFINE</literal>+ and <literal>PATTERN</literal> clause is as follows.++<programlisting>+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)+);+</programlisting>+<screen>+ company | tdate | price | first_value | max | count +----------+------------+-------+-------------+-----+-------+ company1 | 2023-07-01 | 100 | 100 | 200 | 4+ company1 | 2023-07-02 | 200 | | | + company1 | 2023-07-03 | 150 | | | + company1 | 2023-07-04 | 140 | | | + company1 | 2023-07-05 | 150 | | | 0+ company1 | 2023-07-06 | 90 | 90 | 130 | 4+ company1 | 2023-07-07 | 110 | | | + company1 | 2023-07-08 | 130 | | | + company1 | 2023-07-09 | 120 | | | + company1 | 2023-07-10 | 130 | | | 0+</screen>+ </para>+
<para>
When a query involves multiple window functions, it is possible to write
out each one with a separate <literal>OVER</literal> clause, but this is
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex d963f0a0a0..c3a8167c8e 100644--- a/doc/src/sgml/func.sgml+++ b/doc/src/sgml/func.sgml@@ -21933,6 +21933,7 @@ SELECT count(*) FROM sometable;
returns <literal>NULL</literal> if there is no such row.
</para></entry>
</row>
+
</tbody>
</tgroup>
</table>
@@ -21972,6 +21973,59 @@ SELECT count(*) FROM sometable;
Other frame specifications can be used to obtain other effects.
</para>
+ <para>+ Row pattern recognition navigation functions are listed in+ <xref linkend="functions-rpr-navigation-table"/>. These functions+ can be used to describe DEFINE clause of Row pattern recognition.+ </para>++ <table id="functions-rpr-navigation-table">+ <title>Row Pattern Navigation Functions</title>+ <tgroup cols="1">+ <thead>+ <row>+ <entry role="func_table_entry"><para role="func_signature">+ Function+ </para>+ <para>+ Description+ </para></entry>+ </row>+ </thead>++ <tbody>+ <row>+ <entry role="func_table_entry"><para role="func_signature">+ <indexterm>+ <primary>prev</primary>+ </indexterm>+ <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> )+ <returnvalue>anyelement</returnvalue>+ </para>+ <para>+ Returns the column value at the previous row;+ returns NULL if there is no previous row in the window frame.+ </para></entry>+ </row>++ <row>+ <entry role="func_table_entry"><para role="func_signature">+ <indexterm>+ <primary>next</primary>+ </indexterm>+ <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> )+ <returnvalue>anyelement</returnvalue>+ </para>+ <para>+ Returns the column value at the next row;+ returns NULL if there is no next row in the window frame.+ </para></entry>+ </row>++ </tbody>+ </tgroup>+ </table>+
<note>
<para>
The SQL standard defines a <literal>RESPECT NULLS</literal> or
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgmlindex 42d78913cf..522ad9dd70 100644--- a/doc/src/sgml/ref/select.sgml+++ b/doc/src/sgml/ref/select.sgml@@ -969,8 +969,8 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
The <replaceable class="parameter">frame_clause</replaceable> can be one of
<synopsis>
-{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ]-{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ]+{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]+{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
</synopsis>
where <replaceable>frame_start</replaceable>
@@ -1077,6 +1077,40 @@ EXCLUDE NO OTHERS
a given peer group will be in the frame or excluded from it.
</para>
+ <para>+ The+ optional <replaceable class="parameter">row_pattern_common_syntax</replaceable>+ defines the <firstterm>row pattern recognition condition</firstterm> for+ this+ window. <replaceable class="parameter">row_pattern_common_syntax</replaceable>+ includes following subclauses. <literal>AFTER MATCH SKIP PAST LAST+ ROW</literal> or <literal>AFTER MATCH SKIP TO NEXT ROW</literal> controls+ how to proceed to next row position after a match+ found. With <literal>AFTER MATCH SKIP PAST LAST ROW</literal> (the+ default) next row position is next to the last row of previous match. On+ the other hand, with <literal>AFTER MATCH SKIP TO NEXT ROW</literal> next+ row position is always next to the last row of previous+ match. <literal>DEFINE</literal> defines definition variables along with a+ boolean expression. <literal>PATTERN</literal> defines a sequence of rows+ that satisfies certain conditions using variables defined+ in <literal>DEFINE</literal> clause. If the variable is not defined in+ the <literal>DEFINE</literal> clause, it is implicitly assumed+ following is defined in the <literal>DEFINE</literal> clause.++<synopsis>+<literal>variable_name</literal> AS TRUE+</synopsis>++ Note that the maximu number of variables defined+ in <literal>DEFINE</literal> clause is 26.++<synopsis>+[ AFTER MATCH SKIP PAST LAST ROW | AFTER MATCH SKIP TO NEXT ROW ]+PATTERN <replaceable class="parameter">pattern_variable_name</replaceable>[+] [, ...]+DEFINE <replaceable class="parameter">definition_varible_name</replaceable> AS <replaceable class="parameter">expression</replaceable> [, ...]+</synopsis>+ </para>+
<para>
The purpose of a <literal>WINDOW</literal> clause is to specify the
behavior of <firstterm>window functions</firstterm> appearing in the query's
--
2.25.1
----Next_Part(Wed_Nov__8_16_37_05_2023_872)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v11-0006-Row-pattern-recognition-patch-tests.patch"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ishii@postgresql.org
Subject: Re: [PATCH v11 5/7] Row pattern recognition patch (docs).
In-Reply-To: <no-message-id-92125@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox