According to the documentation:
UNBOUNDED PRECEDING Applies to: SQL Server 2012 (11.x) through SQL Server 2017.
Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as window starting point.
How come then, when using this:
avg(Qty) over (partition by [Name]
ORDER BY [Period] desc ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
I still get rolling averages.
I understand that above construction should get me equivalent of Select TOP(3) avg(qty)..., at least according to the documentation.
Am I missing something?
After research, test and analysing the available documentation one thing to remember is that my understanding was incomplete: when working with ROWS in any configuration the reference point is always current row. So my query:
avg(Qty) over (partition by [Name]
ORDER BY [Period] desc ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
simply means "get all rows from start of the partition of the window to current row and 2 rows more". The unspoken part is "current row", which is there always regardless.
Thanks to Damien_the_Unbeliever for stating the obvious, which kept me thinking.