Skip to content
Snippets Groups Projects

Logements modif

Merged Logements modif
logements_modif into master
Merged
Benoît Courtyrequested to merge
logements_modif into master
Viewing commit 5b9ba2d1
Prev Next
Show latest version
1 file
+ 27
− 0
Compare changes
  • Side-by-side
  • Inline
notebooks/requete 0 → 100644
+ 27
− 0
 
SELECT classe_ges, coalesce(a.annee_2013, 0) as annee_2013, coalesce(a.annee_2014, 0) as annee_2014, coalesce(a.annee_2015, 0) as annee_2015,
 
coalesce(a.annee_2016, 0) as annee_2016, coalesce(a.annee_2017, 0) as annee_2017, coalesce(a.annee_2018, 0) as annee_2018,
 
coalesce(a.annee_2019, 0) as annee_2019, coalesce(a.annee_2020, 0) as annee_2020, coalesce(a.annee_2021, 0) as annee_2021
 
FROM (
 
select * from crosstab(
 
$$SELECT d.classe_estimation_ges, LEFT(d.date_etablissement_dpe,4) as annee_etablissement_DPE,
 
coalesce(COUNT(DISTINCT d.numero_dpe), 0) as nb_ges
 
FROM ademe_dpe_logements as d
 
LEFT JOIN zone_circo c
 
ON st_intersects(c.wkb_geometry, d.geom)
 
WHERE c.ref = '015-02' OR (d.geom IS NULL AND (d.code_insee_commune_actualise in ('15025','15001','15002','15003','15004','15005','15006','15007','15008','15009','15013','15015','15018','15019','15022','15024','15026','15032','15033','15034','15036','15037','15038','15040','15043','15045','15046','15049','15051','15052','15053','15054','15055','15059','15060','15061','15063','15064','15065','15069','15070','15073','15077','15078','15079','15080','15041','15042','15128','15091','15097','15241','15098','15100','15101','15102','15050','15066','15067','15131','15235','15249','15261','15106','15107','15110','15111','15112','15114','15116','15119','15120','15121','15123','15124','15125','15126','15127','15129','15130','15137','15138','15139','15141','15142','15148','15149','15151','15152','15153','15155','15158','15161','15162','15164','15168','15169','15170','15173','15174','15186','15198','15185','15187','15188','15190','15199','15200','15201','15202','15203','15205','15206','15207','15208','15209','15213','15216','15218','15219','15220','15223','15225','15229','15230','15231','15232','15237','15240','15243','15244','15245','15108','15246','15247','15248','15250','15251','15253','15254','15256','15259','15262','15263','15265','15010','15020','15086','15092','15105','15132','15176','15048')))
 
AND LEFT(d.date_etablissement_dpe,4) between '2013' and '2021'
 
AND d.classe_estimation_ges IN ('A', 'B', 'C', 'D', 'E', 'F', 'G')
 
GROUP BY 1,2 ORDER BY 1,2$$,
 
$$SELECT generate_series(2013,2021)$$
 
) as T (
 
classe_ges text,
 
"annee_2013" int,
 
"annee_2014" int,
 
"annee_2015" int,
 
"annee_2016" int,
 
"annee_2017" int,
 
"annee_2018" int,
 
"annee_2019" int,
 
"annee_2020" int,
 
"annee_2021" int
 
)) as a;
 
\ No newline at end of file