在Oracle中引用先前计算的值

Question

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

原始数据如下所示：

Eventno  Eventtype   Totalcharge
3        ACQ         32
2        OUT         NULL
1        OUT         NULL

假设每个 Eventtype=OUT 都应该将前一行 totalcharge 的值减半，并显示在名为 Remaincharge 的列中：

Eventno  Eventtype   Totalcharge  Remaincharge
3        ACQ         32           32
2        OUT         NULL         16
1        OUT         NULL         8

我已经尝试使用 LAG 分析函数，但它不允许我从上一行获取计算值。尝试了像这样的东西： LAG(remaincharge, 1, totalcharge) OVER (PARTITION BY ...) as remaincharge 但这行不通，因为找不到 remaingcharge。

有什么想法吗？需要一个可以给我累积总和的分析函数，但是却提供了一个可以访问前一个值的函数。

先谢谢你！

- Kristoffer

Answer 1

在您的情况下，您可以使用FIRST_VALUE()分析函数计算出第一个值，并使用子查询中的RANK()除以2的幂。这非常适用于您的示例，但应该能够提供一般想法：

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，用于演示。我没有按任何方式进行分区，因为您的原始数据中没有可以分区的内容...

- Ben

---

+1，但我认为您需要在eventype上添加一个case，并使用rank - 1来获取OP请求的结果http://www.sqlfiddle.com/#!4/c7eda/13 - A.B.Cade

非常感谢 @A.B.Cade；我完全忽略了那个要求。 - Ben

很抱歉，我的问题在试图提出一个一般性的问题时变得混乱了。现在已经更新为更贴近实际情况的问题描述。对于造成的困惑表示歉意，我非常感谢您的帮助。 - Kristoffer

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降至零，并混合另一个非“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

有一个假设，即剩余数量是一致的，并且您可以有效地跟踪之前的运行总数，但从您展示的数据来看，这个假设似乎是合理的。内部查询计算每行的运行总数，外部查询执行计算；这可以被压缩，但希望像这样更清晰...

- Alex Poole

---

我之前点赞了，但这个解决方案很好。当前行是不同之处... 我没有考虑到它。 - Ben

非常感谢@Ben和@alex-poole！我想我们搞定了。为了防止剩余数量增加（可能会发生），进行了一些小的修改。不再使用固定的100进行除法，而是使用之前的剩余数量。通过使用LAG()函数得到了这个结果： LAG(fae.remainqty, 1, 0) OVER (PARTITION BY fac.facid ORDER BY eventno DESC) AS previous_remainqty - Kristoffer

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;

我认为这样更易于阅读。

这里有一个演示

- A.B.Cade