Window functions works only in non-grouped select statement.
Basically they allow us to look backwards or forwards in data – from the point of view of currently processed record.
With this said we can easily think about values we would like to have:
- row number of the current row in some row interval
- the same in %
- value of some column from previous / next row or rows
- first / last value of some column in some row interval
- the same but some Nth value
If it makes sense for specific window function we can use with this function in column computation:
- ORDER BY
- DISTINCT/ ALL
- FILTER – Pg 9.5
- WITHIN GROUP
And of course sometimes we need to define interval of rows. This is the “window” and this is the point when we need OVER clause;
- Window can be defined as partition of values in some columns – OVER(PARTITION BY … ).
- we could need some sort order for values in window OVER(ORDER BY … )
- range of rows – RANGE / ROW
- In PostgreSQL 9.5 we can also limit processed rows with FILTER clause – FILTER (WHERE …)