Recette pour calculer les effectifs migrants par espèce

Retour à Recette SQL

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

Somme des effectifs du genre Alose sur notre passe à bassins

(allez voir les codes dans la table ref.tr_taxon_tax)

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

Somme des effectifs d'anguilles en fonction du devenir de lot entre 2002 et 2006

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

Liste des taxons ayant fréquenté la passe entre 2005 et 2006

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

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)

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

Somme des effectifs de civelles sur les deux passes du barrage d'Arzal par mois et années

/*
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