back to first page ..
back to CookBook Eda
back to Obstacle pressure
back to CookBook join ROE_CCM

Table franchissabilite

Build a view allowing to know the score of the different dams

in #46

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

-- 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 lines

-- 6/ Requète pour sélectionner toutes les valeurs dupliquées --320 values

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

-- 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) -- 58 lines to delete, 55 lines to keep