[SQL] Somme progressive [Résolu]

[NeoKript]

Bonjour a tous,

j'ai une table qui ressemble a ceci :

Code :

1
2
3
4
5
6
 
Id          Amount           Datetime         
-------   ------------    -----------     
1           -50                 2011-01-22   
2           60                  2011-02-23    
3           90                  2011-03-01

Et je voudrais, sélectionner toutes les donner et que pour chaque ligne, j'ai une colonne en plus [Preview] qui fasse la somme des opérations dont la date est antérieur à l’opération actuel ainsi sur l'exemple, j'aurai :

Code :

1
2
3
4
5
6
 
Id          Amount           Datetime         Preview
-------   ------------    -----------     -----------
1           -50                 2011-01-22    -50
2           60                  2011-02-23     10
3           90                  2011-03-01     100

Et je n'arrive pas à faire la requête, c'est pour moi une sorte de jointure mais sur la même table + un SUM mais du coup je bug un peu.

Merci d'avance
Cordialement,
NeoKript

[punkoff]

bonjour,

quel est votre sgbd ?

[NeoKript]

Salut,

Mon SGBDest SQL Server 2008,
j'avais oublie de préciser désolé.

Merci

[pacmann]

Salut !

Effectivement, les fonctions analytiques te permettent d'appliquer des fonctions d'agrégats sur une "fenêtre" définie par la clause over :

Code :

1
2
3
 
SELECT sum(amount) OVER(ORDER BY dtsal)
FROM ...

Si tu veux le calcul sur un groupe donné (genre par compte ou par client), tu ajoutes :

Code :

1
2
3
 
SELECT sum(amount) OVER(PARTITION BY compte ORDER BY dtsal)
FROM ...

[fab256]

Indépendament de tout SGBD pourvu que ca accepte les requetes imbriquées, moi je l'aurais écris :

Code :

1
2
3
4
5
6
7
SELECT A.Id, A.DateJour, (
SELECT sum( B.Amout )
FROM matable B
WHERE B.DateJour <= A.DateJour
) AS Preview
FROM matable A
ORDER BY A.DateJour

[NeoKript]

Salut et merci pour la réponse,

j'avoue ne pas tout comprendre...

J'ai juste essaye une requête du type :

Code :

1
2
SELECT sum([amount]) OVER(ORDER BY [date])
FROM [TABLE]

Et ça me dit :

Syntaxe incorrecte vers 'order'.

Pourrais-tu expliquer plus en détail, ça serait sympa.

Merci

[NeoKript]

Envoyé par fab256

Indépendament de tout SGBD pourvu que ca accepte les requetes imbriquées, moi je l'aurais écris :

Code :

En effet, ça fonctionne a merveille.

Je vais utiliser ceci, il n'y a pas de clauses compliquées dedans...

Merci

[fab256]

Sinon on peux aussi le faire avec un JOIN

Code :

1
2
3
4
SELECT A.Id, A.DateJour, sum(B.Amout) AS Preview
FROM matable A JOIN matable B ON B.DateJour <= A.DateJour
GROUP BY A.Id, A.DateJour
ORDER BY DateJour

[elbj]

Bonjour

Je vous propose une solution toute simple :

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT 
    MT1.Id,
    MT1.Amount,
    MT1.Datetime,
    SUM(MT2.Amount) AS Preview
FROM MaTable MT1
  JOIN MaTable MT2 ON MT2.Datetime <= MT1.Datetime
GROUP BY
    MT1.Id,
    MT1.Amount,
    MT1.Datetime

Cordialement

EDIT : Oups ! Je n'avais pas raffraichi et donc pas vu la réponse de fab256.

[punkoff]

Qui est horrible en terme de performance comparé aux 2 1ere requête.

la requête de pacmann induira 1 seul scannage de table + la fonction de fenêtrage.

La 1ere requête de fab va induire 2 scannages de tables pour arriver au même résultat, ou des scannages d'index si ceux-ci existent.

N'ayant pas accès aux création de plan sous SQL serveur je ne saurai laquelle choisir.

[Waldar]

Envoyé par NeoKript

Pourrais-tu expliquer plus en détail, ça serait sympa.

SQL-Server 2008 n'implémente pas la partie ORDER dans les fonctions de fenêtrage d'agrégats, ce qui est bien dommage !

D'après le site de Microsoft, ce ne sera toujours pas le cas pour SQL-Server 2011 :
http://msdn.microsoft.com/en-us/libr...=SQL.110).aspx

C'est vraiment dommage.

Envoyé par punkoff

Qui est horrible en terme de performance comparé aux 2 1ere requête.

Malheureusement, pas le choix !

[pacmann]

Comme quoi, il n'y a pas que MySQL qui oublie d'implémenter les fonctionnalités sympas, hein

[aieeeuuuuu]

Bonjour,

Heu j'ai du louper un épisode... mais... quel est l’intérêt de trier avant de faire une somme ?

pour moi :

3 + 2 + 5 = 2 + 3 + 5

de même le max/min/avg... d'une liste de valeurs est le même, que les valeurs soient triées ou non...

[Waldar]

L'intérêt c'est justement la somme cumulative. On n'est pas obligé de trier sur la valeur, on peut le faire sur une autre colonne.

Par exemple, pour calculer un chiffre d'affaires mensuel avec un cumul au fur et à mesure de l'année, ça donnerait ça (écrit à la mode SQL-Server) :

Code :

1
2
3
4
5
6
7
8
9
  SELECT Year(MaDate)                                                       AS Annee
       , Month(MaDate)                                                      AS Mois
       , sum(CA)                                                            AS CA_mensuel
       , sum(CA) over(partition BY Year(MaDate) ORDER BY Month(MaDate) ASC) AS CA_cumul
    FROM MesVentes
GROUP BY Year(MaDate)
       , Month(MaDate)
ORDER BY Year(MaDate)  ASC
       , Month(MaDate) ASC

Comme précisé par punkoff, il n'y a qu'un seul table scan, avec SQL-Server il faut faire une théta jointure plus coûteuse.