agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Дмитрий Иванов <firstdismay@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: LAST_VALUE returns the entire partition
Date: Fri, 02 Jul 2021 10:04:10 -0400
Message-ID: <807951.1625234650@sss.pgh.pa.us> (raw)
In-Reply-To: <CAPL5KHp2AfXb_xi+-gpffaS1-idNarvxFuUOqkCs+rq-yWz1Lw@mail.gmail.com>
References: <CAPL5KHp2AfXb_xi+-gpffaS1-idNarvxFuUOqkCs+rq-yWz1Lw@mail.gmail.com>

=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
> I cannot understand the behavior of the last_value function. Conditions
> opposite to first_value conditions. Why such a result?

The default window frame isn't symmetrical is why.  Per the manual [1]:

    Note that first_value, last_value, and nth_value consider only the
    rows within the “window frame”, which by default contains the rows
    from the start of the partition through the last peer of the current
    row. This is likely to give unhelpful results for last_value and
    sometimes also nth_value. You can redefine the frame by adding a
    suitable frame specification (RANGE, ROWS or GROUPS) to the OVER
    clause. See Section 4.2.8 for more information about frame
    specifications.

That is, if you do nothing to change it then first_value returns the
value at the first row of the partition, while last_value returns the
value at the last peer of the current row (which isn't even very
well-defined when the current row has some peers).

			regards, tom lane

[1] https://www.postgresql.org/docs/current/functions-window.html

view thread (3+ messages)  latest in thread

Message-ID: <807951.1625234650@sss.pgh.pa.us>
Permalink:  ../807951.1625234650@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/807951.1625234650@sss.pgh.pa.us

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, firstdismay@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: LAST_VALUE returns the entire partition
  In-Reply-To: <807951.1625234650@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox