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 ...)
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.