Calculer delta(différence de courant et de la rangée précédente) en sql

J'ai une table :
trans est le nom de la table par exemple

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

Et je dois mettre à jour colonne Delta. la valeur de la différence de la ligne actuelle du même compte et en les précédant ligne de compte même en supposant qu'il existe une transaction par mois.
Voici un mannequin sql qui peuvent générer de la valeur 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 ;

Le résultat de cette requête est

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

Mais le delta de la ligne qui n'a aucun précédent, la ligne devrait être son montant comme

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

ceux-ci sont manquantes.

Merci de m'aider dans la résolution de cette requête.

OriginalL'auteur pinkb | 2011-02-22

  1. 10

    Voici votre requête d'origine modifiés en conséquence:

    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;

    Le mois comparaison est déplacée de where à on, ce qui fait une différence pour left join, et tt2.amount est remplacé par ifnull(tt2.amount, 0).


    Le UPDATE version du 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;

    La bonne syntaxe de MySQL pour la mise à jour devrait en fait être:

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

    (Merci @pinkb.)

    OUI IL FONCTIONNE, LE RÉSULTAT DE LA REQUÊTE EST EXACTEMENT CE QUE NOUS RECHERCHIONS...MERCI ANDRIY. EN UTILISANT CETTE VALEUR DE DELTA , COMMENT PUIS-JE METTRE À JOUR LA MÊME TABLE..SA CONFUSION...L'INTERROGATION AINSI QUE LA MISE À JOUR D'UN TABLEAU À UN SEUL TERME. DANS CE CAS, LE DELTA DOIT ÊTRE MIS À JOUR.
    Ajouté le UPDATE script.
    Hey Andriy, Merci beaucoup pour votre soutien. En fait, après un peu de peaufinage dans votre script de mise à jour, tout fonctionne comme nous le souhaitons. Voici donc une version remaniée de vos scripts de mise à jour, mise à jour de trans tt1 JOINTURE externe gauche trans vat 2 sur tt1.accountid = vat 2.accountid et le mois(tt1.date1)-mois(vat 2.date1)=1 jeu tt1.delta = (tt1.montant-ifnull(vat 2.montant, 0));
    Je vois, j'ai la syntaxe de mal. (MySQL n'est pas mon "native" dialecte SQL.) Merci pour les commentaires.

    OriginalL'auteur Andriy M

  2. 1

    Vous pouvez utiliser une requête interne, mais ce n'est pas forcément le plus efficace de la requête.

    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)
    Hey Tim merci pour votre réponse. Mais malheureusement, je ne suis pas en mesure d'exécuter votre requête avec succès...mysql jette quelques exceptions près.
    C'est parce que TOP 1 n'est pas la partie de MySQL. L'équivalent est probablement LIMIT 1, si je ne me trompe pas.
    Désolé de ne pas le voir la base de données MySQL peu. J'ai édité le au-dessus de laquelle doit travail pour MySQL.

    OriginalL'auteur Tim Rogers

  3. 0

    Pouvez-vous "l'union de tous" de votre requête avec une requête qui sélectionne simplement le premier élément, pour chaque compte, le solde initial défini comme le delta, et l'ID de l'enregistrement de l'id pour le delta de l'enregistrement? Le résultat serait commandé par ID. Sale, mais est-elle applicable?

    OriginalL'auteur user629392