Postgres essentials: window functions

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)
  • name
  • a performance score from 1 to 100
  • marital status field

Something like this:

Selection_542

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:

Selection_547

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: 
 
Selection_549
 
Which leads to the following result: 
 
Selection_550
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. 
 
Selection_001
Selection_002
 

Conclusion

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.

There’s much more you can do with window functions. Have a look a the documentation page for the feature and for the available functions.

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.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s