AVG() in Window Functions

AVG() in Window Functions

Window Functions perform computation over a set of rows called windows and return an aggregated value for each row. Aggregate Functions are used to return a summarised value of multiple rows that make some mathematical sense. You can use aggregate functions as Window Functions with the help of OVER() clause.

There are 5 types of Aggregate Window Functions:

• SUM()
• AVG()
• COUNT()
• MAX()
• MIN()

In this notebook, we will look at one of the aggregate window functions called AVG(). AVG() as an aggregate function is used to return a single value as the average of a stated numeric column. When used as a window function, it will still return the average of a specific column but instead of returning a single value, it will return the running average at each row/window.

For grasping this better, I've considered using the Classic Models database and will be working with MySQL workbench. You can follow the steps below to set up this database locally:

1. Download this SQL file
2. In MySQL Workbench, click "File" > "Open SQL Script" to open the script;
3. Execute the script to create and populate the database.

Once executed, you should be able to view and browse tables in the "Schema" section of the sidebar. If you face an error, make sure you have MySQL server running.

About the Database:

Classic Models Inc. is a distributor of small scale models of cars, motorcycles, planes, ships trains etc. Products manufactured by Classic Models are sold in toy & gift stores around the world. Here's a small sample of their products (source):

Classic Models has offices around the world with dozens of employees. The customers of Classic Models are typically toy/gift stores. Each customer has a designated sales representative (an employee of Classic Models) they interact with. Customers typically place orders requesting several products in different quantities and pay for multiple orders at once via cheques.

Here's the Entity Relationship Diagram (ERD) for the database:

Let's understand this with the help of a sample question/query:

Question: Calculate the running average of payments made by a customer.

Below is a screenshot of the payments table:

AVG as a Window Function

We can simply implement a window function using the Over clause. OVER indicates to SQL, that we are now implementing a Window Function.

1. To answer our query above about calculating the running average of payments made by customers we will first create Partitions based on customerNumber.
2. Further to arrange them, we can use ORDER BY. In the query below I have ordered my result by paymentDate.
3. Finally, I've used AVG() to aggregate the resultant windows one row at a time.

AVG as an Aggregate Function

Below is the result of AVG() as an aggregate function that uses GROUP BY to return the average amount paid by each customer.

Conclusion

Window Functions prevent rows from getting grouped into a single resultant row, as we saw in the example above, and allow rows to retain their separate identities.

You can follow up on other Aggregate Window Functions like SUM, COUNT, MIN & MAX, etc.. in upcoming articles.