SQL, data modeling, data quality, EDA, visualization · insight-1036
Spell out the window frame when you use LAST_VALUE or NTH_VALUE
- Evidence type
- documented fact
- Difficulty
- easy
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A query takes the "latest fare in the booking window" with LAST_VALUE(fare) OVER (PARTITION BY flight_no ORDER BY snapshot_day) and returns the current row's own fare on every row.
Action
Add an explicit frame, ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, when you want the partition's last or nth value. Or reverse the sort and use FIRST_VALUE. Check one partition by hand.
Reason
The default frame runs from the partition start to the current row's last peer, and value functions only read inside the frame.
Boundary
LAST_VALUE, NTH_VALUE and any aggregate used as a window function with ORDER BY.
Symptom or risk
With ORDER BY and no explicit frame, the frame stops at the current row's last peer, so the "last" value is just the newest row seen so far and the result looks plausible.
Exceptions
FIRST_VALUE with the default frame is already correct, and ranking functions ignore the frame.
Trade-offs
A full-partition frame can force the engine to buffer whole partitions on very large tables.
Pitfalls
Testing on a one-row-per-partition sample, where every frame definition returns the same value.
Lessons and concepts
Claims and their sources
In PostgreSQL the default frame with ORDER BY runs from the partition start through the current row's last peer, and the documentation notes this is likely to give unhelpful results for last_value and sometimes nth_value.