Requete sur des données horaires avec des trous..

[edmc73]

Bonjour,

J'ai un probleme surment tout bete que je n'arrive pas a résoudre.

Prenons le cas d'une table toutes bêtes avec 2 champs :
1 champ date et heure
et
1 champ entier avec une valeur

Je veux par exemple récupérer les 24 valeurs d'une journée

Je vais donc une requete

Code :

SELECT date,valeur FROM public.donnees_heure WHERE date BETWEEN '2007-08-01 00:00:00' AND 2007-08-01 23:00:00' ORDER BY date

Jusque la tout se passe bien tant qu'il y a 24 valeurs dans la table... Seulement le hic est que quand il manque des heures dans la table, si il manque 3 heures de données, j'ai comme résultat seulement 21 enregistremens et j'aimerais en avoir 24 !

Par exemple je voudrais:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
*------------------*--------*
| date             | valeur |
*------------------*--------|
| 2007-08-01 00:00 |   NULL |
| 2007-08-01 01:00 |   NULL |
| 2007-08-01 02:00 |   NULL |
| 2007-08-01 03:00 |   NULL |
| 2007-08-01 04:00 |   NULL |
| 2007-08-01 05:00 |   NULL |
| 2007-08-01 06:00 |   NULL |
| 2007-08-01 07:00 |   NULL |
| 2007-08-01 08:00 |   NULL |
| 2007-08-01 09:00 |   NULL |
| 2007-08-01 10:00 |   NULL |
| 2007-08-01 11:00 |   1000 |
| 2007-08-01 12:00 |   1234 |
| 2007-08-01 13:00 |   1222 |
| 2007-08-01 14:00 |   1333 |
| 2007-08-01 15:00 |   NULL |
| 2007-08-01 16:00 |   NULL |
| 2007-08-01 17:00 |   NULL |
| 2007-08-01 18:00 |   NULL |
| 2007-08-01 19:00 |   NULL |
| 2007-08-01 20:00 |   5421 |
| 2007-08-01 21:00 |   5487 |
| 2007-08-01 22:00 |   NULL |
| 2007-08-01 23:00 |   NULL |
-----------------------------

De toutes les facon que j'ai essayé, je n'obtiens que les quelques lignes ou il y a de la valeurs..

Si quelqu'un a une solution, je lui en serait grandemant reconnaissant..

[gleu_]

La solution se trouve dans la fonction generate_series. Elle renvoit une liste de nombres, donc elle peut renvoyer de 0 à 24, qui multiplié par une 1h, fournit les différentes heures.

Voici ce à quoi je suis arrivé :

Code :

1
2
3
4
5
SELECT '2007-08-01 '||i*'1 hour'::interval, valeur
FROM generate_series(0,23) AS i
  LEFT JOIN public.donnees_heure ON date-'2007-08-01'=i*'1 hour'::interval
WHERE date BETWEEN '2007-08-01 00:00:00' AND 2007-08-01 23:00:00'
ORDER BY date

Évidemment, il faut encore travailler cette requête mais la solution n'est pas loin

[edmc73]

Merci beaucoup, mais la fonction generate_series n'existe pas

