是否有 SQL 窗口函数可以使用上一行的计算值？

这是在 SQL Server 中使用递归 CTE 的解决方案。但我不确定您在第 6 行的值。值 552.8 是正确的吗？如果是，您能否提供解释？请参阅以下方法。如果要针对任何数据库运行，可以调整代码。

with base_cte as
(
    select min(SALES_DATE) as minDate , max(SALES_DATE) as maxdate from example_data
) , 
recursive_cte as
(
    select 
        SALES_DATE ,
        CURR_DATE_UNITS_PURCHASED , 
        LTD_UNITS_PURCHASED ,
        cast(CURR_DATE_UNITS_PURCHASED as decimal(10,2)) as PREV_DAY_LTD_EXTRAP , 
        cast(CURR_DATE_UNITS_PURCHASED*0.1 as decimal(10,1)) as
        CURR_DATE_EXTRAP_NUM_SOLD 
    from example_data where SALES_DATE = (select minDate from base_cte)
union all
    select 
        b.SALES_DATE , 
        b.CURR_DATE_UNITS_PURCHASED , 
        b.LTD_UNITS_PURCHASED , 
        cast(a.PREV_DAY_LTD_EXTRAP+b.CURR_DATE_UNITS_PURCHASED-a.CURR_DATE_EXTRAP_NUM_SOLD as decimal(10,2)) as PREV_DAY_LTD_EXTRAP , 
        cast((a.PREV_DAY_LTD_EXTRAP+b.CURR_DATE_UNITS_PURCHASED-a.CURR_DATE_EXTRAP_NUM_SOLD)*0.1 as decimal(10,1)) as CURR_DATE_EXTRAP_NUM_SOLD
    from recursive_cte as a inner join example_data as b on dateadd(day,1,a.SALES_DATE) = b.SALES_DATE
    where b.SALES_DATE <= (select maxdate from base_cte)
)
select SALES_DATE , CURR_DATE_UNITS_PURCHASED , LTD_UNITS_PURCHASED , CURR_DATE_EXTRAP_NUM_SOLD ,
sum(CURR_DATE_EXTRAP_NUM_SOLD) over(order by SALES_DATE) as LTD_EXTRAP_NUM_SOLD from recursive_cte;

请看另一个解决方案。

with params as(select cast(0.9 as decimal(38,10)) x)
,cte1 as(
select t.*
  ,row_number()over(order by sales_date) k
  ,sum(CURR_DATE_UNITS_PURCHASED)over(order by sales_date) roll_sum_purchased
  ,params.x
from example_data_expected t
cross apply params
)
,cte2 as(
select * 
  ,roll_sum_purchased 
   -  (sum(CURR_DATE_UNITS_PURCHASED*power(x,1-k))over(order by sales_date)) 
      *power(x,k) extrap_sum
from cte1
)
select SALES_DATE,k,CURR_DATE_UNITS_PURCHASED,roll_sum_purchased 
  ,extrap_sum-lag(extrap_sum,1,0)over(order by SALES_DATE) curr_day_extrap
  ,CURR_DATE_EXTRAP_NUM_SOLD
  ,extrap_sum
  ,LTD_EXTRAP_NUM_SOLD
  ,LTD_EXTRAP_NUM_SOLD - extrap_sum check_extrap_sum
from cte2

参数可以以任何其他方式传递，也可以作为常量写入请求中的任何位置。
还可以改进计算的操作。x=0.9power(x,k)

我在测试表中添加了一些数据

INSERT INTO example_data_expected VALUES
 ('2023-11-09', 0,    1250, 58.906521, 719.841311)
,('2023-11-10', 0,    1250, 53.0158689, 772.8571799)
,('2023-11-11', 0,    1250, 47.71428201, 820.5714619)

建议另外检查此值。我会检查@Saikat查询。差异是令人满意的。

包含您和其他数据的查询结果草稿

在这里摆弄小提琴

Upd1 中。
关于使用 power（）。 购买 1000 台，外推销售

(extrap)=1000*0.1=100 and remains =1000*(1-0.1)=1000*0.9=1000*power(0.9,1)=900. 

第二天

extrap=900*0.1=90 and remains 900*(1-0.1)=900*0.9=1000*0.9*0.9=1000*power(0.9,2)=1000*0.81=810  

对于白天剩余k

1000*power(0.9,k)  

tod ->a1,a2,a3 - current day purchased values(CURR_DATE_UNITS_PURCHASED)  
a1k=1,a2k=4,a3k=7
rest - remains from purchased
LTD_EXTRAP_NUM_SOLD - rolling sum =(rolling sum purcased)-rest  
xk=power(x,k), where k - day number  

rest from a1 for first day -> a1*power(x,1) ->1000*power(0.9,1)->1000*0.9=900  
extrap for first day for a1=1000.0-900.0=100  
rest from a1 for day num=k  =a1*power(x,k) ->1000*power(0.9,k)  
rest from a2 for day num=k  =a2*power(x,k-4+1) ->200*power(0.9,k)  
rest from a3 for day num=k  =a3*power(x,k-7+1) ->50*power(0.9,k)  
rest from sum of (a1+a2)  for day number k=4 is  
a1*power(x,k)+a2*power(x,1)  
->a1*power(x,k)+a2*power(x,k)/power(x,1-k)  
->(a1+a2/power(x,1-k))*power(x,k)
->((a1+a2*power(x,k-1))*power(x,k)  
->((a1*power(x,a1k-1)+a2*power(x,a2k-1))*power(x,k)  
Note that for a1 with k=1 a1*power(x,1-k)*power(x,k)  
->a1*power(x,0)*power(x,1)->a1*1.0*0.9
For row with k=7  
((a1*power(x,a1k-1)+a2*power(x,a2k-1)+a3*power(x,a3k-1))*power(x,k)  
Sou: sum(tod*power(x,todK))over(order by ...)*power(x,k)

请注意，要使用此方法，您需要研究行序列长度的影响以进行计算。随着 值的增加，乘数尾数的使用部分会减少。例如，power（0.9,80）=0.000218474500528393。
这可能会降低计算的准确性（如果需要准确性）。
例如，购买 1000 个单位，80 天后仍为 0.218474500528393 个单位，额外为 0,0218474500528393 个单位。
似乎您不需要使用所有数量 - 之前的所有行。您可以。在这种情况下，必须对计算进行相同的更改。ksum(...)over (order by date of sale)sum(...)over(order by sales_date rows ... nearest 100)

下一个变体。计算的准确性应该没有问题。

with cte0 as(
select t.*
  ,row_number()over(order by sales_date) k
  ,cast(0.9 as decimal(38,20)) x
from example_data_expected t
)
,cte1 as(
select t.*
  ,cast(CURR_DATE_UNITS_PURCHASED*power(x,1-k) as decimal(38,20)) dayP1_K
  ,cast(k*log(x) as decimal(38,20)) kxL
from cte0 t
)
,cte2 as(
select * 
  ,sum(CURR_DATE_UNITS_PURCHASED)over(order by sales_date)  -- roll_sum_purchased
   -cast(exp(log((sum(dayP1_K)over(order by sales_date)))+kxL) as decimal(38,20)) extrap_sum
from cte1
)
,cte3 as(
select *
  ,extrap_sum-lag(extrap_sum,1,0)over(order by SALES_DATE) curr_day_extrap
from cte2
)

那里用过equality

x=exp(log(x))
log(x*y)=log(x)+log(y)
log(power(x,k))=k*log(x)