sql - 如何将Excel公式转换为SQL来计算每日复利

Question

我正在尝试将 Excel 公式转换为 SQL 以求每日复利。在 Excel 中，我们使用的公式如下所示:

Date   Rate    Balance  Accrual     (Formula)
11/19  0.0529  8000     1.159452    (=C2*(B2/365))
11/20  0.0529  8000     1.159620    (=(C3+SUM($D$2:$D2))*(B3/365))
11/21  0.0529  7000     1.014857    (=(C4+SUM($D$2:$D3))*(B4/365))

我需要能够提取每日应计费用，以便我们可以根据费率变化或余额变化进行预测，因此最终总计在这种情况下效果不佳。

我尝试了一些公式来获得相同的结果，例如:

1. balance*(apr/365)
2. sum((balance * (apr/365))) over (order by date)
3. (balance+sum(balance*(apr/365)) over (order by date))*(apr/365)

1. 适用于单利，但我需要当前行之前的连续利息总和
2. 离我要找的东西还很远
3. 让我非常接近但不准确

问题是我需要将所有以前的应计金额添加到余额中，然后计算利息。我错过了一些东西，但无法完全指出它。查询也将在 SSRS 报告中使用，因此 SSRS 解决方案也可以工作。

Answer 1

这里有一些效果很好的东西。我将逐步介绍整个发现过程，以帮助其他可能需要执行类似操作的人，但您可以直接跳到下面的尝试 3。

首先，这是我创建的工作表:

CREATE TABLE #tmp (
    RecDate Date not null Primary Key,    -- Obviously not appropriate PK for real table
    Rate decimal(7,4),
    Balance Decimal(16,4),
    Accrual Decimal(16,6)
    )

INSERT INTO #tmp VALUES 
    ('20191119',0.0529,8000,0),
    ('20191120',0.0529,8000,0),
    ('20191121',0.0529,7000,0)

- 尝试 1 - 简单公式

对于具有一定 SQL 编码经验的人来说，这通常是第一次尝试。

UPDATE #tmp
SET Accrual = (
    Balance + ISNULL(
        (
        SELECT SUM(Accrual) 
        FROM #tmp x 
        WHERE x.RecDate < #tmp.RecDate
        ), 0)) * (Rate / 365)

这适用于计算单利，但不适用于计算复利，结果如下:

RecDate        Rate    Balance        Accrual
2019-11-19    0.0529    8000.0000    1.159440
2019-11-20    0.0529    8000.0000    1.159440
2019-11-21    0.0529    7000.0000    1.014510

请注意，第 1 行和第 2 行的利息相同。这是因为所有行都是同时更新的，因此在计算下一行之前不会更新任何单独行的应计余额。这就是 SQL Server 的工作原理。

同样有趣的是，如果每行都是在一天结束时计算的，因为每行都将处理具有他们的应计费用已经计算出来了。我们可以通过使用游标来模拟这种形式的数据，一次计算每一行。

- 尝试 2 - 光标

这适用于批处理，并模拟逐日处理。

DECLARE cx CURSOR FOR
    SELECT RecDate
    FROM #tmp
    ORDER BY RecDate

DECLARE @Dt Date

OPEN cx
FETCH NEXT FROM cx
    INTO @dt

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE #tmp
        SET Accrual = (
            Balance + ISNULL((SELECT SUM(Accrual) FROM #tmp x WHERE x.RecDate < @dt), 0)) 
                * (Rate / 365)
        WHERE CURRENT OF cx

    FETCH NEXT FROM cx
    INTO @dt
END
CLOSE cx
DEALLOCATE cx

结果是:

RecDate       Rate      Balance      Accrual
2019-11-19    0.0529    8000.0000    1.159440
2019-11-20    0.0529    8000.0000    1.159608
2019-11-21    0.0529    7000.0000    1.014846

这会正确累积数据，并添加之前的应计费用。但是，您会注意到该输出与提供的示例不匹配，即使计算是正确的。为什么？

- 尝试 3 - 转换 RATE 值以在计算中留出足够的小数位

在 Excel 中，数值是高精度浮点型，而我的示例数据库使用的是 decimal 数据类型。为什么？小数数据类型“防止”计算中的舍入错误。

但是，虽然decimal数据类型可以防止舍入错误，但它们也具有固定长度的精度。计算小数数据类型的公式将截断结果，其小数位数大约等于 (2 * [计算中所有值的小数位数总数] )。因此，当每日利率计算为(利率[4位小数]/365(0位小数))时，对于提供的利率(0.0529)，结果为0.00014493(2*4 = 8 位小数)。

但是，在这种情况下，计算需要比这更高的精度才能返回正确的值。因此，这是光标选项的又一个版本:

DECLARE cx CURSOR FOR
    SELECT RecDate
    FROM #tmp
    ORDER BY RecDate

DECLARE @Dt Date

OPEN cx
FETCH NEXT FROM cx
    INTO @dt

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE #tmp
        SET Accrual = (
            Balance + ISNULL((SELECT SUM(Accrual) FROM #tmp x WHERE x.RecDate < @dt), 0))
                * (CAST(Rate as decimal(38,35)) / 365)   --<<-- CHANGED HERE
        WHERE CURRENT OF cx

    FETCH NEXT FROM cx
    INTO @dt
END
CLOSE cx
DEALLOCATE cx

通过这个高精度版本，我们的结果与用户的结果相匹配:

RecDate       Rate      Balance      Accrual
2019-11-19    0.0529    8000.0000    1.159452
2019-11-20    0.0529    8000.0000    1.159620
2019-11-21    0.0529    7000.0000    1.014857

希望这能够足够彻底地涵盖这个问题。