我想對一個列進行 "級聯 "更新，該列除 "第一 "外均為空值。順序是由日期升序決定的。 我有這個表
SET ANSI_NULLS ON
啟用
SET QUOTED_IDENTIFIER ON GO
啟用
創建 表 [dbo].[StockA] 。
(
[date] [date] NOT NULL,
[PercentChange] [decimal](19, 6) NULL,
[價格] [decimal](16, 2) NULL)
) ON [PRIMARY]
GO
INSERT INTO [dbo].[StockA] ([span class="hljs-type">date], [PercentChange], [Price] )
VALUES (CAST(N'2021-08-19' AS Date) 。CAST（-0. 005100 AS Decimal(19, 6) ），NULL)
INSERT INTO [dbo].[StockA] ([span class="hljs-type">date], [PercentChange], [Price] )
VALUES (CAST(N'2021-08-20' AS Date) 。CAST（0. 013000 AS Decimal(19, 6) ), NULL)
INSERT INTO [dbo].[StockA] ([span class="hljs-type">date], [PercentChange], [Price] )
VALUES (CAST(N'2021-08-23' AS Date) 。CAST（0. 015400 AS Decimal(19, 6) ），NULL)
INSERT INTO [dbo].[StockA] ([span class="hljs-type">date], [PercentChange], [Price] )
VALUES (CAST(N'2021-08-24' AS Date) 。CAST（0. 009500 AS Decimal(19, 6) ），NULL)
INSERT INTO [dbo].[StockA] ([span class="hljs-type">date], [PercentChange], [Price] )
VALUES (CAST(N'2021-08-18' AS Date) 。CAST（0. 010000 AS Decimal(19, 6）），CAST（100. 00 AS Decimal(16, 2) ) )
去
SELECT *
FROM StockA
ORDER BY date ASC
四個NULL值應該根據之前的計算，從2021-08-19日期的第一個NULL值開始，給出的計算結果是100（這是之前的值）*（1 百分比變化）=99.49
我試過這樣做WITH CTE as
(
SELECT[/span
date, PercentChange, Price,
(LAG(Price) OVER (Order by date) type">date))*(1 PercentChange) AS NextPrice
FROM[/span
股票A
)
UPDATE CTE
SET Price = NextPrice;
這不正確地給了我這樣的結果：
計算是正確的。2021-08-19這一行應該是99.49。那里沒有任何問題。但是我的更新陳述句顯然有問題。如果我再次運行我的CTE查詢，正確的值被添加到下一行，而從上一行消失了。
有誰知道如何用基于 "第一"（按日期升序排列）價格的計算值來填充整個 "價格 "列，以便沒有NULLS？
我使用的是SQL Server 2019.
。uj5u.com熱心網友回復：
一種方法是使用日志來模擬product聚合函式：
with toupdate as (
select a.*,
exp(sum(log(1 percent_change) over (order by date) / (1 first_value( percent_change) over (order by date) as factor,
first_value(price) over (order by date) as orig_price
from stockA
)
update toupdate
設定價格 = orig_price * factor
where price is null;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/311079.html
標籤：