cumulative product over a big MySQL table

Question

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.

Have you tried something yet? A solution to this might require heavy use of session variables, and it might be a big job. — Tim Biegeleisen, May 16 '17 at 14:24
Do you already have an `order` column, or would that need to be computed as well? — Tim Biegeleisen, May 16 '17 at 14:25
Thanks for your help guys, but Gordon's answer is just perfect. — Samuel, May 16 '17 at 14:55

score 1 · Accepted Answer · answered May 16 '17 at 14:26

1

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`;

answered May 16 '17 at 14:26

Gordon Linoff

1,242,037
58
646
786

cumulative product over a big MySQL table

1 Answers1