From 4907ba304c346051a6535e67c043779755a78e84 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 21 Jan 2025 14:43:21 -0500 Subject: 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" Author: Tom Lane Discussion: https://postgr.es/m/173737973383.1070.1832752929070067441@wrigleys.postgresql.org --- src/tutorial/advanced.source | 49 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 49 insertions(+) (limited to 'src/tutorial/advanced.source') 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 @@ -10,6 +10,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 -- cgit v1.2.3