mysql - SQL query to order by the top averages of previous data -
given table data these columns (other columns too, but, these important ones):
gameid awayscore homescore 1 3 8 2 4 2 3 4 9 4 15 5 5 0 8 6 4 10 ...
what looking average margin of victory on previous 3 games (assuming gameid order played), so, on game 6, should (6+8+10=24, average 8), on game 5 (8+10+5=23, average 7.666), etc. , show weeks 3-game average highest.
i tried this:
select g.gameid, (select avg(scores.ave) (select abs(awayscore-homescore) ave games gs (gs.gameid<=g.gameid) order gameid desc limit 3) scores) margin `games` g group g.gameid order margin desc limit 10;
but "unknown column 'g.gameid' in 'where clause'", assume because subquery of subquery(?), , loses reference? @ loss of how structure otherwise though work correctly (if possible @ all).
try query
select g.gameid, (select avg(abs(awayscore-homescore)) games gs (gs.gameid<=g.gameid) , (gs.gameid>g.gameid-3) ) margin `games` g order margin desc limit 10;
update if want use gamedate (datetime) order game, can create game order (gorder) number based on sort everytime select games below query.
select g.gameid, (select avg(abs(awayscore-homescore)) (select awayscore,homescore,@rank:=@rank+1 gorder games,(select @rank:=0)r order gamedate asc )gs (gs.gorder <= g.gorder) , (gs.gorder > g.gorder-3) ) margin (select games.gameid,@rank2:=@rank2+1 gorder games,(select @rank2:=0)r2 order gamedate asc) g order margin desc limit 10;
Comments
Post a Comment