Query failed: ERROR: function generate_series(integer, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts.

Bon voici ma requete finale

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT '$date_select '||i*'1 hour'::interval, valeur, id_st
FROM (SELECT 0 AS i UNION
SELECT 1 UNION 
SELECT 2 UNION 
SELECT 3 UNION 
SELECT 4 UNION 
SELECT 5 UNION 
SELECT 6 UNION 
SELECT 7 UNION 
SELECT 8 UNION 
SELECT 9 UNION 
SELECT 10 UNION 
SELECT 11 UNION 
SELECT 12 UNION 
SELECT 13 UNION 
SELECT 14 UNION 
SELECT 15 UNION 
SELECT 16 UNION 
SELECT 17 UNION 
SELECT 18 UNION 
SELECT 19 UNION 
SELECT 20 UNION 
SELECT 21 UNION 
SELECT 22 UNION 
SELECT 23) AS i
  LEFT JOIN public.donnees_heure ON date-'$date_select'=i*'1 hour'::interval
WHERE date BETWEEN '$date_select 00:00:00' AND '$date_select 23:00:00' AND id_st IN(SELECT id_st FROM public.stations WHERE id_zone IN($zone_selection)) AND id_pr=$id_pr
ORDER BY id_st,date

Mais le probleme est toujours la...
Je ne sais pas pourquoi, peut etre que quelques choses bloque le fait d'afficher des valeurs NULL ? est ce possibles?

Merci encore

[gleu_]

Pour generate_series, ta version de PostgreSQL doit être vraiment ancienne parce que même la version 8.0 dispose de cette fonction (et on en est à la 8.2). Fais ton possible pour pouvoir mettre à jour ton serveur PostgreSQL.

[edmc73]

je dispose effectivement de la version PostgreSQL 7.4.8

Je ne suis pas admin de la machine je ne peux donc pas mettre a jour ce serveur...

Mais je ne comprend pas pourquoi cette requete ne veut pas m'afficher ce que je veux.

Peut etre que la table virtuel que je créé avec les SELECT et UNION n'est pas pris en compte comme si c'était une table...

Je ne sais pas et je désèspère...

Le but de cette requete est de créer un fichier xml qui va etre lu par du flash pour créer un graph

Je dois d'abord renseigner l'abcisse avec

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<categories>
<category name="0"/>
<category name="1"/>
<category name="2"/>
<category name="3"/>
<category name="4"/>
<category name="5"/>
<category name="6"/>
<category name="7"/>
<category name="8"/>
<category name="9"/>
<category name="10"/>
<category name="11"/>
<category name="12"/>
<category name="13"/>
<category name="14"/>
<category name="15"/>
<category name="16"/>
<category name="17"/>
<category name="18"/>
<category name="19"/>
<category name="20"/>
<category name="21"/>
<category name="22"/>
<category name="23"/>
</categories>

et ensuite les valeurs avec

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<dataset seriesName="Seuil" color="ff0000" showAnchors="0" alpha="20" lineThickness="2">
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
<SET value="130"/>
</dataset>

Le probleme est que si j'ai une valeur que sur une heure (par exemlpe a 10:00) cette valeur sera représenté a 00:00...

je dois pas etre le seul a avoir ce genre de problème..

[gleu_]

Le problème vient de la partie WHERE (et j'ai fait la même erreur que toi). En ajoutant le « date BETWEEN x AND y », tu forces le fait que date doit être renseignée. Or tu peux avoir un créneau horaire où tu n'as pas de date dans ta table. Tu dois donc remplacer

Code :

date BETWEEN x AND y

par

Code :

(date BETWEEN x AND y OR date IS NULL)

[edmc73]

Merci encore pour tes efforts mais le résultat est bizarrement le meme...

voici la table en question

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE TABLE donnees_heure (
    id_st integer NOT NULL,
    id_pr integer NOT NULL,
    id_stat integer NOT NULL,
    date timestamp without time zone NOT NULL,
    code integer NOT NULL,
    valeur double precision
) WITHOUT OIDS;
 
--
-- TOC entry 4 (OID 21349687)
-- Name: donnees_heure_pkey; Type: CONSTRAINT; Schema: public; Owner: transalpair
--
 
ALTER TABLE ONLY donnees_heure
    ADD CONSTRAINT donnees_heure_pkey PRIMARY KEY (date, id_st, id_pr, id_stat);
 
 
--
-- TOC entry 5 (OID 21349693)
-- Name: Ref_32; Type: FK CONSTRAINT; Schema: public; Owner: transalpair
--
 
ALTER TABLE ONLY donnees_heure
    ADD CONSTRAINT "Ref_32" FOREIGN KEY (id_st, id_pr) REFERENCES stations_parametres(id_st, id_pr) MATCH FULL;
 
 
--
-- TOC entry 6 (OID 21349697)
-- Name: ref_1015; Type: FK CONSTRAINT; Schema: public; Owner: transalpair
--
 
ALTER TABLE ONLY donnees_heure
    ADD CONSTRAINT ref_1015 FOREIGN KEY (id_stat, id_pr) REFERENCES statistiques_parametres(id_stat, id_pr);

peut etre y verras tu quelque chose d'incorrect..

J'ai vraiment tourné la requete dans tous les sens.. si j'inverse le RIGHT en LEFT et que j'enleve la condition sur les dates, le NULL apparait pour la table i mais avec le RIGHT aucun NULL n'apparait de l'autre coté...

[gleu_]

Pour moi, cela fonctionne bien après avoir ajouté le « OR date IS NULL ». Soit tu as ajouté d'autres éléments dans ta requête qui empêchent le renvoi de la ligne...

Dans un précédent message, il y avait aussi :

Code :

AND id_st IN(SELECT id_st FROM public.stations WHERE id_zone IN($zone_selection)) AND id_pr=$id_pr

Il est évident que, comme pour date, il faut aussi accepter des valeurs NULL pour eux...

[edmc73]

voici donc ma requete modifié

Code :

1
2
3
4
5
6
7
8
9
10
SELECT '2007-8-9 '||i*'1 hour'::interval AS date, valeur, id_st FROM 
(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23)
AS i LEFT JOIN public.donnees_heure ON date-'2007-8-9'=i*'1 hour'::interval 
WHERE 
        ((date BETWEEN '2007-8-9 00:00:00' AND '2007-8-9 23:00:00') OR date IS NULL)
       AND 
       (id_st IN(SELECT id_st FROM public.stations WHERE id_zone IN(10)) OR id_st IS NULL) 
       AND 
       (id_pr=3 OR id_pr IS NULL) 
ORDER BY id_st,date

et voici le resultat

Code :

1
2
3
4
5
6
7
8
9
10
date	              valeur	 id_st
2007-8-9 15:00:00	87	33411
2007-8-9 16:00:00	87	33411
2007-8-9 17:00:00	84	33411
2007-8-9 18:00:00	78	33411
2007-8-9 19:00:00	73	33411
2007-8-9 20:00:00	73	33411
2007-8-9 21:00:00	70	33411
2007-8-9 22:00:00	67	33411
2007-8-9 23:00:00	65	33411

Maintenant si je remplace LEFT par RIGHT et que je change la condition de date entre le 8 aout et le 9 aout (sinon l'exemple ne marche pas) j'ai bien un resultat comme ca

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
date	valeur	id_st
2007-8-9 15:00:00	87	33411
2007-8-9 16:00:00	87	33411
2007-8-9 17:00:00	84	33411
2007-8-9 18:00:00	78	33411
2007-8-9 19:00:00	73	33411
2007-8-9 20:00:00	73	33411
2007-8-9 21:00:00	70	33411
2007-8-9 22:00:00	67	33411
2007-8-9 23:00:00	65	33411
NULL	102	33411
NULL	97	33411
NULL	91	33411
NULL	85	33411
NULL	65	33411
NULL	67	33411
NULL	66	33411
NULL	74	33411
NULL	78	33411
NULL	80	33411

j'ai bien du NULL dans la colonne date, mais ce n'est pas ce que je recherche.. enfin je ne sais pas comment expliquer mais en gros, pourquoi avec un LEFT les NULL n'apparaissent pas !! ca me paraissait pourtant logique que ca s'affiche..

[edmc73]

finalement j'ai opté pour une solution annexe.. certes c'est surment pas optimisé, mais je n'ai pas d'autre solutions !!

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
$req="SELECT id_st,nm_st FROM public.stations WHERE id_zone in($zone_selection) AND tp_st in($typo_selection)";
$res=pg_query($req);
 
//je récupère la liste de toutes les stations concernés par mes choix précédent
 
while ($line = pg_fetch_array($res)) { //tant que y'a des stations, on cherche les valeurs
 
	//echelle qui servira a générer l'axe des X
	FOR($x=0;$x<24;$x++)
		$ech[$x]=$x;
 
	//valeur
	FOR($h=0;$h<24;$h++){
		$req="SELECT valeur FROM public.donnees_heure WHERE date = '$date_selection $h:00:00' AND id_st=".$line[id_st]." AND id_pr=$id_pr";
		$valeur=@pg_fetch_result(pg_query($req),0,0);
 
		$val[]=array(
			"date" => "$date_selection $h:00:00",
			"id_st" => $line[id_st],
			"nm_st" => $line[nm_st],
			"valeur" => $valeur
		);
	}
}

voila, si y'a mieux n'hésitez pas !!
un grand merci déja de m'avoir aider !!