How to work out average position over a certain period

Question

I am trying to show some stats for a fictitious game.

There's a Team model, a Player model, and a Run model.

I am able to get runs for a particular month in the player model:

def count_runs(date)
 self.runs.count(:conditions => {:created_at => (date.beginning_of_month..date.end_of_month)})
end

I am able to get them in the correct order in the Team controller and model:

@players = @team.players_by_count(Date.today)

def players_by_count(date)
 @date = date
 self.players.all.sort_by{|p| [-p.count_runs(@date)]}
end

I display that in a table to show their position:

<table>
  <% @players.each_with_index do |player, index| %>
  <tr>
    <td><%= (index+1).ordinalize %></td>
    <td><%= player.name %></td>
    <td><%= player.count_runs(Date.today) %></td>
  </tr>
  <% end %>
</table>

My schema is as follows:

create_table "teams", :force => true do |t|
    t.string   "name"
    t.datetime "created_at",    :null => false
    t.datetime "updated_at",    :null => false
  end

  create_table "players", :force => true do |t|
    t.string   "name"
    t.integer  "team_id"
    t.datetime "created_at",   :null => false
    t.datetime "updated_at",   :null => false
  end

  create_table "runs", :force => true do |t|
    t.integer  "player_id"
    t.datetime "created_at",               :null => false
    t.datetime "updated_at",               :null => false
  end

I want to be able to work out what their end-of-the-month average position has been. So at the end of every month they were in positions (1st, 3rd, 1st, 5th / 4 months) = Avg position = 2.5

I'm also trying to figure out how I'd get the winner (top placed player) for each month.

Any ideas?

Answer 1

I would suggest creating a model that just stored these monthly aggregations:

create_table "placements", :force => true do |t|
  t.integer  "player_id"
  t.date "month"
  t.integer "runs_count", :default => 0
end

So after each run is created, perhaps add a callback like so: (You will want to do something for if you delete a run as well)

def aggregate!
  # Many different ways to accomplish this, this is just the first I thought of.
  Placements.where(player_id: self.player_id, month: self.created_at.beginning_of_month).first_or_create do |placement|
    placement.runs_count += 1
  end
end

This approach will allow you to then do the following:

class Player < ActiveRecord::Base
  has_many :placements
  belongs_to :team
end

class Team < ActiveRecord::Base
  has_many :players
  has_many :placements, through: game
end

class Placement < ActiveRecord::Base
  belongs_to :player
end

Which will allow you to then do the following:

@players = @team.players_by_count(Date.today)

becomes

@placements = @team.placements.includes(:player).where(month: Date.today.beginning_of_month).order('runs_count DESC')

That way you get the Player, their runs_count AND their positions.

So in your view you could display them like so:

<table>
  <% @placements.each_with_index do |placement, index| %>
  <tr>
    <td><%= (index+1).ordinalize %></td>
    <td><%= placement.player.name %></td>
    <td><%= placement.runs_count %></td>
  </tr>
  <% end %>
</table>

If you want to get their average placements, I'd recommend you add a new column to placements, which will store their position for that given month, so you can do the following:

positions = player.placements.pluck(:position)
average = positions.sum / positions.size.to_f

The reason I suggested this path, was because I see how query intensive your current implementation was. If you had 10 players on a team, your @team.players_by_count(Date.today) would create 11 queries (1 + players_count). Where as @team.placements.includes(:player).where(month: Date.today.beginning_of_month).order('runs_count DESC') is 2 queries regardless of Player count.

Hope this helps!