Execution of a Window Function in SQL

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.

SYNTAX:

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 BY divides 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.

Query:

SELECT *, ROW_NUMBER() 
  OVER(PARTITION BY customerNumber 
  ORDER BY paymentDate) AS paymentNumber
FROM payments;

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:

Original Data

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: customerNumber, checkNumber, and paymentDate.

Partition By Column 1:

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 customerNumber column.

Ordering By Column 2:

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 paymentDate.

Application of Window Function

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.

Union of Windows

Finally, each window is now amalgamated and returned as a single table just like we can see below.

Conclusion

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.

Database Setup

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:

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.