Oracle SQL中的增量总计

Question

在编写计算增量总计的SQL查询时，我需要帮助。

例如，2015年的初始值为10，按年计算有百分比的变化。我需要每年的总价值。简单来说，若定期存款为10元，而利息每年均有变动，按年计算的总累积价值是多少？

Year  Initial Deposit    Change Percent  Change Value  Final Value  
----  -----------------  --------------  ------------  -----------
2015     Initial - 10                10           1            11              
2016               11                10           1.1          12.1              
2017               12.1               5                                       
2018                                 15                                       
2019                                 20                                      
2020                                 30                                      
2021                                  0                                      
2022                                -10                                      
2023                                 -5                                      
2024                                  1                                      

在表中只计算前两个步骤。

Answer 1

SQL Fiddle

Oracle 11g R2架构设置

create table your_table(year integer, perc integer );

insert into your_table values (2015, 10);
insert into your_table values (2016,10);
insert into your_table values (2017,5);
insert into your_table values (2018,15);
insert into your_table values (2019,20);
insert into your_table values (2020,30);
insert into your_table values (2021,0);
insert into your_table values (2022,-10);
insert into your_table values (2023,-5);
insert into your_table values (2024,1);

查询1

WITH initial_value AS (
  SELECT 10 AS Initial_value FROM DUAL
)
SELECT YEAR,
       initial_value * POWER( 10, COALESCE( SUM( LOG( 10, 1+PERC/100 ) ) OVER ( ORDER BY YEAR ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ), 0 ) ) AS "Initial Value",
       PERC,
       POWER( 10, COALESCE( SUM( LOG( 10, 1+PERC/100 ) ) OVER ( ORDER BY YEAR ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ), 0 ) ) AS "Previous Cumulative Multiplier",
       initial_value * POWER( 10, COALESCE( SUM( LOG( 10, 1+PERC/100 ) ) OVER ( ORDER BY YEAR ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ), 0 ) ) AS "Final Value"
FROM   your_table
       CROSS JOIN
       initial_value

结果

| YEAR | Initial Value | PERC | Previous Cumulative Multiplier |   Final Value |
|------|---------------|------|--------------------------------|---------------|
| 2015 |            10 |   10 |                              1 |            11 |
| 2016 |            11 |   10 |                            1.1 |          12.1 |
| 2017 |          12.1 |    5 |                           1.21 |        12.705 |
| 2018 |        12.705 |   15 |                         1.2705 |      14.61075 |
| 2019 |      14.61075 |   20 |                       1.461075 |       17.5329 |
| 2020 |       17.5329 |   30 |                        1.75329 |      22.79277 |
| 2021 |      22.79277 |    0 |                       2.279277 |      22.79277 |
| 2022 |      22.79277 |  -10 |                       2.279277 |     20.513493 |
| 2023 |     20.513493 |   -5 |                      2.0513493 |   19.48781835 |
| 2024 |   19.48781835 |    1 |                    1.948781835 | 19.6826965335 |

Answer 2

这可以使用递归的CTE(公共表表达式)来完成。

初始载荷：

create table a(year integer, perc integer
);

insert into a values (2015, 10);
insert into a values (2016,10);
insert into a values (2017,5);
insert into a values (2018,15);
insert into a values (2019,20);
insert into a values (2020,30);
insert into a values (2021,0);
insert into a values (2022,-10);
insert into a values (2023,-5);
insert into a values (2024,1);

现在计算：

with s(year, deposit, perc, interest, final_val) as (

  select 2015 year, initial_amount, perc, 
        initial_amount*perc interest/100, 
        initial_amount*(perc+100)/100 as final_val 
  from a cross join (select 10 as initial_amount from dual)
  where year = 2015

  union all

  select a.year, s.final_val, a.perc, s.final_val*a.perc/100, s.final_val*(a.perc+100)/100
  from s join a on s.year+1 = a.year

  )
  select * from s
  ;

SQLFIDDLE

结果：

| YEAR |     DEPOSIT | PERC |     INTEREST |     FINAL_VAL |
|------|-------------|------|--------------|---------------|
| 2015 |          10 |   10 |            1 |            11 |
| 2016 |          11 |   10 |          1.1 |          12.1 |
| 2017 |        12.1 |    5 |        0.605 |        12.705 |
| 2018 |      12.705 |   15 |      1.90575 |      14.61075 |
| 2019 |    14.61075 |   20 |      2.92215 |       17.5329 |
| 2020 |     17.5329 |   30 |      5.25987 |      22.79277 |
| 2021 |    22.79277 |    0 |            0 |      22.79277 |
| 2022 |    22.79277 |  -10 |    -2.279277 |     20.513493 |
| 2023 |   20.513493 |   -5 |  -1.02567465 |   19.48781835 |
| 2024 | 19.48781835 |    1 | 0.1948781835 | 19.6826965335 |