SQL Multiply values from all previous rows

Question

Refer to the image above.

I have Table 1, and I want to produce Table 2 with SQL.

The first year has to be set to the value 10. The values following will multiply 10 by the multiplier for that year and previous years in Table 1.

For example:

• For 2002, the value will be 10 * 2 (2002 multiplier) * 1 (2001 multiplier) = 20.
• For 2005, the value will be 10 * 5 * 3 * 1 * 2 * 1 (all previous year multipliers) = 300.

How would I go about doing this? I'd appreciate any help.

Answer 1

A colleague of mine long ago taught me a trick to solve this kind of problems using logarithm properties.

Basically you can do:

 Exp(sum(ln(multiplier)))

Edited after the OP made me realize it was incomplete

To do the cumulative logic you need you should apply this on a self-join

 select a.youryear, Exp(sum(ln(b.multiplier))) cumulative_mutiplier
   from yourtable as a 
        join
        yourtable as b on a.youryear>=b.youryear
  group by a.youryear;

I've prepared a test on rextester

 create table yourtable (
      youryear integer,
      multiplier integer
  );

  insert into yourtable(youryear,multiplier) values (2000,10);
  insert into yourtable(youryear,multiplier) values (2001,1);
  insert into yourtable(youryear,multiplier) values (2002,2);
  insert into yourtable(youryear,multiplier) values (2003,1);
  insert into yourtable(youryear,multiplier) values (2004,3);
  insert into yourtable(youryear,multiplier) values (2005,5);

   select a.youryear, Exp(sum(ln(b.multiplier))) cumulative_mutiplier
     from yourtable as a 
         join
         yourtable as b on a.youryear>=b.youryear
   group by a.youryear; 

The result is:

    youryear    cumulative_mutiplier
 1  2000    10
 2  2001    10
 3  2002    20
 4  2003    20
 5  2004    60
 6  2005    300

Answer 2

select      year
           ,x
           ,@result := @result*coalesce(x,10) as result

from        table1,(select @result:=1) i

order by    year
;

---

# year, x, result
2000, , 10
2001, 1, 10
2002, 2, 20
2003, 1, 20
2004, 3, 60
2005, 5, 300