CODEWITHSUNDEEP
mysql
Samuel
Samuel

Reputation: 686

cumulative product over a big MySQL table

I have a big MySQL table on which I'd like to calculate a cumulative product. This product has to be calculated for each group, a group is defined by the value of the first column.

For example :

name | number | cumul | order
-----------------------------
a    | 1      | 1     | 1
a    | 2      | 2     | 2
a    | 1      | 2     | 3
a    | 4      | 8     | 4
b    | 1      | 1     | 1
b    | 1      | 1     | 2
b    | 2      | 2     | 3
b    | 1      | 2     | 4

I've seen this solution but don't think it would be efficient to join or subselect in my case.

I've seen this solution which is what I want except it does not partition by name.

Upvotes: 1

Views: 140

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This is similar to a cumulative sum:

select t.*,
       (@p := if(@n = name, @p * number,
                 if(@n := name, number, number)
                )
       ) as cumul
from t cross join
     (select @n := '', @p := 1) params
order by name, `order`;

Upvotes: 1

Related Questions