ActiveRecord average value from last n rows

Question

I'm attempting to average the last n rows in a column in an activerecord query like so:

Points.where(player_id: <player_id>).limit(5).average(:points)

but it gives me the same answer as if I leave the limit out like:

Points.where(player_id: <player_id>).average(:points)

If I test without the .average(:points) then I get only five rows, but it seems the average is ignoring this.

Any help much appreciated!

I believe this is because the `limit` is executed after `aggregate` functions and/or `group_by` clause in SQL. If you check the generated query you should be able to see the order of the clauses. But in either case, I think you should add your DBMS as a tag in question. — vee, Jan 04 '14 at 05:10
@vee yes, but apparently count() is an exception to that in actionpack-3.2.13 at least. It pushes the limit into a subquery and applies the count on top of that. Not the case for other aggregate functiopns though, it seems. — David Aldridge, Jan 04 '14 at 12:34

score 0 · Answer 1 · edited May 23 '17 at 10:32

Firstly you need to ensure that you're getting the last 5.

Points.where(player_id: <player_id>).order("created_at desc").limit(5)

Then pop the points into an array ...

points = Points.where(player_id: <player_id>).order("created_at desc").limit(5).pluck(:points)

Then average them (as in How do I create an average from a Ruby array?)

points = Points.where(player_id: <player_id>).order("created_at desc").limit(5).pluck(:points)
ave    = points.reduce(:+).to_f / points.size

edit: Lookout for players having no points though, as points.size would equal zero and the result would be NaN.

Another edit: Reading further in that other link, you can simplify with:

points = Points.where(player_id: <player_id>).
                order("created_at desc").
                limit(5).
                pluck(:points).
                instance_eval { reduce(:+) / size.to_f }

ActiveRecord average value from last n rows

1 Answers1