back to first page [..] [[BR]]
back to ["CookBook Eda"] [[BR]]
back to ["Obstacle pressure"] [[BR]]
back to ["CookBook join ROE_CCM"][[BR]]

== Table franchissabilite ==
== Build a view allowing to know the score of the different dams ==
in #46 [[BR]]

-- 1/ CONTRAINTE, ne marche pas
-- Il ne peut y avoir qu'une seule note pour un barrage et par espèce sinon
{{{
ALTER TABLE geobs2010.franchissabilite_piscicole add constraint uk_franchissabilite_piscicole UNIQUE(fra_ref_id,fra_esp_id);
}}}

-- 2/ recherche des valeurs dupliquées
{{{
SELECT * FROM (

    SELECT count(*) as nombre, fra_esp_id,fra_ref_id
    from geobs2010.franchissabilite_piscicole
    where fra_note is not null
    group by fra_ref_id,fra_esp_id) as sub
    where nombre>1; -- 62 doublons (dont un avec 4 données identiques)
}}}

-- 3/Nombre de valeurs distinctes
{{{
SELECT count(*) from (
SELECT DISTINCT ON (fra_esp_id,fra_ref_id)*
from geobs.franchissabilite_piscicole

    where fra_note is not null) as sub; -- 23461
}}}

-- 4/ nombre de valeurs totales
{{{
select count (*) from geobs2010.franchissabilite_piscicole -- 26510 (ancienne version : 24964)
}}}

-- 5/ Requête pour selectionner les identifiants des valeurs dupliquées
{{{
SELECT * from geobs.franchissabilite_piscicole where fra_id not in(

    SELECT fra_id from (
    SELECT DISTINCT ON (fra_esp_id,fra_ref_id)*
    from geobs2010.franchissabilite_piscicole) as distinctes)

ORDER BY fra_ref_id; -- 493 lignes (ancienne version : 107 lignes)
}}}

-- 6/ Requète pour sélectionner toutes les valeurs dupliquées
{{{
select * from geobs.franchissabilite_piscicole where fra_ref_id in(

    SELECT fra_ref_id from geobs2010.franchissabilite_piscicole where fra_id not in(

        SELECT fra_id from (
        SELECT DISTINCT ON (fra_esp_id,fra_ref_id)*
        from geobs2010.franchissabilite_piscicole) as distinctes)

)
ORDER BY fra_ref_id, fra_esp_id;
-- 7/ je crée une table contenant barrages avec plus que une seule note pour la même espèce
drop table if exists duplicated;
create temporary table duplicated as(
select *from geobs2010.franchissabilite_piscicole where fra_ref_id in(

    SELECT fra_ref_id from geobs2010.franchissabilite_piscicole where fra_id not in(

        SELECT fra_id from (
        SELECT DISTINCT ON (fra_esp_id,fra_ref_id)*
        from geobs2010.franchissabilite_piscicole) as distinctes)

)
ORDER BY fra_ref_id, fra_esp_id);
-- 8/ Liste de tous les dupliqués avec la même note pour la même espèce
drop table if exists identiques;
create temporary table identiques as(
select * from duplicated where (fra_ref_id, fra_esp_id,fra_note) IN

    (SELECT fra_ref_id, fra_esp_id,fra_note FROM duplicated
    group by fra_ref_id, fra_esp_id,fra_note
    HAVING COUNT(*) > 1) )-- 86 (ancienne version :115)
}}}

-- 9/ Je veux n'en garder qu'un donc avoir la liste des autres à virer pour sûr, je choisis un
-- id au hasard avec distinct on et je vais demander de virer les autres
{{{
select * from identiques where fra_id not in(
select distinct on (fra_ref_id,fra_esp_id,fra_note) fra_id
from identiques) -- 59 lignes à dégager, 56 gardées
}}}