Computing Moving Average
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:
date | value | moving_avg |
---|---|---|
2024-01-01 | 10.50 | 10.50000 |
2024-01-02 | 12.30 | 11.40000 |
2024-01-03 | 11.80 | 11.53333 |
2024-01-04 | 14.20 | 12.76667 |
2024-01-05 | 13.50 | 13.16666 |
2024-01-06 | 12.60 | 13.43333 |
2024-01-07 | 13.80 | 13.30000 |
2024-01-08 | 15.10 | 13.83333 |
2024-01-09 | 16.50 | 15.13333 |
2024-01-10 | 17.20 | 16.26667 |
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:
Date |
Amount |
---|---|
2024-04-01 |
50.00 |
2024-04-05 |
75.25 |
2024-04-10 |
120.25 |
2024-04-15 |
90.30 |
2024-04-20 |
110.60 |
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:
date | amount | running_total |
---|---|---|
2024-04-01 | 50.00 | 50.00 |
2024-04-01 | 50.00 | 100.00 |
2024-04-05 | 75.25 | 175.25 |
2024-04-05 | 75.25 | 250.50 |
2024-04-10 | 120.75 | 371.25 |
2024-04-10 | 120.75 | 492.00 |
2024-04-15 | 90.30 | 582.30 |
2024-04-15 | 90.30 | 672.60 |
2024-04-20 | 110.60 | 783.20 |
2024-04-20 | 110.60 | 893.80 |
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:
Date |
Amount |
---|---|
2024-04-01 |
100.50 |
2024-04-02 |
150.25 |
2024-04-03 |
200.75 |
2024-04-04 |
175.30 |
2024-04-05 |
225.60 |
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:
date | amount | running_total |
---|---|---|
2024-04-01 | 100.50 | 100.50 |
2024-04-02 | 150.25 | 250.75 |
2024-04-03 | 200.75 | 451.50 |
2024-04-04 | 175.30 | 626.80 |
2024-04-05 | 225.60 | 852.40 |
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.
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.