6 Most Useful SQL Window Functions You Should Definitely Know about!

Window Functions or Analytic Functions in SQL

A window function (or Analytic Function) in SQL uses values from multiple rows (or one row) to return values for each row. Do not confuse this with aggregate functions, which return a single value after aggregating multiple rows. There’s a simple way to identify the window functions. They have an OVER clause. Any function in SQL without an OVER clause is not a window function. It will be either an aggregate function or a scalar (single-row) function.

In this article, I am going to use this table, named population to showcase the examples and explain the codes. The first 5 rows of the table are as below. This is the data set which has a population for every state of USA for years from 1990 to 2013 and also has split over the total population and population of people above 18 years of age.

To understand the concepts, we will use the USA population dataset here. You may download the data set from my GitHub page here, and practice along with it.

Note: I am doing these operations in Jupyter Notebook(in Python Environment, on SQLite Syntax), and when you do the same in SQL editor, the table layout may look a bit different, but the content and codes are same.

SELECT * FROM population limit 5;

Example: Here is a SQL Query which uses a windows function to compare the population of each state for every year with the average population of that state over the period of all the years available in dataset.

SELECT *, avg(population) OVER (PARTITION BY state) FROM population;

The PARTITION BY is grouping the rows into partitions (by year) and the function (average function here) is applied to each partition separately. If you omit the PARTITION BY clause, and the OVER() a clause is empty, then the entire data set is treated as a single partition, and instead of getting the AVG population of states over the years, you will get the average population of all the states for the full period of time.

Now let us dive in and see different Window functions and their Python Counterpart along with codes and results.

LEAD() and LAG()

lead() and LAG() are used to compare the rows with their previous(preceding) or next(following) rows. For this to work best, the data shall be in order or sorted. These functions are most useful when you want to compare one period of time with a previous period of time based on the certain metrics. Some generic examples can be as below.

Example:

• The share price difference of a company from yesterday to today.
• Increase or decrease in the number of visitors to your website on a daily basis
• Sales of this year compared to sales of previous year etc.

Let’s see the comparison of the population for next year, as compared to the population of the previous year.

We can use LEAD() and LAG() to create new columns where we pull values from other rows. LEAD() pulls the value from the next row and LAG() pulls the value from the previous row.

Cumulative Sum

These are also known as running totals. Doing the running totals is simple when you use a windows function like sum(). This is particularly used when you want to show the accumulation or growth of any metric over time.

• Get a running total of sales from stores over days of a month.
• show running total or cumulative time spent on your website per user
• Get a running total of customers acquired by a sales representative

The following example will show you how to do the cumulative sum of the population for states over years.

Moving Averages

Moving Averages are very effective in forecasting values in time series. If you have ever looked at share price analysis, the analysts talk in terms of DMA. This DMA is nothing but Day Moving Average. So when they say this share is now priced above its 200 DMA, it means it is now trading at a price that is higher than the average price of this share, and this Average is the calculated average of the previous 200 Days closing prices.

• Moving Averages can be a good metric of weekly sales
• Moving Averages can be used to compare the price of objects with their average price in the previous month
• Moving averages can also give an idea of average hits on your website over the last 90 days

Our Population data is for the years 1990 to 2013. Here, I am interested in knowing, how the population for the present year is as compared to the previous 5 years average population. This Moving Average for the last 5 years can be calculated in SQL by following the window function.

ROW_NUMBER()

When you are working on data that has an element of time in it, or more specifically time series data, you can conclusively say that the events are happening in a particular order. Now you need to work upon groups of data and need to use some particular events, say the latest ones, or the oldest ones. These are still simpler, provided some of the time series functions (not the scope of this article).

But what if you want the 2nd last event or the 3rd from the latest? How nice it would be if you could number the events like they are separate rows, for each group, and then use the row numbers to access them when needed. This ROW_NUMBER() the function helps you do exactly that, and much more.

Let us see an example where we will number the rows, and then use one particular row number for all groups in order to use that data.

Now let us use the row numbers to meaningfully take out the third year for each state. This we can now do easily as we have numbered the rows, and 3rd row for each group is what we seek.

SELECT *     FROM population     WHERE row_num = 3

If you notice, in the previous SQL code, we did not create a table to save the row_num column. You can do so if you want to, but here to show you how the code works, I am going to combine the two SQL queries, and they work like charm.

The output table here may look long and overwhelming, but notice that its population of all States and Regions of USA for the year 1992, the third year in the list of years from 1990 to 2013.

RANKING in SQL

We have the following rank functions in SQL.

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• NTILE()

To give rank as per the value of records in your table, you may use RANK() function in SQL. The working of the same is similar to the ROW_NUMBER() in SQL, with a slight difference. The RANK() function gives same rank to equal values. The need for a ranking function is quite intuitive. You might want to

• rank the students based on their grades
• rank the countries based on their GDP or Population
• rank the stores based on their profit

Use the DESC option when you want the ranking to be done in Descending order. In this case you will observe that the rank here is in the reverse order of the rank in table above, and row_num in the table before that.

Conclusion:

In this article, you got introduced to the windows functions in SQL. These are the backbone of any data preparation operation using SQL. They will come in handy when you prepare data to feed into your machine learning model, Feature engineering or maybe just analyzing some pattern in the data.

In this article, you saw how to use SQL window functions work. But if you want to connect the two most powerful workhorses of the Data Science world, SQL and Python. This is not the end, but only the first step towards getting the “Best of Both Worlds”.

Now you can start using Python to work upon your data which rests in SQL Databases. In able to connect to your SQL databases, go thru my article How to Access & Use SQL Database with pyodbc in Python. Once you brought it as DataFrame, then all the operations are usual Pandas operations or SQL queries being operated on Pandas DataFrame as you saw in this article.

Apart from the function of SQL shown in this article, many other popular SQL functions are easily implementable in Python. Read 15 Pandas functions to replicate basic SQL Queries in Python for learning how to do that.

The implied learning in this article was, that you can use Python to do things that you thought were only possible using SQL. There may or may not be straight forward solution to things, but if you are inclined to find it, there are enough resources at your disposal to find a way out. You can look at the mix and match the learning from my book, PYTHON MADE EASY – Step by Step Guide to Programming and Data Analysis using Python for Beginners and Intermediate Level.

About the Author: I am Nilabh Nishchhal. I like making seemingly difficult topics easy and write about them. Check out more at https://www.authornilabh.com/. My attempt to make Python easy and Accessible to all is Python Made Easy.

Cover Photo Credit: Photo by Mari Madriz on Unsplash