Doc: simplify the tutorial's window-function examples.

For the purposes of this discussion, row_number() is just as good
as rank(), and its behavior is easier to understand and describe.
So let's switch the examples to using row_number().

Along the way to checking the results given in the tutorial,
I found it helpful to extract the empsalary table we use in the
regression tests, which is evidently the same data that was used
to make these results.  So I shoved that into advanced.source
to improve the coverage of that file a little.  (There's still
several pages of the tutorial that are not included in it,
but at least now 3.5 Window Functions is covered.)

Suggested-by: "David G. Johnston" <david.g.johnston@gmail.com>
Author: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/173737973383.1070.1832752929070067441@wrigleys.postgresql.org

1 files changed, 49 insertions, 0 deletions

diff --git a/src/tutorial/advanced.source b/src/tutorial/advanced.source
index 0c68b3344c3..f46fced1a5f 100644
--- a/src/tutorial/advanced.source
+++ b/src/tutorial/advanced.source
@@ -11,6 +11,55 @@
 ---------------------------------------------------------------------------
 
 -----------------------------
+-- Window Functions
+-----------------------------
+
+-- a sample table
+CREATE TABLE empsalary (
+    depname text,
+    empno bigint,
+    salary int,
+    enroll_date date
+);
+
+INSERT INTO empsalary VALUES
+('develop', 10, 5200, '2007-08-01'),
+('sales', 1, 5000, '2006-10-01'),
+('personnel', 5, 3500, '2007-12-10'),
+('sales', 4, 4800, '2007-08-08'),
+('personnel', 2, 3900, '2006-12-23'),
+('develop', 7, 4200, '2008-01-01'),
+('develop', 9, 4500, '2008-01-01'),
+('sales', 3, 4800, '2007-08-01'),
+('develop', 8, 6000, '2006-10-01'),
+('develop', 11, 5200, '2007-08-15');
+
+SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname)
+  FROM empsalary;
+
+SELECT depname, empno, salary,
+       row_number() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary;
+
+SELECT salary, sum(salary) OVER () FROM empsalary;
+
+SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
+
+SELECT depname, empno, salary, enroll_date
+FROM
+  (SELECT depname, empno, salary, enroll_date,
+     row_number() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
+     FROM empsalary
+  ) AS ss
+WHERE pos < 3;
+
+SELECT sum(salary) OVER w, avg(salary) OVER w
+  FROM empsalary
+  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
+
+DROP TABLE empsalary;
+
+-----------------------------
 -- Inheritance:
 --	A table can inherit from zero or more tables.  A query can reference
 --	either all rows of a table or all rows of a table plus all of its