agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedTypo on tutorial window page
6+ messages / 4 participants
[nested] [flat]
* Typo on tutorial window page
@ 2025-01-20 13:28 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: PG Doc comments form @ 2025-01-20 13:28 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: oselemg@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/17/tutorial-window.html
Description:
EXPECTED:
As shown here, the rank function produces a numerical rank for each distinct
PARTITION BY value in the current row's partition, using the order defined
by the ORDER BY clause. rank needs no explicit parameter, because its
behavior is entirely determined by the OVER clause.
ACTUAL:
As shown here, the rank function produces a numerical rank for each distinct
ORDER BY value in the current row's partition, using the order defined by
the ORDER BY clause. rank needs no explicit parameter, because its behavior
is entirely determined by the OVER clause.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Typo on tutorial window page
@ 2025-01-21 17:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2025-01-21 17:39 UTC (permalink / raw)
To: oselemg@gmail.com; +Cc: pgsql-docs@lists.postgresql.org
PG Doc comments form <noreply@postgresql.org> writes:
> EXPECTED:
> As shown here, the rank function produces a numerical rank for each distinct
> PARTITION BY value in the current row's partition, using the order defined
> by the ORDER BY clause. rank needs no explicit parameter, because its
> behavior is entirely determined by the OVER clause.
> ACTUAL:
> As shown here, the rank function produces a numerical rank for each distinct
> ORDER BY value in the current row's partition, using the order defined by
> the ORDER BY clause. rank needs no explicit parameter, because its behavior
> is entirely determined by the OVER clause.
Hmm, I think the current text is correct, or at least more nearly
correct than what you suggest. Look at the example:
<programlisting>
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
</programlisting>
<screen>
depname | empno | salary | rank
-----------+-------+--------+------
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
develop | 11 | 5200 | 2
develop | 9 | 4500 | 4
develop | 7 | 4200 | 5
personnel | 2 | 3900 | 1
personnel | 5 | 3500 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
sales | 3 | 4800 | 2
(10 rows)
</screen>
The ranks are separate within each partition (depname), and
within a partition rows having the same salary get the
same rank.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Typo on tutorial window page
@ 2025-01-21 17:39 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2025-01-21 17:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: oselemg@gmail.com; pgsql-docs@lists.postgresql.org
On Tue, Jan 21, 2025 at 10:39 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> PG Doc comments form <noreply@postgresql.org> writes:
> > EXPECTED:
> > As shown here, the rank function produces a numerical rank for each
> distinct
> > PARTITION BY value in the current row's partition, using the order
> defined
> > by the ORDER BY clause. rank needs no explicit parameter, because its
> > behavior is entirely determined by the OVER clause.
>
> > ACTUAL:
> > As shown here, the rank function produces a numerical rank for each
> distinct
> > ORDER BY value in the current row's partition, using the order defined by
> > the ORDER BY clause. rank needs no explicit parameter, because its
> behavior
> > is entirely determined by the OVER clause.
>
> Hmm, I think the current text is correct, or at least more nearly
> correct than what you suggest.
> Look at the example:
>
> <programlisting>
> SELECT depname, empno, salary,
> rank() OVER (PARTITION BY depname ORDER BY salary DESC)
> FROM empsalary;
> </programlisting>
>
> <screen>
> depname | empno | salary | rank
> -----------+-------+--------+------
> develop | 8 | 6000 | 1
> develop | 10 | 5200 | 2
> develop | 11 | 5200 | 2
> develop | 9 | 4500 | 4
> develop | 7 | 4200 | 5
> personnel | 2 | 3900 | 1
> personnel | 5 | 3500 | 2
> sales | 1 | 5000 | 1
> sales | 4 | 4800 | 2
> sales | 3 | 4800 | 2
> (10 rows)
> </screen>
>
> The ranks are separate within each partition (depname), and
> within a partition rows having the same salary get the
> same rank.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Typo on tutorial window page
@ 2025-01-21 17:44 Олег Семенов <oselemg@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Олег Семенов @ 2025-01-21 17:44 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs@lists.postgresql.org
sorry, my bad, thanks for the clarification!
вт, 21 січ. 2025 р., 18:40 користувач David G. Johnston <
david.g.johnston@gmail.com> пише:
> On Tue, Jan 21, 2025 at 10:39 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> PG Doc comments form <noreply@postgresql.org> writes:
>> > EXPECTED:
>> > As shown here, the rank function produces a numerical rank for each
>> distinct
>> > PARTITION BY value in the current row's partition, using the order
>> defined
>> > by the ORDER BY clause. rank needs no explicit parameter, because its
>> > behavior is entirely determined by the OVER clause.
>>
>> > ACTUAL:
>> > As shown here, the rank function produces a numerical rank for each
>> distinct
>> > ORDER BY value in the current row's partition, using the order defined
>> by
>> > the ORDER BY clause. rank needs no explicit parameter, because its
>> behavior
>> > is entirely determined by the OVER clause.
>>
>> Hmm, I think the current text is correct, or at least more nearly
>> correct than what you suggest.
>
>
>
>
>
>> Look at the example:
>>
>> <programlisting>
>> SELECT depname, empno, salary,
>> rank() OVER (PARTITION BY depname ORDER BY salary DESC)
>> FROM empsalary;
>> </programlisting>
>>
>> <screen>
>> depname | empno | salary | rank
>> -----------+-------+--------+------
>> develop | 8 | 6000 | 1
>> develop | 10 | 5200 | 2
>> develop | 11 | 5200 | 2
>> develop | 9 | 4500 | 4
>> develop | 7 | 4200 | 5
>> personnel | 2 | 3900 | 1
>> personnel | 5 | 3500 | 2
>> sales | 1 | 5000 | 1
>> sales | 4 | 4800 | 2
>> sales | 3 | 4800 | 2
>> (10 rows)
>> </screen>
>>
>> The ranks are separate within each partition (depname), and
>> within a partition rows having the same salary get the
>> same rank.
>>
>> regards, tom lane
>>
>>
>>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Typo on tutorial window page
@ 2025-01-21 17:55 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2025-01-21 17:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: oselemg@gmail.com; pgsql-docs@lists.postgresql.org
On Tue, Jan 21, 2025 at 10:39 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> PG Doc comments form <noreply@postgresql.org> writes:
> > EXPECTED:
> > As shown here, the rank function produces a numerical rank for each
> distinct
> > PARTITION BY value in the current row's partition, using the order
> defined
> > by the ORDER BY clause. rank needs no explicit parameter, because its
> > behavior is entirely determined by the OVER clause.
>
> Hmm, I think the current text is correct, or at least more nearly
> correct than what you suggest.
> The ranks are separate within each partition (depname), and
> within a partition rows having the same salary get the
> same rank.
>
>
Sorry for the empty email - finger spasm hit send.
I was going to write basically that but something feels off to me. Maybe
something like this:
"As shown here, the rank function produces a numerical ranking within each
partition, using the order defined by the ORDER BY clause. Ranking assigns
the same rank to all rows that tie according to the order by criteria,
while still incrementing the rank counter. Thus there are gaps in the
serial numbering. These can be removed by instead using the dense_rank
window function. Ties can instead be given their own unique value by using
the row_number window function. In all these cases, as the window function
is effectively just counting rows, the function itself has no input
parameter."
If we don't want to get into that level of nuance in the tutorial I suggest
we use the row_number() window function instead of rank, and just say
because we count rows no parameter is needed.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Typo on tutorial window page
@ 2025-01-21 18:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2025-01-21 18:10 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: oselemg@gmail.com; pgsql-docs@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I was going to write basically that but something feels off to me. Maybe
> something like this:
> "As shown here, the rank function produces a numerical ranking within each
> partition, using the order defined by the ORDER BY clause. Ranking assigns
> the same rank to all rows that tie according to the order by criteria,
> while still incrementing the rank counter. Thus there are gaps in the
> serial numbering. These can be removed by instead using the dense_rank
> window function. Ties can instead be given their own unique value by using
> the row_number window function. In all these cases, as the window function
> is effectively just counting rows, the function itself has no input
> parameter."
> If we don't want to get into that level of nuance in the tutorial I suggest
> we use the row_number() window function instead of rank, and just say
> because we count rows no parameter is needed.
Yeah, I was wondering if it'd be worth bringing up dense_rank,
but decided "probably not". I like your idea of switching the
example to use row_number to simplify things. What would the
text be then? Perhaps
As shown here, the <function>row_number</function> function
assigns sequential numbers to the rows within each partition,
in the order defined by the <literal>ORDER BY</literal> clause
(with tied rows numbered in an unspecified order).
<function>row_number</function> needs no explicit parameter,
because its behavior
is entirely determined by the <literal>OVER</literal> clause.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-01-21 18:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-20 13:28 Typo on tutorial window page PG Doc comments form <noreply@postgresql.org>
2025-01-21 17:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-01-21 17:39 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-21 17:44 ` Олег Семенов <oselemg@gmail.com>
2025-01-21 17:55 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-21 18:10 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox