Is there a way to rewrite this MySQL query to use window functions, and not use session variables?

Question

I have the following query

drop table if exists tradesAndActions;
create table tradesAndActions (transik int, ca_date date, terms float, multiply bit, originalPrice float, originalShares float);
insert into tradesAndActions values
  (1, '2010-01-01', 10, 0, 50.0, 1000.0),
  (1, '2010-02-01', 2, 1, null, null),
  (2, '2010-02-01', 2, 1, 10.0, 50.0),
  (2, '2010-02-01', 10, 0, null, null)

;


set @currentTransik := 0;
set @adjustedPrice := 0.0;
set @adjustedShares := 0.0;

select *,
  @adjustedPrice := 
      if(@currentTransik = a.transik,
        if(a.multiply = 1, @adjustedPrice * a.terms, @adjustedPrice + a.terms),
        if(a.multiply = 1, a.originalPrice * a.terms, a.originalPrice + a.terms)
      ) adjustedPrice,
  @adjustedShares :=
      if(@currentTransik = a.transik,
        if(a.multiply = 1, @adjustedShares / a.terms, @adjustedShares),
        if(a.multiply = 1, a.originalShares / a.terms, a.originalShares)
      ) adjustedShares,
  @currentTransik := a.TRANSIK currentTransik
from 
  (
    select *
    from tradesAndActions b
    order by b.transik, b.ca_date, b.multiply desc
  ) a
;

By way of background, this is about taking a bunch of trades, and adjusting the prices to account for corporate actions, to answer the question: "what would the trade price have been if there was no corporate action". I am pretty sure the query returns the desired result. However, I am wondering if it can be rewritten to not use any session variables, by way of window functions.

Going a bit deeper: some corporate action adjustments require you to add the term to the price, others require you to multiply the term and the price. The effect is cumulative for successive corporate actions. I have arranged for there to be a flag indicating whether a given adjustment term is a multiply type, or an add type.

The difficulty in using window functions here is that you only have things like

sum(x) over (partition by y ...)

or

exp(sum(log(x)) over (partition by y ...)

There is no way, as far as I know, to specify some cumulation function that does different things depending on whether or not you should multiply or add.

But perhaps there is some sort of multi stage approach that does the same thing? Note: this is not a practical question in that I need some help to get a job done; I already have a working query. This is more a case of me exploring the limits of window functions. I am wondering if a window function would be more elegant. For some reason I have some unexplained preference for window functions over iterating session variables.

Answer 1

Ok, I solved it using danblack's suggestion of a recursive CTE.

with recursive rankedTradesAndActions AS
(
  select t.*, ROW_NUMBER() over (PARTITION by t.transik order by t.ca_date, t.multiply desc) rowNumber
  from tradesAndActions t
), adjustedForCAs(transik, adjustedPrice, adjustedShares, rowNumber) as
(
  select t.transik
    , if(t.multiply = 1, t.originalPrice * t.terms, t.originalPrice + t.terms) adjustedPrice
    , if(t.multiply = 1, t.originalShares / t.terms, t.originalShares) adjustedShares
    , 1
  from rankedTradesAndActions t
  where t.rowNumber = 1
  UNION ALL
  select t.transik
    , if(t.multiply = 1, a.adjustedPrice * t.terms, a.adjustedPrice + t.terms) adjustedPrice
    , if(t.multiply = 1, a.adjustedShares / t.terms, a.adjustedShares) adjustedShares
    , a.rowNumber + 1
  from rankedTradesAndActions t
    inner join adjustedForCAs a on t.transik = a.transik and t.rowNumber = a.rowNumber + 1
  where t.rowNumber > 1
)
select * from adjustedForCAs a order by a.transik, a.rowNumber
;

No session variables used, so mission accomplished as far as I am concerned.

If anyone can come up with another interesting (possibly simpler) way to solve this, please post it. For now I consider this to be the answer, but if someone can come up with a simpler way to do it, I will consider that to be the answer.