| | 1 | Requête de Marion Legrand (Merci Marion !) |
| | 2 | |
| | 3 | {{{ |
| | 4 | #!sql |
| | 5 | --################################################# |
| | 6 | --## Requête sur bd_contmig_nat - schéma logrami ## |
| | 7 | --## Auteur : Marion Legrand - LOGRAMI ## |
| | 8 | --################################################# |
| | 9 | |
| | 10 | --Spécifie le schéma par défaut où aller chercher les tables appelées dans les requêtes |
| | 11 | --(évite de remettre le nom du schéma à chaque fois) changer en fonction de votre schéma |
| | 12 | SET search_path TO logrami, public; |
| | 13 | |
| | 14 | --Création d'une vue pour voir les poissons sans adipeuse |
| | 15 | -- 1) vue sans lot_pere |
| | 16 | DROP VIEW if exists logrami.vue_lot_car_mqe; |
| | 17 | CREATE VIEW logrami.vue_lot_car_mqe AS |
| | 18 | Select s.sta_nom, s.sta_coordonnee_x,s.sta_coordonnee_y, |
| | 19 | df.dif_code, df.dif_orientation, |
| | 20 | dc.dic_code,tdc.tdc_libelle, |
| | 21 | op.ope_date_debut,op.ope_date_fin,op.ope_organisme, |
| | 22 | 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, |
| | 23 | a.act_action, |
| | 24 | m.mqe_nmq_code,nmq.nmq_libelle,m.mqe_loc_code,loc.loc_libelle, |
| | 25 | c.car_par_code,par.par_nom,c.car_valeur_quantitatif,c.car_val_identifiant,c.car_precision |
| | 26 | From t_station_sta s |
| | 27 | join t_ouvrage_ouv o on (s.sta_code=o.ouv_sta_code) |
| | 28 | join t_dispositiffranchissement_dif df on (df.dif_ouv_identifiant=o.ouv_identifiant) |
| | 29 | join t_dispositifcomptage_dic dc on (dc.dic_dif_identifiant=df.dif_dis_identifiant) |
| | 30 | join ref.tr_typedc_tdc tdc on (tdc.tdc_code=dc.dic_tdc_code) |
| | 31 | join t_operation_ope op on (op.ope_dic_identifiant = dc.dic_dis_identifiant) |
| | 32 | join t_lot_lot l on (l.lot_ope_identifiant = op.ope_identifiant) |
| | 33 | JOIN ref.tr_taxon_tax t ON (t.tax_code::text = l.lot_tax_code::text) |
| | 34 | JOIN ref.tr_stadedeveloppement_std st on (st.std_code=l.lot_std_code) |
| | 35 | JOIN ref.tr_devenirlot_dev d on (d.dev_code=l.lot_dev_code) |
| | 36 | left join tj_actionmarquage_act a on (a.act_lot_identifiant=l.lot_identifiant) |
| | 37 | left join t_marque_mqe m on (m.mqe_reference=a.act_mqe_reference) |
| | 38 | left join ref.tr_naturemarque_nmq nmq on (nmq.nmq_code=m.mqe_nmq_code) |
| | 39 | left join ref.tr_localisationanatomique_loc loc on (loc.loc_code=m.mqe_loc_code) |
| | 40 | left join tj_caracteristiquelot_car c on (c.car_lot_identifiant=l.lot_identifiant) |
| | 41 | left join ref.tg_parametre_par par on (par.par_code=c.car_par_code) |
| | 42 | |
| | 43 | -- 2) Requête avec lot_pere |
| | 44 | DROP VIEW if exists logrami.vue_lot_car_mqe_pere; |
| | 45 | CREATE VIEW logrami.vue_lot_car_mqe_pere AS |
| | 46 | Select s.sta_nom, s.sta_coordonnee_x,s.sta_coordonnee_y, |
| | 47 | df.dif_code, df.dif_orientation, |
| | 48 | dc.dic_code,tdc.tdc_libelle, |
| | 49 | op.ope_date_debut,op.ope_date_fin,op.ope_organisme, |
| | 50 | 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, |
| | 51 | 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, |
| | 52 | 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 |
| | 53 | lot_pere_car_methode_obtention, parqual.car_val_identifiant AS lot_pere_val_identifiant, parqual.val_libelle AS lot_pere_val_libelle, |
| | 54 | a.act_action, |
| | 55 | m.mqe_nmq_code,nmq.nmq_libelle,m.mqe_loc_code,loc.loc_libelle, |
| | 56 | c.car_par_code,par.par_nom,c.car_valeur_quantitatif,c.car_val_identifiant,c.car_precision |
| | 57 | From t_station_sta s |
| | 58 | join t_ouvrage_ouv o on (s.sta_code=o.ouv_sta_code) |
| | 59 | join t_dispositiffranchissement_dif df on (df.dif_ouv_identifiant=o.ouv_identifiant) |
| | 60 | join t_dispositifcomptage_dic dc on (dc.dic_dif_identifiant=df.dif_dis_identifiant) |
| | 61 | join ref.tr_typedc_tdc tdc on (tdc.tdc_code=dc.dic_tdc_code) |
| | 62 | join t_operation_ope op on (op.ope_dic_identifiant = dc.dic_dis_identifiant) |
| | 63 | join t_lot_lot l on (l.lot_ope_identifiant = op.ope_identifiant) |
| | 64 | JOIN ref.tr_taxon_tax t ON (t.tax_code::text = l.lot_tax_code::text) |
| | 65 | JOIN ref.tr_stadedeveloppement_std st on (st.std_code=l.lot_std_code) |
| | 66 | JOIN ref.tr_devenirlot_dev d on (d.dev_code=l.lot_dev_code) |
| | 67 | left join tj_actionmarquage_act a on (a.act_lot_identifiant=l.lot_identifiant) |
| | 68 | left join t_marque_mqe m on (m.mqe_reference=a.act_mqe_reference) |
| | 69 | left join ref.tr_naturemarque_nmq nmq on (nmq.nmq_code=m.mqe_nmq_code) |
| | 70 | left join ref.tr_localisationanatomique_loc loc on (loc.loc_code=m.mqe_loc_code) |
| | 71 | left join tj_caracteristiquelot_car c on (c.car_lot_identifiant=l.lot_identifiant) |
| | 72 | left join ref.tg_parametre_par par on (par.par_code=c.car_par_code) |
| | 73 | LEFT JOIN logrami.t_lot_lot lot_pere ON lot_pere.lot_identifiant = l.lot_lot_identifiant |
| | 74 | LEFT JOIN ref.tr_devenirlot_dev dev_pere ON dev_pere.dev_code::text = lot_pere.lot_dev_code::text |
| | 75 | 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 |
| | 76 | FROM logrami.tj_caracteristiquelot_car car_pere |
| | 77 | LEFT JOIN ref.tg_parametre_par par_pere ON car_pere.car_par_code::text = par_pere.par_code::text |
| | 78 | JOIN ref.tr_parametrequalitatif_qal qal_pere ON qal_pere.qal_par_code::text = par_pere.par_code::text |
| | 79 | 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; |
| | 80 | |
| | 81 | --Comptage des saumons avec et sans adipeuse en fonction de la station et de l'année |
| | 82 | Select nom,annee,sum(a) as sans_adipeuse,sum(b) as avec_adipeuse |
| | 83 | from( |
| | 84 | select v.sta_nom as nom, |
| | 85 | extract(year from v.ope_date_debut)as annee, |
| | 86 | CASE WHEN v.act_action is not null THEN sum(v.lot_effectif) |
| | 87 | ELSE 0 END AS a , |
| | 88 | CASE WHEN v.act_action is null THEN sum(v.lot_effectif) |
| | 89 | ELSE 0 END AS b |
| | 90 | from vue_lot_car_mqe v |
| | 91 | where v.lot_tax_code like '2220' and std_libelle like 'Adulte' |
| | 92 | group by 1,2,v.act_action |
| | 93 | ) as toto |
| | 94 | group by 1,2 |
| | 95 | order by 1,2 |
| | 96 | }}} |
