In my role as a PostgreSQL consultant and trainer there are a couple of issues, which pop up on a regular basis.
Little things you’d better remember.
Window functions in Postgres allow you to perform computations related to the set of rows being returned by your query. Imagine you can group your query by a certain column, and have computations be limited by the boundaries of that group (in other words, a window).
To better explain the concept, let’s look at a very simple example (data can be downloaded here).
Imagine we have a table with 100 records, with four columns:
- id (primary key)
- a performance score from 1 to 100
- a marital status field
Something like this:
Now let’s suppose you want to view the best performers in each marital status. First thing to do is to order the list by marital status and performance. From the following image you can see what a window is:
Now that we have clear what a window is, we can introduce the concept of window functions. Very easily, window functions are functions which operate on those windows of data, in other words on those sub-recordsets. We can add columns with the result of a function that takes into account only the values of other rows inside the boundaries of the window.We can use the rank() function to show the rank of each record in its own window. A rank is not like a row number. A ranks output an equal value for an equal input inside a given set. Let’s write this query: Which leads to the following result: As you can see ranking restarts when a new window starts. Now go back and take a look at the query. Right after the call to the rank() function we define a partition criteria and a sorting. Those two parameters are required to specify the scope, the field of action of the window function. Now let’s suppose we want to query for just the first ranked people in each marital status. It’s very easy now that we have rank column in place. Just wrap everything inside a subquery and add a where condition.
Before window functions it was not that easy to get the same result. I don’t know precisely because I’m not that old. Anyway I suppose the same result was a matter of nested subqueries and similar sorcery.
I hope you found this useful. Here you can download the data I used for this post, in case you want to try it yourself.