How to Calculate a Moving Average Using MySQL

Calculating a Moving Average MySQL?

Use something like

SELECT 
  sum(close) as sum,
  avg(close) as average
FROM (
    SELECT 
      (close)
    FROM 
      tbl
    WHERE 
      date <= '2002-07-05'
      AND name_id = 2
    ORDER BY 
      date DESC
    LIMIT 9 ) temp

The inner query returns all filtered rows in desc order, and then you avg, sum up those rows returned.

The reason why the query given by you doesn't work is due to the fact that the sum is calculated first and the LIMIT clause is applied after the sum has already been calculated, giving you the sum of all the rows present

MySQL calculate moving average of N rows

plan

• self join history on last 50 days
• take average grouping by date and security id ( of current )

query

select curr.date, curr.security_id, avg(prev.close)
from history curr
inner join history prev 
on prev.`date` between date_sub(curr.`date`, interval 49 day) and curr.`date`
and curr.security_id = prev.security_id
group by 1, 2
order by 2, 1
;

output

+---------------------------+-------------+--------------------+
|           date            | security_id |  avg(prev.close)   |
+---------------------------+-------------+--------------------+
| January, 04 2016 00:00:00 | 1           | 10.770000457763672 |
| January, 05 2016 00:00:00 | 1           | 10.800000190734863 |
| January, 06 2016 00:00:00 | 1           | 10.673333485921225 |
| January, 07 2016 00:00:00 | 1           | 10.59250020980835  |
| January, 08 2016 00:00:00 | 1           | 10.432000160217285 |
| January, 11 2016 00:00:00 | 1           | 10.40166680018107  |
| January, 12 2016 00:00:00 | 1           | 10.344285828726631 |
| January, 13 2016 00:00:00 | 1           | 10.297500133514404 |
| January, 14 2016 00:00:00 | 1           | 10.2877779006958   |
| January, 04 2016 00:00:00 | 2           | 56.15999984741211  |
| January, 05 2016 00:00:00 | 2           | 56.18499946594238  |
| ..                        | ..          | ..                 |
+---------------------------+-------------+--------------------+

sqlfiddle

reference

• sql rolling averages

---

modified to use last 50 rows

select
rnk_curr.`date`, rnk_curr.security_id, avg(rnk_prev50.close)
from
(
select `date`, security_id,
@row_num := if(@lag = security_id, @row_num + 1,
               if(@lag := security_id, 1, 1)) as row_num
from history 
cross join ( select @row_num := 1, @lag := null ) params
order by security_id, `date`
) rnk_curr
inner join
(
select date, security_id, close,
@row_num := if(@lag = security_id, @row_num + 1,
               if(@lag := security_id, 1, 1)) as row_num
from history 
cross join ( select @row_num := 1, @lag := null ) params
order by security_id, `date`
) rnk_prev50
on  rnk_curr.security_id = rnk_prev50.security_id
and rnk_prev50.row_num between rnk_curr.row_num - 49 and rnk_curr.row_num
group by 1,2
order by 2,1
;

sqlfiddle

note

the if function is to force the correct order of evaluation of variables.

Calculating moving average for different values in a column MySQL

You're missing the PARTITION BY clause:

SELECT team, 
       date,
       AVG(score) OVER (
         PARTITION BY team
         ORDER BY date ASC ROWS 3 PRECEDING
       ) AS MA3
FROM   table

Note that there will always be an average calculation, regardless of the window size. If you want the average to be null if your window size is smaller than 3, you could do it like this:

SELECT team, 
       date,
       CASE 
         WHEN count(*) OVER w <= 3 THEN null 
         ELSE AVG(score) OVER w
       END AS MA3
FROM   table
WINDOW w AS (PARTITION BY team ORDER BY date ASC ROWS 3 PRECEDING)

dbfiddle

Side note

Your next question might be about logical windowing, because often, you don't actually want to calculate the average over 3 rows, but over some interval,
like e.g. 3 days. Luckily, MySQL implements this. You could then write:

