MySQL 在sql中计算delta（当前行和上一行的差异）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5078987/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate delta(difference of current and previous row) in sql
提问by pinkb
I have a table like : trans is the name of the table for example
我有一个表格，例如：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
------------------------------------------------------
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
我必须更新 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 ;
The result of this query is
这个查询的结果是
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.
顺便说一下，这些都不见了。
Please help me in resolving this query.
请帮我解决这个查询。
回答by Andriy M
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;
The month comparison is moved from whereto on, which makes a difference for left join, and tt2.amountis replaced with ifnull(tt2.amount, 0).
月份比较从where移到on，这对 产生了影响left join，并tt2.amount替换为ifnull(tt2.amount, 0)。
The UPDATEversion of the script:
该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;
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;
The correct MySQL syntax for the above update should actually be:
上述更新的正确 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));
(Thanks @pinkb.)
（谢谢@pinkb。）
回答by Tim Rogers
You can use an inner query, but it's not necessarily the most efficient query.
您可以使用内部查询，但它不一定是最有效的查询。
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)
回答by user629392
Can you "union all" your query with a query that simply selects the first item for each account with the initial balance set as the delta, and the ID of that record as the id for the delta record? The result would be ordered by ID. Dirty but is it applicable?
您能否通过一个查询“联合所有”您的查询，该查询只需选择每个帐户的第一项，初始余额设置为增量，该记录的 ID 作为增量记录的 ID？结果将按 ID 排序。脏但适用吗？
