Search code examples
sqlpostgresqlwindow-functions

Why Last_value need frame_clause to have results like First_Value - Window Function PostgreSQL

This code show 3 try and why Last_value need frame_clause to have results like First_Value

select timestamp, 
last_value(timestamp) over (partition by campaign_id order by timestamp
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_day,
last_value(timestamp) over (partition by campaign_id order by timestamp) as last_day2,
first_value(timestamp) over (partition by campaign_id order by timestamp desc) as last_day3
from t1

Frame_clause is

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Result:

enter image description here

Solution

  • Technically all window definitions are supposed to have a RANGE.

    The custom is that if no RANGE is specified, then it is assumed to be `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'.

    With that default RANGE, last_value() is always CURRENT ROW, hence UNBOUNDED FOLLOWING must be specified to get what you want.