英文:
Calculate Inflation rates in Snowflake SQL
问题
在Snowflake SQL中，我有一个包含年度通货膨胀率的表，如下所示：
| 年份 | 通货膨胀率 |
|---|---|
| 2000 | 1.1 % |
| 2001 | 1.3 % |
| 2002 | -1.3 % |
| 2003 | 2.3 % |
我想要创建一个表，其中对于每一年，显示相对于基准年2000的总通货膨胀率。
谢谢！
英文:
In snowflake SQL, I have a table with inflation factors, year on year, like this:
| Year | Inflation |
|---|---|
| 2000 | 1.1 % |
| 2001 | 1.3 % |
| 2002 | -1.3 % |
| 2003 | 2.3 % |
I want to produce a table with, for every year, the total inflation from base year 2000
Thanks!
There is an old, similar question here, but I can't figure out how to adapt it
*** UPDATE ***
I tried the suggested solution, but it doesn't work:
答案1
得分: 1
你可以使用SQL 窗口函数，以及一些算术技巧来实现这个目标。简而言之，结合SUM()、LOG()和EXP()函数来获得你想要的结果。
这个想法背后的算术技巧是
A X B = EXP(LOG(EXP(1),A)+LOG(EXP(1),B))
我们使用基于窗口的sum()函数来计算截止到年份X的累积对数（通货膨胀率）值，然后应用exp()来获得原始的累积通货膨胀率。
看下面的示例代码（在Snowflake SQL语法中，代码中使用了Snowflake特定的ln()函数）：
select
record_year,
inflation_rate,
round(exp(SUM(ln(1+inflation_rate)) OVER (ORDER BY record_year)) -1,3) as cum_inflation_rate
FROM inflation_data
示例输出：
| record_year | inflation_rate | cum_inflation_rate |
|---|---|---|
| 2000 | 0.011 | 0.011 |
| 2001 | 0.013 | 0.024 |
| 2002 | -0.013 | 0.011 |
| 2003 | 0.023 | 0.034 |
英文:
You can use SQL Window Function, and some arithmetic trick to do that.In short, combine SUM(), LOG() and EXP() functions to get the result you wants.
The arithmetic trick behind the idea is
A X B = EXP(LOG(EXP(1),A)+LOG(EXP(1),B))
We use window-based sum() function to calculate the cumulative log(inflation rate) value up to year X. and then apply exp() to get raw cumulative inflation rate.
See the following code as example(in snowflake sql syntax, use snowflake-specific ln() function in the code):
select
record_year,
inflation_rate,
round(exp(SUM(ln(1+inflation_rate)) OVER (ORDER BY record_year)) -1,3) as cum_inflation_rate
FROM inflation_data
VERIFIED RUNNING EXAMPLE SQLFIDDLE LINK
example output:
| record_year | inflation_rate | cum_inflation_rate |
|---|---|---|
| 2000 | 0.011 | 0.011 |
| 2001 | 0.013 | 0.024 |
| 2002 | -0.013 | 0.011 |
| 2003 | 0.023 | 0.034 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论