array_to_string avec dates manquantes

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 :

Citation:

---

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:

---

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 !

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:

---

1 2 3 4 5 6

with tmp as ( select current_date - 90 + generate_series(1, 90) as dte)   select * from tmp

---

Citation:

---

Envoyé par 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:

---

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:

---

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:

---

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 !