最后非空值当行不是子查询的一部分时

Question

考虑以下表格'Demo'

Id  Value
1   Sample 1
2   Sample 2
3   NULL
4   NULL
5   NULL
6   Value 4
7   NULL
8   Value5
9   NULL
10  value6

定义为

CREATE TABLE [dbo].[Demo](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Value] [nvarchar](max) SPARSE  NULL,
CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

在该表中，Value字段(根据业务逻辑)应该只有在发生更改时才会更改。如果值没有改变，它将被表示为NULL(意味着它具有最后一个非空值)。

为了检索遇到的每个空值的最后一个已知值，我使用以下查询(灵感来自链接)。

WITH C AS
(
SELECT id, [Value],
MAX( CASE WHEN [Value] IS NOT NULL THEN id END )
OVER( ORDER BY id
ROWS UNBOUNDED PRECEDING ) AS grp
FROM demo
)
SELECT id, [value],
MAX([value]) OVER( PARTITION BY grp
ORDER BY id
ROWS UNBOUNDED PRECEDING ) AS LastValue
FROM C 

这将检索所需的所有值。

id  value   LastValue
1   Sample 1    Sample 1
2   Sample 2    Sample 2
3   NULL        Sample 2
4   NULL        Sample 2
5   NULL        Sample 2
6   Value 4     Value 4
7   NULL        Value 4
8   Value5      Value5
9   NULL        Value5
10  value6      value6

这，如所见的屏幕截图，工作良好，只要我检索整个表。但是，当我只检索一个子集时，就会出现问题。例如，如果我只需要获取Id>2中的信息，我将得到以下结果:

id  value   LastValue
3   NULL    NULL
4   NULL    NULL
5   NULL    NULL
6   Value 4 Value 4
7   NULL    Value 4
8   Value5  Value5
9   NULL    Value5
10  value6  value6

注意到第一行(ID=3)的值为'NULL'。但是，在数据库中，它有一个来自行(ID=2)的值Sample 2，由于我们添加的条件，它不是查询的一部分。

有人可以建议这样一种方式，我仍然可以获取最后的非值值，即使子查询不包括特定的行(行不需要是前一行，它应该一个与最后的非空值)?我对上述场景的预期结果如下:

id  value   LastValue
3   NULL    Sample 2
4   NULL    Sample 2
5   NULL    Sample 2
6   Value 4 Value 4
7   NULL    Value 4
8   Value5  Value5
9   NULL    Value5
10  value6  value6

在实际场景中，可能有多个像Value这样的稀疏/可空列，对于每个列，我都需要获取最后一个已知值。但是，为了简单起见，我们使用单列作为示例。

PS:但是存在表中first为NULL的情况。从业务逻辑的角度来看，这被认为是一个异常值。

Answer 1

只需使用子查询在选定范围之前提取第一个非空值，然后将其用于第一个Grp(如果为空)。

WITH C AS
(
SELECT Id, [Value]
, MAX(CASE WHEN [Value] IS NOT NULL THEN Id END) OVER (ORDER BY Id ROWS UNBOUNDED PRECEDING) AS Grp
-- Find the first value prior to our resultset in case we start from null
, (SELECT TOP 1 [Value] FROM #demo D1 WHERE D1.Id < D.Id AND [Value] IS NOT NULL ORDER BY D1.Id DESC) InitialValue
FROM #demo D
WHERE Id > 2
)
SELECT Id, [value], Grp, InitialValue
, CASE WHEN Grp IS NULL THEN InitialValue ELSE MAX([value]) OVER (PARTITION BY Grp ORDER BY Id ROWS UNBOUNDED PRECEDING) END AS LastValue
FROM C;

的回报:

<表类>Id价值LastValuetbody><<tr>3空示例24空示例25空示例26价值4价值47空价值48价值5价值59空价值510价值6价值6