# Introduction to PostgreSQL window functions

# What are window functions?

A window function allows us to perform a calculation across a set of rows related to the current row. This is similar to using GROUP BY, because we have access to data related to a row, but it doesn't group rows together in the output.

A window is a set of rows related to the current row, on which we can perform calculations using a window function.

# A simple example

This is the data set we're working with:

id name salary department
1 Rolf Smith 35000 Product
2 Bob Smith 55000 Research
3 Anne Pun 87000 Product
4 Charlie Robert 100000 Research

Let's say that for each employee, we want to get their salary and the average salary of the group. Here's an invalid query:

SELECT salary, AVG(salary) FROM employees;

This is invalid because in order to evaluate AVG(salary), we must group by it first. Otherwise, the intuitive result should be something like this:

salary avg
35000 69250
55000 69250
87000 69250
100000 69250

If we GROUP BY salary then it works, but the end result is normally even less useful:

SELECT salary, AVG(salary) FROM employees GROUP BY salary;
salary avg
35000 35000
55000 55000
87000 87000
100000 100000

What's happening here is that the AVG function is operating on what we call the current row.

The current row is each group of data in this case, since that is what GROUP BY is used for.

However, we can use a window function to make the AVG function operate on another set of rows, rather than just the current row.

This is valid:

SELECT salary, AVG(salary) OVER() FROM employees;

What OVER() does is tell AVG that it should operate on related rows, which by default is the whole table, instead of the current row.

Because AVG is operating on the whole table, the result is:

salary sum
35000 69250
55000 69250
87000 69250
100000 69250

Inside the brackets of OVER(), we could place a definition of the window. That would affect which rows the AVG operates on. More examples in just a moment!

What part is the window function?

PostgreSQL comes with a bunch of functions that must use OVER() in order to function. Those functions are "window functions". Some other functions, such as AVG(), COUNT(), or SUM(), can also be used as window functions.

I'd recommend reading the "The ROW_NUMBER(), RANK(), and DENSE_RANK() functions" section of the PostgreSQL tutorial[1] for three really cool examples of specific window functions in use.

# When are window functions evaluated?

From the official documentation[2]:

If the query contains any window functions[3][4][5], these functions are evaluated after any grouping, aggregation, and HAVING filtering is performed. That is, if the query uses any aggregates, GROUP BY, or HAVING, then the rows seen by the window functions are the group rows instead of the original table rows from FROM/WHERE.

The documentation isn't explicit on this, but window functions also run after the normal WHERE filtering is applied. In a window function, you have access to the full result set (so you're not limited by the columns you're SELECTing).

Window functions can be used in the SELECT list and in ORDER BY, but nowhere else!

# Next steps

We've learned about window functions in this chapter. Window functions run in related rows, and don't affect the grouping or values of the main result set.

Some window functions need a sorted column to operate on, and we give it using ORDER BY column.

We can further subdivide the rows on which a window function operates by using PARTITION BY column.

Some window functions also accept arguments, although we haven't looked at those here. For more examples, check out the PostgreSQL tutorial page on window functions[1:1]. It's good!

  1. PostgreSQL Window Functions (PostgreSQL Tutorial) (opens new window) ↩︎ ↩︎

  2. 7.2.5. Window Function Processing (PostgreSQL Official Documentation) (opens new window) ↩︎

  3. 3.5. Window Functions (PostgreSQL Official Documentation) (opens new window) ↩︎

  4. 4.2.8. Window Function Calls (PostgreSQL Official Documentation) (opens new window) ↩︎

  5. 9.21. Functions Available in a Window (PostgreSQL Official Documentation) (opens new window) ↩︎