Requête de Marion Legrand (Merci Marion !)

--#################################################
--## Requête sur bd_contmig_nat - schéma logrami ##
--## Auteur : Marion Legrand - LOGRAMI           ##
--#################################################

--Spécifie le schéma par défaut où aller chercher les tables appelées dans les requêtes
--(évite de remettre le nom du schéma à chaque fois) changer en fonction de votre schéma
SET search_path TO logrami, public;

--Création d'une vue pour voir les poissons sans adipeuse
--      1) vue sans lot_pere
DROP VIEW if exists logrami.vue_lot_car_mqe; 
CREATE VIEW logrami.vue_lot_car_mqe AS
Select  s.sta_nom, s.sta_coordonnee_x,s.sta_coordonnee_y,
        df.dif_code, df.dif_orientation,
        dc.dic_code,tdc.tdc_libelle,
        op.ope_date_debut,op.ope_date_fin,op.ope_organisme,
        l.lot_identifiant,l.lot_tax_code,t.tax_nom_latin,t.tax_nom_commun,l.lot_std_code,st.std_libelle,l.lot_effectif,l.lot_quantite,l.lot_methode_obtention,l.lot_dev_code,d.dev_libelle,
        a.act_action,
        m.mqe_nmq_code,nmq.nmq_libelle,m.mqe_loc_code,loc.loc_libelle,
        c.car_par_code,par.par_nom,c.car_valeur_quantitatif,c.car_val_identifiant,c.car_precision
From t_station_sta s
join t_ouvrage_ouv o on (s.sta_code=o.ouv_sta_code)
join t_dispositiffranchissement_dif df on (df.dif_ouv_identifiant=o.ouv_identifiant)
join t_dispositifcomptage_dic dc on (dc.dic_dif_identifiant=df.dif_dis_identifiant)
join ref.tr_typedc_tdc tdc on (tdc.tdc_code=dc.dic_tdc_code)
join t_operation_ope op on (op.ope_dic_identifiant = dc.dic_dis_identifiant)
join t_lot_lot l on (l.lot_ope_identifiant = op.ope_identifiant)
JOIN ref.tr_taxon_tax t ON (t.tax_code::text = l.lot_tax_code::text)
JOIN ref.tr_stadedeveloppement_std st on (st.std_code=l.lot_std_code)
JOIN ref.tr_devenirlot_dev d on (d.dev_code=l.lot_dev_code)
left join tj_actionmarquage_act a on (a.act_lot_identifiant=l.lot_identifiant)
left join t_marque_mqe m on (m.mqe_reference=a.act_mqe_reference) 
left join ref.tr_naturemarque_nmq nmq on (nmq.nmq_code=m.mqe_nmq_code)
left join ref.tr_localisationanatomique_loc loc on (loc.loc_code=m.mqe_loc_code)
left join tj_caracteristiquelot_car c on (c.car_lot_identifiant=l.lot_identifiant)
left join ref.tg_parametre_par par on (par.par_code=c.car_par_code)

--      2) Requête avec lot_pere
DROP VIEW if exists logrami.vue_lot_car_mqe_pere; 
CREATE VIEW logrami.vue_lot_car_mqe_pere AS
Select  s.sta_nom, s.sta_coordonnee_x,s.sta_coordonnee_y,
        df.dif_code, df.dif_orientation,
        dc.dic_code,tdc.tdc_libelle,
        op.ope_date_debut,op.ope_date_fin,op.ope_organisme,
        l.lot_identifiant,l.lot_tax_code,t.tax_nom_latin,t.tax_nom_commun,l.lot_std_code,st.std_libelle,l.lot_effectif,l.lot_quantite,l.lot_methode_obtention,l.lot_dev_code,d.dev_libelle,
        l.lot_lot_identifiant AS lot_pere, lot_pere.lot_effectif AS lot_pere_effectif, lot_pere.lot_quantite AS lot_pere_quantite, dev_pere.dev_code AS lot_pere_dev_code, 
        dev_pere.dev_libelle AS lot_pere_dev_libelle, parqual.par_nom AS lot_pere_par_nom, parqual.car_par_code AS lot_pere_par_code, parqual.car_methode_obtention AS 
        lot_pere_car_methode_obtention, parqual.car_val_identifiant AS lot_pere_val_identifiant, parqual.val_libelle AS lot_pere_val_libelle,
        a.act_action,
        m.mqe_nmq_code,nmq.nmq_libelle,m.mqe_loc_code,loc.loc_libelle,
        c.car_par_code,par.par_nom,c.car_valeur_quantitatif,c.car_val_identifiant,c.car_precision
