我使用以下公式在 之间计算增长率 5年(:
growth rate = ((2016 net income/2012 net income) * 1/(5 years)) - 1
我的 IncomeStatements
表以某种方式构成这样的结构:
id | stockid | year | netincome
1 | 1 | 2016 | 235235346
2 | 1 | 2015 | 432434545
..2014-2013 rows
5 | 1 | 2012 | 324324234
6 | 2 | 2016 | 234235234
7 | 2 | cycle continues..
如何选择最新的和大多数过去的年(2016年和2012年(每个股票ID(FOREIGN KEY
(应用公式,然后更新结果在stock
表中的growthrate
列中?
以下是我的不完整代码。我可以帮助我改善它或提供解决方法,因为我是SQL的新手。
UPDATE stock SET growthrate = (Help)
FROM IncomeStatements WHERE IncomeStatements.stockid= stock.id
如果我正确理解,则需要获得年度和净收入的第一个也是最后一个值。您可以使用窗口函数进行此操作。
其余的只是算术:
with i as (
select distinct stockid,
first_value(year) over (partition by stockid order by year) as year_first,
first_value(year) over (partition by stockid order by year desc) as year_last,
first_value(netincome) over (partition by stockid order by year) as netincome_first,
first_value(netincome) over (partition by stockid order by year desc) as netincome_last
from incomestatements i
update s
set growthrate = ((i.netincome_last - i.netincome_first) / nullif(i.year_last - i.year_first, 0)) - 1
from stock s
i
on s.stock_id = i.stock_id;