WINDOW w AS (PARTITION BY team ORDER BY date ASC RANGE INTERVAL 3 DAY PRECEDING)

Moving average using window function in MySQL

You can use Window Functions with Frames:

SELECT
    Date,
    Price,
    CASE WHEN 
           ROW_NUMBER() OVER (ORDER BY DATE) >= 3 THEN 
             AVG(Price) OVER (ORDER BY Date 
                              ROWS BETWEEN 2 PRECEDING AND 
                                           CURRENT ROW) 
         ELSE NULL 
    END AS avg
FROM yourTable
ORDER BY Date;

DB Fiddle Demo

Details:

• 2 PRECEDING means two rows above the current row (excluding the current row). We explicitly define Ascending order on Date. So that would means two closest dates, lower than the current row's date
• CURRENT ROW means the current row.
• BETWEEN allows us to consider the rows in the defined range (including boundary conditions).
• Since, you want moving average to be null for the first two rows, we can check for this using Row_number() function in Case .. When

How do I calculate a moving average using MySQL?

This is just off the top of my head, and I'm on the way out the door, so it's untested. I also can't imagine that it would perform very well on any kind of large data set. I did confirm that it at least runs without an error though. :)

SELECT
     value_column1,
     (
     SELECT
          AVG(value_column1) AS moving_average
     FROM
          Table1 T2
     WHERE
          (
               SELECT
                    COUNT(*)
               FROM
                    Table1 T3
               WHERE
                    date_column1 BETWEEN T2.date_column1 AND T1.date_column1
          ) BETWEEN 1 AND 20
     )
FROM
     Table1 T1

Getting moving average with window function

You may try this version:

WITH cte AS (
    SELECT visited_on, SUM(amount) AS amount
    FROM Customer
    GROUP BY visited_on
)

SELECT
    visited_on,
    SUM(amount) OVER (ORDER BY visited_on
                      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount,
    ROUND(AVG(amount) OVER (ORDER BY visited_on
                      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS average_amount
FROM cte
ORDER BY
    visited_on;

The logic here is to first aggregate the customers table by the visited date, to get the sum of the amounts per day. Then, we use your current logic to get the rolling 7 day sums and averages using analytic functions. The CTE I have used above is critical, without which you might not be taking the previous 7 days. For example, if a given day had 7 customers active, then your current logic would only take a single 7 for the rolling sum/average, not an actual 7 day window.

How to calculate a moving average in MYSQL

That is what I call a good challenge. My solution first creates a counter for the values and uses it as a table. From it I select everything and join with the same query as a subquery checking the position of the counter on both. Once the query works it just need an inner join with the actual table to do the update. Here it is my solution:

update stock_history tb1
   inner join 
(
select a.id, 
    case when a.step < 3 then null
    else
    (select avg(b.close) 
       from (
            select hh.*, 
                   @stp:=@stp+1 stp
              from stock_history hh,
                   (select @sum:=0, @stp:=0) x
             order by hh.dt
             limit 17823232
             ) b
      where b.stp >= a.step-2 and b.stp <= a.step
     ) 
     end dmal_3
  from (select h1.*, 
               @step:=@step+1 step
          from stock_history h1,
               (select @sum:=0, @step:=0) x
         order by h1.dt
         limit 17823232
        ) a
) x on tb1.id = x.id
set tb1.dmal_3 = x.dmal_3;

I changed some columns names for easiness of my test. Here it is the working SQLFiddle: http://sqlfiddle.com/#!9/e7dc00/1

If you have any doubt, let me know so I can clarify!

Edit

The limit 17823232 clause was added there in the subqueries because I don't know which version of MySql you are in. Depending on it (>= 5.7, not sure exactly) the database optimizer will ignore the internal order by clauses making it not work the way it should. I just chose a random big number usually you can use the maximum allowed.

The only column with different colunm name between your table and mine is the date one which I named dt because date is a reserved word and you should use backticks ( ` ) to use such columns, therefore I will left it as dt in above query.

---

Related Topics