Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4907ba3

Browse files
committed
Doc: simplify the tutorial's window-function examples.
For the purposes of this discussion, row_number() is just as goodas 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 theregression tests, which is evidently the same data that was usedto make these results. So I shoved that into advanced.sourceto improve the coverage of that file a little. (There's stillseveral 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 parentdb19a50 commit4907ba3

File tree

2 files changed

+71
-19
lines changed

2 files changed

+71
-19
lines changed

‎doc/src/sgml/advanced.sgml‎

Lines changed: 22 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -389,30 +389,32 @@ SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM emps
389389

390390
<programlisting>
391391
SELECT depname, empno, salary,
392-
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
392+
row_number() OVER (PARTITION BY depname ORDER BY salary DESC)
393393
FROM empsalary;
394394
</programlisting>
395395

396396
<screen>
397-
depname | empno | salary |rank
398-
-----------+-------+--------+------
399-
develop | 8 | 6000 | 1
400-
develop | 10 | 5200 | 2
401-
develop | 11 | 5200 |2
402-
develop | 9 | 4500 | 4
403-
develop | 7 | 4200 | 5
404-
personnel | 2 | 3900 | 1
405-
personnel | 5 | 3500 | 2
406-
sales | 1 | 5000 | 1
407-
sales | 4 | 4800 | 2
408-
sales | 3 | 4800 |2
397+
depname | empno | salary |row_number
398+
-----------+-------+--------+------------
399+
develop | 8 | 6000 |1
400+
develop | 10 | 5200 |2
401+
develop | 11 | 5200 | 3
402+
develop | 9 | 4500 |4
403+
develop | 7 | 4200 |5
404+
personnel | 2 | 3900 |1
405+
personnel | 5 | 3500 |2
406+
sales | 1 | 5000 |1
407+
sales | 4 | 4800 |2
408+
sales | 3 | 4800 | 3
409409
(10 rows)
410410
</screen>
411411

412-
As shown here, the <function>rank</function> function produces a numerical rank
413-
for each distinct <literal>ORDER BY</literal> value in the current row's
414-
partition, using the order defined by the <literal>ORDER BY</literal> clause.
415-
<function>rank</function> needs no explicit parameter, because its behavior
412+
As shown here, the <function>row_number</function> window function
413+
assigns sequential numbers to the rows within each partition,
414+
in the order defined by the <literal>ORDER BY</literal> clause
415+
(with tied rows numbered in an unspecified order).
416+
<function>row_number</function> needs no explicit parameter,
417+
because its behavior
416418
is entirely determined by the <literal>OVER</literal> clause.
417419
</para>
418420

@@ -527,14 +529,15 @@ SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
527529
SELECT depname, empno, salary, enroll_date
528530
FROM
529531
(SELECT depname, empno, salary, enroll_date,
530-
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
532+
row_number() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
531533
FROM empsalary
532534
) AS ss
533535
WHERE pos &lt; 3;
534536
</programlisting>
535537

536538
The above query only shows the rows from the inner query having
537-
<literal>rank</literal> less than 3.
539+
<literal>row_number</literal> less than 3 (that is, the first
540+
two rows for each department).
538541
</para>
539542

540543
<para>

‎src/tutorial/advanced.source‎

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,55 @@
1010
--
1111
---------------------------------------------------------------------------
1212

13+
-----------------------------
14+
-- Window Functions
15+
-----------------------------
16+
17+
-- a sample table
18+
CREATE TABLE empsalary (
19+
depname text,
20+
empno bigint,
21+
salary int,
22+
enroll_date date
23+
);
24+
25+
INSERT INTO empsalary VALUES
26+
('develop', 10, 5200, '2007-08-01'),
27+
('sales', 1, 5000, '2006-10-01'),
28+
('personnel', 5, 3500, '2007-12-10'),
29+
('sales', 4, 4800, '2007-08-08'),
30+
('personnel', 2, 3900, '2006-12-23'),
31+
('develop', 7, 4200, '2008-01-01'),
32+
('develop', 9, 4500, '2008-01-01'),
33+
('sales', 3, 4800, '2007-08-01'),
34+
('develop', 8, 6000, '2006-10-01'),
35+
('develop', 11, 5200, '2007-08-15');
36+
37+
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname)
38+
FROM empsalary;
39+
40+
SELECT depname, empno, salary,
41+
row_number() OVER (PARTITION BY depname ORDER BY salary DESC)
42+
FROM empsalary;
43+
44+
SELECT salary, sum(salary) OVER () FROM empsalary;
45+
46+
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
47+
48+
SELECT depname, empno, salary, enroll_date
49+
FROM
50+
(SELECT depname, empno, salary, enroll_date,
51+
row_number() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
52+
FROM empsalary
53+
) AS ss
54+
WHERE pos < 3;
55+
56+
SELECT sum(salary) OVER w, avg(salary) OVER w
57+
FROM empsalary
58+
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
59+
60+
DROP TABLE empsalary;
61+
1362
-----------------------------
1463
-- Inheritance:
1564
--A table can inherit from zero or more tables. A query can reference

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp