使用当前行和前一行的值进行Oracle SQL数学计算

Question

我有以下表格：

ID  A   B   C
1   1   23  22
2   2   19  20
3   5   18  15
4   3   12  12

A列和B列是常数。我希望使用以下公式计算C列:

对于第一行

 C = B - A

之后

 C = previous(C) - A

我希望只使用Oracle SQL来实现这个目标。

- Sorin Ciocoiu

---

什么是行顺序？因为数据库可以以任何顺序检索行。 - Gurwinder Singh

我添加了一个ID列，用于行顺序。 - Sorin Ciocoiu

你想为第一行获取什么？没有“previous（前一个）”。 - Wernfried Domscheit

@WernfriedDomscheit 对于第一行，C列应该是B-A。 - Sorin Ciocoiu

@Boneist 的输入是列 ID、A 和 B，我想要得到的是列 C。 - Sorin Ciocoiu

Answer 1

如果您将数据以以下形式输入：

    A    B       C
   a1   b1   b1 - a1     == b1 - a1
   a2   b2   c1 - a2     == b1 - a1 - a2 
   a3   b3   c2 - a3     == b1 - a1 - a2 - a3
   a4   b4   c3 - a4     == b1 - a1 - a2 - a3 - a4
   ...
   an   bn   cn-1 - an   == b1 - a1 - a2 - ... - an

您可以在分析函数中轻松找到解决方案，例如。

select A,
       B, 
       first_value(B) over(order by id) - sum(A) over (order by id) C
  from myTable   

请检查实际订单（我已经放置了order by id）

- Dmitry Bychenko

---

老鼠啊，我慢了16秒！*{;-) 另外，我认为应该用(order by id)。 - Boneist

@Dmitry 太棒了 - Gurwinder Singh

@Dmitry - 感谢你清晰的解释，我正在苦苦挣扎，而你的解释让我恍然大悟。我能够运用这个逻辑来分解我的公式并让它正常工作了。 - Gayathri

Answer 2

看一下 Oracle 分析函数。https://oracle-base.com/articles/misc/lag-lead-analytic-functions

在你的情况下，你正试图计算列 C，这是一个派生列，前面的派生列不能在 LAG 函数的公式中直接引用。

因此，诀窍是使用列 C 的定义来推导每一行。在第 3 行，C 的值应该是 C@row2 - A@row3 = C@row1 - (A@row3 + A@row2) = C@row1 + A@row1 - sum(a @ row1,2,3)，这不是 SQL 语法，我只是试图向你解释算法。

当你尝试使用 Oracle SQL 编写相同的查询时，查询应该如下所示：

SELECT ID,A,B, (LAG(C_+A,RN-1,0) OVER (ORDER BY ID)) - SUM(A) OVER (ORDER BY ID) as c FROM(
  SELECT ID, A, B, B-A AS C_, ROWNUM RN FROM TEST
)

假设表名为 TEST，列名为 ID，A，B 结果将如下所示

ID  A   B   C
1   1   23  22
2   2   19  20
3   5   18  15
4   3   12  12

- Vijayakumar Udupa