A short focus on window functions

In this hopefully short article, we will try to understand what window functions are and why since 2003, there are the application programmer's best friend. We will first get through well known aggregate functions to spot the differences between theses functions families.

Let's say we have the data about some European factories with production information. See what we can do with Postgres

Setup

If you are willing to get your hands dirty with this article, fire up a Postgres client and create the following table:

Normally, without a WHERE clause that filters rows, every query on this table would return a set composed of 10 rows. But an aggregate function reduces the output to a determined number of rows. In the case above, the result set is composed of one row indicating the cardinality of the factory table. In counter part, we did loose a lot of the information contained in the table. It is of course possible to tell the database about how we do want to reduce the set:

Window functions

Let's now imagine we need to rank factories by production. The first think one may state is that it is not a reducing operation: we will output as many rows as there are factories in the table. We need a different beast here able to compare rows in a determined window of data: a window function.

The window function rank is ranking rows defined in the given window order by production desc. It is even able to manage ex aequo (look at ranking 3). What if we want to rank factories by country ? It is just a matter of defining the data window:

To close this article with a more complex example, let's say we want to have for each factory, the name of the preceding more productive European factory and the following less productive. The functions lead and lag are here for that:

Copyright Grégoire HUBERT 2014 - 2015. All contents on this site are under the CC by-sa licence.CSS nicely adapted by Mikael Paris.Free tools for a free network. This site has been made and is powered using open source and free softwares only.