Perhaps this approach itself is not correct, but, here is what I did and what I am trying to achieve.
I have a series that looks like this:
value
1.0000
2.0000
3.0000
4.0000
5.0000
6.0000
7.0000
8.0000
9.0000
10.0000
I want to add a growth rate to it as a column. So I made a scalar-value function, which is not working as I thought it would. It returns NULL where I thought it would return a number.
Here is my function:
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;
Here is what gets returned in a query, I have a case expression that produces what I want the function to return.
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
1条答案
按热度按时间xhv8bpkk1#
You built your function to look at LAG() inside itself, in which case is not known to the scalar function. Think of the scalar function as being it's own environment, it takes what you give it for data, and returns based solely on the data you gave it. It's returning NULL because a LAG() on nothing is NULL and anything multiplied by NULL = NULL.
LAG() is an analytic function an not a scalar or table valued, in which are the only options for custom functions. This, of course, is leaving out the conversation of CLRs.
The only way your function can know the lag value is if you pass it the lag value:
Then be sure to pass the @lag value from your query: