CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Roberto Pezzali
Roberto Pezzali

Reputation: 2504

Activerecord, calculate average percentage for multiple value of grouped value

I'm try to save some precious millisecond in a query.

I have a stats table and I'm calculating the average value of some records grouped by a key

Activity.group(:company_id).average(:completed_courses)

Everything is ok, but I need also the average on other columns of the same table (:readed_news, :logins, etc)

Is there a way to get all the averages with a single query?

I'm using Postgres

Upvotes: 0

Views: 1235

Answers (3)

mahatmanich
mahatmanich

Reputation: 11033

Use scopes or class methods on you model:

class Activity < ActiveRecord::Base
    self.average_company
      group(:company_id).average(:completed_courses)
    end

    self.average_readed_news
      group(:readed_news).average(:completed_courses)
    end
end

then call:

Activity.average_company
Actvitity.average_readed_news

You could then try to merge scopes

Activity.average_company.merge(Activity.average_readed_news)

Look at the documentation here: http://guides.rubyonrails.org/active_record_querying.html

Upvotes: 0

Daniil Maksimov
Daniil Maksimov

Reputation: 139

You can write select.

Activity.group(:company_id)
        .select('AVG(completed_courses) as avg_completed_courses, 
                 AVG(readed_news) as avg_readed_news, 
                 AVG(logins) as avg_logins')

Also, you can write method for generating select expression:

def select_exp(attrs)
  attrs.sum { |attr| ("AVG(#{attr}) as avg_#{attr},") }.chop
end 

Activity.group(:company_id)
        .select select_exp(%w(completed_courses readed_news logins))

Upvotes: 2

Hardik Upadhyay
Hardik Upadhyay

Reputation: 2659

You can use single field on average method but you can do this

activities = Activity.group(:company_id)

completed_courses = activities.average(:completed_courses)

readed_news = activities.average(:readed_news)
etc..

Hope this will help you.

Upvotes: 0

Related Questions