v5-0010-Raise-qsort-insertion-sort-threshold.patch
text/x-patch
Filename: v5-0010-Raise-qsort-insertion-sort-threshold.patch
Type: text/x-patch
Part: 0
Message:
Re: A qsort template
Patch
Format: format-patch
Series: patch v5-0010
Subject: Raise qsort insertion sort threshold
| File | + | − |
|---|---|---|
| contrib/pg_stat_statements/expected/pg_stat_statements.out | 3 | 3 |
| src/include/lib/sort_template.h | 26 | 12 |
| src/test/regress/expected/create_index.out | 1 | 1 |
| src/test/regress/expected/geometry.out | 1 | 1 |
| src/test/regress/expected/groupingsets.out | 2 | 2 |
| src/test/regress/expected/inet.out | 2 | 2 |
| src/test/regress/expected/join.out | 1 | 1 |
| src/test/regress/expected/sqljson.out | 5 | 5 |
| src/test/regress/expected/tsrf.out | 14 | 14 |
| src/test/regress/expected/tuplesort.out | 5 | 5 |
| src/test/regress/expected/window.out | 271 | 271 |
From 74b934bc5ed8f6733c064c0ef832e1aa9949f216 Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Wed, 6 Apr 2022 16:38:28 +0700
Subject: [PATCH v5] Raise qsort insertion sort threshold
Our qsort algorithm is from NetBSD and is described in the 1993 paper
"Engineering a Sort Function". It includes three hard coded thresholds that
control the point at which we drop to insertion sort, and whether we use 1,
3 or 9 samples for choosing a pivot. The insertion sort threshold of 7 was
chosen by testing on hardware available in 1993. Our testing has shown
that hardware from 2010 to 2020 prefer a larger threshold. The ideal value
varies with input distribution, but 20 seems to be a good compromise that
works well with all of them. This is more in line with thresholds found
in other software as well.
Since the new insertion sort threshold is larger then the singleton range
where a single sample is chosen for the pivot, get rid of the middle
range. There are now two thresholds.
Also use macros intead of hard-coded values. This improves readability
and enables specializing the thresholds if desired.
The changes in the regression tests are needed for the change in sort
stability when the sort key contains duplicates.
Thomas Munro and John Naylor
Discussion:
https://www.postgresql.org/message-id/CA%2BhUKGJhOtjQH%2BwjCodtJzHAFCAPYyt6Qm9E1mUoJph42qo1hg%40mail.gmail.com
Discussion:
https://www.postgresql.org/message-id/CAFBsxsHr-C1xqjUMjeUN3-FvNzKiAt3gcfBKt8PFN2mov7z2gQ%40mail.gmail.com
---
.../expected/pg_stat_statements.out | 6 +-
src/include/lib/sort_template.h | 38 +-
src/test/regress/expected/create_index.out | 2 +-
src/test/regress/expected/geometry.out | 2 +-
src/test/regress/expected/groupingsets.out | 4 +-
src/test/regress/expected/inet.out | 4 +-
src/test/regress/expected/join.out | 2 +-
src/test/regress/expected/sqljson.out | 10 +-
src/test/regress/expected/tsrf.out | 28 +-
src/test/regress/expected/tuplesort.out | 10 +-
src/test/regress/expected/window.out | 542 +++++++++---------
11 files changed, 331 insertions(+), 317 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index e0abe34bb6..aeb8f04aea 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -169,12 +169,12 @@ SELECT *
SELECT * FROM test ORDER BY a;
a | b
---+----------------------
- 1 | a
1 | 111
- 2 | b
+ 1 | a
2 | 222
- 3 | c
+ 2 | b
3 | 333
+ 3 | c
4 | 444
5 | 555
6 | 666
diff --git a/src/include/lib/sort_template.h b/src/include/lib/sort_template.h
index 3122a93009..7b92b2c084 100644
--- a/src/include/lib/sort_template.h
+++ b/src/include/lib/sort_template.h
@@ -40,6 +40,11 @@
*
* - ST_COMPARE_ARG_TYPE - type of extra argument
*
+ * Optional macros for tuning algorithm choices:
+ *
+ * - ST_THRESHOLD_INSERTION_SORT - below this size we do insertion sort
+ * - ST_THRESHOLD_MED9 - above this size we partition with med9, otherwise with med3
+ *
* The prototype of the generated sort function is:
*
* void ST_SORT(ST_ELEMENT_TYPE *data, size_t n,
@@ -172,6 +177,14 @@
#define ST_SORT_INVOKE_ARG
#endif
+/* Default input size thresholds to control algorithm behavior. */
+#ifndef ST_THRESHOLD_INSERTION_SORT
+#define ST_THRESHOLD_INSERTION_SORT 20
+#endif
+#ifndef ST_THRESHOLD_MED9
+#define ST_THRESHOLD_MED9 40
+#endif
+
#ifdef ST_DECLARE
#ifdef ST_COMPARE_RUNTIME_POINTER
@@ -296,7 +309,7 @@ ST_SORT(ST_ELEMENT_TYPE * data, size_t n
loop:
DO_CHECK_FOR_INTERRUPTS();
- if (n < 7)
+ if (n < ST_THRESHOLD_INSERTION_SORT)
{
for (pm = a + ST_POINTER_STEP; pm < a + n * ST_POINTER_STEP;
pm += ST_POINTER_STEP)
@@ -318,21 +331,20 @@ loop:
}
if (presorted)
return;
+ /* Partition with median of three for medium input. */
+ pl = a;
pm = a + (n / 2) * ST_POINTER_STEP;
- if (n > 7)
+ pn = a + (n - 1) * ST_POINTER_STEP;
+ if (n > ST_THRESHOLD_MED9)
{
- pl = a;
- pn = a + (n - 1) * ST_POINTER_STEP;
- if (n > 40)
- {
- size_t d = (n / 8) * ST_POINTER_STEP;
+ /* Partition with median of nine for large input. */
+ size_t d = (n / 8) * ST_POINTER_STEP;
- pl = DO_MED3(pl, pl + d, pl + 2 * d);
- pm = DO_MED3(pm - d, pm, pm + d);
- pn = DO_MED3(pn - 2 * d, pn - d, pn);
- }
- pm = DO_MED3(pl, pm, pn);
+ pl = DO_MED3(pl, pl + d, pl + 2 * d);
+ pm = DO_MED3(pm - d, pm, pm + d);
+ pn = DO_MED3(pn - 2 * d, pn - d, pn);
}
+ pm = DO_MED3(pl, pm, pn);
DO_SWAP(a, pm);
pa = pb = a + ST_POINTER_STEP;
pc = pd = a + (n - 1) * ST_POINTER_STEP;
@@ -430,3 +442,5 @@ loop:
#undef ST_SORT_PROTO_ELEMENT_SIZE
#undef ST_SWAP
#undef ST_SWAPN
+#undef ST_THRESHOLD_INSERTION_SORT
+#undef ST_THRESHOLD_MED9
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index d55aec3a1d..9b548fe3e8 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -172,8 +172,8 @@ SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
(10,10)
(-5,-12)
(5.1,34.5)
- (Infinity,1e+300)
(1e+300,Infinity)
+ (Infinity,1e+300)
(NaN,NaN)
(11 rows)
diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out
index 3b364d1e3b..275d915f92 100644
--- a/src/test/regress/expected/geometry.out
+++ b/src/test/regress/expected/geometry.out
@@ -4321,8 +4321,8 @@ SELECT c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance
<(100,1),115> | (1e+300,Infinity) | Infinity
<(100,1),115> | (Infinity,1e+300) | Infinity
<(3,5),0> | (NaN,NaN) | NaN
- <(1,2),3> | (NaN,NaN) | NaN
<(5,1),3> | (NaN,NaN) | NaN
+ <(1,2),3> | (NaN,NaN) | NaN
<(1,3),5> | (NaN,NaN) | NaN
<(100,200),10> | (NaN,NaN) | NaN
<(1,2),100> | (NaN,NaN) | NaN
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 6a56f0b09c..f0be23af7f 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -94,12 +94,12 @@ select a, b, grouping(a,b), sum(v), count(*), max(v)
1 | | 1 | 60 | 5 | 14
1 | 1 | 0 | 21 | 2 | 11
2 | | 1 | 15 | 1 | 15
- 3 | | 1 | 33 | 2 | 17
1 | 2 | 0 | 25 | 2 | 13
+ 3 | | 1 | 33 | 2 | 17
1 | 3 | 0 | 14 | 1 | 14
4 | | 1 | 37 | 2 | 19
- 4 | 1 | 0 | 37 | 2 | 19
2 | 3 | 0 | 15 | 1 | 15
+ 4 | 1 | 0 | 37 | 2 | 19
3 | 3 | 0 | 16 | 1 | 16
3 | 4 | 0 | 17 | 1 | 17
(12 rows)
diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index d5bf9e2aaa..c3afcc1574 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -390,8 +390,8 @@ SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
- 10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
+ 10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.1.0.0/16 | 10.1.2.3/16
10.1.2.0/24 | 10.1.2.3/24
@@ -538,8 +538,8 @@ SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
- 10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
+ 10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.1.0.0/16 | 10.1.2.3/16
10.1.2.0/24 | 10.1.2.3/24
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index bf1a2db2cf..98655f94ff 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2386,8 +2386,8 @@ select * from
---+---+-------+---+----
| | | | 0
| | | |
- | 0 | zero | |
| | null | |
+ | 0 | zero | |
8 | 8 | eight | |
7 | 7 | seven | |
6 | 6 | six | |
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 6cadd87868..2574b72218 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -873,13 +873,13 @@ FROM
(VALUES (NULL), (3), (1), (NULL), (NULL), (5), (2), (4), (NULL), (5), (4)) foo(bar);
bar | json_arrayagg
-----+---------------
+ 2 | [4, 4]
4 | [4, 4]
4 | [4, 4]
- 2 | [4, 4]
- 5 | [5, 3, 5]
- 3 | [5, 3, 5]
- 1 | [5, 3, 5]
- 5 | [5, 3, 5]
+ 3 | [3, 5, 5]
+ 1 | [3, 5, 5]
+ 5 | [3, 5, 5]
+ 5 | [3, 5, 5]
|
|
|
diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out
index d47b5f6ec5..8be60cfd98 100644
--- a/src/test/regress/expected/tsrf.out
+++ b/src/test/regress/expected/tsrf.out
@@ -356,14 +356,14 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
| | 1 | 3
| bar | 1 | 2
| foo | 1 | 1
- | foo | 2 | 1
a | bar | 2 | 1
- b | | 2 | 1
a | foo | 2 | 1
- | bar | 2 | 2
a | | 2 | 2
- | | 2 | 3
b | bar | 2 | 1
+ b | | 2 | 1
+ | | 2 | 3
+ | bar | 2 | 2
+ | foo | 2 | 1
(16 rows)
SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g);
@@ -400,19 +400,19 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
-------+-----+---+-------
a | foo | | 2
a | | | 4
+ a | | 1 | 2
a | | 2 | 2
a | bar | 1 | 1
a | bar | 2 | 1
a | bar | | 2
a | foo | 1 | 1
a | foo | 2 | 1
- a | | 1 | 2
b | bar | 1 | 1
- b | | | 2
b | | 1 | 1
b | bar | 2 | 1
- b | bar | | 2
b | | 2 | 1
+ b | bar | | 2
+ b | | | 2
| | 2 | 3
| | | 6
| bar | 1 | 2
@@ -435,22 +435,22 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
a | | 1 | 2
b | | 1 | 1
| | 1 | 3
- a | | 2 | 2
+ b | bar | 2 | 1
b | | 2 | 1
| bar | 2 | 2
| | 2 | 3
| foo | 2 | 1
a | bar | 2 | 1
a | foo | 2 | 1
- b | bar | 2 | 1
- a | | | 4
+ a | | 2 | 2
b | bar | | 2
b | | | 2
| | | 6
- a | foo | | 2
- a | bar | | 2
| bar | | 4
+ a | bar | | 2
| foo | | 2
+ a | foo | | 2
+ a | | | 4
(24 rows)
reset enable_hashagg;
@@ -598,8 +598,8 @@ FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b);
a | b | g
---+---+---
3 | 2 | 1
- 5 | 1 | 2
- 3 | 1 | 3
+ 3 | 2 | 2
+ 3 | 2 | 3
(3 rows)
-- LIMIT / OFFSET is evaluated after SRF evaluation
diff --git a/src/test/regress/expected/tuplesort.out b/src/test/regress/expected/tuplesort.out
index 418f296a3f..28953f8dd7 100644
--- a/src/test/regress/expected/tuplesort.out
+++ b/src/test/regress/expected/tuplesort.out
@@ -231,8 +231,8 @@ ORDER BY ctid LIMIT 5;
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000
2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999
- 3 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
20004 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
+ 3 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
4 | 00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003 | 00009988-0000-0000-0000-000000019997
(5 rows)
@@ -242,11 +242,11 @@ FROM abbrev_abort_uuids
ORDER BY ctid DESC LIMIT 5;
id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
- 0 | | | |
- 20002 | | | |
20003 | | | |
- 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20002 | | | |
+ 0 | | | |
20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
(5 rows)
ROLLBACK;
@@ -335,9 +335,9 @@ FROM abbrev_abort_uuids
ORDER BY ctid DESC LIMIT 5;
id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
- 0 | | | |
20003 | | | |
20002 | | | |
+ 0 | | | |
9993 | 00000000-0000-0000-0000-000000009992 | 00000000-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008
9994 | 00000000-0000-0000-0000-000000009993 | 00000000-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007
(5 rows)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index bb9ff7f07b..2a3353b5d8 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -23,13 +23,13 @@ SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM emps
-----------+-------+--------+-------
develop | 7 | 4200 | 25100
develop | 9 | 4500 | 25100
- develop | 11 | 5200 | 25100
develop | 10 | 5200 | 25100
+ develop | 11 | 5200 | 25100
develop | 8 | 6000 | 25100
personnel | 5 | 3500 | 7400
personnel | 2 | 3900 | 7400
- sales | 3 | 4800 | 14600
sales | 4 | 4800 | 14600
+ sales | 3 | 4800 | 14600
sales | 1 | 5000 | 14600
(10 rows)
@@ -38,13 +38,13 @@ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary
-----------+-------+--------+------
develop | 7 | 4200 | 1
develop | 9 | 4500 | 2
- develop | 11 | 5200 | 3
develop | 10 | 5200 | 3
+ develop | 11 | 5200 | 3
develop | 8 | 6000 | 5
personnel | 5 | 3500 | 1
personnel | 2 | 3900 | 2
- sales | 3 | 4800 | 1
sales | 4 | 4800 | 1
+ sales | 3 | 4800 | 1
sales | 1 | 5000 | 3
(10 rows)
@@ -78,16 +78,16 @@ GROUP BY four, ten ORDER BY four, ten;
SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
depname | empno | salary | sum
-----------+-------+--------+-------
- develop | 11 | 5200 | 25100
+ develop | 10 | 5200 | 25100
develop | 7 | 4200 | 25100
develop | 9 | 4500 | 25100
develop | 8 | 6000 | 25100
- develop | 10 | 5200 | 25100
+ develop | 11 | 5200 | 25100
personnel | 5 | 3500 | 7400
personnel | 2 | 3900 | 7400
- sales | 3 | 4800 | 14600
sales | 1 | 5000 | 14600
sales | 4 | 4800 | 14600
+ sales | 3 | 4800 | 14600
(10 rows)
SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w;
@@ -95,13 +95,13 @@ SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITI
-----------+-------+--------+------
develop | 7 | 4200 | 1
personnel | 5 | 3500 | 1
- sales | 3 | 4800 | 1
sales | 4 | 4800 | 1
- personnel | 2 | 3900 | 2
+ sales | 3 | 4800 | 1
develop | 9 | 4500 | 2
- sales | 1 | 5000 | 3
- develop | 11 | 5200 | 3
+ personnel | 2 | 3900 | 2
develop | 10 | 5200 | 3
+ develop | 11 | 5200 | 3
+ sales | 1 | 5000 | 3
develop | 8 | 6000 | 5
(10 rows)
@@ -394,12 +394,12 @@ SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM te
SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
last_value | ten | four
------------+-----+------
- 0 | 0 | 0
- 0 | 0 | 2
- 0 | 0 | 0
- 1 | 1 | 1
+ 2 | 0 | 0
+ 2 | 0 | 0
+ 2 | 0 | 2
1 | 1 | 3
1 | 1 | 1
+ 1 | 1 | 1
3 | 3 | 3
0 | 4 | 0
1 | 7 | 1
@@ -803,14 +803,14 @@ SELECT sum(unique1) over (order by four range between current row and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 33 | 5 | 1
- 33 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
33 | 1 | 1
- 18 | 6 | 2
+ 33 | 9 | 1
+ 33 | 5 | 1
18 | 2 | 2
+ 18 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -922,14 +922,14 @@ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 f
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
- 8 | 0 | 0
- 4 | 8 | 0
- 5 | 4 | 0
- 9 | 5 | 1
- 1 | 9 | 1
- 6 | 1 | 1
- 2 | 6 | 2
- 3 | 2 | 2
+ 8 | 4 | 0
+ 0 | 8 | 0
+ 1 | 0 | 0
+ 9 | 1 | 1
+ 5 | 9 | 1
+ 2 | 5 | 1
+ 6 | 2 | 2
+ 3 | 6 | 2
7 | 3 | 3
| 7 | 3
(10 rows)
@@ -939,14 +939,14 @@ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 f
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
- | 0 | 0
- 5 | 8 | 0
- 5 | 4 | 0
- | 5 | 1
- 6 | 9 | 1
- 6 | 1 | 1
- 3 | 6 | 2
+ | 4 | 0
+ 1 | 8 | 0
+ 1 | 0 | 0
+ | 1 | 1
+ 2 | 9 | 1
+ 2 | 5 | 1
3 | 2 | 2
+ 3 | 6 | 2
| 3 | 3
| 7 | 3
(10 rows)
@@ -956,14 +956,14 @@ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 f
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
- 0 | 0 | 0
- 8 | 8 | 0
4 | 4 | 0
- 5 | 5 | 1
- 9 | 9 | 1
+ 8 | 8 | 0
+ 0 | 0 | 0
1 | 1 | 1
- 6 | 6 | 2
+ 9 | 9 | 1
+ 5 | 5 | 1
2 | 2 | 2
+ 6 | 6 | 2
3 | 3 | 3
7 | 7 | 3
(10 rows)
@@ -973,14 +973,14 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
- 4 | 0 | 0
- 5 | 8 | 0
- 9 | 4 | 0
- 1 | 5 | 1
- 6 | 9 | 1
- 2 | 1 | 1
- 3 | 6 | 2
- 7 | 2 | 2
+ 0 | 4 | 0
+ 1 | 8 | 0
+ 9 | 0 | 0
+ 5 | 1 | 1
+ 2 | 9 | 1
+ 6 | 5 | 1
+ 3 | 2 | 2
+ 7 | 6 | 2
7 | 3 | 3
| 7 | 3
(10 rows)
@@ -990,14 +990,14 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
- | 0 | 0
- 5 | 8 | 0
- 9 | 4 | 0
- | 5 | 1
- 6 | 9 | 1
- 2 | 1 | 1
- 3 | 6 | 2
- 7 | 2 | 2
+ | 4 | 0
+ 1 | 8 | 0
+ 9 | 0 | 0
+ | 1 | 1
+ 2 | 9 | 1
+ 6 | 5 | 1
+ 3 | 2 | 2
+ 7 | 6 | 2
| 3 | 3
| 7 | 3
(10 rows)
@@ -1007,14 +1007,14 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
- 0 | 0 | 0
- 5 | 8 | 0
- 9 | 4 | 0
- 5 | 5 | 1
- 6 | 9 | 1
- 2 | 1 | 1
- 3 | 6 | 2
- 7 | 2 | 2
+ 4 | 4 | 0
+ 1 | 8 | 0
+ 9 | 0 | 0
+ 1 | 1 | 1
+ 2 | 9 | 1
+ 6 | 5 | 1
+ 3 | 2 | 2
+ 7 | 6 | 2
3 | 3 | 3
7 | 7 | 3
(10 rows)
@@ -1075,14 +1075,14 @@ SELECT sum(unique1) over (w range between current row and unbounded following),
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 33 | 5 | 1
- 33 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
33 | 1 | 1
- 18 | 6 | 2
+ 33 | 9 | 1
+ 33 | 5 | 1
18 | 2 | 2
+ 18 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -1092,14 +1092,14 @@ SELECT sum(unique1) over (w range between unbounded preceding and current row ex
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- 12 | 0 | 0
- 4 | 8 | 0
8 | 4 | 0
- 22 | 5 | 1
- 18 | 9 | 1
+ 4 | 8 | 0
+ 12 | 0 | 0
26 | 1 | 1
- 29 | 6 | 2
+ 18 | 9 | 1
+ 22 | 5 | 1
33 | 2 | 2
+ 29 | 6 | 2
42 | 3 | 3
38 | 7 | 3
(10 rows)
@@ -1109,14 +1109,14 @@ SELECT sum(unique1) over (w range between unbounded preceding and current row ex
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
35 | 3 | 3
35 | 7 | 3
(10 rows)
@@ -1126,14 +1126,14 @@ SELECT sum(unique1) over (w range between unbounded preceding and current row ex
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- 0 | 0 | 0
- 8 | 8 | 0
4 | 4 | 0
- 17 | 5 | 1
- 21 | 9 | 1
+ 8 | 8 | 0
+ 0 | 0 | 0
13 | 1 | 1
- 33 | 6 | 2
+ 21 | 9 | 1
+ 17 | 5 | 1
29 | 2 | 2
+ 33 | 6 | 2
38 | 3 | 3
42 | 7 | 3
(10 rows)
@@ -1145,14 +1145,14 @@ FROM tenk1 WHERE unique1 < 10
WINDOW w AS (order by four range between current row and unbounded following);
first_value | nth_2 | last_value | unique1 | four
-------------+-------+------------+---------+------
- 0 | 8 | 7 | 0 | 0
- 0 | 8 | 7 | 8 | 0
- 0 | 8 | 7 | 4 | 0
- 5 | 9 | 7 | 5 | 1
- 5 | 9 | 7 | 9 | 1
- 5 | 9 | 7 | 1 | 1
- 6 | 2 | 7 | 6 | 2
- 6 | 2 | 7 | 2 | 2
+ 4 | 8 | 7 | 4 | 0
+ 4 | 8 | 7 | 8 | 0
+ 4 | 8 | 7 | 0 | 0
+ 1 | 9 | 7 | 1 | 1
+ 1 | 9 | 7 | 9 | 1
+ 1 | 9 | 7 | 5 | 1
+ 2 | 6 | 7 | 2 | 2
+ 2 | 6 | 7 | 6 | 2
3 | 7 | 7 | 3 | 3
3 | 7 | 7 | 7 | 3
(10 rows)
@@ -1349,14 +1349,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1368,14 +1368,14 @@ FROM tenk1 WHERE unique1 < 10;
-----+---------+------
| 3 | 3
| 7 | 3
- 10 | 6 | 2
10 | 2 | 2
+ 10 | 6 | 2
+ 18 | 1 | 1
18 | 9 | 1
18 | 5 | 1
- 18 | 1 | 1
- 23 | 0 | 0
- 23 | 8 | 0
23 | 4 | 0
+ 23 | 8 | 0
+ 23 | 0 | 0
(10 rows)
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others),
@@ -1383,14 +1383,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1400,14 +1400,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1417,14 +1417,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1434,14 +1434,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1451,14 +1451,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 33 | 0 | 0
- 41 | 8 | 0
37 | 4 | 0
- 35 | 5 | 1
- 39 | 9 | 1
+ 41 | 8 | 0
+ 33 | 0 | 0
31 | 1 | 1
- 43 | 6 | 2
+ 39 | 9 | 1
+ 35 | 5 | 1
39 | 2 | 2
+ 43 | 6 | 2
26 | 3 | 3
30 | 7 | 3
(10 rows)
@@ -1468,14 +1468,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 33 | 0 | 0
- 33 | 8 | 0
33 | 4 | 0
- 30 | 5 | 1
- 30 | 9 | 1
+ 33 | 8 | 0
+ 33 | 0 | 0
30 | 1 | 1
- 37 | 6 | 2
+ 30 | 9 | 1
+ 30 | 5 | 1
37 | 2 | 2
+ 37 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1521,13 +1521,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
34900 | 5000 | 10-01-2006
34900 | 6000 | 10-01-2006
38400 | 3900 | 12-23-2006
- 47100 | 4800 | 08-01-2007
47100 | 5200 | 08-01-2007
+ 47100 | 4800 | 08-01-2007
47100 | 4800 | 08-08-2007
47100 | 5200 | 08-15-2007
36100 | 3500 | 12-10-2007
- 32200 | 4500 | 01-01-2008
32200 | 4200 | 01-01-2008
+ 32200 | 4500 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following),
@@ -1539,8 +1539,8 @@ select sum(salary) over (order by enroll_date desc range between '1 year'::inter
36100 | 3500 | 12-10-2007
47100 | 5200 | 08-15-2007
47100 | 4800 | 08-08-2007
- 47100 | 4800 | 08-01-2007
47100 | 5200 | 08-01-2007
+ 47100 | 4800 | 08-01-2007
38400 | 3900 | 12-23-2006
34900 | 5000 | 10-01-2006
34900 | 6000 | 10-01-2006
@@ -1555,8 +1555,8 @@ select sum(salary) over (order by enroll_date desc range between '1 year'::inter
| 3500 | 12-10-2007
| 5200 | 08-15-2007
| 4800 | 08-08-2007
- | 4800 | 08-01-2007
| 5200 | 08-01-2007
+ | 4800 | 08-01-2007
| 3900 | 12-23-2006
| 5000 | 10-01-2006
| 6000 | 10-01-2006
@@ -1569,13 +1569,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
29900 | 5000 | 10-01-2006
28900 | 6000 | 10-01-2006
34500 | 3900 | 12-23-2006
- 42300 | 4800 | 08-01-2007
41900 | 5200 | 08-01-2007
+ 42300 | 4800 | 08-01-2007
42300 | 4800 | 08-08-2007
41900 | 5200 | 08-15-2007
32600 | 3500 | 12-10-2007
- 27700 | 4500 | 01-01-2008
28000 | 4200 | 01-01-2008
+ 27700 | 4500 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
@@ -1585,13 +1585,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
23900 | 5000 | 10-01-2006
23900 | 6000 | 10-01-2006
34500 | 3900 | 12-23-2006
- 37100 | 4800 | 08-01-2007
37100 | 5200 | 08-01-2007
+ 37100 | 4800 | 08-01-2007
42300 | 4800 | 08-08-2007
41900 | 5200 | 08-15-2007
32600 | 3500 | 12-10-2007
- 23500 | 4500 | 01-01-2008
23500 | 4200 | 01-01-2008
+ 23500 | 4500 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
@@ -1601,13 +1601,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
28900 | 5000 | 10-01-2006
29900 | 6000 | 10-01-2006
38400 | 3900 | 12-23-2006
- 41900 | 4800 | 08-01-2007
42300 | 5200 | 08-01-2007
+ 41900 | 4800 | 08-01-2007
47100 | 4800 | 08-08-2007
47100 | 5200 | 08-15-2007
36100 | 3500 | 12-10-2007
- 28000 | 4500 | 01-01-2008
27700 | 4200 | 01-01-2008
+ 28000 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by salary range between 1000 preceding and 1000 following),
@@ -1692,13 +1692,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
5000 | 5200 | 5000 | 10-01-2006
6000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 4200 | 4500 | 01-01-2008
- 5000 | 4200 | 4200 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
+ 5000 | 4500 | 4200 | 01-01-2008
+ 5000 | 4500 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
@@ -1711,13 +1711,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
5000 | 5200 | 5000 | 10-01-2006
6000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 4500 | 4500 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
5000 | 4200 | 4200 | 01-01-2008
+ 5000 | 4500 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
@@ -1730,13 +1730,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
3900 | 5200 | 5000 | 10-01-2006
3900 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 3500 | 4500 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
5000 | 3500 | 4200 | 01-01-2008
+ 5000 | 3500 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
@@ -1749,13 +1749,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
6000 | 5200 | 5000 | 10-01-2006
5000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 4200 | 4500 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
5000 | 4500 | 4200 | 01-01-2008
+ 5000 | 4200 | 4500 | 01-01-2008
(10 rows)
-- RANGE offset PRECEDING/FOLLOWING with null values
@@ -1810,8 +1810,8 @@ window w as
(order by x desc nulls first range between 2 preceding and 2 following);
x | y | first_value | last_value
---+----+-------------+------------
- | 43 | 43 | 42
- | 42 | 43 | 42
+ | 42 | 42 | 43
+ | 43 | 42 | 43
5 | 5 | 5 | 3
4 | 4 | 5 | 2
3 | 3 | 5 | 1
@@ -2392,10 +2392,10 @@ window w as (order by f_time desc range between
10 | 20:00:00 | 10 | 8
9 | 19:00:00 | 10 | 7
8 | 18:00:00 | 9 | 7
- 7 | 17:00:00 | 8 | 5
- 6 | 15:00:00 | 6 | 3
- 5 | 15:00:00 | 6 | 3
- 4 | 14:00:00 | 6 | 2
+ 7 | 17:00:00 | 8 | 6
+ 5 | 15:00:00 | 5 | 3
+ 6 | 15:00:00 | 5 | 3
+ 4 | 14:00:00 | 5 | 2
3 | 13:00:00 | 4 | 1
2 | 12:00:00 | 3 | 1
1 | 11:00:00 | 2 | 1
@@ -2428,10 +2428,10 @@ window w as (order by f_timetz desc range between
10 | 20:00:00+01 | 10 | 8
9 | 19:00:00+01 | 10 | 7
8 | 18:00:00+01 | 9 | 7
- 7 | 17:00:00+01 | 8 | 5
- 6 | 15:00:00+01 | 6 | 3
- 5 | 15:00:00+01 | 6 | 3
- 4 | 14:00:00+01 | 6 | 2
+ 7 | 17:00:00+01 | 8 | 6
+ 5 | 15:00:00+01 | 5 | 3
+ 6 | 15:00:00+01 | 5 | 3
+ 4 | 14:00:00+01 | 5 | 2
3 | 13:00:00+01 | 4 | 1
2 | 12:00:00+01 | 3 | 1
1 | 11:00:00+01 | 2 | 1
@@ -2465,9 +2465,9 @@ window w as (order by f_interval desc range between
9 | @ 9 years | 10 | 8
8 | @ 8 years | 9 | 7
7 | @ 7 years | 8 | 7
- 6 | @ 5 years | 6 | 4
- 5 | @ 5 years | 6 | 4
- 4 | @ 4 years | 6 | 3
+ 5 | @ 5 years | 5 | 4
+ 6 | @ 5 years | 5 | 4
+ 4 | @ 4 years | 5 | 3
3 | @ 3 years | 4 | 2
2 | @ 2 years | 3 | 1
1 | @ 1 year | 2 | 1
@@ -2503,10 +2503,10 @@ window w as (order by f_timestamptz desc range between
7 | Wed Oct 19 02:23:54 2005 PDT | 8 | 6
6 | Tue Oct 19 02:23:54 2004 PDT | 7 | 5
5 | Sun Oct 19 02:23:54 2003 PDT | 6 | 4
- 4 | Sat Oct 19 02:23:54 2002 PDT | 5 | 2
- 3 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
+ 4 | Sat Oct 19 02:23:54 2002 PDT | 5 | 3
2 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
- 1 | Thu Oct 19 02:23:54 2000 PDT | 3 | 1
+ 3 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
+ 1 | Thu Oct 19 02:23:54 2000 PDT | 2 | 1
(10 rows)
select id, f_timestamp, first_value(id) over w, last_value(id) over w
@@ -2539,10 +2539,10 @@ window w as (order by f_timestamp desc range between
7 | Wed Oct 19 10:23:54 2005 | 8 | 6
6 | Tue Oct 19 10:23:54 2004 | 7 | 5
5 | Sun Oct 19 10:23:54 2003 | 6 | 4
- 4 | Sat Oct 19 10:23:54 2002 | 5 | 2
- 3 | Fri Oct 19 10:23:54 2001 | 4 | 1
+ 4 | Sat Oct 19 10:23:54 2002 | 5 | 3
2 | Fri Oct 19 10:23:54 2001 | 4 | 1
- 1 | Thu Oct 19 10:23:54 2000 | 3 | 1
+ 3 | Fri Oct 19 10:23:54 2001 | 4 | 1
+ 1 | Thu Oct 19 10:23:54 2000 | 2 | 1
(10 rows)
-- RANGE offset PRECEDING/FOLLOWING error cases
@@ -2588,14 +2588,14 @@ SELECT sum(unique1) over (order by four groups between unbounded preceding and c
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 12 | 0 | 0
- 12 | 8 | 0
12 | 4 | 0
- 27 | 5 | 1
- 27 | 9 | 1
+ 12 | 8 | 0
+ 12 | 0 | 0
27 | 1 | 1
- 35 | 6 | 2
+ 27 | 9 | 1
+ 27 | 5 | 1
35 | 2 | 2
+ 35 | 6 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
@@ -2605,14 +2605,14 @@ SELECT sum(unique1) over (order by four groups between unbounded preceding and u
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 45 | 5 | 1
- 45 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
45 | 1 | 1
- 45 | 6 | 2
+ 45 | 9 | 1
+ 45 | 5 | 1
45 | 2 | 2
+ 45 | 6 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
@@ -2622,14 +2622,14 @@ SELECT sum(unique1) over (order by four groups between current row and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 33 | 5 | 1
- 33 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
33 | 1 | 1
- 18 | 6 | 2
+ 33 | 9 | 1
+ 33 | 5 | 1
18 | 2 | 2
+ 18 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -2639,14 +2639,14 @@ SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 45 | 5 | 1
- 45 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
45 | 1 | 1
- 33 | 6 | 2
+ 45 | 9 | 1
+ 45 | 5 | 1
33 | 2 | 2
+ 33 | 6 | 2
18 | 3 | 3
18 | 7 | 3
(10 rows)
@@ -2656,14 +2656,14 @@ SELECT sum(unique1) over (order by four groups between 1 following and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 33 | 0 | 0
- 33 | 8 | 0
33 | 4 | 0
- 18 | 5 | 1
- 18 | 9 | 1
+ 33 | 8 | 0
+ 33 | 0 | 0
18 | 1 | 1
- 10 | 6 | 2
+ 18 | 9 | 1
+ 18 | 5 | 1
10 | 2 | 2
+ 10 | 6 | 2
| 3 | 3
| 7 | 3
(10 rows)
@@ -2673,14 +2673,14 @@ SELECT sum(unique1) over (order by four groups between unbounded preceding and 2
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 35 | 0 | 0
- 35 | 8 | 0
35 | 4 | 0
- 45 | 5 | 1
- 45 | 9 | 1
+ 35 | 8 | 0
+ 35 | 0 | 0
45 | 1 | 1
- 45 | 6 | 2
+ 45 | 9 | 1
+ 45 | 5 | 1
45 | 2 | 2
+ 45 | 6 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
@@ -2690,14 +2690,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 precedi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -2707,14 +2707,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 27 | 0 | 0
- 27 | 8 | 0
27 | 4 | 0
- 35 | 5 | 1
- 35 | 9 | 1
+ 27 | 8 | 0
+ 27 | 0 | 0
35 | 1 | 1
- 45 | 6 | 2
+ 35 | 9 | 1
+ 35 | 5 | 1
45 | 2 | 2
+ 45 | 6 | 2
33 | 3 | 3
33 | 7 | 3
(10 rows)
@@ -2724,14 +2724,14 @@ SELECT sum(unique1) over (order by four groups between 0 preceding and 0 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 12 | 0 | 0
- 12 | 8 | 0
12 | 4 | 0
- 15 | 5 | 1
- 15 | 9 | 1
+ 12 | 8 | 0
+ 12 | 0 | 0
15 | 1 | 1
- 8 | 6 | 2
+ 15 | 9 | 1
+ 15 | 5 | 1
8 | 2 | 2
+ 8 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -2741,14 +2741,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 27 | 0 | 0
- 19 | 8 | 0
23 | 4 | 0
- 30 | 5 | 1
- 26 | 9 | 1
+ 19 | 8 | 0
+ 27 | 0 | 0
34 | 1 | 1
- 39 | 6 | 2
+ 26 | 9 | 1
+ 30 | 5 | 1
43 | 2 | 2
+ 39 | 6 | 2
30 | 3 | 3
26 | 7 | 3
(10 rows)
@@ -2758,14 +2758,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 15 | 0 | 0
- 15 | 8 | 0
15 | 4 | 0
- 20 | 5 | 1
- 20 | 9 | 1
+ 15 | 8 | 0
+ 15 | 0 | 0
20 | 1 | 1
- 37 | 6 | 2
+ 20 | 9 | 1
+ 20 | 5 | 1
37 | 2 | 2
+ 37 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -2775,14 +2775,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 15 | 0 | 0
- 23 | 8 | 0
19 | 4 | 0
- 25 | 5 | 1
- 29 | 9 | 1
+ 23 | 8 | 0
+ 15 | 0 | 0
21 | 1 | 1
- 43 | 6 | 2
+ 29 | 9 | 1
+ 25 | 5 | 1
39 | 2 | 2
+ 43 | 6 | 2
26 | 3 | 3
30 | 7 | 3
(10 rows)
@@ -2863,14 +2863,14 @@ select first_value(salary) over(order by enroll_date groups between 1 preceding
-------------+------+-----------+--------+-------------
5000 | 6000 | 5000 | 5000 | 10-01-2006
5000 | 3900 | 5000 | 6000 | 10-01-2006
- 5000 | 4800 | 5000 | 3900 | 12-23-2006
- 3900 | 5200 | 3900 | 4800 | 08-01-2007
+ 5000 | 5200 | 5000 | 3900 | 12-23-2006
3900 | 4800 | 3900 | 5200 | 08-01-2007
- 4800 | 5200 | 4800 | 4800 | 08-08-2007
+ 3900 | 4800 | 3900 | 4800 | 08-01-2007
+ 5200 | 5200 | 5200 | 4800 | 08-08-2007
4800 | 3500 | 4800 | 5200 | 08-15-2007
- 5200 | 4500 | 5200 | 3500 | 12-10-2007
- 3500 | 4200 | 3500 | 4500 | 01-01-2008
- 3500 | | 3500 | 4200 | 01-01-2008
+ 5200 | 4200 | 5200 | 3500 | 12-10-2007
+ 3500 | 4500 | 3500 | 4200 | 01-01-2008
+ 3500 | | 3500 | 4500 | 01-01-2008
(10 rows)
select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following),
@@ -2880,14 +2880,14 @@ select last_value(salary) over(order by enroll_date groups between 1 preceding a
------------+------+--------+-------------
3900 | | 5000 | 10-01-2006
3900 | 5000 | 6000 | 10-01-2006
- 5200 | 6000 | 3900 | 12-23-2006
- 4800 | 3900 | 4800 | 08-01-2007
- 4800 | 4800 | 5200 | 08-01-2007
- 5200 | 5200 | 4800 | 08-08-2007
+ 4800 | 6000 | 3900 | 12-23-2006
+ 4800 | 3900 | 5200 | 08-01-2007
+ 4800 | 5200 | 4800 | 08-01-2007
+ 5200 | 4800 | 4800 | 08-08-2007
3500 | 4800 | 5200 | 08-15-2007
- 4200 | 5200 | 3500 | 12-10-2007
- 4200 | 3500 | 4500 | 01-01-2008
- 4200 | 4500 | 4200 | 01-01-2008
+ 4500 | 5200 | 3500 | 12-10-2007
+ 4500 | 3500 | 4200 | 01-01-2008
+ 4500 | 4200 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date groups between 1 following and 3 following
@@ -2900,14 +2900,14 @@ select first_value(salary) over(order by enroll_date groups between 1 following
-------------+------+-----------+--------+-------------
3900 | 6000 | 3900 | 5000 | 10-01-2006
3900 | 3900 | 3900 | 6000 | 10-01-2006
- 4800 | 4800 | 4800 | 3900 | 12-23-2006
- 4800 | 5200 | 4800 | 4800 | 08-01-2007
+ 5200 | 5200 | 5200 | 3900 | 12-23-2006
4800 | 4800 | 4800 | 5200 | 08-01-2007
+ 4800 | 4800 | 4800 | 4800 | 08-01-2007
5200 | 5200 | 5200 | 4800 | 08-08-2007
3500 | 3500 | 3500 | 5200 | 08-15-2007
- 4500 | 4500 | 4500 | 3500 | 12-10-2007
- | 4200 | | 4500 | 01-01-2008
- | | | 4200 | 01-01-2008
+ 4200 | 4200 | 4200 | 3500 | 12-10-2007
+ | 4500 | | 4200 | 01-01-2008
+ | | | 4500 | 01-01-2008
(10 rows)
select last_value(salary) over(order by enroll_date groups between 1 following and 3 following
@@ -2919,13 +2919,13 @@ select last_value(salary) over(order by enroll_date groups between 1 following a
4800 | | 5000 | 10-01-2006
4800 | 5000 | 6000 | 10-01-2006
5200 | 6000 | 3900 | 12-23-2006
- 3500 | 3900 | 4800 | 08-01-2007
- 3500 | 4800 | 5200 | 08-01-2007
- 4200 | 5200 | 4800 | 08-08-2007
- 4200 | 4800 | 5200 | 08-15-2007
- 4200 | 5200 | 3500 | 12-10-2007
- | 3500 | 4500 | 01-01-2008
- | 4500 | 4200 | 01-01-2008
+ 3500 | 3900 | 5200 | 08-01-2007
+ 3500 | 5200 | 4800 | 08-01-2007
+ 4500 | 4800 | 4800 | 08-08-2007
+ 4500 | 4800 | 5200 | 08-15-2007
+ 4500 | 5200 | 3500 | 12-10-2007
+ | 3500 | 4200 | 01-01-2008
+ | 4200 | 4500 | 01-01-2008
(10 rows)
-- Show differences in offset interpretation between ROWS, RANGE, and GROUPS
@@ -3407,7 +3407,7 @@ WHERE first_emp = 1 OR last_emp = 1;
depname | empno | salary | enroll_date | first_emp | last_emp
-----------+-------+--------+-------------+-----------+----------
develop | 8 | 6000 | 10-01-2006 | 1 | 5
- develop | 7 | 4200 | 01-01-2008 | 5 | 1
+ develop | 7 | 4200 | 01-01-2008 | 4 | 1
personnel | 2 | 3900 | 12-23-2006 | 1 | 2
personnel | 5 | 3500 | 12-10-2007 | 2 | 1
sales | 1 | 5000 | 10-01-2006 | 1 | 3
--
2.35.1