Requête |
with meilleures_pue as
(
with T1 as (
select distinct no_site,no_menage, exp((ln(min_qte_pechee_meil_saison)+ln(max_qte_pechee_meil_saison))/2) as captures
from ci.v_liste_menage where
date_enquete_m is not null and min_qte_pechee_meil_saison>0 and nb_sortie_meilleure_saison<=7
and max_qte_pechee_meil_saison>0 and min_qte_pechee_meil_saison<100 and max_qte_pechee_meil_saison<200
order by captures desc )
select distinct A.region_niv1 as ABSCISSE,avg(captures) as TOTAL from ci.pre_enquete_village A inner join ci.liste_site using (no_enquete)
inner join T1 using (no_site) where captures>0 group by A.region_niv1 )
,
moinsbonne_pue as (
with T1 as ( select distinct no_site,no_menage, exp((ln(min_pue_moins_bonnel_saison)+ln(max_pue_semaine_moins_bonnel_saison))/2)
as captures from ci.v_liste_menage where date_enquete_m is not null and min_pue_moins_bonnel_saison>0
and nb_sortie_meilleure_saison<=7 and max_pue_semaine_moins_bonnel_saison>0 and min_pue_moins_bonnel_saison<100
and max_pue_semaine_moins_bonnel_saison<200 order by captures desc ) select distinct A.region_niv1 as ABSCISSE,avg(captures) as TOTAL
from ci.pre_enquete_village A inner join ci.liste_site using (no_enquete) inner join T1 using (no_site) where captures>0
group by A.region_niv1 )
select distinct ABSCISSE,'PUE meilleure saison' as GROUPE, TOTAL from meilleures_pue
union
select distinct ABSCISSE,'PUE mauvaise saison' as GROUPE, TOTAL
from moinsbonne_pue order BY ABSCISSE
|