在SQL中计算Delta（当前行与前一行的差异）

Question

我有一个像这样的表格：

其中trans是表格的名称，例如。

Id | Trans_Date          | Account_Id | Amount | Delta
------------------------------------------------------
1  | 2011-02-20 00:00:00 |     2      | 1200   | NULL
------------------------------------------------------
2  | 2011-03-21 00:00:00 |     2      | 2000   | NULL
------------------------------------------------------
3  | 2011-04-22 00:00:00 |     2      | 4500   | NULL
------------------------------------------------------
4  | 2011-02-20 00:00:00 |     4      | 1000   | NULL
------------------------------------------------------
5  | 2011-03-21 00:00:00 |     4      | 2400   | NULL
------------------------------------------------------
6  | 2011-04-22 00:00:00 |     4      | 3000   | NULL
------------------------------------------------------

我需要更新Delta列。该列的值是当前账户的当前行和上一行之间的差，假设每月只有一次交易。

以下是一个可以生成delta值的虚拟SQL语句：

select tt1.id, tt1.amount , tt1.AccountId,(tt1.amount-tt2.amount) as delta 
from trans tt1 left outer  JOIN trans  tt2 
on tt1.accountid = tt2.accountid
where month(tt1.date1)-month(tt2.date1)=1 ;

这个查询的结果是

id | amount | AccountId  | delta  |
-------------------------------------
2  | 2000   |     2      | 800    | 
-------------------------------------
3  | 4500   |     2      | 2500   |
-------------------------------------
5  | 2400   |     4      | 1400   | 
-------------------------------------
6  | 3000   |     4      | 600    | 
-------------------------------------

但是没有前一行的行的增量应该是它的数量，例如

1  | 1200   |     2      | 1200   | 
-----------------------------------------
4  | 1000   |     4      | 1000   | 
-----------------------------------------

顺便提一下这些是缺失的。

请帮我解决这个问题。

- pinkb

Answer 1

以下是相应修改过的原始查询：

select
  tt1.id,
  tt1.amount,
  tt1.AccountId,
  (tt1.amount-ifnull(tt2.amount, 0)) as delta
from trans tt1
  left outer JOIN trans tt2 on tt1.accountid = tt2.accountid
    and month(tt1.date1)-month(tt2.date1)=1;

将月份比较从where移动到on，这对于left join有区别，并用ifnull(tt2.amount, 0)替换了tt2.amount。

---

脚本的UPDATE版本：

update tt1
set delta = (tt1.amount-ifnull(tt2.amount, 0))
from trans tt1
  left outer JOIN trans tt2 on tt1.accountid = tt2.accountid
    and month(tt1.date1)-month(tt2.date1)=1;

上述更新的正确MySQL语法实际上应该是：

update trans tt1 
             left outer JOIN trans tt2 
             on tt1.accountid = tt2.accountid 
             and month(tt1.date1)-month(tt2.date1)=1 
set tt1.delta = (tt1.amount-ifnull(tt2.amount, 0));

（感谢@pinkb。）

- Andriy M

---

是的，它有效了，查询结果正是我们所需要的...谢谢Andriy。使用这个DELTA值，我该如何更新同一张表...这很令人困惑...在一个运行中同时查询和更新一张表。在这种情况下，DELTA必须被更新。 - pinkb

嗨，安德里，非常感谢你的支持。实际上，在对您的更新脚本进行一些微调之后，一切都按照我们的要求运行。因此，这是您的更新脚本的微调版本， update trans tt1 left outer JOIN trans tt2 on tt1.accountid = tt2.accountid and month(tt1.date1)-month(tt2.date1)=1 set tt1.delta = (tt1.amount-ifnull(tt2.amount, 0)); - pinkb

我明白了，我语法写错了。（MySQL不是我的“母语”SQL方言。）感谢您的反馈。 - Andriy M

Answer 2

你可以使用内部查询，但这不一定是最有效的查询。

UPDATE trans
SET Delta = Amount - 
(SELECT Amount FROM trans t1
WHERE t1.Trans_Date < trans.Trans_Date
ORDER BY t1.Trans_Date DESC LIMIT 1)

- Tim Rogers

---

嗨，Tim，谢谢你的回复。但不幸的是，我无法成功执行你的查询...MySQL抛出了一些异常。 - pinkb

这是因为 TOP 1 不是 MySQL 的一部分。相当的语句可能是 LIMIT 1，如果我没理解错误的话。 - Andriy M

抱歉，我没有看到MySQL部分。我已经编辑了上面的内容，应该适用于MySQL。 - Tim Rogers

Answer 3

你能否将“union all”查询与一个仅选择每个账户的第一项并将初始余额设置为增量以及该记录的ID作为增量记录的ID的查询合并？结果将按ID排序。虽然有点不规范，但是否适用？

- user629392