In the previous tutorial, you learned how to write a
WINDOW clause with paritioning and
ordering. In this tutorial you will extend that to define window frames by rows,
ranges, and groups.
Commands to run are shown in grey boxes throughout the tutorial. Click on the boxes or copy and paste the commands into the terminal to execute each command.
The data we will be using for the examples comes from the pagila sample database available on GitHub. We will look at customer payments for rentals.
Congratulations! Using Postgres you have now learned how to:
- Specify a window frame
- Factorize the definitions
- Use shortcuts to specify frames
You also learned these window functions:
- All of the aggregate functions
There are more window functions that you can use. Please refer to the Postgres documentation.
In a future tutorial, you will learn to frame by
GROUPS and also exclude rows from the
Window Functions in Postgres, Part II
Step 1 - Connect to Postgres
Connect to the sample database like you did in Part I.
su - postgres
You should now see the psql command prompt.
Window frames are defined over an ordered window partition. This means that the
ORDER BY clause is necessary for the frame to make any sense. The
PARTITION BY clause
is optional; without it, there will just be one partition over the entire window.
Here is the data we will be looking at:
SELECT customer_id, payment_date, rental_id, amount
WHERE customer_id IN (61, 318)
ORDER BY customer_id, payment_date;
WHERE clause is just to keep the size down a bit for these examples. You are
invited to play around with larger samples on your own.
Now let us define some frames!