リアルタイム・ランキングの実装例をRailsで書いてみた

  create_table "high_score_partitions", :force => true do |t|
    t.integer  "min"
    t.integer  "max"
    t.integer  "user_count", :default => 0
    t.datetime "created_at",                :null => false
    t.datetime "updated_at",                :null => false
  end
  add_index "high_score_partitions", ["user_count"], :name => "index_high_score_partitions_on_user_count"

  create_table "user_high_scores", :force => true do |t|
    t.integer  "user_id"
    t.integer  "high_score_partition_id"
    t.integer  "score"
    t.datetime "created_at",              :null => false
    t.datetime "updated_at",              :null => false
  end
  add_index "user_high_scores", ["high_score_partition_id"], :name => "index_user_high_scores_on_high_score_partition_id"
  add_index "user_high_scores", ["score"], :name => "index_user_high_scores_on_score"
  add_index "user_high_scores", ["user_id"], :name => "index_user_high_scores_on_user_id"

pt_max = 128
pt_more = [120000,280000]
pt_last = 0
pt_last_size = 0
pt_max.times.each do |pt_id|
  pt = HighScorePartition.where(id: (pt_id + 1)).first_or_initialize
  if pt_more[0] <= pt_last and pt_last <= pt_more[1]
    size = 3000
  else
    size = 8000
  end
  pt.min = pt_last + pt_last_size
  pt.max = pt_last + pt_last_size + size - 1
  pt_last = pt.min
  pt.user_count = 0
  pt.save!
 pt_last_size = size
 p "#{pt.min} => {id: #{pt.id}, max: #{pt.max}},"
end

class HighScorePartition < ActiveRecord::Base
  attr_accessible :id, :max, :min, :user_count

  has_many :user_high_scores

  def self.target score
    partition_map = HighScorePartitionMap::PARTITIONS.select {|k,v| k <= score and v[:max] >= score}.values.map {|v| v[:id]}
    self.where(id: partition_map)
  end

  def self.upper score
    partition_map = HighScorePartitionMap::PARTITIONS.select {|k,v| k <= score }.values.map {|v| v[:id]}
    self.where(id: partition_map)
  end

end

class HighScorePartitionMap

  PARTITIONS = {
       0 => {id: 1, max: 7999},
       8000 => {id: 2, max: 15999},
       16000 => {id: 3, max: 23999},
       24000 => {id: 4, max: 31999},
       32000 => {id: 5, max: 39999},
       40000 => {id: 6, max: 47999},
       48000 => {id: 7, max: 55999},
       56000 => {id: 8, max: 63999},
~~中略~~
      740000 => {id: 126, max: 747999},
       748000 => {id: 127, max: 755999},
       756000 => {id: 128, max: 763999}
    }

end

class UserHighScore < ActiveRecord::Base
  belongs_to :user
  belongs_to :high_score_partition
  attr_accessible :score

  before_save do |r|
    partition_map = HighScorePartitionMap::PARTITIONS.select {|k,v| k <= r.score and v[:max] >= r.score}.values.first
    # Not modify
    return true if r.high_score_partition_id == partition_map[:id]
    if r.high_score_partition_id
     where = [r.high_score_partition_id, partition_map[:id]]
    else
     where = partition_map[:id]
    end
    partition = HighScorePartition.where(id: where).lock(true).all.select{|v| v.id == partition_map[:id]}.first

    # Not found partition if cheat?
    raise "Score not found range partition" unless partition
    # Remove user for old pertition
    HighScorePartition.decrement_counter(:user_count, r.high_score_partition_id) if r.high_score_partition_id
    # Add user for new partition
    HighScorePartition.increment_counter(:user_count, partition)
    # New partition
    self.high_score_partition = partition
  end

  def current_rank
    total = HighScorePartition.upper(self.score).sum(:user_count)
    range = self.class.upper_byrange(self.score).count(:score)
    total + range + 1
  end

  def rank_range
    user_count = 0

    users = self.class.upper_byrange(self.score).limit(10).all
    return users if users.length >= 10

    user_count = users.count if users.count
    if self.current_rank >= 10
        partitions = HighScorePartition.order('min DESC').all
    else
        partitions = HighScorePartition.upper(self.score).order('min DESC').all
    end
    partitions.each do |partition|
      users << partition.user_high_scores.limit(10).all
      user_count += partition.user_count
      return users if user_count >= 10
    end
  end

  def self.upper_byrange score
    target = HighScorePartition.target(score).first
    self.where(score: (score + 1)..target.max)
  end

end

u1 = User.where(id: 1).first
hs1 = UserHighScore.where(user_id: u1).first_or_initialize
hs1.user = u
hs1.score = 2235
hs1.save!

u2 = User.where(id: 2).first
hs2 = UserHighScore.where(user_id: u2).first_or_initialize
hs2.user = u2
hs2.score = 2234
hs2.save!


u3 = User.where(id: 3).first
hs3 = UserHighScore.where(user_id: u3).first_or_initialize
hs3.user = u3
hs3.score = 4334
hs3.save!

hs1.current_rank
hs2.current_rank
hs3.current_rank

irb(main):020:0* hs1.current_rank
   (0.7ms)  SELECT SUM("high_score_partitions"."user_count") AS sum_id FROM "high_score_partitions" WHERE (min > 2235)
  HighScorePartition Load (0.2ms)  SELECT "high_score_partitions".* FROM "high_score_partitions" WHERE ((min <= 2235 AND 2235 <= max)) LIMIT 1
   (0.2ms)  SELECT COUNT("user_high_scores"."score") FROM "user_high_scores" WHERE ("user_high_scores"."score" BETWEEN 2236 AND 2999)
=> 2
irb(main):021:0> hs2.current_rank
   (0.4ms)  SELECT SUM("high_score_partitions"."user_count") AS sum_id FROM "high_score_partitions" WHERE (min > 2234)
  HighScorePartition Load (0.1ms)  SELECT "high_score_partitions".* FROM "high_score_partitions" WHERE ((min <= 2234 AND 2234 <= max)) LIMIT 1
   (0.1ms)  SELECT COUNT("user_high_scores"."score") FROM "user_high_scores" WHERE ("user_high_scores"."score" BETWEEN 2235 AND 2999)
=> 3
irb(main):022:0> hs3.current_rank
   (0.5ms)  SELECT SUM("high_score_partitions"."user_count") AS sum_id FROM "high_score_partitions" WHERE (min > 4334)
  HighScorePartition Load (0.1ms)  SELECT "high_score_partitions".* FROM "high_score_partitions" WHERE ((min <= 4334 AND 4334 <= max)) LIMIT 1
   (0.1ms)  SELECT COUNT("user_high_scores"."score") FROM "user_high_scores" WHERE ("user_high_scores"."score" BETWEEN 4335 AND 4999)
=> 1

          user     system      total        real