Using PostgreSQL's materialized views as background for ActiveRecord models for flexible statistics

20131118172653_create_transactional_items_view.rb

# db/migrate/20131118172653_create_transactional_items_view.rb
class CreateTransactionalItemsView < ActiveRecord::Migration
  def up
    select_sql = File.open("#{Rails.root}/db/migrate/20131118172653_create_transactional_items_view.sql", 'r') { |f| f.read } 
    
    # for materialized view:
    view_sql = "CREATE MATERIALIZED VIEW transactional_items AS (#{select_sql})"
    # for normal view:
    view_sql = "CREATE VIEW transactional_items AS (#{select_sql})"
    
    execute view_sql.gsub(/\s+/, " ").strip
  end


  def down
    execute "DROP VIEW transactional_items"
  end
end

20131118172653_create_transactional_items_view.sql

-- db/migrate/20131118172653_create_transactional_items_view.sql
-- do some "crazy" stuff in your view - in this case use union to join tables
(
  SELECT NULL::integer AS id,
    'Transaction' AS source_type,
    transactions.id AS source_id,
    accounts.user_id,
    transactions.date,
    transactions.amount
  FROM transactions
    INNER JOIN accounts ON accounts.id = transactions.account_id
) UNION (
  SELECT NULL::integer AS id,
    'RecurringTransaction' AS source_type,
    recurring_transactions.id AS source_id,
    accounts.user_id,
    recurring_transactions.date,
    recurring_transactions.amount
  FROM recurring_transactions
    INNER JOIN accounts ON accounts.id = recurring_transactions.account_id
) UNION (
  SELECT NULL::integer AS id,
    'Budget' AS source_type,
    budgets.id AS source_id,
    budgets.user_id,
    budgets.start_date AS date,
    budgets.amount
  FROM budgets
)

README.md

Usage

>> TransactionalItem.refresh! # manually refresh (recalculate) materialized view
>> user = User.first
>> user.transactional_items.positive.between(Date.today.at_beginning_of_month, Date.today.at_end_of_month) # your incomes for this month
+----+-------------+-----------+---------+------------+---------+
| id | source_type | source_id | user_id | date       | amount  |
+----+-------------+-----------+---------+------------+---------+
|    | Transaction | 2831      | 1       | 2013-12-01 | 10000.0 |
|    | Transaction | 2920      | 1       | 2013-12-10 | 800.0   |
|    | Transaction | 2844      | 1       | 2013-12-02 | 30000.0 |
+----+-------------+-----------+---------+------------+---------+
3 rows in set

>> user.transactional_items.negative.future # your planned outcomes
+----+----------------------+-----------+---------+------------+---------+
| id | source_type          | source_id | user_id | date       | amount  |
+----+----------------------+-----------+---------+------------+---------+
|    | RecurringTransaction | 983       | 1       | 2014-10-08 | -100.0  |
|    | RecurringTransaction | 951       | 1       | 2014-02-08 | -1750.0 |
|    | RecurringTransaction | 967       | 1       | 2014-06-08 | -1890.0 |
|    | RecurringTransaction | 955       | 1       | 2014-06-08 | -1750.0 |
|    | RecurringTransaction | 968       | 1       | 2014-07-08 | -1890.0 |
...

>> user.transactional_items.past.negative.stats # total stats for your outcomes
+----+------------+-------+
| id | sum        | count |
+----+------------+-------+
|    | -263554.37 | 126   |
+----+------------+-------+
1 row in set

>> user.transactional_items.past.negative.monthly_stats # total stats for your outcomes by month
+----+-----------+-------+------+-------+
| id | sum       | count | year | month |
+----+-----------+-------+------+-------+
|    | -15000.0  | 2     | 2013 | 1     |
|    | -5000.0   | 2     | 2013 | 2     |
|    | -4350.0   | 1     | 2013 | 3     |
|    | -4000.0   | 1     | 2013 | 4     |
|    | -14000.0  | 2     | 2013 | 5     |
|    | -17991.0  | 6     | 2013 | 6     |
|    | -15524.0  | 6     | 2013 | 7     |
|    | -22922.0  | 10    | 2013 | 8     |
|    | -73756.4  | 13    | 2013 | 9     |
|    | -36031.67 | 35    | 2013 | 10    |
|    | -23898.1  | 27    | 2013 | 11    |
|    | -31081.2  | 21    | 2013 | 12    |
+----+-----------+-------+------+-------+
12 rows in set

