创建SQL标量值函数计算增长率时返回NULL的问题求助

场景与目标 #

我有如下数值序列：

value
1.0000
2.0000
3.0000
4.0000
5.0000
6.0000
7.0000
8.0000
9.0000
10.0000

想要新增一列增长率，于是创建了标量值函数，但函数返回结果全是NULL，直接用CASE表达式却能得到预期结果。

函数代码 #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[ts_growth_rate] (
    @x NUMERIC(28, 10),
    @scale NUMERIC(28, 10) = 100,
    @power NUMERIC(28, 10) = 1,
    @log_diff BIT = 0
    )
RETURNS NUMERIC(28, 10)
AS
BEGIN
    DECLARE @growth_rate NUMERIC(28, 10);

    SELECT @growth_rate = CASE 
            WHEN @log_diff = 1
                THEN CASE 
                        WHEN LAG(@x) OVER (ORDER BY @x) IS NOT NULL
                            THEN LOG(@x / LAG(@x) OVER (ORDER BY @x)) * @scale
                        ELSE NULL
                        END
            ELSE CASE 
                    WHEN LAG(@x) OVER (ORDER BY @x) IS NOT NULL
                        THEN ((POWER(@x / LAG(@x) OVER (ORDER BY @x), @power) - 1) * @scale)
                    ELSE NULL
                    END
            END;

    RETURN @growth_rate;
END;

查询结果 #

declare @x NUMERIC(28, 10);
declare @scale NUMERIC(28, 10) = 100;
declare @power NUMERIC(28, 10) = 1;
declare @log_diff BIT = 1;

select value,
    [gr] = dbo.ts_growth_rate(value, 100, 1, 0),
    [gr2] = CASE 
            WHEN @log_diff = 1
                THEN CASE 
                        WHEN LAG(value) OVER (ORDER BY value) IS NOT NULL
                            THEN LOG(value / LAG(value) OVER (ORDER BY value)) * @scale
                        ELSE NULL
                        END
            ELSE CASE 
                    WHEN LAG(value) OVER (ORDER BY value) IS NOT NULL
                        THEN ((POWER(value / LAG(value) OVER (ORDER BY value), @power) - 1) * @scale)
                    ELSE NULL
                    END
            END 
from #tempa

返回结果：

value   gr  gr2
1.0000  NULL    NULL
2.0000  NULL    69.3147180559945
3.0000  NULL    40.5465108108164
4.0000  NULL    28.7682072451781
5.0000  NULL    22.314355131421
6.0000  NULL    18.2321556793955
7.0000  NULL    15.4150679827258
8.0000  NULL    13.3531392624522
9.0000  NULL    11.7783035656383
10.0000 NULL    10.5360515657826

问题原因 #

标量函数里的LAG(@x)逻辑完全错误：

• 标量函数是逐行独立调用的，每次调用时@x只是当前行的单个值，没有整个数据集的上下文，LAG函数无法找到"前一行"的数据，自然返回NULL。
• 而查询中的LAG(value)是在整个#tempa表的窗口上下文中运行的，能正确获取到排序后的前一行值。

解决方案 #

方案1：直接使用窗口函数逻辑（推荐） #

既然直接写CASE表达式能得到正确结果，就没必要用标量函数，直接把这段逻辑整合到查询中即可，性能也更好：

select value,
    [growth_rate] = CASE 
            WHEN 1 = 1 -- 替换为你的@log_diff参数逻辑
                THEN CASE 
                        WHEN LAG(value) OVER (ORDER BY value) IS NOT NULL
                            THEN LOG(value / LAG(value) OVER (ORDER BY value)) * 100
                        ELSE NULL
                        END
            ELSE CASE 
                    WHEN LAG(value) OVER (ORDER BY value) IS NOT NULL
                        THEN ((POWER(value / LAG(value) OVER (ORDER BY value), 1) - 1) * 100)
                    ELSE NULL
                    END
            END 
from #tempa

方案2：改用表值函数（如果需要复用逻辑） #

如果需要复用增长率计算逻辑，可以创建内联表值函数，传入参数并基于数据集处理：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[tvf_growth_rate] (
    @scale NUMERIC(28, 10) = 100,
    @power NUMERIC(28, 10) = 1,
    @log_diff BIT = 0
)
RETURNS TABLE
AS
RETURN (
    SELECT 
        value,
        CASE 
            WHEN @log_diff = 1
                THEN CASE 
                        WHEN LAG(value) OVER (ORDER BY value) IS NOT NULL
                            THEN LOG(value / LAG(value) OVER (ORDER BY value)) * @scale
                        ELSE NULL
                        END
            ELSE CASE 
                    WHEN LAG(value) OVER (ORDER BY value) IS NOT NULL
                        THEN ((POWER(value / LAG(value) OVER (ORDER BY value), @power) - 1) * @scale)
                    ELSE NULL
                    END
            END AS growth_rate
    FROM #tempa
)
GO

-- 使用方式
SELECT * FROM [dbo].[tvf_growth_rate](100,1,1)

方案3：修改调用方式，传入前一行值 #

如果非要保留标量函数，需要在查询中先计算出前一行的值，再传入函数：

-- 先计算LAG值，再调用函数
WITH data_with_prev AS (
    SELECT 
        value,
        LAG(value) OVER (ORDER BY value) AS prev_value
    FROM #tempa
)
SELECT 
    value,
    dbo.ts_growth_rate_modified(value, prev_value, 100, 1, 0) AS growth_rate
FROM data_with_prev

-- 修改后的标量函数
ALTER FUNCTION [dbo].[ts_growth_rate_modified] (
    @x NUMERIC(28, 10),
    @prev_x NUMERIC(28, 10),
    @scale NUMERIC(28, 10) = 100,
    @power NUMERIC(28, 10) = 1,
    @log_diff BIT = 0
)
RETURNS NUMERIC(28, 10)
AS
BEGIN
    DECLARE @growth_rate NUMERIC(28, 10);

    SELECT @growth_rate = CASE 
            WHEN @log_diff = 1
                THEN CASE 
                        WHEN @prev_x IS NOT NULL
                            THEN LOG(@x / @prev_x) * @scale
                        ELSE NULL
                        END
            ELSE CASE 
                    WHEN @prev_x IS NOT NULL
                        THEN ((POWER(@x / @prev_x, @power) - 1) * @scale)
                    ELSE NULL
                    END
            END;

    RETURN @growth_rate;
END;

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