# Introduction to PostgreSQL window functions

What are window functions?
- A simple example
When are window functions evaluated?
Next steps

# 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!

← GROUP BY and calculating vote percentages PostgreSQL window functions (part 2) →