= Recette pour calculer les effectifs migrants par espèce =

Retour à [wiki:"Recette SQL"]

[[PageOutline]]

[[BR]]
Note : ces exemples sont compatibles avec la version 0.3 du logiciel ...

[[BR]]
== Somme des effectifs du genre Alose sur notre passe à bassins  ==

(allez voir les codes dans la table ref.tr_taxon_tax)
{{{#!sql
select sum(s.lot_effectif),s.annee,s.mois from(
select *, extract(month from ope_date_debut) as mois,extract(year from ope_date_debut) as annee from  iav.vue_lot_ope_car 
where lot_tax_code='2055'
and ope_dic_identifiant=5
) as s
group by s.annee,s.mois
order by s.annee,s.mois
}}}
||4||1996||4||
||21||1996||5||
||21||1996||6||
||1||1996||7||
||2||1997||3||
||64||1997||4||
||93||1997||5||
||16||1997||6||
||9||1997||7||
||1||1997||9||

[[BR]]
== Somme des effectifs d'anguilles en fonction du devenir de lot entre 2002 et 2006 ==
{{{#!sql
select sum(s.lot_effectif) as Effectif,s.annee as Annee,s.dev_libelle as Devenir from(
select *, extract(year from ope_date_debut) as annee from  iav.vue_lot_ope_car 
where lot_tax_code='2038' --anguille
and ope_dic_identifiant=6 --passe à anguille
and ope_date_debut > '2002-01-01 00:00:00'
and ope_date_debut < '2006-01-01 00:00:00'
) as s
group by s.annee,s.dev_libelle
order by s.annee,s.dev_libelle
}}}
 ||Effectif||Annee||Devenir||
 ||33786||2002||Relâché au droit de la station||
 ||99||2002||Trépassé||
 ||29163||2003||Relâché au droit de la station||
 ||4||2003||Trépassé||
 ||16503||2004||Relâché au droit de la station||
 ||41||2004||Trépassé||
 ||2997||2005||Relâché au droit de la station||
 ||161||2005||Trépassé||
 ||14||2005||||

[[BR]]
== Liste des taxons ayant fréquenté la passe entre 2005 et 2006 ==
{{{#!sql
select distinct on (tax_nom_latin) tax_nom_latin as Taxon from iav.vue_lot_ope_car 
where ope_dic_identifiant=5
and ope_date_debut > '2005-01-01 00:00:00'
and ope_date_debut < '2006-01-01 00:00:00'
order by Taxon
}}}
||Alosa||
||Anguilla anguilla||
||Dicentrarchus labrax||
||Liza ramada||
||Petromyzon marinus||
||Salmo||

[[BR]]
== Comparaison des effectifs de taxons de deux dispositifs de comptage ==
note : j'ai que des anguilles dans le dc6 et le symétrique ne marche pas (il me manque des noms de taxons)
{{{#!sql
Select taxon, effectifdc5,effectifdc6 FROM(
	select tax_nom_latin,
	 sum (lot_effectif) as effectifdc6 
	 FROM iav.vue_lot_ope_car v1
	 WHERE ope_dic_identifiant=6
	 AND ope_date_debut > '2007-01-01 00:00:00'
	 AND ope_date_debut < '2008-01-01 00:00:00'
	 GROUP BY tax_nom_latin) as sub1
FULL OUTER JOIN
	 (select tax_nom_latin as taxon,
	 sum(lot_effectif)  as effectifdc5
	 FROM iav.vue_lot_ope_car v2 
	 WHERE ope_dic_identifiant=5
	 AND ope_date_debut > '2007-01-01 00:00:00'
	 AND ope_date_debut < '2008-01-01 00:00:00'
	 GROUP BY tax_nom_latin) as sub2
On sub2.taxon = sub1.tax_nom_latin;
}}}
||taxon||effectifDC5||effectifDC6||
||Alosa||1112||||
||Anguilla anguilla||28||19652||
||Ctenopharyngodon idellus||8||||
||Cyprinus carpio||6||||
||Petromyzon marinus||115||||
||Salmo||1||||
||Silurus glanis||3||||
||Tinca tinca||1||||


[[BR]]
== Somme des effectifs de civelles sur les deux passes du barrage d'Arzal par mois et années ==
{{{#!sql
/*
requete complete
*/
with lot_ope_detail as (
SELECT t_operation_ope.ope_identifiant, t_lot_lot.lot_identifiant, t_operation_ope.ope_dic_identifiant, 
t_lot_lot.lot_lot_identifiant AS lot_pere, t_operation_ope.ope_date_debut, t_operation_ope.ope_date_fin, 
t_lot_lot.lot_effectif, t_lot_lot.lot_quantite, t_lot_lot.lot_tax_code, t_lot_lot.lot_std_code, tr_taxon_tax.tax_nom_latin, 
tr_stadedeveloppement_std.std_libelle, tr_devenirlot_dev.dev_code, tr_devenirlot_dev.dev_libelle
   FROM iav.t_operation_ope
   JOIN iav.t_lot_lot ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant
   LEFT JOIN ref.tr_typequantitelot_qte ON tr_typequantitelot_qte.qte_code::text = t_lot_lot.lot_qte_code::text
   LEFT JOIN ref.tr_devenirlot_dev ON tr_devenirlot_dev.dev_code::text = t_lot_lot.lot_dev_code::text
   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = t_lot_lot.lot_tax_code::text
   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = t_lot_lot.lot_std_code::text)
select * from lot_ope_detail


/*
Somme des effectifs de civelles sur les deux passes d'Arzal par mois
*/

with lot_ope_detail as (
SELECT t_operation_ope.ope_identifiant, t_lot_lot.lot_identifiant, t_operation_ope.ope_dic_identifiant, 
t_lot_lot.lot_lot_identifiant AS lot_pere, t_operation_ope.ope_date_debut, t_operation_ope.ope_date_fin, 
t_lot_lot.lot_effectif, t_lot_lot.lot_quantite, t_lot_lot.lot_tax_code, t_lot_lot.lot_std_code, tr_taxon_tax.tax_nom_latin, 
tr_stadedeveloppement_std.std_libelle, tr_devenirlot_dev.dev_code, tr_devenirlot_dev.dev_libelle
   FROM iav.t_operation_ope
   JOIN iav.t_lot_lot ON t_lot_lot.lot_ope_identifiant = t_operation_ope.ope_identifiant
   LEFT JOIN ref.tr_typequantitelot_qte ON tr_typequantitelot_qte.qte_code::text = t_lot_lot.lot_qte_code::text
   LEFT JOIN ref.tr_devenirlot_dev ON tr_devenirlot_dev.dev_code::text = t_lot_lot.lot_dev_code::text
   JOIN ref.tr_taxon_tax ON tr_taxon_tax.tax_code::text = t_lot_lot.lot_tax_code::text
   JOIN ref.tr_stadedeveloppement_std ON tr_stadedeveloppement_std.std_code::text = t_lot_lot.lot_std_code::text)

select  sum(s.lot_quantite),s.annee,s.mois from(
select *, extract(month from ope_date_debut) as mois,extract(year from ope_date_debut) as annee  from lot_ope_detail
where lot_tax_code='2038'
and (ope_dic_identifiant=6 or ope_dic_identifiant=12)
and lot_quantite is not null
and lot_std_code='CIV'
) as s
group by s.annee,s.mois
order by s.annee,s.mois

}}}