MySQL Moving Average gives wrong result

Question

I'm using MySQL 7.3

I have adapted another answer to my on needs from Stackoverflow here but can't get it working. I want to calculate the rolling (moving) average of fantasy points for NFL players.

I think the problem may be that the data is not in the DB in Player order but Game_ID order..but I don't know. It calculates something just not the 5 game rolling average for each player.

The table has these columns Player, Game_ID, Date and DK_Total_FP where DK_Total_FP are the fantasy points awarded in a singe match.

This is my query

select
rnk_curr.Date, rnk_curr.Player,rnk_curr.Game_ID, ROUND(avg(rnk_prev5.DK_Total_FP),2) AS "5 match rolling Av",rnk_curr.DK_Total_FP
from
(
select Date,Player,Game_ID,DK_Total_FP,
@row_num := if(@lag = Game_ID, @row_num + 1,
               if(@lag := Game_ID, 1, 1)) as row_num
from nfl_stats    

cross join ( select @row_num := 1, @lag := null ) params
order by Player,Game_ID
) rnk_curr
inner join
(
select date, Player, DK_Total_FP, 
@row_num := if(@lag = Game_ID, @row_num + 1,
               if(@lag := Game_ID, 1, 1)) as row_num
from nfl_stats   
cross join ( select @row_num := 1, @lag := null ) params
order by Player, Game_ID
) rnk_prev5
on  rnk_curr.Player = rnk_prev5.Player
and rnk_prev5.row_num - 1  between rnk_curr.row_num - 6 and rnk_curr.row_num
group by Game_ID
order by Player,Date DESC

Although this data show the data in Player order (for testing without me providing 100s of lines)

CREATE TABLE `nfl_stats` (
  `Player` varchar(50) DEFAULT NULL,
  `Date` datetime DEFAULT NULL,
  `Game_ID` varchar(50) DEFAULT NULL,
  `DK_Total_FP` varchar(50) DEFAULT NULL
);


INSERT INTO `nfl_stats` (`Player`, `Date`, `Game_ID`, `DK_Total_FP`) VALUES
('Aaron Burbridge', '2016-09-18 00:00:00', '201609180car', '0'),
('Aaron Burbridge', '2016-10-02 00:00:00', '201610020sfo', '1.5'),
('Aaron Burbridge', '2016-11-06 00:00:00', '201611060sfo', '0'),
('Aaron Burbridge', '2016-11-20 00:00:00', '201611200sfo', '0'),
('Aaron Burbridge', '2016-10-23 00:00:00', '201610230sfo', '1.5'),
('Aaron Burbridge', '2016-12-24 00:00:00', '201612240ram', '3.5'),
('Aaron Burbridge', '2017-01-01 00:00:00', '201701010sfo', '3.4'),
('Aaron Burbridge', '2016-12-18 00:00:00', '201612180atl', '4.9'),
('Aaron Jones', '2018-11-15 00:00:00', '201811150sea', '27.3'),
('Aaron Jones', '2018-12-02 00:00:00', '201812020gnb', '15.2'),
('Aaron Jones', '2018-12-09 00:00:00', '201812090gnb', '19.6'),
('Aaron Jones', '2018-11-25 00:00:00', '201811250min', '18.3'),
('Aaron Jones', '2018-11-04 00:00:00', '201811040nwe', '9.5'),
('Aaron Jones', '2018-10-28 00:00:00', '201810280ram', '16.6'),
('Aaron Jones', '2018-11-11 00:00:00', '201811110gnb', '35.2');

The SELECT got cut off (it was on the same line as the code indentation). I have corrected this now, it is a query. I have MySQL 7.3 — user2818170, Jun 06 '20 at 14:53
OK, I have added the CREATE TABLE, if that is what you mean. I am hoping that someone can refer to the link I have provided to another stackoverflow question and work out why that works and mine does not. As I say, I think it is because the order of the DB is not the same. I need 5 previous rows of a Player rather than of the DB as it is originally provided. I have tried every combination of grouping and ORDER BY but I cannot get it to give the right answer. — user2818170, Jun 06 '20 at 17:31
OK, understood. If you run the CREATE TABLE, the first INSERT INTO and the SELECT provided, you will get result which has the 2 players all having the same rolling average for all games. I have just done it, so this, after your input thanks, is now a working example. It seems to average all the games rather than roll the average. — user2818170, Jun 06 '20 at 18:03
The desired result is the rolling average of the previous 5 games (defined by their timestamp) of an individual player from a dataset that is not ordered either by player or time. — user2818170, Jun 06 '20 at 18:42

