Compute a Moving Average in MySQL

Moving the averages is an important technique in the field of data analysis and time-series data processing. By reducing data fluctuations moving averages aid in highlighting trends in the data.

We’ll examine how to compute moving averages in MySQL a well-liked relational database administration system in this post. Gaining an understanding of this concept can improve your capacity to get useful information from the data.

Let’s review moving averages in brief before getting into implementation. By calculating a series of averages for the various subsets of the entire dataset, one can examine data points using the moving average technique. By smoothing out the data this is done to find trends or patterns.

The simplest form of the moving average is the simple moving average which calculates the mean of the fixed number of the data points over a specified period. Let’s illustrate this with an example of computing a 5-day SMA for the daily stock prices.

To demonstrate computing moving averages in the MySQL let’s first create some sample data. We’ll create a simple table with the two columns: date and value representing the date and corresponding numeric value.

CREATE TABLE sample_data (
    date DATE,
    value DECIMAL(10, 2)
);
INSERT INTO sample_data (date, value)
VALUES
    ('2024-01-01', 10.5),
    ('2024-01-02', 12.3),
    ('2024-01-03', 11.8),
    ('2024-01-04', 14.2),
    ('2024-01-05', 13.5),
    ('2024-01-06', 12.6),
    ('2024-01-07', 13.8),
    ('2024-01-08', 15.1),
    ('2024-01-09', 16.5),
    ('2024-01-10', 17.2);

Output:

Now, let’s compute the moving average using the MySQL. We’ll use a window function to calculate the average of the last n rows where n is the window size.

SELECT
    date,
    value,
    (SELECT AVG(value) 
     FROM sample_data AS s2 
     WHERE s2.date BETWEEN DATE_SUB(s1.date, INTERVAL 2 DAY) AND s1.date) AS moving_avg
FROM
    sample_data AS s1;

Output:

Explanation: This query computes the moving average of the value column over a window of the 3 rows by using the correlated subquery. It calculates the average of the value column for the current row and the two preceding rows.

Example: Computing Running Totals for Monthly Expenses

Let’s consider a similar scenario but with the monthly expenses data. Suppose we have a table named expenses with the following data:

We want to compute the running total of the expenses for each day.

SELECT
    date,
    amount,
    @running_total := @running_total + amount AS running_total
FROM
    expenses,
    (SELECT @running_total := 0) AS rt
ORDER BY
    date;

Output:

Explanation:

• The amount column represents daily expenses.
• The running_total column displays the cumulative sum of the expenses up to each day.
• The @running_total variable is initialized to 0 and it accumulates the running total as the rows are processed.

Example: Computing Running Totals for Daily Sales

Suppose we have a table named sales with the following data:

We want to compute the running total of sales for the each day.

SELECT
    date,
    amount,
    @running_total := @running_total + amount AS running_total
FROM
    sales,
    (SELECT @running_total := 0) AS rt
ORDER BY
    date;

Output:

Explanation:

• The amount column represents daily sales figures.
• The running_total column displays the cumulative sum of the sales up to each day.
• The @running_total variable is initialized to 0 and it accumulates the running total as the rows are processed.

In this article, we’ve explored how to compute moving averages in MySQL using the window functions. By applying moving averages to the data we can gain insights into the trends and patterns which can be valuable for various analytical tasks. Experiment with the different window sizes and datasets to see how moving averages can help better understand your data.