I have seen a number of posts on this topic but I couldn't find any that are exactly the same.

I would like to make a view with a column that refers to itself. In the example below:

• Value 1 2 are static values that continue throughout the view.
• Value 3 has data that updates so I want it to do Value 3 (Value 1 - Value 2) when there is data in Value 3.
• When Value 3 has no data, use it's prior value to calculate.

Is this possible? So far I have got it to calculate the row where index = 2 using LAG or Recursive CTE's but can't seem to work out how to get it to continue calculating.

CodePudding user response：

With slightly extended example data to show a new value3 taking precedent:

WITH data(Index, Value1, Value2, Value3) AS (
    SELECT * FROM VALUES   
    (1, 0.5, 0.25,  1),
    (2, 0.5, 0.25,  null),
    (3, 0.5, 0.25,  null),
    (4, 0.5, 0.25,  10),
    (5, 0.5, 0.25,  null),
    (6, 0.5, 0.25,  null),
    (7, 0.5, 0.25,  null)
)
select d.*
    ,iff(d.value3 is not null, d.index, null) as idx_k
    ,lag(d.value3) ignore nulls over(order by d.index) as l_value3
    ,lag(idx_k) ignore nulls over(order by d.index) as l_idx_k
    ,d.index - iff(idx_k is not null, idx_k, l_idx_k) 1 as r_d
    ,iff(d.value3 is not null, d.value3, l_value3)   r_d*(d.value1-d.value2) as calc
from data as d
order by 1;

gives:

which can be smashed together into:

select d.*
    ,iff(d.value3 is not null, d.value3, lag(d.value3) ignore nulls over(order by d.index))   (d.index - iff(d.value3 is not null, iff(d.value3 is not null, d.index, null), lag(iff(d.value3 is not null, d.index, null)) ignore nulls over(order by d.index)) 1)*(d.value1-d.value2) as calc
from data as d

if you really want, but I would be more inclined to wrap that in a sub-select just to make it present nicer:

select index, value1, value2, value3, calc 
from (
    select d.*
        ,iff(d.value3 is not null, d.index, null) as idx_k
        ,lag(d.value3) ignore nulls over(order by d.index) as l_value3
        ,lag(idx_k) ignore nulls over(order by d.index) as l_idx_k
        ,d.index - iff(idx_k is not null, idx_k, l_idx_k) 1 as r_d
        ,iff(d.value3 is not null, d.value3, l_value3)   r_d*(d.value1-d.value2) as calc
    from data as d
)
order by 1

Recursive CTE:

WITH recursive r_cte as (
    select index, value1, value2, value3, value3 as calc
    from data 
    where index = 1
    
    union all
    
    select d.index, d.value1, d.value2, d.value3, iff(d.value3 is null, r.calc, d.value3)   d.value1 - d.value2 as calc
    from r_cte as r
    join data d on r.index   1 = d.index
)
select * from r_cte