正在获取ActiveRecord中每个组的最小值/最大值

Question

这是一个古老的问题，在给定一个具有属性"type"、"variety"one_answers"price"的表时，您会以每种类型的最低价格获取记录。

在SQL中，我们可以通过以下方式做到这一点：

select f.type, f.variety, f.price   
from (  select type, min(price) as minprice from table group by type ) as x  
inner join table as f on f.type = x.type and f.price = x.minprice;`

我们也许可以模仿一下：

minprices = Table.minimum(:price, :group => type)  
result = []
minprices.each_pair do |t, p|  
   result << Table.find(:first, :conditions => ["type = ? and price = ?", t, p])
end

还有比这更好的实现吗？

Answer 1

Table.minimum(:price, :group => :type)

请参阅http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-minimum更多。

Answer 2

这对我有效。

Table.group(:type).minimum(:price)

它返回一个像这样的对象。

{
 "type1"=>500.0,
 "type2"=>200.0
}

Answer 3

您可以使用#find_by_sql，但这意味着返回一个模型对象，这可能不是您想要的。

如果你想裸露在金属上，你也可以使用#select_values:

data = ActiveRecord::Base.connection.select_values("
        SELECT f.type, f.variety, f.price
        FROM (SELECT type, MIN(price) AS minprice FROM table GROUP BY type ) AS x
        INNER JOIN table AS f ON f.type = x.type AND f.price = x.minprice")
puts data.inspect
[["type", "variety", 0.00]]

ActiveRecord只是一个工具。你在方便的时候使用它。当SQL做得更好时，您可以使用它。

Answer 4

我已经和这个问题斗争了一段时间，目前看来您在生成SQL方面几乎陷入了困境。

不过，我还有一些改进之处。

与@François建议的find_by_sql不同，我使用了ActiveRecord的to_sql和joins来"指导"我的SQL：

subquery_sql = Table.select(["MIN(price) as price", :type]).group(:type).to_sql
joins_sql    = "INNER JOIN (#{subquery_sql}) as S
                ON table.type = S.type
                AND table.price = S.price"
Table.joins(joins_sql).where(<other conditions>).order(<your order>)

正如你所看到的，我仍然在使用原始SQL，但至少它只是AR不支持的部分（AFAIK-ActiveRecord根本无法管理INNER JOIN ... ON ...），而不是全部。

使用joins而不是find_by_sql使查询具有可链接性——您可以添加额外的条件，或者对表进行排序，或者将所有内容都放在一个范围中。

Answer 5

虽然这个问题很老套，但我今天也问了同样的问题。以下是一个解决方案的要点，该解决方案用于编写用最少（2）个查询实现目标所需的SQL。

请看看这些天有没有更好的方法！

使用Security和Price模型，其中证券有许多（历史）价格，并且您正在寻找证券的最新价格：

module MostRecentBy
  def self.included(klass)
    klass.scope :most_recent_by, ->(group_by_col, max_by_col) {
      from(
        <<~SQL
          (
            SELECT #{table_name}.*
            FROM #{table_name} JOIN (
               SELECT #{group_by_col}, MAX(#{max_by_col}) AS #{max_by_col}
               FROM #{table_name}
               GROUP BY #{group_by_col}
            ) latest
            ON #{table_name}.date = latest.#{max_by_col}
            AND #{table_name}.#{group_by_col} = latest.#{group_by_col}
          ) #{table_name}
        SQL
      )
    }
  end
end
class Price < ActiveRecord::Base
  include MostRecentBy
  belongs_to :security
  scope :most_recent_by_security, -> { most_recent_by(:security_id, :date) }
end
class Security < ActiveRecord::Base
  has_many :prices
  has_one :latest_price, 
    -> { Price.most_recent_by_security },
    class_name: 'Price'
end

现在您可以在控制器代码中调用以下内容：

def index
  @resources = Security.all.includes(:latest_price)
  render json: @resources.as_json(include: :latest_price)
end

这导致两个查询：

  Security Load (4.4ms)  SELECT "securities".* FROM "securities"
  Price Load (140.3ms)  SELECT "prices".* FROM (
    SELECT prices.*
    FROM prices JOIN (
       SELECT security_id, MAX(date) AS date
       FROM prices
       GROUP BY security_id
    ) latest
    ON prices.date = latest.date
    AND prices.security_id = latest.security_id
  ) prices
  WHERE "prices"."price_type" = $1 AND "prices"."security_id" IN (...)

供参考：https://gist.github.com/pmn4/eb58b036cc78fb41a36c56bcd6189d68

Answer 6

要更新Avdi上面的答案：

表.最低（：价格，：组=>：类型）

这是更新后的URL:

http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i最小