如果性能是一个问题,我建议参考这篇文章提供的解决方案:
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并直接将其转换为二进制。