Strawberry · Accepted Answer · 2020-06-06T19:14:49.987

Consider the following, for versions of MySQL prior to 8:

DROP TABLE IF EXISTS nfl_stats;

CREATE TABLE nfl_stats (
  player varchar(50)  NOT NULL,
  date date NOT NULL,
  DK_Total_FP varchar(50) DEFAULT NULL,
PRIMARY KEY (player,date)
);


INSERT INTO `nfl_stats`  VALUES
('Aaron Burbridge', '2016-09-18',  0),
('Aaron Burbridge', '2016-10-02',  1.5),
('Aaron Burbridge', '2016-11-06',  0),
('Aaron Burbridge', '2016-11-20',  0),
('Aaron Burbridge', '2016-10-23',  1.5),
('Aaron Burbridge', '2016-12-24',  3.5),
('Aaron Burbridge', '2017-01-01',  3.4),
('Aaron Burbridge', '2016-12-18',  4.9),
('Aaron Jones', '2018-11-15', 27.3),
('Aaron Jones', '2018-12-02', 15.2),
('Aaron Jones', '2018-12-09', 19.6),
('Aaron Jones', '2018-11-25',18.3),
('Aaron Jones', '2018-11-04', 9.5),
('Aaron Jones', '2018-10-28', 16.6),
('Aaron Jones', '2018-11-11', 35.2);

SELECT a.*
     , ROUND(AVG(b.dk_total_fp),2) rolling
  FROM 
     ( SELECT x.* 
            , COUNT(*) seq 
         FROM nfl_stats x 
         JOIN nfl_stats y 
           ON y.player = x.player 
          AND y.date <= x.date 
        GROUP  
           BY x.player 
            , x.date
     ) a
  JOIN
     ( SELECT x.* 
            , COUNT(*) seq 
         FROM nfl_stats x 
         JOIN nfl_stats y 
           ON y.player = x.player 
          AND y.date <= x.date 
        GROUP  
           BY x.player 
            , x.date
     ) b
    ON b.player = a.player 
   AND a.seq BETWEEN b.seq AND b.seq + 4
 GROUP 
    BY a.player
     , a.date
 ORDER
    BY a.player
     , a.date;

+-----------------+------------+-------------+-----+---------+
| Player          | Date       | DK_Total_FP | seq | rolling |
+-----------------+------------+-------------+-----+---------+
| Aaron Burbridge | 2016-09-18 | 0           |   1 |    0.00 |
| Aaron Burbridge | 2016-10-02 | 1.5         |   2 |    0.75 |
| Aaron Burbridge | 2016-10-23 | 1.5         |   3 |    1.00 |
| Aaron Burbridge | 2016-11-06 | 0           |   4 |    0.75 |
| Aaron Burbridge | 2016-11-20 | 0           |   5 |    0.60 |
| Aaron Burbridge | 2016-12-18 | 4.9         |   6 |    1.58 |
| Aaron Burbridge | 2016-12-24 | 3.5         |   7 |    1.98 |
| Aaron Burbridge | 2017-01-01 | 3.4         |   8 |    2.36 |
| Aaron Jones     | 2018-10-28 | 16.6        |   1 |   16.60 |
| Aaron Jones     | 2018-11-04 | 9.5         |   2 |   13.05 |
| Aaron Jones     | 2018-11-11 | 35.2        |   3 |   20.43 |
| Aaron Jones     | 2018-11-15 | 27.3        |   4 |   22.15 |
| Aaron Jones     | 2018-11-25 | 18.3        |   5 |   21.38 |
| Aaron Jones     | 2018-12-02 | 15.2        |   6 |   21.10 |
| Aaron Jones     | 2018-12-09 | 19.6        |   7 |   23.12 |
+-----------------+------------+-------------+-----+---------+

This kind of problem is considerably simplified in MySQL 8+

MySQL Moving Average gives wrong result

1 Answers1