Forums PostgreSQL.fr

liofer

Membre

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

Membre

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

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

Membre

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

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

Membre

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

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

Membre

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

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

Membre

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

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

Membre

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

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

Membre

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

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

Membre

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

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

Membre

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

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

Membre

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

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

Problème résolu !!!!

Encore merci !

Geo-x

Membre

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

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

Membre

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

désolé, mais je ne comprends pas

rjuju

Administrateur

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

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 (&&).

---

Julien.
https://rjuju.github.io/

Geo-x

Membre

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

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

Membre

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

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

arthurr

Membre

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

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

Membre

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

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

Membre

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

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

Membre

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

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

Membre

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

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)