PeriodicCalculations

Periodic Calculations gem allows you to retrieve periodic results of aggregates that can be accumulated over time with PostgreSQL. The results are returned in real time (there are no scheduled precalculations).

The returned data is ready to be displayed in a graph, for example, using the jQuery Flot library.

Demo

Please check out the demo to see it in action.

Installation

Add this line to your application's Gemfile:

gem 'periodic_calculations'

Usage

The gem adds theses methods to active record instances: periodic_operation, periodic_count_all, periodic_sum, periodic_minium, periodic_max, periodic_average.

It will return an array composed of pairs [Time, result]. One pair for each period interval.

@data = Purchase
  .where("price > 0")     # custom scope
  .periodic_sum(
    :price,               # target column
    30.days.ago,          # start time
    Time.now,             # end time
    :cumulative => true   # options
  )

# Example result
# [
#   [#Time<"2013-11-11 00:00:00 -0800">, 200],
#   [#Time<"2013-11-12 00:00:00 -0800">, 200],
#   [#Time<"2013-11-13 00:00:00 -0800">, 500],
#   [#Time<"2013-11-14 00:00:00 -0800">, 800],
#   ...
#   [#Time<"2013-12-08 00:00:00 -0800">, 1100],
#   [#Time<"2013-12-09 00:00:00 -0800">, 1700],
#   [#Time<"2013-12-10 00:00:00 -0800">, 1700],
# ]

You can play with the different options and see the code produced in the demo page

How does it work

The gem takes advantage of the window_functions to be able to generate accumulated metrics over time.