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
– thesecretmaster, Commented May 2, 2018 at 10:43
@thesecretmaster I prefer SQL, but a ruby solution is helpful too. Thank you. — Yassine
– Yassine, Commented May 2, 2018 at 10:44

D-Shih · Accepted Answer · 2018-05-02 11:11:21Z

4

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

answered May 2, 2018 at 11:11

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

stwienert · Accepted Answer · 2018-05-02 11:03:10Z

1

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

answered May 2, 2018 at 11:03

stwienert

3,6621 gold badge29 silver badges32 bronze badges

Comments

Naveen Kumar · Accepted Answer · 2018-05-02 14:07:56Z

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 2, 2018 at 14:07

Naveen Kumar

374 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related