主页 / dba / 问题 / 229771
Accepted
James
Asked: 2019-02-15 08:18:08 +0800 CST

计算同比通货膨胀率的最佳方法是什么？

  • 772

我的最终目标是将往年的钱计算为 2019 年的美元。

我从 BLS 获得了这些数字，并在 sql 中创建 CPI_U，如下所示：

    create table CPI_U (year int, dec decimal(4,2), annual_avg decimal(4,2))

    insert into CPI_U (year, dec, annual_avg)

    values 
    (2000, 3.4,3.4),
    (2001, 1.6,2.8),
    (2002, 2.4,1.6),
    (2003, 1.9,2.3),
    (2004, 3.3,2.7),
    (2005, 3.4,3.4),
    (2006, 2.5,3.2),
    (2007, 4.1,2.8),
    (2008, 0.1,3.8),
    (2009, 2.7,-0.4),
    (2010, 1.5,1.6),
    (2011, 3.0,3.2),
    (2012, 1.7,2.1),
    (2013, 1.5,1.5),
    (2014, 0.8,1.6),
    (2015, 0.7,0.1),
    (2016, 2.1,1.3),
    (2017, 2.1,2.1),
    (2018, 1.9,2.4)

然后我像这样构建一个三角形：

        with cpi_triangle as (

        select  c1.year,    c1.dec as c1, c2.dec as c2, c3.dec as c3, c4.dec as c4, c5.dec as c5,
                            c6.dec as c6, c7.dec as c7, c8.dec as c8, c9.dec as c9, c10.dec as c10,
                            c11.dec as c11, c12.dec as c12, c13.dec as c13, c14.dec as c14, c15.dec as c15,
                            c16.dec as c16, c17.dec as c17, c18.dec as c18, c19.dec as c19, c20.dec as c20


        from cpi_u c1
        left join cpi_u c2 on c1.year + 1 = c2.year
        left join cpi_u c3 on c1.year + 2 = c3.year
        left join cpi_u c4 on c1.year + 3 = c4.year
        left join cpi_u c5 on c1.year + 4 = c5.year
        left join cpi_u c6 on c1.year + 5 = c6.year
        left join cpi_u c7 on c1.year + 6 = c7.year
        left join cpi_u c8 on c1.year + 7 = c8.year
        left join cpi_u c9 on c1.year + 8 = c9.year
        left join cpi_u c10 on c1.year + 9 = c10.year
        left join cpi_u c11 on c1.year + 10 = c11.year
        left join cpi_u c12 on c1.year + 11 = c12.year
        left join cpi_u c13 on c1.year + 12 = c13.year
        left join cpi_u c14 on c1.year + 13 = c14.year
        left join cpi_u c15 on c1.year + 14 = c15.year
        left join cpi_u c16 on c1.year + 15 = c16.year
        left join cpi_u c17 on c1.year + 16 = c17.year
        left join cpi_u c18 on c1.year + 17 = c18.year
        left join cpi_u c19 on c1.year + 18 = c19.year
        left join cpi_u c20 on c1.year + 19 = c20.year)

        select *, 
      1 * (1 + isnull(c1,0)/100)* (1 + isnull(c2,0)/100)* (1 + isnull(c3,0)/100)* (1 + isnull(c4,0)/100)* (1 + isnull(c5,0)/100) 
        * (1 + isnull(c6,0)/100)* (1 + isnull(c7,0)/100)* (1 + isnull(c8,0)/100)* (1 + isnull(c9,0)/100) * (1 + isnull(c10,0)/100)
        * (1 + isnull(c11,0)/100)* (1 + isnull(c12,0)/100)* (1 + isnull(c13,0)/100)* (1 + isnull(c14,0)/100) * (1 + isnull(c15,0)/100)
        * (1 + isnull(c16,0)/100)* (1 + isnull(c17,0)/100)* (1 + isnull(c18,0)/100)* (1 + isnull(c19,0)/100) * (1 + isnull(c20,0)/100) as adj_factor
        from cpi_triangle

三角形看起来像这样：

    year    c1      c2      c3      c4      c5      c6      c7      c8      c9      c10     c11     c12     c13     c14     c15     c16     c17     c18     c19     c20     adj_factor
    2000    3.40    1.60    2.40    1.90    3.30    3.40    2.50    4.10    0.10    2.70    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    1.494493
    2001    1.60    2.40    1.90    3.30    3.40    2.50    4.10    0.10    2.70    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    1.445353
    2002    2.40    1.90    3.30    3.40    2.50    4.10    0.10    2.70    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    1.422590
    2003    1.90    3.30    3.40    2.50    4.10    0.10    2.70    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    1.389250
    2004    3.30    3.40    2.50    4.10    0.10    2.70    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    1.363346
    2005    3.40    2.50    4.10    0.10    2.70    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    1.319792
    2006    2.50    4.10    0.10    2.70    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.276395
    2007    4.10    0.10    2.70    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.245263
    2008    0.10    2.70    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.196218
    2009    2.70    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.195023
    2010    1.50    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.163606
    2011    3.00    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.146410
    2012    1.70    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.113019
    2013    1.50    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.094414
    2014    0.80    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.078241
    2015    0.70    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.069683
    2016    2.10    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.062247
    2017    2.10    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.040399
    2018    1.90    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    1.019000

