优化每日排名列表
每首诗分为两票,一票为诗歌 ID,另一票为诗歌 ID,获胜者为投票者。第二条记录与第一条记录相反。也许有更好的方法,但我试图找到一段时间内获胜率最高的诗歌。由于每次比较都有双重记录,这很令人困惑。我是否应该添加另一个表 Results,其中包含两条投票记录的 Comparison_id?
Here is a sample
poem_id:1 other_poem_id:2 wins:3
poem_id:2 other_poem_id:1 wins:3
so it is 50% rather than a running tally
scope :recent, lambda {
{ :joins => "JOIN votes ON votes.poem_id = poems.id",
:conditions => ["poems.created_at > ?", 8.days.ago],
:order => "votes.wins DESC",
:limit => 10
}
}
ActiveRecord::StatementInvalid: SQLite3::SQLException:不明确 列名称:created_at:SELECT
“诗”。* 来自“诗”加入 投票 ON votes.poem_id = Poems.id 哪里(创建于>'2010-02-12 15:12:35.764252') ORDER BY 胜出 DESC 限制 10
编辑:我更改了架构,这就是我现在正在处理的内容......
以下是跟踪诗歌排名的模型。我昨天刚写了第一稿。看起来有点笨拙,但我还不知道如何改进它。 DailyRanking.tabulate 每晚都会被 cron 调用。 (模型后面是比较的架构。)
# == Schema Information
# Schema version: 20100221120442
#
# Table name: daily_rankings
#
# id :integer not null, primary key
# poem_id :integer
# rank :integer
# percentile :integer
# wins :integer
# losses :integer
# draws :integer
# comparisons :integer
# created_at :datetime
# updated_at :datetime
#
class DailyRanking < ActiveRecord::Base
belongs_to :poem
class << self
def tabulate
# 1. get all comparisons over the past 24 hours
comparisons = Comparison.day.all
# 2. collect poem id for each time it wins
# TODO make hash of "poem_id" => {:wins => a, :losses => b, :draws => c}
a, results = 0, []
while a < comparisons.size
c = comparisons[a]
if c.poem1_id == c.winner_id
results << c.poem1_id
elsif c.poem2_id == c.winner_id
results << c.poem2_id
end
a += 1
end
# 3. presort by poem count
a, unsorted_wins = 0, []
until results.empty?
unsorted_wins << [results.first, results.count(results.first)]
results.delete(results.first)
end
# 4. sort by win count
sorted_wins = unsorted_wins.sort { |a, b| b[1] <=> a[1] }
# 5. repeat for losses
a, results = 0, []
while a < comparisons.size
c = comparisons[a]
if c.poem1_id == c.loser_id
results << c.poem1_id
elsif c.poem2_id == c.loser_id
results << c.poem2_id
end
a += 1
end
unsorted_losses = []
until results.empty?
unsorted_losses << [results.first, results.count(results.first)]
results.delete(results.first)
end
sorted_losses = unsorted_losses.sort { |a, b| b[1] <=> a[1] }
# 6. sort wins v losses
# a. sort wins[poem] v losses[poem]
# b. get poem and pct wins for wins[poem]
# c. delete wins[poem] and losses[poem]
# repeat
unsorted_results, a = [], 0
while a < sorted_wins.size
poem_id = sorted_wins[a][0]
wins = sorted_wins[a][1]
losses = sorted_losses.select do |item|
item.second if item.first == poem_id
end.compact.first.second
unsorted_results << [ poem_id, wins / (wins + losses).to_f ]
a += 1
end
# 7. sort by pct
sorted_results = unsorted_results.sort { |a, b| b[1] <=> a[1] }
# 8. persist rankings
sorted_results.each_with_index do |result, index|
ranking = find_or_create_by_rank(index + 1)
ranking.poem_id = result.first
ranking.save!
end
end
end
end
# == Schema Information
# Schema version: 20100221120442
#
# Table name: comparisons
#
# id :integer not null, primary key
# poem1_id :integer
# poem2_id :integer
# response :string(4) default("none"), not null
# winner_id :integer
# loser_id :integer
# user_id :integer
# session_id :integer
# ip :string(15)
# created_at :datetime
# updated_at :datetime
#
class Comparison < ActiveRecord::Base
scope :day, lambda { { :conditions => ["created_at > ?", 1.day.ago] } }
end
Each Poem as two Votes, one as poem_id, other_poem_id, wins & the second record which is the inverse of the first. Maybe there is a better way, but I'm trying to find the poems with the highest win percent over a period of time. It's confusing because of the double records for each comparison. Should I add another table, Results, which has a comparison_id for the two Vote records?
Here is a sample
poem_id:1 other_poem_id:2 wins:3
poem_id:2 other_poem_id:1 wins:3
so it is 50% rather than a running tally
scope :recent, lambda {
{ :joins => "JOIN votes ON votes.poem_id = poems.id",
:conditions => ["poems.created_at > ?", 8.days.ago],
:order => "votes.wins DESC",
:limit => 10
}
}
ActiveRecord::StatementInvalid:
SQLite3::SQLException: ambiguous
column name: created_at: SELECT
"poems".* FROM "poems" JOIN
votes ON votes.poem_id = poems.id
WHERE (created_at > '2010-02-12
15:12:35.764252') ORDER BY wins DESC
LIMIT 10
edit: I changed the schema, here is what I'm working with now...
the following is a model keeping track of rankings for poems. I just wrote this 1st draft yesterday. It seems a bit clunky, but I don't know how to improve it just yet. DailyRanking.tabulate will be called every night by cron. (following the model is the schema for the Comparison.)
# == Schema Information
# Schema version: 20100221120442
#
# Table name: daily_rankings
#
# id :integer not null, primary key
# poem_id :integer
# rank :integer
# percentile :integer
# wins :integer
# losses :integer
# draws :integer
# comparisons :integer
# created_at :datetime
# updated_at :datetime
#
class DailyRanking < ActiveRecord::Base
belongs_to :poem
class << self
def tabulate
# 1. get all comparisons over the past 24 hours
comparisons = Comparison.day.all
# 2. collect poem id for each time it wins
# TODO make hash of "poem_id" => {:wins => a, :losses => b, :draws => c}
a, results = 0, []
while a < comparisons.size
c = comparisons[a]
if c.poem1_id == c.winner_id
results << c.poem1_id
elsif c.poem2_id == c.winner_id
results << c.poem2_id
end
a += 1
end
# 3. presort by poem count
a, unsorted_wins = 0, []
until results.empty?
unsorted_wins << [results.first, results.count(results.first)]
results.delete(results.first)
end
# 4. sort by win count
sorted_wins = unsorted_wins.sort { |a, b| b[1] <=> a[1] }
# 5. repeat for losses
a, results = 0, []
while a < comparisons.size
c = comparisons[a]
if c.poem1_id == c.loser_id
results << c.poem1_id
elsif c.poem2_id == c.loser_id
results << c.poem2_id
end
a += 1
end
unsorted_losses = []
until results.empty?
unsorted_losses << [results.first, results.count(results.first)]
results.delete(results.first)
end
sorted_losses = unsorted_losses.sort { |a, b| b[1] <=> a[1] }
# 6. sort wins v losses
# a. sort wins[poem] v losses[poem]
# b. get poem and pct wins for wins[poem]
# c. delete wins[poem] and losses[poem]
# repeat
unsorted_results, a = [], 0
while a < sorted_wins.size
poem_id = sorted_wins[a][0]
wins = sorted_wins[a][1]
losses = sorted_losses.select do |item|
item.second if item.first == poem_id
end.compact.first.second
unsorted_results << [ poem_id, wins / (wins + losses).to_f ]
a += 1
end
# 7. sort by pct
sorted_results = unsorted_results.sort { |a, b| b[1] <=> a[1] }
# 8. persist rankings
sorted_results.each_with_index do |result, index|
ranking = find_or_create_by_rank(index + 1)
ranking.poem_id = result.first
ranking.save!
end
end
end
end
# == Schema Information
# Schema version: 20100221120442
#
# Table name: comparisons
#
# id :integer not null, primary key
# poem1_id :integer
# poem2_id :integer
# response :string(4) default("none"), not null
# winner_id :integer
# loser_id :integer
# user_id :integer
# session_id :integer
# ip :string(15)
# created_at :datetime
# updated_at :datetime
#
class Comparison < ActiveRecord::Base
scope :day, lambda { { :conditions => ["created_at > ?", 1.day.ago] } }
end
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我认为适合您的 SQL 查询类似于 SELECT Poems.*, Percentage as ((SELECT Wins FROM votes WHERE Poem_id = Poem.id WHERE Created_at > 8.days.ago) / (SELECT Wins来自投票 WHERE other_poem_id =诗歌.id WHERE created_at > 8.days.ago)) 按百分比 DESC LIMIT 10 排序。至于如何优化它并将其转换为 Rails 范围,我不确定。
但是,您遇到的错误是由于
["poems.created_at > ?", 8.days.ago]
条件转换为 SQL 的方式造成的。 SQLite 不知道您是在寻找诗歌.created_at 还是 votes.created_at(顺便说一句,根据您的描述,我认为您正在寻找votes.created_at
)。I think a SQL query that would work for you would be something like
SELECT poems.*, percentage as ((SELECT wins FROM votes WHERE poem_id = poem.id WHERE created_at > 8.days.ago) / (SELECT wins FROM votes WHERE other_poem_id = poem.id WHERE created_at > 8.days.ago)) ORDER BY percentage DESC LIMIT 10
. As far as how to optimize that and translate it to a Rails scope, I'm not sure.However, the error you are experiencing is due to the way that the
["poems.created_at > ?", 8.days.ago]
condition is being translated to SQL. SQLite doesn't know whether you are looking for poems.created_at or votes.created_at (and by the way, from your description, I think you are wantingvotes.created_at
).