Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save romansklenar/7943316 to your computer and use it in GitHub Desktop.
Save romansklenar/7943316 to your computer and use it in GitHub Desktop.
Using PostgreSQL's materialized views as background for ActiveRecord models for flexible statistics
# 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
-- 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
)

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
# 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
# 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
# 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
# app/models/user.rb
class User < ActiveRecord::Base
# ...
has_many :transactional_items
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment