How To Get Aggregate Data By Time Slice (sum, Avg, Min, Max, Etc.) In Rails 3

How can I create active relation queries in Rails 3 that are aggregated by time slice? I'd like to build queries that for every n minute interval can return min, max, avg, sum, c

Solution 1:

Unfortunately I've never used Postgres, so this solution works in MySQL. But I think you can find out Postgres analogs.

classCounter < ActiveRecord::Base
  has_many :samplesdo# default 30 minutesdefper_time_slice(slice = 30)
      start = "2000-01-01 00:00:00"self.select("*, 
                   CONCAT( FLOOR(TIMESTAMPDIFF(MINUTE,'#{start}',created_at)/#{slice})*#{slice},  
                     (FLOOR(TIMESTAMPDIFF(MINUTE,'#{start}',created_at)/#{slice})+1)*#{slice} ) as slice,
                   avg(value) as avg_value, 
                   min(value) as min_value, 
                   max(value) as max_value, 
                   sum(value) as sum_value, 
                   count(value) as count_value").
                   group("slice").order("slice")
    endendend

Usage

counter = find_some_counter
samples = counter.samples.per_time_slice(60).where(:name => "Bobby")
samples.map(&:avg_value)samples.map(&:min_value)samples.map(&:max_value)

etc

Solution 2:

This should do the trick:

Samples.where("SQL for the time slice goes here").where(:name, "Views").average(:value)

You can also swap average for any of minimum, maximum and sum.