在Oracle中引用先前计算值的值

Question

如何在SQL查询中引用前一行的计算值?在我的例子中，每一行都是一个事件，以某种方式操作与前一行相同的值。

原始数据如下所示:

<>之前Eventno Eventtype总收费3 acq 322 out空1输出空之前

让我们假设每个Eventtype=OUT应该在一个名为Remaincharge的列中减半前一行的totalcharge:

<>之前Eventno Eventtype Totalcharge剩余费用3 acq 32 322输出null 161 out null 8之前

我已经尝试了LAG分析函数，但这不允许我从前一行获得计算值。我试过这样做:

LAG(remaincharge, 1, totalcharge) OVER (PARTITION BY ...) as remaincharge

但是这不起作用，因为找不到剩余电荷。

有什么办法吗?将需要一个分析函数，可以给我的累积和，但给一个函数，而不是访问前一个值。

提前感谢!

更新问题描述

恐怕我的例子问题太笼统了，这里有一个更好的问题描述:

总电荷的剩余量由输出量/(前一个剩余量)的比值决定。

<>之前Eventno事件类型Totalcharge Remainqty Outqty4 acq 32 100 03其他空100 02出空60 401 out null 0 60之前<>之前Eventno Eventtype Totalcharge Remainqty Outqty剩余费用4 acq 32 100 0 323其他null 100 0 32 - (0/100 * 32) = 322 out null 60 40 32 - (40/100 * 32) = 12.81 out null 0 60 12.8 - (60/60 * 12.8) = 0

Answer 1

在您的情况下，您可以使用FIRST_VALUE()分析函数和2的幂计算出第一个值，您必须在子查询中除以RANK()，然后使用它。这是非常具体的例子，但应该给你一个大致的想法:

select eventno, eventtype, totalcharge
     , case when eventtype <> 'OUT' then firstcharge
            else firstcharge / power(2, "rank" - 1) 
       end as remaincharge
  from ( select a.*
              , first_value(totalcharge) over 
                  ( partition by 1 order by eventno desc ) as firstcharge
              , rank() over ( partition by 1 order by eventno desc ) as "rank"
           from the_table a
                )

下面是一个SQL Fiddle来演示。我没有按任何东西划分因为原始数据中没有任何东西可以按。

Answer 2

在Ben的回答的基础上使用了一个窗口子句，它似乎可以满足您更新的需求:

select eventno, eventtype, totalcharge, remainingqty, outqty,
    initial_charge - case when running_outqty = 0 then 0
    else (running_outqty / 100) * initial_charge end as remainingcharge
from (
    select eventno, eventtype, totalcharge, remainingqty, outqty,
        first_value(totalcharge) over (partition by null
            order by eventno desc) as initial_charge,
        sum(outqty) over (partition by null
            order by eventno desc
            rows between unbounded preceding and current row)
            as running_outqty
    from t42
);

除了它给出19.2而不是12.8第三行，但这是你的公式建议它应该是:

   EVENTNO EVENT TOTALCHARGE REMAININGQTY     OUTQTY REMAININGCHARGE
---------- ----- ----------- ------------ ---------- ---------------
         4 ACQ            32          100          0              32
         3 OTHER                      100          0              32
         2 OUT                         60         40            19.2
         1 OUT                          0         60               0

如果我添加另一个拆分，那么它将在两步中从60到0，并且在混合中也有另一个非out记录:

   EVENTNO EVENT TOTALCHARGE REMAININGQTY     OUTQTY REMAININGCHARGE
---------- ----- ----------- ------------ ---------- ---------------
         6 ACQ            32          100          0              32
         5 OTHER                      100          0              32
         4 OUT                         60         40            19.2
         3 OUT                         30         30             9.6
         2 OTHER                       30          0             9.6
         1 OUT                          0         30               0

假设剩余的数量是一致的，你可以有效地跟踪之前的运行总数，但从你所展示的数据来看，这似乎是合理的。内部查询计算每行的运行总数，外部查询进行计算;可以简化，但希望这样更清楚。

Answer 3

Ben的答案是更好的(可能会更好)，但你也可以这样做:

select t.*, (connect_by_root Totalcharge) / power (2,level-1) Remaincharge
from the_table t
start with EVENTTYPE = 'ACQ'
connect by prior eventno = eventno + 1;

这是一个演示