Have a requirement where I would need to rope the calculated value of the previous row for calculation in the current row.

The following is a sample of how the data currently looks :-

The following is the output What I am expecting :-

The value of CalVal for the first row is Date Days From the second row onwards it should take the CalVal value of the previous row and add it with the current row Days

Is there anyway we can achieve the above via Postgres SQL? I have been tinkering with window functions and even recursive CTEs but have had no luck :(

Would appreciate any direction!

Thanks in advance!

CodePudding user response：

select 
    id,
    date,
    coalesce(
        days - (lag(days, 1) over (order by date, days))
        , days) as days,
     first_date   cast(days as integer) as newdate
from
    (
        select 
            -- get a running sum of days
            id,
            first_date,
            date,
            sum(days) over (order by date, days) as days
        from
        (
            select 
                -- get the first date
                id,
                (select min(date) from table1) as first_date, 
                date,
                days
            from 
                table1
        ) A
    ) B

This query get the exact output you described. I'm not at all ready to say it is the best solution but the strategy employed is to essential create a running total of the "days" ... this means that we can just add this running total to the first date and that will always be the next date in the desired sequence. One finesse: to put the "days" back into the result, we calculated the current running total less the previous running total to arrive at the original amount.

CodePudding user response：

assuming that table name is table1

select 
  id, 
  date, 
  days, 
  first_value(date) over (order by id)   
   (sum(days) over (order by id rows between unbounded preceding and current row))
   *interval '1 day' calval
 from table1;

We just add cumulative sum of days to first date in table. It's not really what you want to do (we don't need date from previous row, just cumulative days sum)