T-SQL 银行家

发布日期 : 4/1/2004 | 更新日期 : 4/1/2004

针对某些 财务问题的基于集合 (set-based) 的解决方案

Itzik Ben-Gan

最近,在我讲授一节 SQL Server 编程课程时,有几个学生给我提出了一个难题。在他们的生产系统中,使用了各种必须向货币值(如存款)应用利率或指数的财务应用程序。这些学生有一个与该系统有关的具体问题,想知道对于这个问题是否有基于集的 T-SQL 解决方案。答案当然是肯定的!让我们研究一下我的学生所提出的这个一般性问题,以便您可以根据自己的具体需要来调整解决方案。

指数化货币值

货币价值是随着时间的推移而发生变化的。财务应用程序很少会以其输入数据库时的原始形式来显示货币值的。财务机构需要向储蓄帐户存款应用利率、向损益报表中的货币值应用指数率(如消费价格指数 - CPI)、向欠外国供应商的债务应用货币兑换率等。

通常,货币值是与生效日期一起输入到财务应用程序的数据库中的。财务应用程序将向本金应用一系列调整,并计算截止到之后某个日期(如“今天”或“2002 年 12 月 31 日)的结果值。例如,假设 2002 年 9 月 7 日您向系统中输入金额 $1,300。您需要将该金额与 CPI(CPI 于每月的 15 日进行更新)进行链接,并显示截止到 2002 年 12 月 31 日的值。CPI 包含月利率,这些月利率反映货币值根据消费者平均购买力而发生的变化。诸如 CPI 之类的指数能够以增长百分比或比例系数来表示。消费者通常关心增长百分比,而会计通常使用指数率进行计算。

在本例中,假设指数率在 9 月到 12 月期间分别增长了 0.3%、0.6%、0.4% 和 0.9%。本金金额则需要进行如下调整:

$1,300.0000 * (1 + 0.3%) = $1,303.9000 
$1,303.9000 * (1 + 0.6%) = $1,311.7234 
$1,311.7234 * (1 + 0.4%) = $1,316.9703 
$1,316.9703 * (1 + 0.9%) = $1,328.8230

在向储蓄帐户存款应用利率时,将使用类似的计算(假设在帐户条款中指定的是非固定的利率),但是利率变化的间隔可能会有所不同。

现在,让我们看一下这个一般性的问题。运行清单 1中的脚本,创建 Amounts 和 IndexRates 表,然后在其中填入示例数据。Amounts 表包含货币值及其生效日期,IndexRates 表包含 CPI 的月增长百分比。您的任务是编写一个查询,让该查询能够通过调整金额来反映将来的一个给定日期(在 @givendate 变量中提供)。例如,第 18 页上的 表 1显示了这个特定日期为 2002 年 12 月 31 日时所需的结果。

模仿聚合 PRODUCT() 的解决方案

我们首先写出要应用于每个金额的公式:

indexed_value =  amt_value * (1+1st idx_growth) * 
(1+2nd idx_growth) * ... * (1+last idx_growth)

其中 1st、2nd(等)比率分别对应于金额的生效日期和其间的几个时段,last 比率则是在这个特定日期生效的比率。

将相应的指数增长与每个金额相关联是这个问题较为简单的部分。可以通过使用下面的 JOIN 条件,将 Amounts 表与 IndexRates 表进行联接:

ON IR.idx_date BETWEEN amt_date AND @givendate

对于每个金额您将获取多个指数行,这些行代表着上述公式中的多个指数变化。使用左外部联接,可以确保生效日期晚于最后一个指数率更改日期的金额(例如,Amounts 中生效日期为 2002 年 12 月 24 日的行)也会显示在输出中。

在此处,更棘手的问题是根据公式的要求计算返回的所有指数变化的乘积。如果 T-SQL支持聚合 PRODUCT() 函数(该函数将计算一组值的乘积,这与 SUM() 函数计算一组值的和非常相似)的话,就可以使用 清单 2中显示的伪查询了。(不过,因为 T-SQL 不支持聚合 PRODUCT() 函数,所以您无法运行该查询。)我在伪查询的 SELECT 列表中使用了 ISNULL(),这样当其生效日期晚于最后一个指数率变化日期时,也会返回本金金额。

在 2001 年 5 月发表的“Adding Performance”(InstantDoc ID 20131) 一文中,我讨论了一个数学方法,此方法可通过在以下公式中使用 LOG10 函数来模仿聚合 PRODUCT() 函数:

PRODUCT() = POWER(10., 
 SUM(LOG10()))

