CODEWITHSUNDEEP
ruby-on-railspostgresql
ironsand
ironsand

Reputation: 15181

How to get weighted average grouped by a column

I have a model Company that have columns pbr, market_cap and category.

To get averages of pbr grouped by category, I can use group method.

Company.group(:category).average(:pbr)

But there is no method for weighted average.

To get weighted averages I need to run this SQL code.

select case when sum(market_cap) = 0 then 0 else sum(pbr * market_cap) / sum(market_cap) end as weighted_average_pbr, category AS category FROM "companies" GROUP BY "companies"."category";

In psql this query works fine. But I don't know how to use from Rails.

sql = %q(select case when sum(market_cap) = 0 then 0 else sum(pbr * market_cap) / sum(market_cap) end as weighted_average_pbr, category AS category FROM "companies" GROUP BY "companies"."category";)
ActiveRecord::Base.connection.select_all(sql)

returns a error:

 output error: #<NoMethodError: undefined method `keys' for #<Array:0x007ff441efa618>>

It would be best if I can extend Rails method so that I can use

Company.group(:category).weighted_average(:pbr)

But I heard that extending rails query is a bit tweaky, now I just want to know how to run the result of sql from Rails.

Does anyone knows how to do it?

Version

rails: 4.2.1

Upvotes: 1

Views: 494

Answers (1)

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24581

What version of Rails are you using? I don't get that error with Rails 4.2. In Rails 3.2 select_all used to return an Array, and in 4.2 it returns an ActiveRecord::Result. But in either case, it is correct that there is no keys method. Instead you need to call keys on each element of the Array or Result. It sounds like the problem isn't from running the query, but from what you're doing afterward.

In any case, to get the more fluent approach you've described, you could do this:

class Company
  scope :weighted_average, lambda{|col|
    select("companies.category").
    select(<<-EOQ)
      (CASE WHEN SUM(market_cap) = 0 THEN 0
            ELSE SUM(#{col} * market_cap) / SUM(market_cap)
       END) AS weighted_average_#{col}
    EOQ
  }

This will let you say Company.group(:category).weighted_average(:pbr), and you will get a collection of Company instances. Each one will have an extra weighted_average_pbr attribute, so you can do this:

Company.group(:category).weighted_average(:pbr).each do |c|
  puts c.weighted_average_pbr
end

These instances will not have their normal attributes, but they will have category. That is because they do not represent individual Companies, but groups of companies with the same category. If you want to group by something else, you could parameterize the lambda to take the grouping column. In that case you might as well move the group call into the lambda too.

Now be warned that the parameter to weighted_average goes straight into your SQL query without escaping, since it is a column name. So make sure you don't pass user input to that method, or you'll have a SQL injection vulnerability. In fact I would probably put a guard inside the lambda, something like raise "NOPE" unless col =~ %r{\A[a-zA-Z0-9_]+\Z}.

The more general lesson is that you can use select to include extra SQL expressions, and have Rails magically treat those as attributes on the instances returned from the query.

Also note that unlike with select_all where you get a bunch of hashes, with this approach you get a bunch of Company instances. So again there is no keys method! :-)

Upvotes: 1

Related Questions