fonction SQL (avec timestamp) pour sommer des quantités de pluies.

liofer · 11/03/2014 20:49:16

Membre

Bonjour,

Je cherche à écrire une fonction SQL qui me permettrait de sommer des quantités de pluies à un pas de temps horaires.

Je m'explique, à la base, j'ai un fichier .csv dans lequel j'ai une colonne de temps (du timestamp) toutes les 5, 6 ou 30min sur une année et une colonne de pluie (en mm). La fonction devrait permettre de calculer un intervalle de temps 1h et de sommer les quantités de pluies correspondantes à cet intervalle.

Exemple :
Timestamp Pluie (mm)
01/01/2000 09:00 0.00
01/01/2000 09:30 0.20
01/01/2000 09:36 0.00
01/01/2000 09:42 0.50 En sortie, 01/01/2000 09:00 et 10:00 1.00 mm
01/01/2000 09:48 0.10
01/01/2000 09:54 0.20
01/01/2000 10:00 0.00
...

C'est possible d'avoir un coup de main,
Merci bcp !

arthurr · 12/03/2014 14:44:25

bonjour,
double post ?
double réponse

select date_trunc('hour', la_colonne_avec_le_timestamp), sum(la_colonne_pluie) from votre_table group by 1 order by 1;

liofer · 12/03/2014 15:42:19

Merci je la teste en ce moment même !
J'ai juste une question tout fonctionne mais la somme de 9h à 10h il me l'écrit pour 09h alors que la somme doit être pour 10h. Une idée pour changer l'écriture stp ?

arthurr · 12/03/2014 16:02:23

Pour avoir date début / date fin :

select date_trunc('hour', la_colonne_avec_le_timestamp), date_trunc('hour', la_colonne_avec_le_timestamp) + interval '1 hours', sum(la_colonne_pluie) from votre_table group by 1,2 order by 1;

liofer · 12/03/2014 17:54:50

Merci, pour l'aide.

Dernière question du coup : la somme de fait de 9h à 9h54 (tjr pour mon exemple) mais je souhaiterai que le 10h soit inclus dans la somme et pas le 9h, c'est possible ?

Timestamp Pluie (mm)
01/01/2000 09:00 0.80
01/01/2000 09:30 0.20
01/01/2000 09:36 0.00
01/01/2000 09:42 0.50 En sortie, 01/01/2000 10:00 1.20 mm
01/01/2000 09:48 0.10
01/01/2000 09:54 0.20
01/01/2000 10:00 0.20

liofer · 13/03/2014 16:04:10

L'écriture marche très bien mais toujours ce problème de somme qui ne se fait pas sur l'intervalle de temps correcte. Si jamais quelqu'un a une idée de comment faire !?

Merci d'avance !

arthurr · 13/03/2014 16:15:57

Les données :

test=# select * from pluie;
       la_date       | val  
---------------------+------
 2000-01-01 09:00:00 | 0.10
 2000-01-01 09:30:00 | 0.20
 2000-01-01 09:42:00 | 0.50
 2000-01-01 09:54:00 | 0.20
 2000-01-01 10:00:00 | 1.00
(5 rows)

La requête :

test=# select date_trunc('hour', new_date), date_trunc('hour', new_date + interval '1 hours'), sum(val) from (select case when date_trunc('hour', la_date) = la_date THEN la_date - interval '1 minutes' ELSE la_date END as new_date, val from pluie) as foo group by 1,2 order by 1;
     date_trunc      |     date_trunc      | sum  
---------------------+---------------------+------
 2000-01-01 08:00:00 | 2000-01-01 09:00:00 | 0.10
 2000-01-01 09:00:00 | 2000-01-01 10:00:00 | 1.90
(2 rows)

C'est ça ?

liofer · 13/03/2014 16:51:23

le tableau final c'est exactement ce qu'il faut mais je n'arrive pas à écrire la requête sans erreur les deux heures ne doivent pas forcément être écrite au moins la supérieure.

horodate | raw_value
------------------- ------+------
2000-01-01 09:00:00 | 0.10
2000-01-01 09:30:00 | 0.20
2000-01-01 09:42:00 | 0.50
2000-01-01 09:54:00 | 0.20
2000-01-01 10:00:00 | 1.00

Encore merci !

arthurr · 13/03/2014 17:17:28

Il reste juste à remplacer ???NOM_DE_LA_TABLE¿¿¿ par le nom de votre table

SELECT 
	DATE_TRUNC('hour', new_date), -- date de début
	DATE_TRUNC('hour', new_date + INTERVAL '1 hours'), -- date de fin
	SUM(raw_value) 
FROM 
	(SELECT 
		CASE WHEN 
			DATE_TRUNC('hour', horodate) = horodate THEN horodate - INTERVAL '1 minutes' 
		ELSE 
			horodate 
		END AS new_date, 
		raw_value 
	FROM 
		???NOM_DE_LA_TABLE¿¿¿) AS foo 
GROUP BY 
	1,2 
ORDER BY 
	1;

liofer · 13/03/2014 18:19:13

Merci bcp du temps que tu m'as accordé, ça marche nickel

Problème résolu !!!!

Encore merci !

Geo-x · 14/03/2014 16:21:03

Bonjour à tous.

Dans une catégorie à peu près similaire, j'essaie de connaitre les dates communes entre plusieurs périodes, je m'explique.

J'ai 3 colonnes :

date_debut | date_fin
-----------------------------
2014-02-21 | 2014-02-25
2014-02-20 | 2014-02-22
2014-02-26 | 2014-02-27

