Here’s an example of a simple window function querying a table in our IBM Cloud Databases for PostgreSQL database containing the United States Census data on estimated population:
If the aggregation you want to run is to be performed across all the rows returned by the query and you don’t need to specify any other conditions, you can use the OVER() clause by itself. Let’s start by looking at using OVER() all by itself. Let’s dive in! OVER( )ĭepending on the purpose and complexity of the window function you want to run, you can use OVER() all by itself or with a handful of conditional clauses. Once you get used to how the OVER() clause is formatted, where it fits in your queries, and the kind of results you can get, you’ll soon start to see lots of ways to apply it. It is important to note that window functions always use the OVER() clause, so if you see OVER(), you’re looking at a window function. In some situations, window functions can minimize the complexity of your query or even speed up the performance. While they are similar to standard aggregations, there are also additional functions that can only be used through window functions (such as the RANK() function we’ll demonstrate below). The good news is that window functions aren’t nearly as esoteric as they may seem.Īs the name implies, window functions provide a “window” into your data, letting you perform aggregations against a set of data rows according to specified criteria that match the current row. However, you may not be familiar with window functions since they’re touted as an advanced feature. If you use PostgreSQL, you’re probably already familiar with many of the common aggregate functions, such as COUNT(), SUM(), MIN(), MAX(), and AVG().
#POSTGRESQL WINDOW FUNCTIONS HOW TO#
In this first article, we’ll look at how to use window functions in PostgreSQL. In our Metrics Maven series, IBM Cloud Database’s data scientist shares database features, tips, tricks, and code that you can use to get the metrics you need from your data. Welcome to the Metrics Maven series: Database features, tips, tricks, and code