我有幾年和一個百分比。目標是每年在累積欄中累積當前百分比。我正在尋找的期望結果在累積列中。
Тhis 是將百分比轉換為數字的公式。百分比/ 100 1
YEARS, PERCENTAGE, ACCUMULATIVE,
2010, 38.15%, 38.15%
2011, -25.51%, 2,93%
2012, -8.47%, -5.80%,
2013, 18.51%, 11.64%
2014, -2.07%, 9.32%
2015, 16.27%, 27.11%
2016, 108.94%, 165.60%
2017, 29.67%, 244.41%
我試過了，但沒有用
SELECT
YEARS,
PERCENTAGE,
Sum(PERCENTAGE) OVER(Order By YR ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "ACCUMULATIVE"
FROM
(
SELECT
YEAR "YEAR",
PERCENTAGE
Sum(PERCENTAGE/100 1) OVER(Partition By (YEAR ) Order By (YEAR )) "PERCENTAGE"
FROM
tbl
ORDER BY
(YEAR )
)
uj5u.com熱心網友回復：
您想要行的乘積（乘法），而不是總和（加法）。
沒有分析乘積函式；但您可以使用以下方法計算EXP(SUM(LN(value)))：
SELECT YEARS,
PERCENTAGE,
ROUND(
(EXP(Sum(LN(1 PERCENTAGE/100)) OVER(Order By Years)) - 1)*100,
2
) AS ACCUMULATIVE
FROM tbl
其中，對于樣本資料：
CREATE TABLE tbl (YEARS, PERCENTAGE) AS
SELECT 2010, 38.15 FROM DUAL UNION ALL
SELECT 2011, -25.51 FROM DUAL UNION ALL
SELECT 2012, -8.47 FROM DUAL UNION ALL
SELECT 2013, 18.51 FROM DUAL UNION ALL
SELECT 2014, -2.07 FROM DUAL UNION ALL
SELECT 2015, 16.27 FROM DUAL UNION ALL
SELECT 2016, 108.94 FROM DUAL UNION ALL
SELECT 2017, 29.67 FROM DUAL
輸出：
年 百分比 累計 2010 38.15 38.15 2011 -25.51 2.91 2012 -8.47 -5.81 2013 18.51 11.63 2014 -2.07 9.32 2015 16.27 27.1 2016 年 108.94 165.57 2017 29.67 244.36
要獲得年化回報：
SELECT YEARS,
PERCENTAGE,
ROUND(
(EXP(Sum(LN(1 PERCENTAGE/100)) OVER(Order By Years)) - 1)*100,
2
) AS ACCUMULATIVE,
ROUND(
POWER(
EXP(Sum(LN(1 PERCENTAGE/100)) OVER(Order By Years)),
1/COUNT(*) OVER (ORDER BY Years)
) * 100 - 100,
2
) AS annualized_return
FROM tbl
哪個輸出：
年 百分比 累計 ANNUALIZED_RETURN 2010 38.15 38.15 38.15 2011 -25.51 2.91 1.44 2012 -8.47 -5.81 -1.97 2013 18.51 11.63 2.79 2014 -2.07 9.32 1.8 2015 16.27 27.1 4.08 2016 年 108.94 165.57 14.97 2017 29.67 244.36 16.71
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/488362.html
上一篇：Year,NextYear,Quartere以財政年度為基礎的下一季度
下一篇：如何從存盤程序中洗掉單個引號