通过略作扩展的示例数据,显示了一个新的值3,这是一个先例:
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;
给予:
INDEX |
VALUE1 |
VALUE2 |
VALUE3 |
IDX_K |
L_VALUE3 |
L_IDX_K |
R_D |
CALC |
1 |
0.5 |
0.25 |
1 |
1 |
|
|
1 |
1.25 |
2 |
0.5 |
0.25 |
|
|
1 |
1 |
2 |
1.5 |
3 |
0.5 |
0.25 |
|
|
1 |
1 |
3 |
1.75 |
4 |
0.5 |
0.25 |
10 |
4 |
1 |
1 |
1 |
10.25 |
5 |
0.5 |
0.25 |
|
|
10 |
4 |
2 |
10.5 |
6 |
0.5 |
0.25 |
|
|
10 |
4 |
3 |
10.75 |
7 |
0.5 |
0.25 |
|
|
10 |
4 |
4 |
11 |
可以被粉碎成:
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
如果你真的想,但我更倾向于将其包装在子 Select 中,以便更好地呈现:
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
递归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