Discussion :

[NeoKript]

Bonjour a tous,

j'ai une table qui ressemble a ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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]

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.

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.