Je souhaiterais savoir combien il y a de lignes pour des périodes communes, ce qui me donnerait au final

date_debut | date_fin | nombre_periode
--------------------------------------------------
2014-02-21 | 2014-02-25 | 2
2014-02-20 | 2014-02-23 | 2
2014-02-26 | 2014-02-27 | 1

Période commune les 21/04/2014, 22/04/2014 et 23/04/2014

C'est possible ce genre de chose? je n'ai rien trouvé qui aille dans ce sens...

Geo-x

arthurr · 14/03/2014 16:33:01

désolé, mais je ne comprends pas

rjuju · 14/03/2014 16:33:22

Il manque la 3ème colonne dans votre exemple. Je vous conseillerais de regarder du côté des types range (http://docs.postgresql.fr/9.3/rangetypes.html), et de l'opérateur de chevauchement (&&).

Geo-x · 14/03/2014 16:40:14

Mouais je me demandais si j'étais très clair, j'ai la réponse ;-)

Mais rjuju m'a donné une bonne piste avec ces intervalles que je ne connaissais pas, je vais jeter un coup d'oeil !

Grossomodo, la question est, pour chaque période donné (donc pour chaque ligne), combien de période se chevauchent...

Mouais, pas sûr d'être très clair non plus...

Geo-x · 14/03/2014 16:51:01

Et mince...je suis en postgres 9.1 ...

arthurr · 14/03/2014 17:56:21

de ce que je vois :

date_debut  | date_fin
-----------------------------
2014-02-21 | 2014-02-25
2014-02-20 | 2014-02-22
2014-02-26 | 2014-02-27

la premiere se chevauche avec la seconde et la dernière avec personne.
donc :

date_debut  | date_fin
-----------------------------
2014-02-21 | 2014-02-25 -> 1
2014-02-20 | 2014-02-22 -> 1
2014-02-26 | 2014-02-27 -> 0

c'est ça ?

Geo-x · 14/03/2014 17:58:02

Oui c'est exactement ça, mais j'ai mis deux car il y a bien deux périodes de réservation qui se chevauchent. Mais en effet, l'important est de savoir si oui ou non il y a d'autre date qui se chevauche et si oui combien.

arthurr · 14/03/2014 18:19:55

OK !

donc mon jeux de test :

create table the_range(id_the_range integer,dt_deb date, dt_fin date);
insert into the_range values(1,'2014-02-21', '2014-02-25');
insert into the_range values(2,'2014-02-20', '2014-02-22');
insert into the_range values(3,'2014-02-26', '2014-02-27');

la requête :

SELECT
	t.id_the_range,
	dt_deb,
	dt_fin,
	(
		SELECT 
			count(*)::text || ' : ' || coalesce(string_agg(id_the_range::text,','),'NA') 
		FROM 
			the_range 
		WHERE
			t.id_the_range != id_the_range AND
			(
				(t.dt_deb>=dt_deb AND t.dt_deb<=dt_fin) OR
				(t.dt_deb<=dt_deb AND t.dt_fin>=dt_deb)
			)
	) as count_and_liste_id
FROM
	the_range t
ORDER BY 1

résultat :

 id_the_range |   dt_deb   |   dt_fin   | count_and_liste_id 
--------------+------------+------------+--------------------
            1 | 2014-02-21 | 2014-02-25 | 1 : 2
            2 | 2014-02-20 | 2014-02-22 | 1 : 1
            3 | 2014-02-26 | 2014-02-27 | 0 : NA
(3 rows)

count_and_liste_id -> count = 1 et 2 est l'id qui le chevauche

vu de loin les 2 clause (t.dt_deb>=dt_deb AND t.dt_deb<=dt_fin) OR (t.dt_deb<=dt_deb AND t.dt_fin>=dt_deb) me semblent couvrir tous les cas, mais je peux me tromper !

Dernière modification par arthurr (14/03/2014 18:29:09)

Geo-x · 14/03/2014 18:36:18

CQFD

C'est diaboliquement efficace, j'aime les choses compliquées qui semblent si simple !

Un grand merci arthurr, ça va au-delà de mes espérances avec la récupération des identifiants.

Je ne vais pas oser parler de ce vers quoi j'étais partie, c'est à dire, une usine à gaz...

Geo-x

ameli · 13/08/2014 14:40:42

Oh, je pense que votre idée est très bonne, et l'idée est réalisable

----------------------------------------------------------------------------------------
etui sony xperia z2

Dernière modification par ameli (14/08/2014 02:48:43)

Forums PostgreSQL.fr

#1 11/03/2014 20:49:16

fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#2 12/03/2014 14:44:25

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#3 12/03/2014 15:42:19

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#4 12/03/2014 16:02:23

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#5 12/03/2014 17:54:50

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#6 13/03/2014 16:04:10

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#7 13/03/2014 16:15:57

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#8 13/03/2014 16:51:23

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#9 13/03/2014 17:17:28

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#10 13/03/2014 18:19:13

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#11 14/03/2014 16:21:03

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#12 14/03/2014 16:33:01

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#13 14/03/2014 16:33:22

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#14 14/03/2014 16:40:14

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#15 14/03/2014 16:51:01

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#16 14/03/2014 17:56:21

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#17 14/03/2014 17:58:02

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#18 14/03/2014 18:19:55

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#19 14/03/2014 18:36:18

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

#20 13/08/2014 14:40:42

Re : fonction SQL (avec timestamp) pour sommer des quantités de pluies.

Pied de page des forums