SQL Server——如何填充缺失的列值

I have set of records at day level with 2 columns:

我每天有两栏记录:

Invoice_date
Invoice_date
Invoice_amount
Invoice_amount

For few records, value of invoice_amount is missing.

对于很少的记录，invoice_amount的值会丢失。

I need to fill invoice_amount values where it is NULL using this logic:

我需要使用以下逻辑填充invoice_amount为空的值:

Look for next available invoice_amount (in dates later than the blank value record date)

查找下一个可用的invoice_amount(在日期之后的日期，而不是空白的值记录日期)
For records with invoice_amount still blank (invoice_amount not present for future dates), look for most previous invoice_amount (in dates before the blank value date)

对于invoice_amount仍然为空的记录(invoice_amount未来日期不存在)，请查找大多数以前的invoice_amount(以空白值日期之前的日期)

Note: We have consecutive multiple days where invoice_amount is blank in the dataset:

注意:我们已经连续好几天，在数据集中，invoice_amount为空白:

3 个解决方案

#1

use CROSS APPLY to find next and previous not null Invoice Amount

使用交叉应用查找下一个和之前的非空发票金额

update  p
set     Invoice_Amount  = coalesce(nx.Invoice_Amount, pr.Invoice_Amount)
from    Problem p
        outer apply -- Next non null value
        (
            select  top 1 *
            from    Problem x
            where   x.Invoice_Amount    is not null
            and     x.Invoice_Date  > p.Invoice_Date
            order by Invoice_Date
        ) nx
        outer apply -- Prev non null value
        (
            select  top 1 *
            from    Problem x
            where   x.Invoice_Amount    is not null
            and     x.Invoice_Date  < p.Invoice_Date
            order by Invoice_Date desc
        ) pr
where   p.Invoice_Amount    is null

this updates back your table. If you need a select query, it can be modify to it easily

这将更新您的表。如果需要select查询，可以很容易地对其进行修改

#2

Not efficient but seems to work. Try:

效率不高，但似乎有效。试一试:

update test set invoice_amount =   
       coalesce ((select top 1 next.invoice_amount from test next 
                   where next.invoiceDate > test.invoiceDate and next.invoice_amount is not null
                   order by next.invoiceDate),
                (select top 1 prev.invoice_amount from test prev 
                   where prev.invoiceDate < test.invoiceDate and prev.invoice_amount is not null
                   order by prev.invoiceDate desc))
where invoice_amount is null;

#3

As per given example you could use window function with self join

根据给定的示例，可以使用带有self join的窗口函数

update t set t.amount = tt.NewAmount 
from table t
inner join (
    select Dates, coalesce(min(amount) over (order by dates desc ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
                           min(amount) over (order by dates asc ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)) NewAmount
    from table t
) tt on tt.dates = t.dates
where t.amount is null

#1