So I am trying to set a day over day target based on a given growth rate for each given day in SQL. We know at the end of the week/month the target is to reach 1000.

What is the way to write a recursive(maybe) function to fill in the target values (x) given we know the end result.

Also would super appreciate it if you could walk me through how you got to the answer so I can learn. Thanks!

using postgress fyi

CodePudding user response：

You can use a logarithmic identity so that the growth factors can be easily summed (Properties of Logarithms):

with data as (
    select *,
        max("target") over () / exp(sum(ln(growth_rate))
            over (order by day desc)) as tgt
    from T
)
select day, growth_rate,
    coalesce(lead(tgt) over (order by day desc), target) as target
from data
order by day

Not sure which of these is the cleanest:

with data as (
    select day,
        max(target) over () as target,
        lead(growth_rate) over (order by day) as growth_rate
    from T
)
select day, growth_rate,
     target / exp(sum(ln(growth_rate)) over (order by day desc)) as target
from data
order by day

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=c9bbef6bb8192b5a8cff843ab6a009fc

CodePudding user response：

Your question is a typical use case of window functions. But no built-in window function exists for the multiplication or division, so you can either follow the answer of @shawnt00 using the sum() aggregate, or you can create your own aggregate based on the division operator :

CREATE OR REPLACE FUNCTION division (x anyelement, y anyelement, z anyelement)
RETURNS anyelement LANGUAGE sql AS $$ SELECT COALESCE(x,y)/z $$ ;

CREATE OR REPLACE AGGREGATE division_agg (x anyelement, z anyelement)
(sfunc = division, stype = anyelement) ;

Then you can use your new aggregate division_agg in a query :

SELECT day, growth_rate
     , COALESCE(division_agg(target, growth_rate) OVER (ORDER BY day DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), target)
  FROM test
 ORDER BY day

The division_agg window function divides the initial target value which corresponds to the highest day because of the ORDER BY day DESC clause successively by all the growth_rate values of the window associated to the current row, ie all the rows with a highest day, except for the row with the highest day for which the window is empty, so division_agg returns NULL which is replaced by the target value thanks to the COALESCE() function.

See the full demo in dbfiddle.