Calcular delta (diferença entre a linha actual e a linha anterior) em sql

Tenho uma mesa tipo ... : trans é o nome da tabela por exemplo 

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
------------------------------------------------------
E tenho de actualizar a coluna Delta. qual o valor que é a diferença da linha actual da mesma conta e que precede a linha da mesma conta, assumindo que existe uma transacção por mês. Aqui está um SQL simulado que pode gerar o valor delta 

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 ;

o resultado desta consulta é 

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

mas o delta da linha que não tem nenhuma linha anterior deve ser o seu montante tal como 

1  | 1200   |     2      | 1200   | 
-----------------------------------------
4  | 1000   |     4      | 1000   | 
-----------------------------------------
A propósito, estes desapareceram.

Por favor, ajude-me a resolver esta questão.

 9
mysqlsql
Author: Andriy M, 2011-02-22
Source

3 answers

Aqui está a sua consulta original modificada em conformidade: 

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;

A comparação mensal é transferida de where para on, o que faz a diferença para left join, e tt2.amount é substituída por ifnull(tt2.amount, 0).

A versão UPDATE do 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;

A sintaxe correcta do MySQL para a actualização acima deve ser: 

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

(Obrigado @pinkb.)

 10
Author: Andriy M, 2014-02-20 06:07:25

Você pode usar uma consulta interna, mas não é necessariamente a consulta mais eficiente.

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)
 1
Author: Tim Rogers, 2011-02-23 12:46:12

Você pode "unificar todos" sua consulta com uma consulta que simplesmente seleciona o primeiro item para cada conta com o saldo inicial definido como o delta, e o ID desse registro como o id para o registro delta? O resultado seria ordenado por ID. Sujo, mas é aplicável?

 0
Author: user629392, 2011-02-23 01:56:18