Рассчитать дельту (разницу текущей и предыдущей строки) в sql

У меня есть таблица, например: 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. это значение является разницей текущей строки одного и того же счета и предыдущей строки одного и того же счета, при условии, что в месяц выполняется одна транзакция. Вот фиктивный 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   | 
-----------------------------------------

эти отсутствуют, кстати.

Пожалуйста, помогите мне в решении этого вопроса.

Источник

3 ответа

Решение

Вот ваш оригинальный запрос, измененный соответственно:

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

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

Источник

Вы можете использовать внутренний запрос, но это не обязательно самый эффективный запрос.

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)
Источник

Можете ли вы "объединить все" ваш запрос с запросом, который просто выбирает первый элемент для каждой учетной записи с начальным балансом, установленным в качестве дельты, и идентификатором этой записи в качестве идентификатора для дельта-записи? Результат будет упорядочен по идентификатору. Грязно, но применимо ли это?

Источник
Другие вопросы по тегам