问题：我觉得这样真的很不优雅。很多聪明人会看到最终产品，如果他们看我的方法，我希望他们也认为我很聪明。

问题：您认为逐年计算通货膨胀率的最佳方法是什么？

挑战模式：在没有滞后功能的情况下进行（我还没有访问该功能）。

期望的输出：

    year    dec annual_avg  adj_factor
    2000    3.40    3.40    1.4944930
    2001    1.60    2.80    1.4453530
    2002    2.40    1.60    1.4225900
    2003    1.90    2.30    1.3892500
    2004    3.30    2.70    1.3633460
    2005    3.40    3.40    1.3197920
    2006    2.50    3.20    1.2763950
    2007    4.10    2.80    1.2452630
    2008    0.10    3.80    1.1962180
    2009    2.70    -0.40   1.1950230
    2010    1.50    1.60    1.1636060
    2011    3.00    3.20    1.1464100
    2012    1.70    2.10    1.1130190
    2013    1.50    1.50    1.0944140
    2014    0.80    1.60    1.0782410
    2015    0.70    0.10    1.0696830
    2016    2.10    1.30    1.0622470
    2017    2.10    2.10    1.0403990
    2018    1.90    2.40    1.0190000
sql-server sql-server-2008

2 个回答

  1. Best Answer

    如果您还记得数字的对数相加与数字相乘相同，这实际上非常简单。使用此代码：

    SELECT [Year],
    [Dec],
    [Annual_Avg],
    CAST((SELECT EXP(SUM(LOG(1 + (dec/100)))) FROM ##CPI_U u WHERE u.year >= c.year) as decimal(9, 7)) adj_factor
FROM ##CPI_U c

    我收到了这个输出：

    Year    Dec Annual_Avg  adj_factor
2000    3.40    3.40    1.4944944
2001    1.60    2.80    1.4453524
2002    2.40    1.60    1.4225910
2003    1.90    2.30    1.3892490
2004    3.30    2.70    1.3633454
2005    3.40    3.40    1.3197923
2006    2.50    3.20    1.2763949
2007    4.10    2.80    1.2452633
2008    0.10    3.80    1.1962183
2009    2.70    -0.40   1.1950233
2010    1.50    1.60    1.1636059
2011    3.00    3.20    1.1464098
2012    1.70    2.10    1.1130192
2013    1.50    1.50    1.0944142
2014    0.80    1.60    1.0782406
2015    0.70    0.10    1.0696831
2016    2.10    1.30    1.0622474
2017    2.10    2.10    1.0403990
2018    1.90    2.40    1.0190000

    但是，这些值与您的列表不同。所以，我去检查了一些东西。看来您的列表正在遭受舍入误差累积，而这个列表实际上更精确。我用这段代码测试了更大的容量值：

    DECLARE @a decimal(28,26) = 1;
With A as (
    SELECT TOP 100 PERCENT 
        1 + Dec/100.00000000 AS year_factor
    FROM ##CPI_U
    WHERE Year >= 2000
    ORDER BY year desc
    )
SELECT @a = @a * year_factor
FROM  A

SELECT Cast(@a as decimal(9,7))

    通过对 2000、2001 和 2002 年的测试，我的通货膨胀调整输出为：

    2000  1.4944944
2001  1.4453524
2002  1.4225910

    基于这些简单的测试，显然 EXP...LOG 方法比您当前的计算更精确。

    • 5

  2. 这个递归 SQL 也解决了这个问题，尽管我认为对数解决方案更优雅。

    with c (y, d, a, adj) as (
select year, dec, annual_avg, (1+dec/100)*adj from CPI_U join c on year=y-1 union all
select year, dec, annual_avg, (1+dec/100)*1   from CPI_U
where year=(select max(year) from CPI_U)
)
select * from c order by 1;

    在http://sqlfiddle.com/#!4/aad15/4尝试一下 （我使用 Oracle 是因为 MS SQL Server 在那儿。只需要对 SQL 做一些小的改动）

    输出：

    Y       D      A      ADJ
2000    3.4    3.4    1.4944944156194961
2001    1.6    2.8    1.4453524329008667
2002    2.4    1.6    1.4225909772646326
2003    1.9    2.3    1.3892490012349927
2004    3.3    2.7    1.363345437914615
2005    3.4    3.4    1.3197922922697145
2006    2.5    3.2    1.2763948667985634
2007    4.1    2.8    1.2452632846815253
2008    0.1    3.8    1.1962183330274019
2009    2.7   -0.4    1.1950233097176841
2010    1.5    1.6    1.1636059490921948
2011    3.0    3.2    1.1464098020612756
2012    1.7    2.1    1.1130192253022093
2013    1.5    1.5    1.0944141841712973
2014    0.8    1.6    1.078240575538224
2015    0.7    0.1    1.069683110653
2016    2.1    1.3    1.062247379
2017    2.1    2.1    1.040399
2018    1.9    2.4    1.019
    • 3

相关问题