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
There are 5 types of Aggregate Window Functions:
In this notebook, we will look at one of the aggregate window functions called
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:
- Download this SQL file
- In MySQL Workbench, click "File" > "Open SQL Script" to open the script;
- 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 indicates to SQL, that we are now implementing a Window Function.
- To answer our query above about calculating the running average of payments made by customers we will first create Partitions based on
- Further to arrange them, we can use
ORDER BY. In the query below I have ordered my result by
- 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.
!pip install jovian --upgrade --quiet
# Execute this to save new versions of the notebook jovian.commit(project="w-aggregate-avg")