public inbox for [email protected]
help / color / mirror / Atom feedFrom: Henson Choi <[email protected]>
To: Tatsuo Ishii <[email protected]>
To: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: Row pattern recognition
Date: Sun, 28 Jun 2026 19:12:22 +0900
Message-ID: <CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAAAe_zAjkjUZHMqu5DjgZH9ZZ6D5v4bBJy60ziwhHRoohnB7Zg@mail.gmail.com>
<CAAAe_zBAwUbzahW3wFM+toS6Ap565j1q9SOa0irhYSRTujGbyA@mail.gmail.com>
<CAAAe_zCBN3QZP6g3P5Yv5sOdJgUZE6iLs=5HxOyyOn7wkKXrMA@mail.gmail.com>
<[email protected]>
Hi Tatsuo,
Two things before the next review round. Both aim at the same thing:
keeping the v50 diff to what RPR genuinely changes, so the next round
reviews RPR on its own merits rather than hunks that drifted in or touch
pre-existing window code.
First, the cfbot is currently red on v49 across all platforms. The cause
is not RPR: master recently reworded the RESPECT/IGNORE NULLS error, which
left rpr_base's expected output stale, so the regression diff fails "make
check" everywhere. That is exactly the one-line fix you already agreed to
fold in; I have posted it here as v50-0021 so the series builds green on
its own. This one is required regardless.
Second, while going over the v49 diff against master I found several hunks
that are either unrelated to RPR or that touch pre-existing window code. I
split them into small patches so each can be decided on its own.
Patch list
----------
Every patch in this reply. revert-* removes the change from RPR;
optional-* re-applies the same change to master separately.
v50-0021 cfbot build fix (required regardless)
revert-0001 drop unrelated include and stray blank line (required
cleanup)
revert-0004 restore doc paragraph line wrapping (required
cleanup)
revert-0002 #2 drop the mark-position diagnostic (your call)
optional-0001 #2 land the diagnostic on master
revert-0003 #3 restore the funcname guard (your call)
optional-0002 #3 land the guard removal on master
revert-0005 #5 drop the EXCLUDE TIES tests (your call)
optional-0003 #5 land the tests on master
Required cleanups
-----------------
These only remove changes that crept in but have nothing to do with RPR.
I split them out so you can fold them into the feature patch; being
required cleanups, I recommend applying them:
revert-0001 drop an unrelated "nodes/plannodes.h" include and a stray
blank line in eval_windowaggregates()
revert-0004 restore the line wrapping of two pre-existing doc
paragraphs (advanced.sgml, ref/select.sgml)
Your call
---------
The next three touch pre-existing window code, so I would rather you decide
where each belongs. I prepared the building blocks so you can pick any
option:
#2 window_gettupleslot() mark-position elog -- add row positions
v49 extended the can't-happen error with the pos/markpos values.
(a) keep it in RPR -> apply neither patch
(b) drop it -> apply revert-0002
(c) land it on master separately -> revert-0002 + optional-0001
A tiny additive diagnostic; I lean to (c), but (a) is fine too.
#3 funcname guard in WinCheckAndInitializeNullTreatment()
v49 removed the "could not get function name" guard as unreachable.
(a) keep the removal in RPR -> apply neither patch
(b) restore the guard, drop it -> apply revert-0003
(c) remove it on master separately -> revert-0003 + optional-0002
The branch is genuinely unreachable, but it drops a deliberate guard
in pre-existing code, so of the three this one most warrants caution.
Preserving the guard via (b) or (c) looks safest, but I will leave the
decision to you.
#5 EXCLUDE TIES window-frame test coverage
Two nth_value/last_value cases. The accounting they exercise already
exists on master and the tests pass without RPR, so they are not RPR
coverage.
(a) keep them in RPR -> apply neither patch
(b) land them on master separately -> revert-0005 + optional-0003
I lean to (b).
For my part, I will carry out the deeper review against v50 with v50-0021
and revert-0001/0004 applied, plus whichever of the above you choose, as
the minimal green baseline.
Best,
Henson
Attachments:
[application/octet-stream] v50-0021-adjust-rpr_base-expected-output.patch (1.5K, ../CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com/3-v50-0021-adjust-rpr_base-expected-output.patch)
download | inline diff:
From cd781ecbfbc2ff7cac2aad07c515081f6332ca7a Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 25 Jun 2026 22:39:36 +0900
Subject: [PATCH] Adjust rpr_base expected output for refined IGNORE NULLS
error
Commit 419ce13b701 refined the error reported when RESPECT/IGNORE NULLS
is used on a non-window function, changing it from "only window functions
accept RESPECT/IGNORE NULLS" to "RESPECT/IGNORE NULLS specified, but %s
is not a window function". The rpr_base test was added before that
refinement and kept the old wording, so update its expected output to
match. No behavior change.
---
src/test/regress/expected/rpr_base.out | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index 33fcb40b1a8..7957595ad8b 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -2143,7 +2143,7 @@ SELECT count(*) OVER w FROM nt
WINDOW w AS (PARTITION BY g ORDER BY id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
PATTERN (A+) DEFINE A AS PREV(val) IGNORE NULLS IS NULL);
-ERROR: only window functions accept RESPECT/IGNORE NULLS
+ERROR: RESPECT/IGNORE NULLS specified, but prev is not a window function
LINE 4: PATTERN (A+) DEFINE A AS PREV(val) IGNORE NULLS IS NULL)...
^
-- Quoting does not escape: "prev" is nav, "PREV" is an ordinary name
--
2.50.1 (Apple Git-155)
[application/octet-stream] revert-0001-remove-incidental-changes.patch (1.2K, ../CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com/4-revert-0001-remove-incidental-changes.patch)
download | inline diff:
From 3ca65293a455be95c6489b946668894cd16e064f Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sun, 28 Jun 2026 13:24:29 +0900
Subject: [PATCH] Remove incidental changes unrelated to row pattern
recognition
- equalfuncs.c: drop unused "nodes/plannodes.h" include
- nodeWindowAgg.c: remove stray blank line in eval_windowaggregates()
---
src/backend/executor/nodeWindowAgg.c | 1 -
src/backend/nodes/equalfuncs.c | 1 -
2 files changed, 2 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index ae0fb036867..e4e97a6ed95 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -1027,7 +1027,6 @@ eval_windowaggregates(WindowAggState *winstate)
agg_row_slot, false);
if (ret < 0)
break;
-
if (ret == 0)
goto next_tuple;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 1bbda9ad367..3d1a1adf86e 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -20,7 +20,6 @@
#include "postgres.h"
#include "miscadmin.h"
-#include "nodes/plannodes.h"
#include "utils/datum.h"
--
2.50.1 (Apple Git-155)
[application/octet-stream] revert-0002-revert-mark-position-elog.patch (1.1K, ../CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com/5-revert-0002-revert-mark-position-elog.patch)
download | inline diff:
From 16e6d7476d51185ece31c2b03b899923cf0ebdab Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sun, 28 Jun 2026 13:26:18 +0900
Subject: [PATCH] Revert window_gettupleslot() mark-position elog to its
original message
The pos/markpos values added in the RPR branch are split out into a
separate preliminary patch, keeping the RPR diff minimal.
---
src/backend/executor/nodeWindowAgg.c | 3 +--
1 file changed, 1 insertion(+), 2 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index e4e97a6ed95..b1a86fb3a14 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3570,8 +3570,7 @@ window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot)
return false;
if (pos < winobj->markpos)
- elog(ERROR, "cannot fetch row: " INT64_FORMAT " before WindowObject's mark position: " INT64_FORMAT,
- pos, winobj->markpos);
+ elog(ERROR, "cannot fetch row before WindowObject's mark position");
oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory);
--
2.50.1 (Apple Git-155)
[application/octet-stream] revert-0003-restore-funcname-guard.patch (1.2K, ../CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com/6-revert-0003-restore-funcname-guard.patch)
download | inline diff:
From b753a11c7f04af8dce5b9baf023da2a3f210bb70 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sun, 28 Jun 2026 13:28:13 +0900
Subject: [PATCH] Restore funcname guard in WinCheckAndInitializeNullTreatment
This unreachable-branch removal is unrelated to row pattern recognition;
revert it to keep the RPR diff minimal.
---
src/backend/executor/nodeWindowAgg.c | 5 +++--
1 file changed, 3 insertions(+), 2 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index b1a86fb3a14..047287a6366 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -4936,11 +4936,12 @@ WinCheckAndInitializeNullTreatment(WindowObject winobj,
{
const char *funcname = get_func_name(fcinfo->flinfo->fn_oid);
- /* the executing function's name always resolves; stay safe regardless */
+ if (!funcname)
+ elog(ERROR, "could not get function name");
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("function %s does not allow RESPECT/IGNORE NULLS",
- funcname ? funcname : "?")));
+ funcname)));
}
else if (winobj->ignore_nulls == PARSER_IGNORE_NULLS)
winobj->ignore_nulls = IGNORE_NULLS;
--
2.50.1 (Apple Git-155)
[application/octet-stream] revert-0004-restore-doc-line-wrapping.patch (2.3K, ../CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com/7-revert-0004-restore-doc-line-wrapping.patch)
download | inline diff:
From 295957484cc7f54fb6055705b3b0ecb9c70bc135 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sun, 28 Jun 2026 14:47:42 +0900
Subject: [PATCH] Restore line wrapping of pre-existing window clause
documentation
The row pattern recognition docs patch re-wrapped two unrelated, pre-existing
paragraphs while inserting new text above them. Restore their original
wrapping to keep the documentation diff minimal:
- advanced.sgml: the tutorial WINDOW/OVER paragraph
- ref/select.sgml: the "purpose of a WINDOW clause" paragraph
---
doc/src/sgml/advanced.sgml | 8 ++++----
doc/src/sgml/ref/select.sgml | 3 +--
2 files changed, 5 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index bdc552ad75f..c9d4e2ffc64 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -679,10 +679,10 @@ FROM stock
<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
- duplicative and error-prone if the same windowing behavior is wanted for
- several functions. Instead, each windowing behavior can be named in
- a <literal>WINDOW</literal> clause and then referenced
- in <literal>OVER</literal>. For example:
+ duplicative and error-prone if the same windowing behavior is wanted
+ for several functions. Instead, each windowing behavior can be named
+ in a <literal>WINDOW</literal> clause and then referenced in <literal>OVER</literal>.
+ For example:
<programlisting>
SELECT sum(salary) OVER w, avg(salary) OVER w
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index be5ed814853..e0423cf5f91 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1216,8 +1216,7 @@ DEFINE <replaceable class="parameter">definition_variable_name</replaceable> AS
<para>
The purpose of a <literal>WINDOW</literal> clause is to specify the
- behavior of <firstterm>window functions</firstterm> appearing in the
- query's
+ behavior of <firstterm>window functions</firstterm> appearing in the query's
<link linkend="sql-select-list"><command>SELECT</command> list</link> or
<link linkend="sql-orderby"><literal>ORDER BY</literal></link> clause.
These functions
--
2.50.1 (Apple Git-155)
[application/octet-stream] revert-0005-remove-exclude-ties-tests.patch (2.9K, ../CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com/8-revert-0005-remove-exclude-ties-tests.patch)
download | inline diff:
From d8b60976ddb1c6b9c24c6e153ef51c28c61d476e Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sun, 28 Jun 2026 13:30:23 +0900
Subject: [PATCH] Remove EXCLUDE TIES window tests unrelated to row pattern
recognition
These nth_value/last_value frame tests exercise exclusion accounting that
already exists on the base branch and pass without RPR, so they don't
cover RPR-specific behavior; drop them to keep the RPR diff minimal.
---
src/test/regress/expected/window.out | 36 ----------------------------
src/test/regress/sql/window.sql | 8 -------
2 files changed, 44 deletions(-)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index dd887c6b925..90d9f953b81 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -1037,42 +1037,6 @@ FROM tenk1 WHERE unique1 < 10;
7 | 7 | 3
(10 rows)
-SELECT nth_value(unique1,2) over (ORDER BY four rows between current row and 3 following exclude ties),
- unique1, four
-FROM tenk1 WHERE unique1 < 10;
- nth_value | unique1 | four
------------+---------+------
- 5 | 0 | 0
- 5 | 8 | 0
- 5 | 4 | 0
- 6 | 5 | 1
- 6 | 9 | 1
- 6 | 1 | 1
- 3 | 6 | 2
- 3 | 2 | 2
- | 3 | 3
- | 7 | 3
-(10 rows)
-
-SELECT last_value(unique1) over (ORDER BY four rows between 1 following and 2 following exclude ties),
- unique1, four
-FROM tenk1 WHERE unique1 < 12 ORDER BY four, unique1;
- last_value | unique1 | four
-------------+---------+------
- 1 | 0 | 0
- | 4 | 0
- 5 | 8 | 0
- 6 | 1 | 1
- | 5 | 1
- 10 | 9 | 1
- 3 | 2 | 2
- | 6 | 2
- 7 | 10 | 2
- | 3 | 3
- | 7 | 3
- | 11 | 3
-(12 rows)
-
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 31fece7fb8a..5ac3a486e16 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -235,14 +235,6 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
unique1, four
FROM tenk1 WHERE unique1 < 10;
-SELECT nth_value(unique1,2) over (ORDER BY four rows between current row and 3 following exclude ties),
- unique1, four
-FROM tenk1 WHERE unique1 < 10;
-
-SELECT last_value(unique1) over (ORDER BY four rows between 1 following and 2 following exclude ties),
- unique1, four
-FROM tenk1 WHERE unique1 < 12 ORDER BY four, unique1;
-
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
--
2.50.1 (Apple Git-155)
[application/octet-stream] optional-0001-include-row-positions-in-mark-position-error.patch (1.2K, ../CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com/9-optional-0001-include-row-positions-in-mark-position-error.patch)
download | inline diff:
From 923a8a9f40f4f6c1cc716e257d315d937403f358 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sun, 28 Jun 2026 15:08:45 +0900
Subject: [PATCH] Include row positions in WindowObject mark-position error
window_gettupleslot() raises a can't-happen error when asked for a row
before the WindowObject's mark position. Include the requested position
and the mark position in the message, which makes the diagnostic directly
useful if it ever fires.
---
src/backend/executor/nodeWindowAgg.c | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index f1c524d00df..9514c684ab9 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3271,7 +3271,8 @@ window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot)
return false;
if (pos < winobj->markpos)
- elog(ERROR, "cannot fetch row before WindowObject's mark position");
+ elog(ERROR, "cannot fetch row: " INT64_FORMAT " before WindowObject's mark position: " INT64_FORMAT,
+ pos, winobj->markpos);
oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory);
--
2.50.1 (Apple Git-155)
[application/octet-stream] optional-0002-remove-unreachable-funcname-guard.patch (1.4K, ../CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com/10-optional-0002-remove-unreachable-funcname-guard.patch)
download | inline diff:
From 7bfc6e71dbdacae1f287c5a3832fafb81dc7cdf5 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sun, 28 Jun 2026 15:09:22 +0900
Subject: [PATCH] Remove unreachable function-name guard in null-treatment
check
In WinCheckAndInitializeNullTreatment() the window function is being
executed, so its OID always resolves and get_func_name() never returns
NULL; the "could not get function name" guard is unreachable. Drop it
and fall back to "?" inline instead.
---
src/backend/executor/nodeWindowAgg.c | 5 ++---
1 file changed, 2 insertions(+), 3 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index f1c524d00df..0ee6059932f 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3657,12 +3657,11 @@ WinCheckAndInitializeNullTreatment(WindowObject winobj,
{
const char *funcname = get_func_name(fcinfo->flinfo->fn_oid);
- if (!funcname)
- elog(ERROR, "could not get function name");
+ /* the executing function's name always resolves; stay safe regardless */
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("function %s does not allow RESPECT/IGNORE NULLS",
- funcname)));
+ funcname ? funcname : "?")));
}
else if (winobj->ignore_nulls == PARSER_IGNORE_NULLS)
winobj->ignore_nulls = IGNORE_NULLS;
--
2.50.1 (Apple Git-155)
[application/octet-stream] optional-0003-add-exclude-ties-test-coverage.patch (2.8K, ../CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com/11-optional-0003-add-exclude-ties-test-coverage.patch)
download | inline diff:
From 705047ac5a8350aaf2a16dd978e7371c31b503bc Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sun, 28 Jun 2026 14:21:41 +0900
Subject: [PATCH] Add EXCLUDE TIES window frame test coverage
Cover the EXCLUDE TIES exclusion accounting for nth_value() and
last_value() over ROWS frames, which was otherwise untested.
---
src/test/regress/expected/window.out | 36 ++++++++++++++++++++++++++++
src/test/regress/sql/window.sql | 8 +++++++
2 files changed, 44 insertions(+)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 90d9f953b81..dd887c6b925 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -1037,6 +1037,42 @@ FROM tenk1 WHERE unique1 < 10;
7 | 7 | 3
(10 rows)
+SELECT nth_value(unique1,2) over (ORDER BY four rows between current row and 3 following exclude ties),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ nth_value | unique1 | four
+-----------+---------+------
+ 5 | 0 | 0
+ 5 | 8 | 0
+ 5 | 4 | 0
+ 6 | 5 | 1
+ 6 | 9 | 1
+ 6 | 1 | 1
+ 3 | 6 | 2
+ 3 | 2 | 2
+ | 3 | 3
+ | 7 | 3
+(10 rows)
+
+SELECT last_value(unique1) over (ORDER BY four rows between 1 following and 2 following exclude ties),
+ unique1, four
+FROM tenk1 WHERE unique1 < 12 ORDER BY four, unique1;
+ last_value | unique1 | four
+------------+---------+------
+ 1 | 0 | 0
+ | 4 | 0
+ 5 | 8 | 0
+ 6 | 1 | 1
+ | 5 | 1
+ 10 | 9 | 1
+ 3 | 2 | 2
+ | 6 | 2
+ 7 | 10 | 2
+ | 3 | 3
+ | 7 | 3
+ | 11 | 3
+(12 rows)
+
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 5ac3a486e16..31fece7fb8a 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -235,6 +235,14 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
unique1, four
FROM tenk1 WHERE unique1 < 10;
+SELECT nth_value(unique1,2) over (ORDER BY four rows between current row and 3 following exclude ties),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT last_value(unique1) over (ORDER BY four rows between 1 following and 2 following exclude ties),
+ unique1, four
+FROM tenk1 WHERE unique1 < 12 ORDER BY four, unique1;
+
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
--
2.50.1 (Apple Git-155)
view thread (181+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Row pattern recognition
In-Reply-To: <CAAAe_zCd+gzpYSsC04NsKyCJeneChVZCaBP540d0p2=c3abvvw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox