Forums PostgreSQL.fr

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');

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

 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)

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;

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;

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

#/bin/bash

# If you have root access you can rename this file to pgmodeler.sh and put it on
# /etc/profile.d (if you system supports this technique).

# If you do not have root access you can change this file and install it
# as a .bashrc or .bash_profile.

# Specify here the full path to the pgmodeler's root directory
export PGMODELER_ROOT="/home/pri/pgmodeler"

export PGMODELER_CONF_DIR="$PGMODELER_ROOT/conf"
export PGMODELER_SCHEMAS_DIR="$PGMODELER_ROOT/schemas"
export PGMODELER_LANG_DIR="$PGMODELER_ROOT/lang"
export PGMODELER_TMP_DIR="$PGMODELER_ROOT/tmp"
export PGMODELER_PLUGINS_DIR="$PGMODELER_ROOT/plugins"
export PGMODELER_CHANDLER_PATH="$PGMODELER_ROOT/pgmodeler-ch"
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:"$PGMODELER_ROOT"
export PATH=$PATH:$PGMODELER_ROOT

SELECT 
  a.insee AS commune, 
  count(distinct b.gid) AS nb_vanne, 
  count(distinct c.gid) AS nb_ventouse

FROM commune a
  LEFT JOIN vanne b ON a.insee = b.insee
  LEFT JOIN ventouse c ON a.insee = c.insee

WHERE a.insee = '86000'

GROUP BY a.insee ;