如何在SQL Server中按特定条件使用LAG()函数处理异常值？

能否实现该逻辑？ #

完全可以实现。核心思路是先识别出有效数据（排除0、0.01的Volume值），再基于这些有效数据的序列，取当前行之前最近的有效值作为填充值。

无需中间LAG列的实现方法 #

直接通过窗口函数结合条件判断即可完成，不需要额外创建中间列。以下是具体实现：

步骤1：准备测试数据 #

CREATE TABLE #TestData (
    [Row] INT,
    Volume DECIMAL(18,2)
);

INSERT INTO #TestData ([Row], Volume)
VALUES
(1, 10000),
(2, 8000),
(3, 0.01),
(4, 0),
(5, 5000),
(6, 0);

步骤2：实现填充逻辑的查询 #

SELECT
    [Row],
    Volume,
    -- 提取当前行之前最近的非0、非0.01值
    MAX(CASE WHEN Volume NOT IN (0, 0.01) THEN Volume END) 
        OVER (ORDER BY [Row] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS [LAG(Volume)]
FROM #TestData;

代码说明 #

• CASE WHEN Volume NOT IN (0, 0.01) THEN Volume END：先将无效值（0、0.01）转为NULL，只保留有效数据
• MAX(...) OVER (...)：在当前行之前的所有记录中取最大的有效值（MAX会自动忽略NULL，自然得到最近的那个有效值）
• 窗口范围ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING：严格限定只取当前行之前的记录，不会包含当前行

执行后会得到你期望的结果：

Row     Volume     LAG(Volume)
1       10000.00   NULL
2       8000.00    10000.00
3       0.01       8000.00
4       0.00       8000.00
5       5000.00    8000.00
6       0.00       5000.00

替代实现方案（用LAST_VALUE） #

也可以用LAST_VALUE函数实现相同逻辑，效果一致：

SELECT
    [Row],
    Volume,
    LAST_VALUE(CASE WHEN Volume NOT IN (0, 0.01) THEN Volume END) 
        OVER (ORDER BY [Row] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS [LAG(Volume)]
FROM #TestData;

注意点 #

如果Volume是浮点类型，建议用ABS(Volume - 0.01) < 0.0001这类方式判断，避免浮点精度导致的匹配问题。

内容的提问来源于stack exchange，提问作者Ethan Mark