Re: LAST_VALUE returns the entire partition

=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay(at)gmail(dot)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