I ran across a question recently from a user about why they had strange results from a windowing query. This is better explained with an example, so let’s look at one.
I have some data in a table. This is NFL data, and a sample of it looks like this:
What I want to do is compare the passing yards each year with the most current value for that player, showing the plus or minus. This means that for Aaron Rodgers, who threw for 1675 yards in 2017, I’d want to show this for the first few years of his career:
PlayerName NFLYear PassYards Most Recent Yards Difference
------------- ------- --------- ----------------- -----------
Aaron Rodgers 2005 65 1675 -1610
Aaron Rodgers 2006 46 1675 -1629
Aaron Rodgers 2007 218 1675 -1457
Aaron Rodgers 2008 4038 1675 2363
This shows
me an easy view of the years where he was better in his career than he is now. Last year was likely a down year because of injury, but we’ll see this year.
In any case, if I run this query using LAST_VALUE() for the final year of his career, I don’t get the right results.
It seems as though in every row, I’m getting the current row as the last value, not the last value of the partition. My partition is by player, so I should only have a window for each player. In this case, I should have the years 2005-2017 for Aaron Rodgers. My ordering is by year, so the last value should be 1675.
Why isn’t it?
The reason has to do with the framing. As the window is consumed, the default values for the framing are between
- start – unbounded preceding
- end – current row
That means the first row for 2005 has the range of 2005-2005. The preceding rows are this row, and the current row is this row. For 2006, we have the first row as 2005 and the current row as 2006. The last value in this case is 46.
What we need to do is specify the entire window if we want that. In this case, we could use the current row as the start, but we certainly need the unbounded following rows.
This is a common mistake when writing window queries. I’d recommend you always include the partition and the framing to avoid any issues.
