Use active_record_calculator to perform subgrouping of aggregate calculations
Example:
calculator = Purchases.calculator(:conditions => "created_at > '2011-07-01'", :group => "user_id") do |c|
c.count :id, "total"
c.cnt :id, "expensive", "price > 100" #cnt is an alias for count
c.sum :price, "cheap_spending", "price < 100"
c.avg :price, "cheap_average", "price < 100" #avg is an alias for average
c.min :price, "least"
c.max :price, "most" #max is an alias for maximum
c.col :item_name #col is an alias for column; adds a column to the result
end
calculator.calculate
OR
calculator = Purchases.calculator(:conditions => "created_at > '2011-07-01'", :group => "user_id")
calculator.count :id, "total"
calculator.cnt :id, "expensive", "price > 100"
calculator.sum :price, "cheap_spending", "price < 100"
calculator.avg :price, "cheap_average", "price < 100"
calculator.min :price, "least"
calculator.max :price, "most"
calculator.calculate
When adding operations, the calculator expects the format
method(column, alias, sub_group_condition)
The calculate method yields an array of hashes with the aliases as keys and results as values Group columns are automatically included
Example:
>> calc = Transaction.calculator(:conditions => “transactions.user_id = 55555”, :group => “transactions.user_id, bonus”) do |c| ?> c.count :id, “transactions_count” >> c.count :id, “approved_bonus_count”, “status = ‘approved’ and bonus = true” >> c.count :id, “approved_offers_count”, “status = ‘approved’ and bonus = false” >> end … >> calc.calculate
> [“group_column_1”=>“55555”, “group_column_2”=>“0”, “transactions_count”=>34, “approved_offers_count”=>0, “group_column_1”=>“55555”, “group_column_2”=>“1”, “transactions_count”=>18, “approved_offers_count”=>17]
You can use statement to see the sql created
The updater can be used for fast direct sql updates. An updater needs to be created where all the operations have aliases that have a respective column name in the update table. The first two arguments are the update table and the key to join. The key is joined with the first group column which is also required.
updater = Purchases.updater(:purchase_history, :user_id, :conditions => "created_at > '2011-07-01'", :group => "user_id")
updater.count :id, "total_purchases"
updater.cnt :id, "expensive_purchases", "price > 100"
updater.sum :price, "cheap_spending", "price < 100"
updater.avg :price, "cheap_average", "price < 100"
updater.min :price, "least_purchase"
updater.max :price, "most_purchase"
Update should work with all ActiveRecord supported databases except sqlite