0Gnb15y4

1drop table if exists tradesAndActions;
2create table if not exists tradesAndActions (transik int, ca_date date, terms float, multiply bit, originalPrice float, originalShares float);
3insert into tradesAndActions values
4  (1, '2010-01-01', 10, 0, 50.0, 1000.0),
5  (1, '2010-02-01', 2, 1, null, null),
6  (2, '2010-02-01', 2, 1, 10.0, 50.0),
7  (2, '2010-02-01', 10, 0, null, null)
8
9;
10
11
12set @currentTransik := 0;
13set @adjustedPrice := 0.0;
14set @adjustedShares := 0.0;
15
16select *,
17  @adjustedPrice := 
18      if(@currentTransik = a.transik,
19        if(a.multiply = 1, @adjustedPrice * a.terms, @adjustedPrice + a.terms),
20        if(a.multiply = 1, a.originalPrice * a.terms, a.originalPrice + a.terms)
21      ) adjustedPrice,
22  @adjustedShares :=
23      if(@currentTransik = a.transik,
24        if(a.multiply = 1, @adjustedShares / a.terms, @adjustedShares),
25        if(a.multiply = 1, a.originalShares / a.terms, a.originalShares)
26      ) adjustedShares,
27  @currentTransik := a.TRANSIK currentTransik
28from 
29  (
30    select *
31    from tradesAndActions b
32    order by b.transik, b.ca_date, b.multiply desc
33  ) a
34;
35	
36sum(x) over (partition by y ...)
37	
38exp(sum(log(x)) over (partition by y ...)