Window Functions in Postgres
This PG Casts episode is sponsored by Hashrocket, a consultancy specializing in PostgreSQL; learn more at https://hashrocket.com. To see more PG Casts videos, visit our YouTube channel or https://www.pgcasts.com
Transcript:
Hey everyone, today we're going to explore the basics of window functions in Postgres.
A window function in Postgres is a function that performs a calculation across a set of rows that are somehow related to the current row.
To see this in action, let's consider our keyboards table.
By reading from the table, we can see that we have a number of keyboards organized into brands.
If we wanted to compare each keyboard's price to the average price for their brand, we could do this with a window function.
A window function starts with the function we're wanting to execute, which in our case is "avg" so we can find the average price, and is followed by an "over" clause.
The "over" clause in a window function allows us to use a "partition by" clause to specify how the rows are to be split up, or divided into partitions, for the calculation. To break the rows apart by brand, we would provide the "partition by" clause with our column name, "brand".
If we provide no arguments to the over clause, the calculation will be executed within a single partition containing all of the rows in the table.
Window functions also support an "order by" clause. The "order by" clause in a window function changes the behavior of what's called the window frame. When an "order by" clause is not present, the default window frame consists of all rows in the current partition. Once an "order by" clause is added, the window frame changes to consist of all rows from the start of the partition up to the current row, plus any rows that are equal to the current row according to the specified order.
An easy way to see this framing behavior is to use the sum function and order by price in our window function.
Looking at the first three rows of results, we can see that the second and third rows have the same sum, and that sum is for the first three rows. This is because these two rows share the same price, so the rows fell into the same window frame and were added to the sum at the same time. We can see the same behavior anywhere the keyboards had matching prices.
It's also possible to use the "order by" clause in conjunction with the "partition by" clause. The only thing we have to do is make sure that the "partition by" clause always precedes the "order by".
Window functions are executed against the virtual table produced by a query's "from" clause and its filtering clauses, "where", "having", and "group by". This means that window functions cannot be part of the logic of any of those clauses; they are only permitted in the "select" list and "order by" clause.
We can see this in action with a quick example. We'll start by using a window function to determine the ranking for each product's price according to brand. We'll call this value brand_rank.
We can see from the output that the returned rows are ordered by brand then by their brand ranking. We could order by brand ranking only by passing our brand_rank alias to the order by clause at the end of the query.
However, if we try to use the brand rank as part of a where clause, Postgres throws an error, saying that the column doesn't exist. Because window functions execute after "where" clauses, our window function alias is not yet defined when we try use it in our last query.
If we wanted to rank our products by price within their brand and only return the three least expensive products per brand, we could use a window function within a subselect.
We can see from our output that our last query worked. This is because the subselect was executed first, allowing us to use the results of the window function in our "where" clause.
Thanks for watching!
Watch video Window Functions in Postgres online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 23 January 2020, don't forget to share it with your friends and acquaintances, it has been viewed on our site 12,404 once and liked it 261 people.