递归调用同一列中的滞后值，或者具有两个相互依赖的列

Question

第一个查询没有运行，因为我在LAG()中有一个SUM()，这是非法的，但我包含它只是为了概念化这个想法。

SELECT
GUID_Key
,AFE_Number
,UWI
,Date
,ACT_NET_AMOUNT
,Cum_Act
,FC_NET_AMOUNT
,Cum_FC
,ROWNUM
,IIF(Cum_FC > Cum_Act, FC_NET_AMOUNT, Cum_Act-ISNULL(LAG(SUM(Cum_Act)OVER(PARTITION BY Date))OVER (ORDER BY ROWNUM),0)) AS Result
FROM ready_set

或

SELECT
GUID_Key
,AFE_Number
,UWI
,Date
,ACT_NET_AMOUNT
,Cum_Act
,FC_NET_AMOUNT
,Cum_FC
,ROWNUM
,IIF(Cum_FC > Cum_Act, FC_NET_AMOUNT, IIF((LAG(CumRes) OVER( ORDER BY ROWNUM)) IS NULL, Cum_Act, Cum_Act - LAG(CumRes) OVER( ORDER BY ROWNUM))) AS Result
,IIF((LAG(CumRes) OVER( ORDER BY ROWNUM)) IS NULL ,/*Result*/IIF(Cum_FC > Cum_Act, FC_NET_AMOUNT, IIF((LAG(CumRes) OVER( ORDER BY ROWNUM)) IS NULL, Cum_Act, Cum_Act - LAG(CumRes) OVER( ORDER BY ROWNUM))), /*Result*/(IIF(Cum_FC > Cum_Act, FC_NET_AMOUNT, IIF((LAG(CumRes) OVER( ORDER BY ROWNUM)) IS NULL, Cum_Act, Cum_Act - LAG(CumRes) OVER( ORDER BY ROWNUM))) + Lag(CumRes) OVER ( ORDER BY ROWNUM))) AS CumRes
FROM ready_set

现在我应该提到的是，在"中有两个空列，result和CumRes；ready_ set"；这就是为什么第二个查询会运行。

这是Excel电子表格中显示的确切想法。它在原理上不是非常复杂，但我想它在编程中！Excel公式相同的想法

编辑：公式的Excel结果

同时，我试图在CumRes下保持结果的运行总数，并根据之前的累积值计算结果。它们是相互依赖的，所以我不能简单地求和Results来得到CumRes列。我不完全知道SQL是如何生成行的，如果我试图对正在生成而不是预先存在的列进行滞后处理，我可能会采取完全错误的方法。你看到我的问题或思维错误了吗？

Answer 1

这似乎与您的公式相匹配：

with cte as 
(
select ACC_NET_AMOUNT, fc_NET_AMOUNT
-- cumulative sums up to the previous rows
, coalesce(sum(ACC_NET_AMOUNT) over (order by rownum rows between unbounded preceding and 1 preceding) ,0) as cumacc
, coalesce(sum( FC_NET_AMOUNT) over (order by rownum rows between unbounded preceding and 1 preceding), 0) as cumfc
from tableX
)
select ACC_NET_AMOUNT, fc_NET_AMOUNT
, ca.CumResult - case when cumfc > cumacc then cumfc else cumacc end as Result
, ca.CumResult
from cte 
cross apply
( select
case when cumfc+FC_NET_AMOUNT > cumacc + ACC_NET_AMOUNT 
then cumfc+FC_NET_AMOUNT 
else cumacc+ACC_NET_AMOUNT 
end as CumResult) as ca

参见小提琴

Answer 2

create table tableX (rownum int not null,
ACC_NET_ int not null, CumACT int not null,
FC_NET_AMOUNT int not null, CumFC int not null);
insert into tableX (rownum, ACC_NET_, CumACT, FC_NET_AMOUNT, CumFc) values
(1, 40, 40, 50, 50), (2, 164, 204, 2050, 2100), (3, 1513, 1717, 0, 2100),
(4, 1000, 2717, 500, 2600), (5, 100, 2817, 0, 2600);    
go
with iterated as (
select rownum, ACC_NET_, CumACT, FC_NET_AMOUNT, CumFc,
iif(CumFc > CumACT, FC_NET_AMOUNT, CumACT - 0) as result,
0 as CumRes
from tableX
where rownum = 1
union all
select x.rownum, x.ACC_NET_, x.CumACT, x.FC_NET_AMOUNT, x.CumFc,
f.result,
f.result + anchor.CumRes
from iterated as anchor inner join tableX x
on x.rownum = anchor.rownum + 1
cross apply (
select iif(x.CumFc > x.CumACT, x.FC_NET_AMOUNT, x.CumACT - anchor.CumRes)
) as f(result)
)
select * from iterated;

递归查询将适用于您的公式。如果问题比你上面的数据所建议的更大，你可能需要阅读更多关于这个主题的内容。此外，我还必须添加一个rownum列才能按顺序排列行。

编辑：我错误地设定了一个初始值的种子，这个问题已经解决了。

https://rextester.com/UXXY92525

Answer 3

如果您想要累积总和：

select rs.*, sum(fc_net_amount) over (order by rownum)
from ready_set rs