ArAggregateByInterval
ActiveRecord time series
For MySQL or Postgres.
Build arrays of counts, sums and averages from Ruby on Rails ActiveRecord models grouped by days, weeks or months. e.g.:
# (group_by_col, from, to = Time.now)
Blog.count_weekly(:created_at, 1.month.ago).values
=> [4, 2, 2, 0]
# (group_by_col, aggregate_col, from, to = Time.now)
Blog.sum_weekly(:created_at, :pageviews, 1.month.ago).values
=> [400, 350, 375, 250]
# (group_by_col, aggregate_col, from, to = Time.now)
Blog.avg_weekly(:created_at, :pageviews, 1.month.ago).values
=> [25, 20, 40, 10]
Why?
- to simplify "group by" SQL queries when weeks or months are involved
- to fill in 0's for days/weeks/months where database has no data
Usage
Blog.{count,sum,avg}_{daily,weekly,monthly}(hash_or_arg_list).{values,values_and_dates}`
1. "method_missing" methods on ActiveRecord
{count,sum,avg}_{daily,weekly,monthly}
2. pass hash or argument list
- pass a Hash
{:group_by_column, :from, :to, :aggregate_column, :normalize_dates}
- or pass arguments
- when using count:
[group_by_col, from, to, options_hash]
- when using sum or avg:
[group_by_col, aggregate_col, from, to, options_hash]
- when using count:
3. methods you can call: #values
or #values_and_dates
#values //(returns an array of numerics)
=> [4, 2, 15, 0, 10]
#values_and_dates //(returns an array of hashes)
=> [{date: DateObject, value: 0}, {date: DateObject, value: 5}]
Examples
Total blog posts created weekly
Blog.count_weekly({
group_by_column: :created_at,
from: 6.months.ago,
to: Time.zone.now
}).values
Total calories burned per week
Exercise.sum_weekly({
group_by_column: :created_at,
aggregate_column: :calories,
from: 6.months.ago,
to: Time.zone.now
}).values
Weekly revenue since beginning of year (with dates)
Billing.sum_weekly({
group_by_column: :transacted_at,
aggregate_column: :cents, # necessary when using sum as opposed to count
from: Time.zone.now.beginning_of_year,
to: Time.zone.now
}).values_and_dates
Options
normalize_dates
defaults toTrue
which means thefrom
argument is converted to beginning_of_day,week,month and theto
argument is converted to end_of_day,week,month