Ruby on Rails:基于其他列数据求和表列行值

I have a table with columns 'id', 'resource_id', 'read_time', 'value' where 'value' is a float

我有一个包含列“id”、“resource_id”、“read_time”、“value”的表，其中“value”是一个浮点数

What I am trying to accomplish is to return a list of records such that the 'value' of each record is the sum of all the records at a specific 'read_time' but having differing 'resource_id' values.

我要做的是返回一个记录列表，以便每个记录的“值”是在一个特定的“read_time”上所有记录的总和，但是具有不同的“resource_id”值。

I am wondering if there is a clever way (ie not looping through all the entries) to accomplish this. Currently I am implementing something along these lines:

我想知道是否有一种聪明的方法(不循环遍历所有条目)来实现这一点。目前，我正在按照以下思路实施:

@aggregate_meters = []
@res_one_meters = Meter.find(:all, :conditions => ["resource_id = ?", 1])

@res_one_meters.each do |meter|
  read_time = meter.read_time
  value = meter.value
  if res_two_meter = Meter.find(:first, :conditions => ["resource_id = ? AND read_time = ?", 2, read_time ])
    value = value + res_two_meter.value
  end
  aggregate_meter = Meter.new(:read_time => read_time, :value => value, :resource_id => 3)
  @aggregate_meters.push(aggregate_meter)
end

Thank you.

谢谢你！

1 个解决方案

#1

ActiveRecord::Calculate is your friend here. Letting you do exactly what you want with one database call. It returns a hash using the unique values in the column used in the group as keys.

计算是你的朋友在这里。允许您对一个数据库调用执行您想要的操作。它使用组中用作键的列中的惟一值返回散列。

Here's the code you wrote, rewritten to use sum.

这是你写的代码，重写后使用sum。

values = Meter.sum(:value, :group => :read_time)

values.each do |read_time, value|
  aggregate_meter = Meter.new(:read_time => read_time, :value => value, :resource_id => 3)
  @aggregates_meter.push(aggregate_meter)
end

#1