What Is the MySQL OVER Clause?

If you want to learn window functions in MySQL, you need to understand the OVER clause. In this article, we use real-world examples to explain what the MySQL OVER clause is, how it works, and why it’s so awesome.

In 2018, MySQL introduced a new feature: window functions. Window functions are a powerful SQL feature that perform a specific calculation (e.g. sum, count, average, etc.) on a set of rows; this set of rows is called a “window” and is defined with the OVER clause.

In this article, we will explain how to use the MySQL OVER clause in different scenarios. This will also introduce you to several window functions. I hope that, after reading this article, you'll be convinced that it's worthwhile to learn window functions. You’ll also know the basics of how to apply them in your queries!

Window Functions: A Very Requested Feature

Window functions have been available in most major databases for quite some time, but until 2018 they were not available in MySQL. To keep MySQL up to date, window functions were introduced in MySQL 8.02. If you plan to work with MySQL version 8 or later, it’s worth learning window functions and the OVER clause, as they’re very powerful.

Example Data

As we go through this article, we’ll create some query examples based on a database containing records for a group of farmers who produce oranges. The farmers share their production data, which is stored in the orange_production table you see below :

Simple OVER() Clause

The simplest use case is an empty OVER clause; this means the window is the complete result set returned by the query. For example, if our farmers want to have a report of every farmer record alongside the total of orange production in 2017, we’d write this query:

SELECT 
  farmer_name, 
  kilos_produced, 
  SUM(kilos_produced) OVER() total_produced
FROM orange_production 
WHERE crop_year = 2017

Here, the OVER clause constructs a window that includes all the records returned by the query – in other words, all the records for year 2017. The result is:

That was a very simple example of the MySQL OVER clause. The window of records was static (the window was the same for all the rows returned by the query).

OVER with PARTITION BY

One of the strong points of the OVER clause is being able to create a dynamic window of records (also called a sliding window frame). Sliding or dynamic window frames mean the window of records can be different for each row returned by the query. Moreover, the window is created based on the current row in the query, so the rows in the window can change when the current row changes.

Example 1

Let’s look at an example of a sliding window. Suppose our farmers want to see their own production along with the total production of the same orange variety.

SELECT 
  farmer_name, 
  orange_variety, 
  crop_year,
  kilos_produced, 
  SUM(kilos_produced) OVER(PARTITION BY orange_variety) AS total_same_variety
FROM orange_production 

The clause OVER(PARTITION BY orange_variety) creates windows by grouping all the records with the same value in the orange_variety column. This gives us two windows: ‘Golden’ and ‘SuperSun’.

Now you can see the result of the query. In the table below, each window is shown in a different color:

Notice that the Total Same Variety column (on the far right) includes the production for all years.

Example 2

Perhaps each farmer prefers to compare his production against the total production for the same variety in the same year. This comparison allows them to see their share of the production rate. To do that, we need to add the column crop_year to the PARTITION BY clause. The query will be as follows:

SELECT 
  farmer, 
  orange_variety, 
  crop_year,
  kilos_produced, 
  SUM(kilos_produced) OVER(PARTITION BY orange_variety, crop_year)                                                                                                                    AS total_same_variety_year
FROM orange_production 

The clause OVER(PARTITION BY orange_variety, crop_year) creates windows by grouping all records with the same value in the orange_variety and crop_year columns. Below, we’ve again used different colors to show the windows of rows created by this OVER clause:

If you are interested in going deeper into MySQL window functions, I suggest LearnSQL.com’s Window Functions in MySQL course, where you can find a complete description of this topic and several examples. If you’ll be using window functions frequently, this window functions cheat sheet – a complete quick guide to window functions – is very helpful.

OVER with ORDER BY

Now, we’ll use the sub-clause ORDER BY in the OVER clause. ORDER BY will generate a window with the records ordered by a defined criteria. Some functions (like SUM(), LAG(), LEAD(), and NTH_VALUE()) can return different results depending on the order of the rows inside the window. Let’s suppose that Farmer Pierre wants to know his cumulative production over the years:

SELECT 
  farmer, 
  crop_year,
  kilos_produced, 
  SUM(kilos_produced) OVER(ORDER BY crop_year) cumulative_previous_years
FROM orange_production
WHERE farmer = 'Pierre'

The SUM(kilos_produced) OVER(ORDER BY crop_year) window function works on an ordered window. It only considers the current and previous rows (i.e. current and previous crop_year values). We can see the result of this cumulative SUM() in the result table:

OVER with PARTITION BY and ORDER BY

In the next example, we will combine two sub-clauses (PARTITION BY and ORDER BY) in the OVER clause. Suppose the farmers want a report showing the total produced by each farmer every year and the total of the previous years. Then we need to partition by the farmer column and order by crop_year:

SELECT 
  farmer, 
  crop_year,
  kilos_produced, 
  SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) cumulative_kilos_produced
FROM orange_production

The following image shows the windows partitioned by farmer_name in different colors; note that inside each window, the rows are ordered by crop_year.

The expression SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) orders rows in the partition (which is based on the farmer value) using crop_year values. You can see this in the results table:

For more information on combining the PARTITION BY and ORDER BY clauses, see the article SQL Window Functions by Explanation.

Positional Window Functions

So, you can now see the importance of being able to order rows within a window. In fact, some MySQL window functions only work on ordered windows. For example, the LAG() function allows us to obtain a column value from the previous row (related to the current row) in an ordered window.

Let’s say we want a report to show the previous year’s production compared to the current year’s production. To do this, we use the LAG() function on a window ordered by crop_year:

SELECT 
  farmer, 
  crop_year,
  kilos_produced AS current_year_production, 
  LAG(kilos_produced) OVER(
    PARTITION BY farmer 
    ORDER BY crop_year) AS previous_year_production
FROM orange_production

The LAG() function can return any column value from the row previous to the current row, as the following query result shows:

The LEAD() function returns any column value from the row after the current row. Also, the NTH_VALUE() function returns the row in any specified position in an ordered window (e.g. first, second, last). These MySQL window functions help you easily create complex reports.

Let’s try a mathematical operation using MySQL window functions. Suppose the farmers want a report with the production delta related to the previous year. We can use the LAG() function to calculate the difference between the previous and current years’ productions:

SELECT 
  farmer, 
  crop_year,
  kilos_produced current_year_production, 
  kilos_produced - LAG(kilos_produced) OVER(
                     PARTITION BY farmer 
                     ORDER BY crop_year) AS production_delta
FROM orange_production

The expression kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) calculates the production delta between the previous and the current years, as we can see in the results:

For those readers who want to go deeper into positional functions, I suggest the article Common SQL Window Functions: Positional Functions, which has several examples of this interesting kind of window function.

Learn More About MySQL Window Functions

In this article, we covered several examples of using the MySQL OVER clause, from very simple to fairly complex usages. Knowing how OVER works and what rows it includes in the window is critical to using window functions.

There are many MySQL window functions you can experiment with: AVG(), MIN(), MAX(), LAG(), LEAD() and NTH_VALUE(). All of them use the OVER clause in the same way we just explained.

Finally, for those readers who want to learn more about MySQL window functions, I recommend the interactive Window Functions in MySQL course. And remember – when you increase your MySQL skills, you increase your assets!