-1

I need to calculate the average score of every player's 3 most recent scores (golf rounds). If it matters to the code / syntax, this is only required when they have a minimum of 3 scores.

I have a view that has these fields:

round_id
player_id
score
round_date

As new scores are entered into the database, I would like to keep track and notified. I thought my options would to be keep this within the database (somehow) or to generate appropriate php code to do the equivalent. But thought keeping inside the database itself, it could / would handle new data insertions / updates better. PHP would have a page that would need to be loaded to execute.

I have seen some examples of nested select statements, and some that have mysql variables (my basic sql skills, not really gone into variables so would need explaining). none seem to directly relate to my specific needs.

Thanks

jingo_man
  • 509
  • 3
  • 13
  • 2
    This is basically a specification. We at SO are not a free coding resource. If you do some research, have a go, but fail to get it completely right, then we are more than willing to help. – RiggsFolly May 14 '17 at 17:42
  • 2
    See http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group for how to get the top 3 scores. Put this in a subquery, then use `AVG()` in the main query to get the averages. – Barmar May 14 '17 at 18:09
  • @Barmar find_in_set doesn't really apply here, as far as I can see – ysth May 15 '17 at 00:15
  • @ysth What does `find_in_set` have to do with this? – Barmar May 15 '17 at 04:11
  • @Barmar sorry, I just saw the approved answer for the question you linked to. Were you suggesting the answer that used variables? – ysth May 15 '17 at 17:43
  • @RiggsFolly - apologies if this it comes across as specification. i have searched through numerous threads. there are so many alternatives that it just adds to the confusion and noise (for me at least!). case in point, Barmar's link. there are many replies and further comments. and to me it doesnt look like any have been marked as the approved answer, just ordered by vote counts. i have again tried some of these responses, but not any further. processing in php now seems more doable with my limited skills / knowledge. – jingo_man May 15 '17 at 19:39
  • @ysth There are 14 answers, and probably most of them would be acceptable. I haven't examined them all in detail, although the most recent one that uses window functions won't work in MySQL. – Barmar May 16 '17 at 15:58
  • @Barmar yet. mysql will have window functions some year soon. and mariadb has them now. – ysth May 16 '17 at 21:15

1 Answers1

0

Something like this (untested):

select player_id, avg(substring_index(substring_index(scores,',',round),',',-1))
from
(
    select 1 round union all select 2 union all select 3
) last_rounds
cross join
(
    select player_id, group_concat(score order by round_date desc) scores
    from player_round
    group by player_id
    having count(*) >= 3
) player_scores
group by player_id
ysth
  • 96,171
  • 6
  • 121
  • 214
  • am i correct that the 3rd query only finds player_id's where they have played more than 3 times? this isnt specifically what i was looking for - i would like to average the last 3 rounds only, but they have to have played 3 rounds. – jingo_man May 15 '17 at 19:52
  • no, it says 3 or more, not more than 3. you can remove that line, but then it will get even players that have played only 1 or 2 rounds – ysth May 16 '17 at 00:12