在SQL Server中使用IGNORE NULLS的LAST_VALUE函数

Question

我有一个时间序列，其中包含空值。我想用最近的非空值替换每个空值。根据我的研究，Oracle SQL可以使用IGNORE NULLS的Last_value轻松实现此操作。是否有类似的方法可以在SQL Server 2016中实现此操作？否则，我将使用C#编写代码，但感觉使用SQL会更快，更清洁，更容易。

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

- Trevor D

---

可能是如何获取空值的上一个值的重复问题。 - ahmed abdelqader

Answer 1

您可以通过两个累积操作来完成此操作：

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。外层子查询将该值“传递”给后续行。

如果您实际上想要更新表格，您可以轻松地将其合并到update中。但是，Oracle不能那样做（容易），所以我猜这不是必要的……

- Gordon Linoff

---

太棒了！完全符合我的需求。但是，我计划经常在数百万行上执行此操作。在3百万条记录上执行以上操作大约需要1分钟。如果我转换到Oracle Express并使用IGNORE NULLS的last_value，是否会获得显着的速度优势？ - Trevor D

@TrevorD . . . 你需要自己测试一下。当然，运行两个窗口函数比运行一个要花费更长的时间；我不确定为了这个收益是否值得切换数据库。 - Gordon Linoff

该死，这真的很棒。可惜我永远也写不出这么好的SQL :@ - Basssprosse

它只采用最大值，而不考虑空值之前是否有最小值，理想情况下应该捕获空值之前的值。 - Mayank Awasthi

@MayankAwasthi……这回答了这里提出的问题。如果您有自己的问题，请作为新问题提出。顺便说一句，这不是计算累积最大值。看看问题中的数据，这就是OP想要的。 - Gordon Linoff

Answer 2

如果性能是一个问题，我建议参考这篇文章提供的解决方案:The Last non NULL Puzzle。他的最终解决方案虽然比较复杂，但在线性查询计划中没有任何连接的情况下表现极佳。以下是我使用的示例实现，它通过 type2 scd staging 表保留了最后一个客户名称。在这个暂存表中，NULL 表示没有更新，'*** DELETED ***'表示显式设置为空值。以下代码将其清理得像一个真正的 SCD 记录：

WITH [SampleNumbered] AS (
    SELECT  *, ROW_NUMBER() OVER ( PARTITION BY [SampleId] ORDER BY [StartDatetime] ) AS [RowNumber]
    FROM [dbo].[SampleDimStage]
), [SamplePrep] AS (
    SELECT  [SampleId]
        ,   [StartDatetime]
        ,   CAST([RowNumber] AS BINARY(8)) + CAST([SampleGroupId] AS VARBINARY(255)) AS [BinarySampleGroupId]
        ,   CAST([RowNumber] AS BINARY(8)) + CAST([SampleStatusCode] AS VARBINARY(255)) AS [BinarySampleStatusCode]
    FROM [SampleNumbered]
), [SampleCleanUp] AS (
    SELECT  [SampleId]
        ,   [StartDatetime]
        ,   CAST(SUBSTRING(MAX([BinarySampleGroupId]) OVER( PARTITION BY [SampleId] ORDER BY [StartDatetime] )
                , 9, 255) AS VARCHAR(255)) AS [LastSampleGroupId]
        ,   CAST(SUBSTRING(MAX([BinarySampleStatusCode]) OVER( PARTITION BY [SampleId] ORDER BY [StartDatetime] )
                , 9, 255) AS VARCHAR(255)) AS [LastSampleStatusCode]
        ,   LEAD([StartDatetime]) OVER( PARTITION BY [SampleId] ORDER BY [StartDatetime] ) AS [EndDatetime]
    FROM [SamplePrep]
)
SELECT  CAST([SampleId] AS NUMERIC(18)) AS [SampleId]
    ,   CAST(NULLIF([sc].[LastSampleGroupId],'*** DELETED ***') AS NUMERIC(18)) AS [GroupId]
    ,   CAST(NULLIF([sc].[LastSampleStatusCode],'*** DELETED ***') AS CHAR(3)) AS [SampleStatusCode]
    ,   [StartDatetime]
    ,   [sc].[EndDatetime]
FROM [SampleCleanUp] [sc];

如果您的排序键是某种整数类型，您可以完全跳过第一个CTE并直接将其转换为二进制。

- Apneal