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.

Are you looking for a pure sql/activerecord solution? Or are you OK with a ruby one? — thesecretmaster, May 02 '18 at 10:43
@thesecretmaster I prefer SQL, but a ruby solution is helpful too. Thank you. — Yassine, May 02 '18 at 10:44

score 4 · Accepted Answer · answered May 02 '18 at 11:11

If used postgresql you can use windows function

UNBOUNDED PRECEDING first row,CURRENT ROW current rows
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

score 1 · Answer 2 · answered May 02 '18 at 11:03

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

score 1 · Answer 3 · answered May 02 '18 at 14:07

1

We can use the following code for that

 quotes = current_user.quotes
 sum = 0
 quotes.each_with_object({}) do |quote,hassh|
     hassh[quote.name] = sum = quote.price + sum
 end
 hassh

answered May 02 '18 at 14:07

Naveen Kumar

37
4

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

3 Answers3