# 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:
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:
GROUP BY salary then it works, but the end result is normally even less useful:
SELECT salary, AVG(salary) FROM employees GROUP BY salary;
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;
OVER() does is tell
AVG that it should operate on related rows, which by default is the whole table, instead of the current row.
AVG is operating on the whole table, the result is:
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
SUM(), can also be used as window functions.
I'd recommend reading the "The
DENSE_RANK() functions" section of the PostgreSQL tutorial for three really cool examples of specific window functions in use.
# When are window functions evaluated?
From the official documentation:
If the query contains any window functions, these functions are evaluated after any grouping, aggregation, and
HAVINGfiltering 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
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!