Rails: Group and sum while adding last values (Fibonacci)

Question

I'm trying to group quotes sum per month, while adding last sums.

For example:

Jan: 300€
Fev: 200€
Mars: 100€

What the group should return is:

Jan:300€
Fev: 500€ (200 + Jan's 300)
Mars: 600€ (100 + Fev's 500)

Current SQL:

current_user.quotes.group_by_month(:created_at, last: 12).sum(:price)

I'm using the groupdate gem.

Thanks.

Answer 1

If used postgresql you can use windows function

1. UNBOUNDED PRECEDING first row,CURRENT ROW current rows
2. use sum function to Accumulate

like this.

select name,sum(price) 
         over(order by (select 1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  "price"
from T

sqlfidde:http://sqlfiddle.com/#!17/763e3/7

Widows function

Answer 2

Solution in Ruby:

s = 0
current_user.quotes.group_by_month(:created_at, last: 12).
  sum(:price).
  transform_values.map { |v| s += v }

as there is only a hash of 12 elements, i suggest the performance penalty of using ruby instead of sql are marginal. Using SQL would be much harder and not possible only with simple ActiveRecord Arel methods or probably database specific, see e.g. Calculating Cumulative Sum in PostgreSQL