Window Functions perform computation over a set of rows called windows and return an aggregated value for each row. In this notebook, we will look at how a window function is executed within a SQL query.
window_function() OVER( PARTITION BY column_name ORDER BY column_name)
OVER()indicates to SQL, that we are now implementing a Window Function. We cannot use a Window Function without the use of the OVER clause.
PARTITION BYdivides the table into windows along a specified column.
To understand how a window function is implemented, we will apply the following query with the function
ROW_NUMBER() on the
payments table from the Classic Models database.
SELECT *, ROW_NUMBER() OVER(PARTITION BY customerNumber ORDER BY paymentDate) AS paymentNumber FROM payments;
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 consider the layout below as a replica of our original table -
payments which contains information of payments made by all the customers along with other columns:
The very first step that occurs after calling a Window Function is the partitioning of rows into windows based on individual values of column1. In the case of our table replica below: YELLOW, BLUE, AND PINK, have seperate Windows.
On closely looking, you can observe that in the table below, windows have now been divided on the basis of the
Next, rows in each window are reordered on the basis of values in Column 2. For example in the table replica below:
BLUE < GREEN < TURQ
You can observe the same thing in the windows of payments table where rows now appear in the ascending order of
Now is the time for our intended Window Function to be applied. That means for each row, a different value is aggregated and returned as a new row in a new column.
In the example below: you can see that the
ROW_NUMBER() function has been applied to each row and a new column has been generated in each window.
Finally, each window is now amalgamated and returned as a single table just like we can see below.
Window Functions prevent rows from getting grouped into a single resultant row, like we saw in the example above, and allows rows to retain their seperate identities.
You can set up the above database Classic Models database locally on your MySQL workbench and try various window functions by following the steps below:
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.
!pip install jovian --upgrade --quiet
# Execute this to save new versions of the notebook jovian.commit(project="execution-window-sql")