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

[cedric]

Premiers essais

-- Il ne peut y avoir qu'une seule note pour un barrage et par espèce sinon 
ALTER TABLE geobs.franchissabilite_piscicole add constraint uk_franchissabilite_piscicole UNIQUE(fra_ref_id,fra_esp_id);


-- analyse des problèmes liés au caractère texte de la colonne note
SELECT * from geobs.franchissabilite_piscicole order by fra_id limit 100;
SELECT fra_esp_id,fra_ref_id,CAST(fra_note as numeric) as fra_note from geobs.franchissabilite_piscicole order by fra_id;
-- marche pas

-- recherche des valeurs dupliquées
SELECT * FROM (
	SELECT count(*) as nombre, fra_esp_id,fra_ref_id 
	from geobs.franchissabilite_piscicole
	where fra_note is not null
	group by fra_ref_id,fra_esp_id as sub
	where nombre>1; -- 63 doublons

-- jointure pour voir
SELECT * FROM geobs.obstacle_referentiel o
LEFT JOIN geobs.franchissabilite_piscicole f on fra_ref_id=ref_id
where esp_id=2
where goodproj=TRUE limit 100

[cedric]

-- 1/ CONTRAINTE, ne marche pas 
-- Il ne peut y avoir qu'une seule note pour un barrage et par espèce sinon 
ALTER TABLE geobs.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 geobs.franchissabilite_piscicole
	where fra_note is not null
	group by fra_ref_id,fra_esp_id as sub
	where nombre>1; -- 63 doublons

-- 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 geobs.franchissabilite_piscicole -- 24964
-- 5/ Requête pour ne garder que les bonnes

SELECT * from geobs.franchissabilite_piscicole where fra_id not in(
	SELECT fra_id from (
	SELECT DISTINCT ON (fra_esp_id,fra_ref_id)*
	from geobs.franchissabilite_piscicole) as distinctes)
ORDER BY fra_ref_id;	-- 107 lignes


-- 5/analyse des problèmes liés au caractère texte de la colonne note
SELECT * from geobs.franchissabilite_piscicole order by fra_id limit 100;
SELECT fra_esp_id,fra_ref_id,CAST(fra_note as numeric) as fra_note 
from geobs.franchissabilite_piscicole order by fra_id;

[cedric]

liste des valeurs dupliquées avec même barrage, même espèce et même note, il en reste une non sélectionnée dans la base

[cedric]

Selection des valeurs identiques et suppression des vrais doublons

-- 1/ CONTRAINTE, ne marche pas 
-- Il ne peut y avoir qu'une seule note pour un barrage et par espèce sinon 
ALTER TABLE geobs.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 geobs.franchissabilite_piscicole
	where fra_note is not null
	group by fra_ref_id,fra_esp_id as sub
	where nombre>1; -- 63 doublons

-- 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 geobs.franchissabilite_piscicole -- 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 geobs.franchissabilite_piscicole) as distinctes)
ORDER BY fra_ref_id;	-- 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 geobs.franchissabilite_piscicole where fra_id not in(
		SELECT fra_id from (
		SELECT DISTINCT ON (fra_esp_id,fra_ref_id)*
		from geobs.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 geobs.franchissabilite_piscicole where fra_ref_id in(
	SELECT fra_ref_id from geobs.franchissabilite_piscicole where fra_id not in(
		SELECT fra_id from (
		SELECT DISTINCT ON (fra_esp_id,fra_ref_id)*
		from geobs.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) )--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

[celine]

-- 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; -- 63 doublons

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

[cedric]

Milestone Data integration deleted