SQL Server 中带有 IGNORE NULLS 的 Last_value [英] Last_value with IGNORE NULLS in SQL Server

问题描述

我有一个带有空值的时间序列.我想用最新的非非值替换每个空值.根据我的研究，Oracle SQL 可以使用带有 IGNORE NULLS 的 Last_value 轻松完成此操作.是否有类似的方法可以使用 SQL Server 2016 完成此操作?否则，我将使用 C# 对其进行编码，但我觉得使用 SQL 会更快、更清晰、更容易.

I have a time series that with null values. I want to be replace each null value with the most recent non-non value. From what I've researched, Oracle SQL can easily accomplish this using Last_value with IGNORE NULLS. Is there a similar way to accomplish this using SQL Server 2016? Otherwise I'm just going to code it using C#, but felt using SQL would be faster, cleaner, and easier.

Sec SCORE
1   Null
2   Null
3   5
4   Null
5   8
6   7
7   Null

应替换为:

Sec SCORE
1   Null
2   Null
3   5
4   5
5   8
6   7
7   7

推荐答案

您可以通过两个累积操作来做到这一点:

You can do this with two cumulative operations:

select t.*,
       coalesce(score, max(score) over (partition by maxid)) as newscore
from (select t.*,
             max(case when score is not null then id end) over (order by id) as maxid
      from t
     ) t;

最里面的子查询在有值的地方获取最近的 id.最外面的那个值传播"到后续行.

The innermost subquery gets the most recent id where there is a value. The outermost one "spreads" that value to the subsequent rows.

如果您确实想要更新表格，您可以轻松地将其合并到update 中.但是，Oracle 不能(很容易)做到这一点，所以我猜这没有必要......

If you actually want to update the table, you can incorporate this easily into an update. But, Oracle cannot do that (easily), so I'm guessing this is not necessary....

这篇关于SQL Server 中带有 IGNORE NULLS 的 Last_value的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！