如何在一段时间内为多个对象运行标量函数

Question

嗨，我正在尝试使用标量值函数对大量数据进行算术计算。我的源数据采用以下格式：

----------
ID  PriceDate   Type    Value
1   14/01/2018  Alpha   1.05
1   14/01/2018  Beta    1.07
2   14/01/2018  Alpha   1.03
2   14/01/2018  Beta    1.2
3   14/01/2018  Alpha   1.6
3   14/01/2018  Beta    1.5
1   15/01/2018  Alpha   1.24
1   15/01/2018  Beta    1.23
2   15/01/2018  Alpha   1.29
2   15/01/2018  Beta    1.1
3   15/01/2018  Alpha   1.16
3   15/01/2018  Beta    1.4

---

完整的数据集有 700 万行+(1900 个唯一 ID，日期可以追溯到 2011 年)。 数据集不完整的情况也是，ID 可能具有 alpha 值，但在特定日期没有 beta 值，反之亦然或根本没有。

我正在尝试为ID同时具有alpha值和beta值的每个单独日期计算(alpha-beta)/beta。

到目前为止，我有一个函数可以为特定值执行此操作：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION instrument.Calculate (@PriceDateF datetime, @ID bigint)
RETURNS decimal(20,10)
AS
-- Returns ((Alpha-Beta)/Beta)
BEGIN
Declare @BetaT decimal(20,10)
Declare @AlphaT decimal(20,10)
Declare @Result decimal (20,10)
SELECT @BetaT = Value
FROM ValueTable I
WHERE I.PriceDate = @PriceDateF
AND I.ID = @ID
AND Type = 'Beta'
SELECT @AlphaT = Value
FROM ValueTable I
WHERE I.PriceDate = @PriceDateF
AND I.ID = @ID
AND Type = 'Alpha'
SELECT @Result = (@AlphaT - @BetaT)/@BetaT
RETURN @Result
END
GO

我的问题是，随着时间的推移，我如何在每个 ID 的"值表"上运行此函数，以获得每个 ID 每天的结果 (a-b)/b。我不确定我如何输入参数以及函数是否应该遍历数据表或任何其他方法。我也不确定如何处理数据中的差距(某些日期没有值，或者只有 beta 或 alpha 值)。

非常感谢:) 我正在使用 SQL Server Management Studio 2014

Answer 1

在您的情况下，join可能很简单：

select da.pricedate, da.id, da.value as alpha, db.value as beta,
(da.value - db.value) / db.value
from data da join
data db
on da.pricedate = db.pricedate and da.id = db.id and
da.type = 'Alpha' and
db.type = 'Beta';

不需要用户定义的函数。

我应该注意，您也可以通过聚合来执行此操作：

select id, pricedate,
max(case when type = 'Alpha' then value end) as Alpha,
max(case when type = 'Beta' then value end) as Beta,
(max(case when type = 'Alpha' then value end) -
max(case when type = 'Beta' then value end)
) / max(case when type = 'Beta' then value end) as calculation
from data
group by id, pricedate;

同样，用户定义的函数在这里没有用。