在SQL中计算增量(当前行与上一行的差) [英] Calculate delta(difference of current and previous row) in sql

问题描述

我有一个像这样的表: trans是表格的名称，例如

I have a table like : trans is the name of the table for example

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列.假设每月有一笔交易，则该值是同一帐户的当前行与同一帐户的先前行之差. 这是一个可以生成增量值的虚拟sql

And I have to update Delta column. which value is the difference of current row of the same account and preceeding row of the same account assuming there is one transaction per month. Here is a dummy sql which can generate the delta value

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    | 
-------------------------------------

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

But the delta of the row which has not any preceeding row should be its amount such as

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

顺便说一句，这些都没了.

these are missing by the way.

请帮助我解决此查询.

推荐答案

以下是您对原始查询的相应修改:

Here's your original query modified accordingly:

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有所不同，并且tt2.amount替换为ifnull(tt2.amount, 0).

The month comparison is moved from where to on, which makes a difference for left join, and tt2.amount is replaced with ifnull(tt2.amount, 0).

脚本的UPDATE版本:

The UPDATE version of the script:

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语法实际上应该是:

The correct MySQL syntax for the above update should actually be:

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 .)

这篇关于在SQL中计算增量(当前行与上一行的差)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！