w...@devauld.ca writes:
> -- The last_value(0 window function appears to be changing values
> -- mid window. An example is detailed below.
So far as I can see, these results are correct per spec. Note the
caution in the manual about these window functions:
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 or ROWS) to the OVER clause. See Section 4.2.8 for
more information about frame specifications.
Since your d1 values are all distinct, the last peer is the same as the
current row, so last_value(v) ends up just producing the current row's v.
Which is what the query is producing.
If you want the last row in the partition, you could use first_value
with the opposite sort order, or use last_value with the same sort
order and RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
(The current SQL committee is nothing if not verbose :-()
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