如果您将上面的公式应用于此问题的查询,则会得到如下表达式:

ISNULL(POWER(CAST(10 AS FLOAT), 
 SUM(LOG10(1+idx_growth))), 
 amt_value) * amt_value

POWER() 函数的返回值与其第一个参数的数据类型相同,因此该代码会将数字 10 显式转换为 FLOAT,以强制输出 FLOAT 结果。最后,将上面的表达式转换为货币数据类型即可完成任务。您可以通过运行 清单 3显示的最后一个查询来获取 Table 1 显示的所需结果。

使用各自指数率系数的解决方案

解决该问题的另一种方法是使用 CPI 的不同表示形式。您可以存储比率系数(该系数表示某个月的指数率与预定义的基月指数率的比值),而不是存储指数率相对于上一个月的变化百分比。例如,可以将 2001 年 12 月确定为基月,并将它的比率系数值设置为 1。1 月的指数增长率为 0.1%,因此 1 月的指数率系数将为 1 * (1+0.1%) = 1.001。2 月的指数增长率为 0.2%,因此 2 月的指数率系数将为 1.001 * (1+0.2%),依此类推。请记住,每个指数率系数都包括自基础日期以来所有的累积指数变化。因此,要向 amt_value 应用从 from_month 到 to_month 期间的指数变化,可以使用下面的公式:

amt_value * (to_index_rate / from_index_rate)

就这么简单。您可以存储指数率系数(而非指数增长),也可以添加另一列,以便可以在所执行的各种财务计算中使用最适合您的需求的系数。甚至还可以在 INSERT 语句中仅指定一个值,并且让触发器为您计算另一个值。对您来说,一个重要的好处是,例如 IndexRates 这样的表将没有更新或删除,而且对于每个期段(例如,每月),只需向该表中插入一行。

运行第 20 页上 清单 4中的代码,可以用另一列 idx_rate 重新创建 IndexRates 表。该代码还会创建一个插入触发器,该触发器会基于存储在 idx_growth 列中的指数增长值来计算指数率系数,并用指数变化重新填充该表。该触发器运行一个 UPDATE 语句将 IndexRates 表和已插入表联接起来,以便查找需要进行更新的新行。SET 子句使用子查询来检索上个月的指数率系数,并将其与 (1 + current_month's_index_growth) 相乘。当在该表中输入第一行时,整个表达式将生成 NULL。因此,在这种情况下,该代码使用 ISNULL() 返回 1。

接着,您需要编写一个查询,以便对于每个金额查找该金额的生效日期和特定日期的指数率系数;您将在本节前面提供的简单公式中使用这些日期。在编写该查询时有一个问题,那就是 amt_date 和 @givendate 值不一定是当月的第 15 日。可以使用 CASE 表达式来检查该日期的日期值是否小于 15,如果小于 15,则生效日期是上个月的第 15 日;否则,则是当月的第 15 日。因此,将按如下方式计算特定日期的生效日期:

DECLARE @givendate AS SMALLDATETIME 
SET @givendate = '20021231' 
DECLARE @effective_givendate AS SMALLDATETIME 
SET @effective_givendate = 
  CASE 
    WHEN DAY(@givendate) < 15 
      THEN CONVERT(CHAR(6), DATEADD(month, -1, 
       @givendate), 112) 
          + '15' 
    ELSE CONVERT(CHAR(6), @givendate, 112) + '15' 
  END

现在,可以使用类似的技术来计算该金额的生效日期,然后在 Amounts 表和 IndexRates 表的两个实例之间使用一个三向联接。一个实例检索该金额生效日期的指数率系数;另一个实例检索该特定日期的指数率系数。 清单 5显示了这个完整的查询。该代码看上去比上一个解决方案中的代码稍长,但是,它的执行速度会更快,因为它无需在每个金额的所需期段内查找所有的指数变化,而只是查找第一个和最后一个指数率系数。

学以致用

通常,用 T-SQL为财务问题提供解决方案看上去较为复杂。但是,当您拥有一个包含许多 T-SQL 技巧(如模仿聚合 PRODUCT() 函数)的工具箱时,就可以解决问题。尝试为该问题提出几个解决方案,在找到一个有效的解决方案时,不要满足。当您找到多个解决方案时,可以从中选择一个此时最能满足需求的解决方案,并使用已掌握的技术为下一个任务设计解决方案。

错误、评价、建议| 法律 | 法律 隐私 | 广告

版权所有? 2002 Penton Media, Inc. 保留所有权利。

转到原英文页面

显示: