根据 SQL 中先前行的总和查找总扣除额

Question

我有一个相当棘手的问题，过去几天我一直在努力解决。我目前正在使用 Oracle SQL 模型子句解决它，并且可能已经编写了一个函数，但我正在寻找一个使用分析函数或其他东西的简单解决方案，但无法弄清楚任何事情。

对于给定的策略（ddpsid），我想总结扣除列（ddddpc）。 [抱歉，这些难懂的列名，它们不是我的]。听起来很简单，但如果 ddbnep 列是“Y”，那么我想总结所有先前的扣除额，并将当前扣除额占已扣除额的百分比。所以，如果当前的扣除额是10%，之前的扣除额是20%（即还剩下80%），那么我想扣除8%（10%或80%），总共是28%。

下面的代码是我当前使用的：

with my_sample_data as (
  select 1 as ddpsid, ddddsq, ddddpc, ddbnep, ddadep
  from (
    select 1 as ddddsq, 10 as ddddpc, 'N' as ddbnep, 'Y' as ddadep from dual union all
    select 2 as ddddsq, 10 as ddddpc, 'Y' as ddbnep, 'Y' as ddadep from dual union all
    select 3 as ddddsq, 10 as ddddpc, 'N' as ddbnep, 'Y' as ddadep from dual union all
    select 4 as ddddsq, 10 as ddddpc, 'Y' as ddbnep, 'Y' as ddadep from dual
  )
)
--        select
--            ddpsid,
--            cumul as ddddpc
--        from (
          select
            ddpsid,
            ddddsq,
            ddadep,
            ddbnep,
            ddddpc,
            rn,
            num_rows,
            100 * (1-cumul) as cumul
          from my_sample_data a
          where ddadep = 'Y'
          model
          return all rows
          partition by (ddpsid)
          dimension by (row_number() over(partition by ddpsid order by ddddsq) as rn)
          measures (ddddsq, ddadep, ddddpc, ddbnep, 0 as cumul,
                        count(*) over(partition by ddpsid) as num_rows)
          rules automatic order (
            cumul[rn] = case
                          when nvl(ddbnep[cv(rn)],'N') = 'N'
                            then nvl(cumul[cv(rn)-1],1)- ddddpc[cv(rn)] /100
                          else nvl(cumul[cv(rn)-1],1)* (1- ddddpc[cv(rn)]/100) end
          )
--        )
--        where rn = num_rows

数据将按ddpsid分组，并按ddddsq的顺序处理。 ddpsid 和 ddddsq 的组合应该是唯一的。扣除百分比位于 ddddpc 列中。我只想处理 ddadep = 'Y' 的行。最后，如果 ddbnep 列 = 'N' 那么我只想将 ddddpc 添加到运行总计中，否则如果 ddbnep = 'Y' 我想将 ddddpc 作为（100% - 运行总计）的百分比并将其添加到运行总计。

注释掉的代码是必要的，因为我实际上只想要每个 ddpsid 的最后一个值，但如果没有它，它会显示工作得更好一些。

抱歉这个问题很长，但这是我能提供的最简洁的描述。

上面的代码显示了四个推论，两个正常推论和两个先验净值推论。

• 第一个 10% 是正常的，总计为 10%。
• 第二个 10% 是扣除之前的净值。之前的扣除额总计为 10%，因此剩余金额为 90%。因此，此扣除额应为 9%，总计为 19%
• 第三个 10% 是正常的，总计为 29%。
• 最终的 10% 也扣除了之前的费用。之前的扣除额总计为 29%，因此剩余金额为 71%。所以这个扣除应该是 7.1%，总共是 36.1%

在尝试了两三天试图找到一个 SQL 解决方案后，我有点失望，因为我不能，并且希望我没有错过任何事情。

那么，有没有什么方法可以在不使用模型子句和不编写函数的情况下重写它呢？

Answer 1

Mikey，

对于您的查询，您需要根据之前计算的值来计算值。
这种类型的操作只能使用 SQL 模型子句或递归子查询因子分解才能有效完成。后者是在版本 11g Release 2 中引入的。您可以阅读有关它 和在博客文章中我的。
由于我不知道你用的是什么版本，所以我不知道这个建议有多大用处。

但为什么要重写它而不使用模型子句呢？

顺便说一句，如果使用此变体，您可以稍微简化查询：

SQL> with my_sample_data as
  2  ( select 1 as ddpsid, ddddsq, ddddpc, ddbnep, ddadep
  3      from ( select 1 as ddddsq, 10 as ddddpc, 'N' as ddbnep, 'Y' as ddadep from dual union all
  4             select 2 as ddddsq, 10 as ddddpc, 'Y' as ddbnep, 'Y' as ddadep from dual union all
  5             select 3 as ddddsq, 10 as ddddpc, 'N' as ddbnep, 'Y' as ddadep from dual union all
  6             select 4 as ddddsq, 10 as ddddpc, 'Y' as ddbnep, 'Y' as ddadep from dual
  7           )
  8  )
  9  select ddpsid
 10       , ddddsq
 11       , 'Y' as ddadep
 12       , ddbnep
 13       , ddddpc
 14       , rn
 15       , num_rows
 16       , cumul
 17    from my_sample_data a
 18   where ddadep = 'Y'
 19   model
 20         partition by (ddpsid, count(*) over (partition by ddpsid) as num_rows)
 21         dimension by (row_number() over (partition by ddpsid order by ddddsq) as rn)
 22         measures (ddddsq, ddddpc, ddbnep, 0 as cumul)
 23         ( cumul[any] order by rn
 24           = case nvl(ddbnep[cv()],'N')
 25             when 'N' then
 26               nvl(cumul[cv()-1],0) + ddddpc[cv()]
 27             when 'Y' then
 28               100 - ((100 - nvl(cumul[cv()-1],0)) * (1-ddddpc[cv()]/100))
 29             end
 30         )
 31  /

    DDPSID     DDDDSQ D D     DDDDPC         RN   NUM_ROWS      CUMUL
---------- ---------- - - ---------- ---------- ---------- ----------
         1          1 Y N         10          1          4         10
         1          2 Y Y         10          2          4         19
         1          3 Y N         10          3          4         29
         1          4 Y Y         10          4          4       36.1

4 rows selected.

希望这会有所帮助。

问候，
抢。