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

user_12544460 0
Viewed 444

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

window-functions postgresql sql
2 Answers

That is because the default frame for window functions with an ORDER BY in the window definition defaults to

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

That happens to give the first value of the window for first_value, but a peer of the the current row for last_value.

user_64643080

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.

user_138083190
Related