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:

  • FILTER – Pg 9.5


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 …)