Oracle递归计算基于税收的总额

Question

我有一个这样的临时表：

id  d           tax_rate    money
1   20210101    5           100
1   20210201    15          0   
1   20210301    20          0   
1   20210401    5           0 

这是我想要选择的输出：

id  d           tax_rate    money   total
1   20210101    5           100     105
1   20210201    15          105     120.75
1   20210301    20          120.75  144.9
1   20210401    5           144.9   152.145

这意味着我需要基于tax_rate和previous total递归地计算total(在前一天的总金额=货币的第一天(。total = previous total (by date) * (1 + tax_rate)(税率百分比(

我试着使用LAG() OVER()，但LAG只计算之前的值，而不是递归的，所以从第三天开始计算的值返回错误的总数。

在我的情况下，如果我可以使用LAG或任何函数来乘以之前的所有tax_rate(例如1.05*1.15*1.2=1.449(，那么我可以计算正确的previous total，但没有找到这样的函数。

WITH tmp AS 
(
    SELECT 1 AS id, 20210101 AS d, 5 AS tax_rate, 1000 AS money FROM dual UNION ALL
    SELECT 1 AS id, 20210201 AS d, 15 AS tax_rate, 0 AS money FROM dual UNION ALL
    SELECT 1 AS id, 20210301 AS d, 20 AS tax_rate, 0 AS money FROM dual UNION ALL
    SELECT 1 AS id, 20210401 AS d, 5 AS tax_rate, 0 AS money FROM dual 
)
SELECT * 
FROM tmp;

Answer 1

您可以尝试使用数学公式进行乘法累加。

然后通过乘法累加来计算钱。

查询1：

SELECT  ID, D, tax_rate, 
        SUM(money) OVER(PARTITION BY ID ORDER BY ID) * EXP(SUM(LN(CAST(tax_rate AS DECIMAL(5,2))/100 + 1))over(PARTITION BY ID ORDER BY d)) total
FROM tmp

结果：

| ID |        D | TAX_RATE |   TOTAL |
|----|----------|----------|---------|
|  1 | 20210101 |        5 |     105 |
|  1 | 20210201 |       15 |  120.75 |
|  1 | 20210301 |       20 |   144.9 |
|  1 | 20210401 |        5 | 152.145 |

Answer 2

一个选项类似于这个

WITH tmp AS 
(
    SELECT 1 AS id, 20210101 AS d, 5 AS tax_rate, 100 AS money FROM dual UNION ALL
    SELECT 1 AS id, 20210201 AS d, 15 AS tax_rate, 0 AS money FROM dual UNION ALL
    SELECT 1 AS id, 20210301 AS d, 20 AS tax_rate, 0 AS money FROM dual UNION ALL
    SELECT 1 AS id, 20210401 AS d, 5 AS tax_rate, 0 AS money FROM dual 
),
running_total( id, d, tax_rate, money, total )
as (
  select id, d, tax_rate, money, money * (1 + tax_rate/100) total
    from tmp
   where money != 0
  union all
  select t.id, t.d, t.tax_rate, t.money, rt.total * (1 + t.tax_rate/100)
    from tmp t
         join running_total rt
           on t.id = rt.id
          and to_date( rt.d, 'yyyyddmm' ) = to_date( t.d, 'yyyyddmm' ) - 1
)
select *
  from running_total;

看看这个小提琴。

我假设构成递归CTE基础的第一行是money != 0所在的行(因此每个id只有一个这样的行(。您可以将其更改为根据CCD_ 10或任何其他"日期"选择具有最早日期的行；第一行"；逻辑您的实际数据支持。

请注意，如果你用实际日期来表示日期，而不是用代表日期的数字，你的生活会更轻松。对于一个4行的虚拟表，在running_total递归CTE中，必须在联接的两侧执行to_date并不重要。但是对于一个具有适当行数的真实表，您希望能够在(id, d)上有一个索引以获得适当的性能。当然，您可以创建一个基于函数的索引，但您要么需要在to_date调用中显式指定NLS环境之类的内容，要么在会话的NLS环境与用于创建索引的NLS设置不匹配时，处理会话不使用索引的可能性。