Discussion :

[giviz]

Bonjour,

Voici ce que je souhaite faire :

Table 1 : "visite"
idsite (int)
date (date)

Table 2 : "site"
idsite (int)
historiquevisites (text)

Je souhaite mettre à jour la valeur "historiquevisites" de la table "site" à partir des données de la table "visite" en agrégeant les 90 derniers jours sous la forme suivante : valeur,valeur,valeur,valeur...

ce qui donne par exemple :

0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,1,1,1,1,1,1

J'utilise pour cela le code suivant imbriqué dans une requête SQL :

Code :

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

1
2
traficsearch90history = (SELECT array_to_string(array(SELECT COUNT(*) 
FROM visite WHERE idsite = s.idsite) GROUP BY date order by date asc), ','))

mais, parce qu'il y a forcément un mais...

la table visite ne contient des valeurs que si le nombre de visite à une date donnée est > 0.

- Il y a donc des cas ou il y a moins de 90 jours de données, il aurait fallut compléter avec des 0
- Il y a donc des cas ou il y a des trous de données, par exemple 2 visites le lundi, 0 le mardi et 6 le mercredi

Logiquement il faudrait à la place d'ordonner par date, prendre la liste des dates des 90 derniers jours de façon croissante, puis contrôler la valeur du COUNT(*) pour la date et si elle est > 0 l'utiliser, sinon mettre 0.

Mais je n'ai pas la moindre idée de comment faire cela.

J'utilise Postgresql 9.1

Si vous avez des suggestions, je suis preneur !

Merci !

[punkoff]

bonjour,


il faut passer par une jointure externe entre une table (ou cte) générée sur les 90 jours et votre table visite actuelle.

un exemple de cte :

Code :

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

1
2
3
4
5
6
 
with tmp as (
select current_date - 90 + generate_series(1, 90) as dte)
 
select * 
from tmp

[giviz]

bonjour,


il faut passer par une jointure externe entre une table (ou cte) générée sur les 90 jours et votre table visite actuelle.

un exemple de cte :

Code :

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

1
2
3
4
5
6
 
with tmp as (
select current_date - 90 + generate_series(1, 90) as dte)
 
select * 
from tmp

Merci beaucoup pour ton aide, ca fonctionne

Voici le résultat :

Code :

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

SELECT array_to_string(array(WITH dates AS (SELECT current_date - 90 + generate_series(1, 90) AS dte) SELECT (SELECT COUNT(*) FROM visite WHERE date = dte AND idsite = 6441) FROM dates ORDER BY dte ASC), ',');

qui donne

Code :

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

0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,2,0,1,1,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,1,0,0,0,0

A noter que pour un SUM à la place du COUNT, il faut utiliser COALESCE avec 0 en valeur par défaut, sinon ca ne fonctionne pas correctement.

Merci encore !