时间戳

首页 > 解决方案 > 根据先前的值更新列中的所有以下值

sql-server - 根据先前的值更新列中的所有以下值

问题描述

我希望对除“第一个”之外的所有空值的列进行“级联”更新。顺序由日期升序确定。我有这张桌子

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[StockA]
(
    [date] [date] NOT NULL,
    [PercentChange] [decimal](19, 6) NULL,
    [Price] [decimal](16, 2) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
VALUES (CAST(N'2021-08-19' AS Date), CAST(-0.005100 AS Decimal(19, 6)), NULL)

INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
VALUES (CAST(N'2021-08-20' AS Date), CAST(0.013000 AS Decimal(19, 6)), NULL)

INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
VALUES (CAST(N'2021-08-23' AS Date), CAST(0.015400 AS Decimal(19, 6)), NULL)

INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
VALUES (CAST(N'2021-08-24' AS Date), CAST(0.009500 AS Decimal(19, 6)), NULL)

INSERT INTO [dbo].[StockA] ([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)))
GO

SELECT * 
FROM StockA 
ORDER BY date ASC

图1

四个 NULL 值应根据前一个计算，从日期 2021-08-19 的第一个 NULL 值开始计算为 100（这是前一个值）*（1+PercentChange）= 99.49

我试过这个

WITH CTE AS
(
    SELECT 
        date, PercentChange, Price,
        (LAG(Price) OVER (Order by date))*(1+PercentChange) AS NextPrice
    FROM
        StockA
)
UPDATE CTE
SET Price = NextPrice;

这错误地给了我这个：

图2

计算是正确的。2021-08-19 行应该是 99.49。那里没有错。但是我的更新声明显然有问题。如果我再次运行我的 CTE 查询，正确的值将添加到下一行并从前一行消失。

有谁知道如何填充整个“价格”列，以便没有 NULLS，计算值基于“第一个”（按日期升序排序）价格？

我正在使用 SQL Server 2019。

标签： sql-servertsql

解决方案

一种方法使用日志来模拟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
    set price = orig_price * factor
    where price is null;

推荐阅读