Difficulty: Intermediate
Estimated Time: 20 minutes

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
  • Use ROWS framing
  • Use RANGE framing
  • Factorize the definitions
  • Use shortcuts to specify frames

You also learned these window functions:

  • first_value(expression)
  • last_value(expression)
  • 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 frame.

Window Functions in Postgres, Part II

Step 1 of 7

Step 1 - Connect to Postgres

Connect to the sample database like you did in Part I.

su - postgres psql pagila

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 FROM payment WHERE customer_id IN (61, 318) ORDER BY customer_id, payment_date;

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