>> user.transactional_items.past.negative.monthly_stats.advanced_stats # advanced stats for your outcomes by month
+----+-----------+-------+------+-------+------------------------+-------------------+---------+----------+----------+---------+
| id | sum       | count | year | month | avg                    | stddev            | median  | mode     | min      | max     |
+----+-----------+-------+------+-------+------------------------+-------------------+---------+----------+----------+---------+
|    | -15000.0  | 2     | 2013 | 1     | -7500.0                | 3535.533905932738 | -7500.0 | -10000.0 | -10000.0 | -5000.0 |
|    | -5000.0   | 2     | 2013 | 2     | -2500.0                | 2121.320343559643 | -2500.0 | -4000.0  | -4000.0  | -1000.0 |
|    | -4350.0   | 1     | 2013 | 3     | -4350.0                |                   | -4350.0 | -4350.0  | -4350.0  | -4350.0 |
|    | -4000.0   | 1     | 2013 | 4     | -4000.0                |                   | -4000.0 | -4000.0  | -4000.0  | -4000.0 |
|    | -14000.0  | 2     | 2013 | 5     | -7000.0                | 5656.85424949238  | -7000.0 | -11000.0 | -11000.0 | -3000.0 |
|    | -17991.0  | 6     | 2013 | 6     | -2998.5                | 1810.708010696368 | -3687.5 | -4000.0  | -5000.0  | -241.0  |
|    | -15524.0  | 6     | 2013 | 7     | -2587.3333333333333333 | 2145.437173786887 | -2187.5 | -5000.0  | -5000.0  | -320.0  |
|    | -22922.0  | 10    | 2013 | 8     | -2292.2                | 3084.230096618748 | -848.5  | -10000.0 | -10000.0 | -100.0  |
|    | -73756.4  | 13    | 2013 | 9     | -5673.5692307692307692 | 8593.169534572659 | -1500.0 | -23990.0 | -23990.0 | -100.0  |
|    | -36031.67 | 35    | 2013 | 10    | -1029.4762857142857143 | 1853.16848866711  | -220.0  | -150.0   | -10000.0 | -37.2   |
|    | -23898.1  | 27    | 2013 | 11    | -885.1148148148148148  | 1096.090939764406 | -339.0  | -150.0   | -4000.0  | -89.0   |
|    | -31081.2  | 21    | 2013 | 12    | -1480.0571428571428571 | 2190.109871346967 | -832.0  | -2000.0  | -10000.0 | -100.0  |
+----+-----------+-------+------+-------+------------------------+-------------------+---------+----------+----------+---------+
12 rows in set

read_only_model.rb

# app/models/mixins/read_only_model.rb
module ReadOnlyModel
  def readonly?
    true
  end
 
  def self.delete_all
    raise ActiveRecord::ReadOnlyRecord
  end
 
  def delete
    raise ActiveRecord::ReadOnlyRecord
  end
 
  def self.destroy_all
    raise ActiveRecord::ReadOnlyRecord
  end
 
  def destroy
    raise ActiveRecord::ReadOnlyRecord
  end
end

scopes.rb

# app/models/transaction/scopes.rb
module Transaction::Scopes
  extend ActiveSupport::Concern

  included do
    scope :zero,        -> { where("#{table_name}.amount = 0.0") }
    scope :positive,    -> { where("#{table_name}.amount > 0.0") }
    scope :negative,    -> { where("#{table_name}.amount < 0.0") }
    scope :non_zero,    -> { where("#{table_name}.amount <> 0.0") }
    scope :significant, ->(x=25) { where("(#{table_name}.amount <= -:x) ^ (#{table_name}.amount >= :x)", x: x) }

    scope :future,  ->(date = Date.today) { where("#{table_name}.date >= ?", date) }
    scope :past,    ->(date = Date.today) { where("#{table_name}.date < ?", date) }
    scope :between, ->(from, to) { where(date: from..to) }

    scope :with_extracted_year,      -> { select("EXTRACT(YEAR FROM date) AS year") }
    scope :with_extracted_month,     -> { select("EXTRACT(MONTH FROM date) AS month") }
    scope :with_extracted_day,       -> { select("EXTRACT(DAY FROM date) AS day") }
    scope :with_extracted_week,      -> { select("EXTRACT(WEEK FROM date) AS week") }
    scope :with_extracted_quarter,   -> { select("EXTRACT(QUARTER FROM date) AS quarter") }
    scope :with_extracted_semester,  -> { select("CASE WHEN EXTRACT(MONTH FROM date) > 6 THEN 2 ELSE 1 END AS semester") }
    scope :with_extracted_date,      -> { with_extracted_year.with_extracted_month.with_extracted_day }

    scope :stats,            -> { select("SUM(amount) AS sum, COUNT(amount) AS count") }
    scope :advanced_stats,   -> { select("AVG(amount) AS avg, STDDEV(amount) AS stddev, MEDIAN(amount) AS median, MODE(amount) AS mode, MIN(amount) AS min, MAX(amount) AS max") }
    scope :annual_stats,     -> { stats.with_extracted_year.group("year").order("year") }
    scope :monthly_stats,    -> { stats.with_extracted_year.with_extracted_month.group("year, month").order("year, month") }
    scope :daily_stats,      -> { stats.with_extracted_date.group("year, month, day").order("year, month, day") }
    scope :weekly_stats,     -> { stats.with_extracted_year.with_extracted_week.group("year, week").order("year, week") }
    scope :quarterly_stats,  -> { stats.with_extracted_year.with_extracted_quarter.group("year, quarter").order("year, quarter") }
    scope :semester_stats,   -> { stats.with_extracted_year.with_extracted_semester.group("year, semester").order("year, semester") }
  end

end

transactional_item.rb

# app/models/transactional_item.rb
class TransactionalItem < ActiveRecord::Base
  self.table_name = "transactional_items"
  self.primary_key = :id

  belongs_to :source, polymorphic: true
  belongs_to :user

  # NOTE: scopes are seperated just for clarification
  # in this example there's no need to seperate it but in my app 
  # scopes are used by several models (Transaction, Budget, ...)
  include Transaction::Scopes
  include ReadOnlyModel

  # in MySQL you'd need to use different command
  def self.refresh!
    connection.execute("REFRESH MATERIALIZED VIEW #{table_name} WITH DATA")
  end
end

user.rb

# app/models/user.rb
class User < ActiveRecord::Base
  # ...
  
  has_many :transactional_items
end