sql - 行不属于子查询时的最后一个非空值

Question

考虑下表“演示”

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 表示（暗示它具有最后一个非空值）。

要检索遇到的每个 null 的最后一个 Known Value，我使用以下查询（灵感来自Link）。

  
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”。但是，在数据库中，它有一个Sample 2来自行 (ID=2) 的值，由于我们添加的条件，它不是查询的一部分。

有人可以建议一种方法，即使子查询不包含特定行（该行不必是前一行，它应该具有最后一个非空值），我仍然可以获取 Last Non-Value 值？我对上述情况的预期结果如下。

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

在实际场景中，可能有多个稀疏/可空列（如值），对于每个列，我都需要获取最后一个已知值。但是，为简单起见，以单列为例。

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

标签： sqlsql-servertsql

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	价值	最后值
3	空值	样品 2
4	空值	样品 2
5	空值	样品 2
6	价值 4	价值 4
7	空值	价值 4
8	价值 5	价值 5
9	空值	价值 5
10	价值 6	价值 6