Forums PostgreSQL.fr

energiesysteme

Membre

Sous requêtes à faire pour chaque enregistrement d'une 1ère requête

Bonjour,

J'ai une table qui ressemble à :

   date        |    value
-------------------------------
2009-09-19 |      1
2009-09-20 |      2
2009-09-21 |      6
2009-09-22 |      9
2009-09-23 |      1

J'aimerai faire une requête qui me remonte pour chaque ligne, la somme de "value" pour les n derniers jours.
Pour exemple, voici la table que je souhaite en retour (pour une sous requête qui traite 3 jours):

   date        | sum_value
-------------------------------
2009-09-19 |      1   (somme de 2009-09-17 à 2009-09-19)
2009-09-20 |      3   (somme de 2009-09-18 à 2009-09-20)
2009-09-21 |      9   (somme de 2009-09-19 à 2009-09-21)
2009-09-22 |      17   (somme de 2009-09-20 à 2009-09-22)
2009-09-23 |      16   (somme de 2009-09-21 à 2009-09-23)

J'ai essayé une requête avec sous requêtes, mais ss succès.

Quelqu'un aurait-il une idée?

Merci d'avance,

Anthony

Dernière modification par energiesysteme (23/09/2009 15:39:48)

gleu

Administrateur

Re : Sous requêtes à faire pour chaque enregistrement d'une 1ère requête

Quelque chose comme ceci fonctionne :

tests=# select d, (select sum(value) from t1 as t2 where t2.d<=t1.d) from t1;
     d      | ?column?
------------+----------
 2009-09-19 |        1
 2009-09-20 |        3
 2009-09-21 |        9
 2009-09-22 |       18
 2009-09-23 |       19
(5 lignes)

À mon avis, les perfs seront très très mauvaises.

---

Guillaume.

energiesysteme

Membre

Re : Sous requêtes à faire pour chaque enregistrement d'une 1ère requête

Ca fonctionne.
Merci beaucoup!

Marc Cousin

Membre

Re : Sous requêtes à faire pour chaque enregistrement d'une 1ère requête

Au fait gleu, je crois que c'était l'occasion de vendre de la 8.4 et des fonctions window

SELECT date,sum(value) over (order by date) from test;
    date    | sum
------------+-----
2009-09-19 |   1
2009-09-20 |   3
2009-09-21 |   9
2009-09-22 |  18
2009-09-23 |  19

---

Marc.

energiesysteme

Membre

Re : Sous requêtes à faire pour chaque enregistrement d'une 1ère requête

Je suis justement en train d'évaluer la 8.4
Merci encore

Dernière modification par energiesysteme (23/09/2009 18:02:34)

gleu

Administrateur

Re : Sous requêtes à faire pour chaque enregistrement d'une 1ère requête

Grmbl. J'ai cherché à le faire mais je n'ai pas réussi. Merci Marc, c'est bien mieux ainsi.

Du coup, j'ai fait un petit test de perfs. J'ai ajouté toutes les dates du 01.01.0001 jusqu'à aujourd'hui grâce à cette requête :

INSERT INTO t1 SELECT madate::date, (random()*10)::int4 FROM generate_series('0001-01-01'::timestamptz, '2009-09-18'::timestamptz, '1 day') AS madate;

C'est tellement facile avec PostgreSQL que ce n'est même pas rigolo

Et voilà ce que ça donne sans index sur la date :

tests=# explain analyze select d,sum(value) over (order by d) from t1;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=102123.01..114962.28 rows=733673 width=8) (actual time=3080.886..6935.207 rows=733673 loops=1)         
   ->  Sort  (cost=102123.01..103957.19 rows=733673 width=8) (actual time=3080.845..4270.888 rows=733673 loops=1)        
         Sort Key: d                                                                                                     
         Sort Method:  external merge  Disk: 12896kB                                                                     
         ->  Seq Scan on t1  (cost=0.00..10583.73 rows=733673 width=8) (actual time=0.037..1071.406 rows=733673 loops=1) 
 Total runtime: 7893.650 ms                                                                                              
(6 lignes)                                                                                                               

Temps : 7896,149 ms

J'ai bien voulu le faire avec ma requête mais, après 34 minutes d'exécution et avoir fait mes courses, j'ai fini par annuler la requête.

Et maintenant la même chose avec un index sur la date :

tests=# explain analyze select d,sum(value) over (order by d) from t1;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=0.00..38951.94 rows=733673 width=8) (actual time=0.059..3906.185 rows=733673 loops=1)
   ->  Index Scan using idx1 on t1  (cost=0.00..27946.84 rows=733673 width=8) (actual time=0.037..1221.258 rows=733673 loops=1)
 Total runtime: 4874.228 ms
(3 lignes)

Temps : 4874,826 ms

On va voir ce que ça donne pour ma requête avec l'index, mais j'ai peu d'espoirs que ça se termine rapidement. Et j'ai même pas 250000 lignes dans cette table.

Conclusion facile : vive la 8.4 \o/

---

Guillaume.

energiesysteme

Membre

Re : Sous requêtes à faire pour chaque enregistrement d'une 1ère requête

Encore une fois, merci de votre aide!

Dernière modification par energiesysteme (24/09/2009 12:11:47)

jhashe

Membre

Re : Sous requêtes à faire pour chaque enregistrement d'une 1ère requête

Bonjour,

J'ai lu l'article qui parle des fonctions Window dans le dernier HS de Linux Magazine (bravo à l'illustre auteur de ce numéro ;-) ), mais le concept n'est vraiment pas clair pour moi. Avez-vous des lectures à me suggérer pour m'aider à digérer ce nouveau concept ?

flo

Membre

Re : Sous requêtes à faire pour chaque enregistrement d'une 1ère requête

Il y a l'article de Marc :
ici :
et la documentation:
tutorial  et
la référence

jhashe

Membre

Re : Sous requêtes à faire pour chaque enregistrement d'une 1ère requête

Merci !

Je suis un peu honteux, j'aurai quand-même pu trouver ces liens sans trop d'effort...
Ceci dit, si c'est une notion particulièrement puissante, ce n'est pas si trivial que ça à mettre en œuvre.