From t_station_sta s
join t_ouvrage_ouv o on (s.sta_code=o.ouv_sta_code)
join t_dispositiffranchissement_dif df on (df.dif_ouv_identifiant=o.ouv_identifiant)
join t_dispositifcomptage_dic dc on (dc.dic_dif_identifiant=df.dif_dis_identifiant)
join ref.tr_typedc_tdc tdc on (tdc.tdc_code=dc.dic_tdc_code)
join t_operation_ope op on (op.ope_dic_identifiant = dc.dic_dis_identifiant)
join t_lot_lot l on (l.lot_ope_identifiant = op.ope_identifiant)
JOIN ref.tr_taxon_tax t ON (t.tax_code::text = l.lot_tax_code::text)
JOIN ref.tr_stadedeveloppement_std st on (st.std_code=l.lot_std_code)
JOIN ref.tr_devenirlot_dev d on (d.dev_code=l.lot_dev_code)
left join tj_actionmarquage_act a on (a.act_lot_identifiant=l.lot_identifiant)
left join t_marque_mqe m on (m.mqe_reference=a.act_mqe_reference) 
left join ref.tr_naturemarque_nmq nmq on (nmq.nmq_code=m.mqe_nmq_code)
left join ref.tr_localisationanatomique_loc loc on (loc.loc_code=m.mqe_loc_code)
left join tj_caracteristiquelot_car c on (c.car_lot_identifiant=l.lot_identifiant)
left join ref.tg_parametre_par par on (par.par_code=c.car_par_code)
LEFT JOIN logrami.t_lot_lot lot_pere ON lot_pere.lot_identifiant = l.lot_lot_identifiant
LEFT JOIN ref.tr_devenirlot_dev dev_pere ON dev_pere.dev_code::text = lot_pere.lot_dev_code::text
LEFT JOIN ( SELECT car_pere.car_lot_identifiant, car_pere.car_par_code, car_pere.car_methode_obtention, car_pere.car_val_identifiant, car_pere.car_valeur_quantitatif, car_pere.car_precision, car_pere.car_commentaires, par_pere.par_code, par_pere.par_nom, par_pere.par_unite, par_pere.par_nature, par_pere.par_definition, qal_pere.qal_par_code, qal_pere.qal_valeurs_possibles, val_pere.val_identifiant, val_pere.val_qal_code, val_pere.val_rang, val_pere.val_libelle
   FROM logrami.tj_caracteristiquelot_car car_pere
   LEFT JOIN ref.tg_parametre_par par_pere ON car_pere.car_par_code::text = par_pere.par_code::text
   JOIN ref.tr_parametrequalitatif_qal qal_pere ON qal_pere.qal_par_code::text = par_pere.par_code::text
   LEFT JOIN ref.tr_valeurparametrequalitatif_val val_pere ON car_pere.car_val_identifiant = val_pere.val_identifiant) parqual ON parqual.car_lot_identifiant = lot_pere.lot_identifiant;

--Comptage des saumons avec et sans adipeuse en fonction de la station et de l'année
Select nom,annee,sum(a) as sans_adipeuse,sum(b) as avec_adipeuse
from(
select  v.sta_nom as nom,
        extract(year from v.ope_date_debut)as annee,
        CASE WHEN v.act_action is not null THEN sum(v.lot_effectif)
                ELSE 0 END AS a ,
        CASE WHEN v.act_action is null THEN sum(v.lot_effectif)
                ELSE 0 END AS b
from vue_lot_car_mqe v 
where v.lot_tax_code like '2220' and std_libelle like 'Adulte'
group by 1,2,v.act_action
) as toto
group by 1,2
order by 1,2