-1

I have a table in MYSQL(version 5.7.33) which looks like shown below:

Date SalesRep Sale
2021-04-01 Jack 10
2021-04-02 Jack 8
2021-03-01 Lisa 10
2021-03-02 Lisa 14
2021-03-03 Lisa 21
2021-03-04 Lisa 7
2021-03-08 Lisa 10
2021-03-09 Lisa 20
2021-03-10 Lisa 15

I want the moving average of Sale column, but don't want that to be based on the dates since the dates have gap, instead I want it based on row numbers and grouped by SalesRep. So something like this:

Date SalesRep Sale MoveAvg
2021-04-01 Jack 10 10
2021-04-02 Jack 8 9
2021-03-01 Lisa 10 10
2021-03-02 Lisa 14 12
2021-03-03 Lisa 21 15
2021-03-04 Lisa 7 13
2021-03-08 Lisa 10 12.4
2021-03-09 Lisa 20 13.6
2021-03-10 Lisa 15 13.8

So the moving average is for all the dates from start to finish for a particular sales rep and then it starts over for another sales rep and so on. Is this possible to do in MYSQL? Thank you in advance!

Priya Patel
  • 31
  • 4

1 Answers1

4

You could use avg as a window function with a frame clause for this:

SELECT dt, salesrep, sale,
     AVG(sale) OVER (PARTITION BY salesrep ORDER BY dt
                    ROWS UNBOUNDED PRECEDING)
       AS moveavg

Without window functions, you simply join all previous rows for each salesrep:

select a.dt, a.salesrep, a.sale, avg(b.sale) as moveavg
from mysterytablename a
join mysterytablename b on b.salesrep=a.salesrep and b.dt <= a.dt
group by a.salesrep, a.dt
ysth
  • 96,171
  • 6
  • 121
  • 214
  • Thanks! I have tried it this way but I keep getting syntax error so decided to ask. SELECT DT, salesrep, sale, AVG(sale) OVER (PARTITION BY salesrep ORDER BY DT ROWS UNBOUNDED PRECEDING) AS moveavg FROM table; – Priya Patel Jun 03 '21 at 07:52
  • The error has to do with the version of MySQL I am using, its 5.7.33, the function OVER is not supported in this version I guess. – Priya Patel Jun 03 '21 at 12:42
  • yes, you need mysql 8. note that oracle will not support version 5.7 for much longer and consider upgrading – ysth Jun 03 '21 at 15:35
  • added a 5.7 version – ysth Jun 03 '21 at 15:51
  • Thanks, yes we are considering updating to 8.0 soon. – Priya Patel Jun 04 '21 at 04:26