SQL查询数据子集时如何获取过滤范围外的最后一个非空值

问题根因 #

你原来的写法问题出在执行顺序：如果先加过滤条件再执行窗口函数，过滤范围外的行不会参与窗口计算，所以Id>2的查询里，Id<=2的行没有被纳入MAX函数的统计范围，自然找不到Id=2的非空值Sample 2，导致前几行的分组grp为NULL，最终LastValue返回NULL。

解决方案 #

方案1：先全表计算填充值再过滤（适合数据量不大、多列填充场景） #

这个方案逻辑最简单兼容性最高，先对全表所有行完成最近非空值填充，再套一层过滤条件返回你需要的子集，多列场景下只要给每个列单独计算分组即可。
示例代码（单列场景，过滤Id>2）：

WITH FullTableFilled 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 FullTableFilled
WHERE id > 2 -- 过滤条件放在最后，先完成全表填充再过滤

多列场景示例（假设有Value1/Value2/Value3三个需要填充的列）：

WITH FullTableFilled AS (
  SELECT 
    id, 
    [Value1], [Value2], [Value3],
    -- 每个列单独计算所属的最近非空值分组
    MAX(CASE WHEN [Value1] IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grp1,
    MAX(CASE WHEN [Value2] IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grp2,
    MAX(CASE WHEN [Value3] IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grp3
  FROM Demo
)
SELECT 
  id,
  [Value1], MAX([Value1]) OVER(PARTITION BY grp1 ORDER BY id) AS LastValue1,
  [Value2], MAX([Value2]) OVER(PARTITION BY grp2 ORDER BY id) AS LastValue2,
  [Value3], MAX([Value3]) OVER(PARTITION BY grp3 ORDER BY id) AS LastValue3
FROM FullTableFilled
WHERE Id > 2;

方案2：预取边界前最后一个非空值（适合大表、过滤范围小的场景） #

如果表数据量极大，全表扫描成本过高，可以先单独查询过滤起始点之前的最后一个非空值，再填充过滤范围内开头的NULL值，避免扫描全表：

-- 定义过滤起始ID
DECLARE @FilterStartId BIGINT = 3;
-- 预取起始ID之前的最后一个非空值
DECLARE @PrevLastValue NVARCHAR(MAX);
SELECT TOP 1 @PrevLastValue = [Value]
FROM Demo
WHERE Id < @FilterStartId AND [Value] IS NOT NULL
ORDER BY Id DESC;

-- 只处理过滤范围内的数据
WITH FilteredRange 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
  WHERE Id >= @FilterStartId
)
SELECT 
  id, 
  [Value],
  COALESCE(
    MAX([Value]) OVER(PARTITION BY grp ORDER BY id ROWS UNBOUNDED PRECEDING),
    @PrevLastValue
  ) AS LastValue
FROM FilteredRange;

多列场景下只要对应增加多个预取变量即可。

内容的提问来源于stack exchange，提问作者Anu Viswan