Bonjour,

j'ai une base de donnée d'adresses postale sur sqlite, avec plusieurs tables, dont les lignes peuvent avoir des quasi doublons entre elles.

il s'agit d'un fichier invitation d'un théâtre dans lequel par exemple nous avons les coordonnées des abonnés de 2019, abonnés de 2018 ou des théâtres partenaires ou de personnalité de la région ect...

bien sur par exemple une personnalité peut aussi être un abonné

les tables abonnés par exemple sont alimenté par un export de donnés de la billetterie qui va faire deux abonnements distinct pour le mari et la femme par exemple mais d'autre table peuvent etre alimenté directement dans la base, donc sans import comme les abonnés.

or la saisie des informations par l'opérateur sur la billetterie peut etre erroné entre le mari et la femme, par exemple : M. Paul Ricard 20 rue de brest 35000 rennes et la femme Mme Hermeline Ricart 20 rue de brest 35000 rennes

mais M paul ricard est aussi une personnalité

du coup lorsque je crée une table associant les deux tables abonnés et la table personnalité, je me retrouve avec 3 ligne :

paul ricard

paul ricard

hermeline ricart

ma question :

comment éliminé les quasi doublons en sqlite ?

Merci.

-
Edité par Anthony12- 5 septembre 2020 à 20:16:46

Bonjour,

Tu as beaucoup dis "par exemple" dans ton message ...

Dans la mesure où tu n'as pas de règle stricte pour qualifier tes "quasi doublons" cela me semble compliqué à gérer ...

Une partie de solution serait de faire un test avec la distance de Levenshtein (il existe sûrement une implémentation pour SQLite) pour déjà identifier les enregistrements similaires ... Après rien n'empêche des "quasi doublon" justifié. Jacques RICARD qui n'a aucun lien avec Paul RICARD ...

Si l'on reprend l'exemple cité dans ton message, que voudrais-tu obtenir comme résultat ?

bonsoir et merci

par exemple

je voudrais obtenir une ligne sur les 3 qui est "paul ricard 20 rue de brest 35000 rennes"

merci.

bien à toi.

Peux-tu poster la structure (colonnes, types, contraintes) des tables en question, avec des exemples de donnée ?

bonjour et merci.

voici sur le fichier .db la structure de la table :

et voici sur la table : temp_abonne qui est une table fait avec ce code :

CREATE TABLE temp_abonne as

SELECT * FROM abonne_2015

UNION 

SELECT * FROM abonne_2016_2017 

UNION 

SELECT * FROM abonne_2017_2018 

UNION 

SELECT * FROM abonne_2018_2019

UNION 

SELECT * FROM abonne_2019_2020

un exemple de quasi doublon :

les quasi doublons sont à 99% voir 100% soit sur le nom (comme ici) soit sur le prénom ou l'adresse postale (comme ici) et jamais sur la "pol", ni sur le code postal, ni sur la ville.

Attention sur les tables "abonnés" je peux avoir deux lignes le mari et la femme à la meme adresse. Or comme il s'agit d'un publipostage, le code SQL doit supprimer la femme, sinon nous aurions trop de frais postaux.

merci.

bien à toi.

-
Edité par Anthony12- 29 août 2020 à 13:47:52

Dans un premier temps tu peux mettre un index UNIQUE( nom, prenom) sur la table de destination pour éviter d'insérer de vrais doublons.

Pour les quasi doublons, mettre en place une fonction "levenshtein" sur le nom pour les identifier, mais attention, ce sera une suppression manuelle ensuite ...

Bonjour et merci pour ton aide.

Si je crée ma table : temp_abonne par la ligne SQL suivante :

CREATE TABLE temp_abonne as
SELECT * FROM abonne_2015
UNION 
SELECT * FROM abonne_2016_2017 
UNION 
SELECT * FROM abonne_2017_2018 
UNION 
SELECT * FROM abonne_2018_2019
UNION 
SELECT * FROM abonne_2019_2020

puis l'index unique par :

CREATE UNIQUE INDEX idxabonne ON temp_abonne (PRENOM, NOM)

j'ai une erreur :

UNIQUE constraint failed: temp_abonne.PRENOM, temp_abonne.NOM

je comprend pas pourquoi ?

Merci beaucoup de ton aide.

Bien à toi.

Il faut poser l'index avant d'insérer ... L'index va empêcher d'insérer les doublons nom/prénom.

Donc dans ton cas, tu crées ta table temporaire, puis tu crées une table définitive avec l'index et enfin tu insères ...

CREATE TABLE temp_abonne as
SELECT * FROM abonne_2015
UNION
SELECT * FROM abonne_2016_2017
UNION
SELECT * FROM abonne_2017_2018
UNION
SELECT * FROM abonne_2018_2019
UNION
SELECT * FROM abonne_2019_2020;

CREATE TABLE publipostage (
	id INTEGER PRIMARY KEY,
	nom VARCHAR,
	prenom VARCHAR,
	...,
	UNIQUE( nom, prenom )
);

INSERT OR IGNORE INTO publipostage ( nom, prenom, ... )
SELECT nom, prenom, ... FROM temp_abonne;

Tu remarqueras l'INSERT OR IGNORE qui permet de ne pas stopper la requête si une erreur est levée (doublon).

merci je vais tester tout à l'heure, je ne travaille pas aujourd'hui.

C'est cool par contre car avec deux tables temporaires, je vais pouvoir regarder le nombre de ligne qui ont été supprimé.

Merci bien.

Par contre je vais regarder sur le net si je trouve du code pour la fonction levenshtein, mais je pense avoir encore besoin de toi, car c'est stratosphérique pour moi

merci.

bien à toi.

bonjour

merci tout fonctionne bien et ta technique de cloner la base est tres utile car j'avais pas de recul sur la puissance de la fonction pour supprimer les clones.

Comme les abonnés sont souvent abonné une année sur l'autre, l'union de mes 4 tables et ta fonction sur les doublons m'ont permis de voir que je supprimais déjà un peu plus de 100 lignes.

J'ai importer la fonction de Levenshtein, ca fonctionne bien, mais le soucis est que je ne sais pas à l'avance qui sont les quasi-doublons ? De plus comme je te le disais le quasi doublon peut être sur le champ 'nom' ou sur le champ 'prenom' ou sur l''adresse', et aussi sur deux champs en même temps...

ma question : si j'utilise cette ligne de code :

SELECT * FROM `temp_abonne` WHERE levenshtein('$keyword', `NOM`) BETWEEN 0 AND 2

puis je automatiser la requête, car comme je te le disais je ne sais pas ou sont les quasi doublons ?

puis je utiliser la fonction de Levenshtein sur plusieurs champ en même temps ?

la fonction mysql : "like concat" dans une ligne de code comme :

DELETE B
FROM temp_abonne AS A, temp_abonne AS B
WHERE A.id > B.id
            AND A.nom LIKE CONCAT('%',B.nom,'%')
            AND A.adresse1 LIKE CONCAT('%',B.adresse1,'%') 
            AND A.code = B.code
            AND A.ville = B.ville;

est-t-elle possible sur SQLite ?

Merci.

Bien à toi.

AnthonyLemoine1 a écrit:

Comme les abonnés sont souvent abonné une année sur l'autre, l'union de mes 4 tables

Premier point, le modèle de données ... Tu devrais plutôt envisager de fonctionner avec un modèle en trois tables :

• personne ( id_personne [pk], nom, prenom, adresse, etc. )
• annee ( id_annee [pk], libelle, montant, etc. )
• adhesion ( id_annee [pk][fk], id_personne [pk][fk] )

Cela te permettrait de réutiliser les personnes d'une année sur l'autre. Du coup au moment de l'adhésion, tu peux vérifier si il n'y a pas doublon entre la saisie de l'utilisateur et la table des personnes déjà existantes (avec la distance de levenshtein ) ...

Autre remarque, il me semble qu'il n'y a pas d'id sur ta table ... Je te conseille fortement d'en ajouter un pour pouvoir clairement identifier unitairement les enregistrements ... en entier auto incrémenté est suffisant (cf. ci-dessous).

AnthonyLemoine1 a écrit:

je ne sais pas à l'avance qui sont les quasi-doublons ?

Pour identifier les doublons, il faut croiser les enregistrements, avec ce que l'on appelle une auto-jointure, exemple :

-- Quasi doublon sur le nom
SELECT *
FROM
	temp_abonne T1
		INNER JOIN temp_abonne T2
			ON T1.id < T2.id
WHERE levenshtein( T1.NOM, T2.NOM ) < 3

Si tu comprends le principe, tu peux ensuite l'appliquer aux adresses et au nom/prénom

AnthonyLemoine1 a écrit:

puis je utiliser la fonction de Levenshtein sur plusieurs champ en même temps ?

la fonction mysql : "like concat"

CONCAT n'existe pas avec SQLite il me semble ... l'opérateur de concaténation est || (Alt Gr + 6 ), exemple :

-- Quasi doublon sur le nom / prénom
SELECT *
FROM
	temp_abonne T1
		INNER JOIN temp_abonne T2
			ON T1.id < T2.id
WHERE levenshtein( T1.NOM || T1.PRENOM, T2.NOM || T2.PRENOM ) < 3

-
Edité par Benzouye 1 septembre 2020 à 14:55:52

Bonjour et encore merci pour ta patience.

L'alimentation des nouvelles tables d'abonnés se fait par l'import d'un CSV à partir du logiciel de billetterie. Je n'ai pas la possibilité de traiter les données avant de les importer dans la base.

Oui pour l'ID, je le fais sur les autres tables de ma base publipostage, mais j'ai toujours un peu de mal avec cette fonction.

J'ai testé la fonction de Levenhstein en MYSQL et j'obtiens sur les quasi doublon concatener (prenom, nom et adresse) un résultat de 116 lignes (sur 523 en tout) en 33 min avec une valeur <5

Avant j'utilisais ce code SQL :

DELETE B
FROM temp_abonne AS A, temp_abonne AS B
WHERE A.id > B.id
            AND A.nom LIKE CONCAT('%',B.nom,'%')
            AND A.adresse1 LIKE CONCAT('%',B.adresse1,'%') 
            AND A.code = B.code
            AND A.ville = B.ville;

ou j'avais en résultat de 85 lignes.

Donc la fonction de Levenhstein est plus performante, et je te remercie de m'avoir fléché sur cette fonction.

Du coup peut on automatiser un delete pour ce code :

SELECT *
FROM
    temp_abonne__union A
        INNER JOIN temp_abonne__union B
            ON A.id < B.id
WHERE lev( CONCAT(A.NOM, ' ', A.PRENOM, ' ', A.ADRESSE1), CONCAT(B.NOM, ' ', B.PRENOM, ' ', B.ADRESSE1)  ) < 5

Merci.

Bien à toi.

Si tu as un id en place, tu peux faire une DELETE en effet :

DELETE FROM temp_abonne__union
WHERE id IN (
	SELECT DISTINCT A.id
	FROM
		temp_abonne__union A
			INNER JOIN temp_abonne__union B
				ON A.id < B.id
	WHERE lev( CONCAT(A.NOM, ' ', A.PRENOM, ' ', A.ADRESSE1), CONCAT(B.NOM, ' ', B.PRENOM, ' ', B.ADRESSE1)  ) < 5
);

Mais je te conseille fortement de vérifier avant tout la liste des id remontés par la sous-requête !

Et de faire une sauvegarde de la table avant d'exécuter le DELETE 

Merci.

J'ai cette structure de table :

or si je fais cette requette :

DELETE FROM temp_abonne__union
    -> WHERE id IN (
    ->     SELECT DISTINCT A.id
    ->     FROM
    ->         temp_abonne__union A
    ->             INNER JOIN temp_abonne__union B
    ->                 ON A.id < B.id
    ->     WHERE lev( CONCAT(A.NOM, ' ', A.PRENOM, ' ', A.ADRESSE1), CONCAT(B.NOM, ' ', B.PRENOM, ' ', B.ADRESSE1)  ) < 5
    -> );

j'ai cette erreur :

ERROR 1093 (HY000): You can't specify target table 'temp_abonne__union' for update in FROM clause

Merci.

Je ne connaissais pas cette erreur, mais avec un peu de recherche il semblerait qu'une solution soit de refaire une sous-requête pour "virtualiser" la table : https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

Bonjour

j'ai réussi en adaptant ton code pour éviter l'erreur 1093 avec ce code :

CREATE TABLE temp_abonne__clone LIKE temp_abonne;
INSERT INTO temp_abonne__clone SELECT * FROM temp_abonne;
DELETE FROM temp_abonne
WHERE id IN (
SELECT A.id
FROM
    temp_abonne__clone A
        INNER JOIN temp_abonne__clone B
            ON A.id < B.id
WHERE lev( CONCAT(A.NOM, ' ', A.PRENOM, ' ', A.ADRESSE1), CONCAT(B.NOM, ' ', B.PRENOM, ' ', B.ADRESSE1)  ) < 11 );

je test depuis la bonne valeur pour la fonction de Levenhstein, '11' ici.

Bien à toi et merci encore.

AnthonyLemoine1 a écrit:

la bonne valeur pour la fonction de Levenhstein, '11' ici

Ouch ! 11 ! Le temps de traitement doit être balèse non ?

bonsoir et merci encore de ton aide, bien precieuse.

sur une table à 511 lignes avec ce code :

ALTER TABLE temp_abonne__union ADD ID INT NOT NULL AUTO_INCREMENT key;
SELECT a.nom, a.prenom, a.type2, a.adresse1, b.nom, b.prenom, b.type2, b.adresse1
FROM
    temp_abonne__union A
        INNER JOIN temp_abonne__union B
            ON A.id < B.id
WHERE lev( CONCAT(A.NOM, ' ', A.PRENOM, ' ', A.ADRESSE1), CONCAT(B.NOM, ' ', B.PRENOM, ' ', B.ADRESSE1)  ) < 10
ORDER BY A.NOM;

donc pour une fonction de levenshtein à 10, sur un ryzen 7, je met 33 min. Il me detecte : 267 doublons et quasi doublons

avec une valeur à 5, il met 33 min aussi, et me détecte 116 rows.

après quelques bidouilles, visiblement le bon code serait plutôt d'enlever le champ 'prénom' et de mettre le lev à 5 :

DELETE FROM temp_abonne
WHERE id IN (
SELECT A.id
FROM
    temp_abonne__clone A
        INNER JOIN temp_abonne__clone B
            ON A.id < B.id
WHERE lev( CONCAT(A.NOM, ' ', A.ADRESSE1), CONCAT(B.NOM, ' ', B.ADRESSE1)  ) < 5 );

en créant juste avant un clone de la table temp_abonne.

Le code est quasi parfait mais il manque juste un détail :

la table temp_abonne à un champ 'pol' avec deux valeurs possible : 'M." ou "Mme".

Est-il possible de recoder ce code sql pour sur un doublon de deux lignes : une pour le mari et une pour la femme, donc avec le même champ "nom" et le même champ "adresse1" ne garder avec ce code sql delete que le mari donc la ligne avec pour le champ 'pol' : "M." et supprimer donc la ligne avec le champ 'pol' : "Mme" ?

Merci.

j'ai en tout cas, grace à toi, fais bcp de progrès, et je t'en remercie.

Bien à toi.

AnthonyLemoine1 a écrit:

je met 33 min

Et oui ... c'est énorme ... imagine si tu avais plusieurs milliers d'enregistrements ... mais bon ce n'est pas le cas ...

AnthonyLemoine1 a écrit:

ne garder avec ce code sql delete que le mari

C'est sexiste ... Pourquoi garder le mari plutôt que l'épouse ?

Plus sérieusement, il faudrait ordonner l'auto-jointure en fonction de la politesse (pol), "M." venant avant "Mme" ça devrait pouvoir le faire. Pour cela, la table clone doit être renumérotée (id) pour que l'id de Mme soit inférieur à l'id de M.

-
Edité par Benzouye 3 septembre 2020 à 21:54:15

Bonjour oui c'est sexiste mais je suis sur la côte avec une sociologie âgée, ainsi si je fais une réservation par téléphone avec une dame :

"Bonjour je voudrais réserver deux places pour le concert de la reformation du groupe punk "sex pistols" ?

oui a quel nom ?

bah y a qu'a le mettre au nom de monsieur... "

Et ca c'est un fait, fréquent en plus.

Je vais regarder pour l'ID du clone.

Merci.

Bien à toi.

Pas de souci, c'était sur le ton de l'humour ...

PS : Les "sex pistols" se reforment ? Et ils passent en France ? Et des vieux s'y intéressent ?

"Pas de souci, c'était sur le ton de l'humour .."

Je sais bien, moi aussi.

Bien à toi.

Bon weekend.

Bonjour et merci

le code est complet, merci encore.

Je passe de 511 à 399, grâce à l'index unique sur les doublons, et de 399 à 313 grâce à la fonction de Levinhstein pour les quasi doublons.

Temps de traitement de la fonction levenhstein sur un ryzen 5 : 9 min

le voici :

/* CREATION TEMP ABONNE VIDE avec INDEX UNIQUE ANTI DOUBLONS*/
CREATE TABLE temp_abonne (
    id INT AUTO_INCREMENT PRIMARY KEY,
	POL varchar(255),
	PRENOM varchar(255),
	NOM varchar(255),
	TYPE2 varchar(255),
	SPECTACLE varchar(255),
	VERNISSAGE varchar(255),
	COMPLEMENT varchar(255),
	PLAGES varchar(255),
	PROMNONS varchar(255),
	SOCIETE varchar(255),
	ADRESSE1 varchar(255),
	ADRESSE2 varchar(255),
	CODE varchar(255),
	VILLE varchar(255),
	CEDEX varchar(255),
	TEL varchar(255),
	PORTABLE varchar(255),
	EMAIL varchar(255),
	UNIQUE (NOM,PRENOM)
);

/* CREATION TEMP UNION ABONNE */
CREATE TABLE temp_abonne__union ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 as
SELECT * FROM abonne_2015
UNION 
SELECT * FROM abonne_2016_2017 
UNION 
SELECT * FROM abonne_2017_2018 
UNION 
SELECT * FROM abonne_2018_2019
UNION 
SELECT * FROM abonne_2019_2020;

/* CREATION temp_abonne__union_trier SUR LE NOM PUIS SUR LA POL ALPHABETIQUE INVERSE */
CREATE TABLE temp_abonne__union_trier ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 as
SELECT * FROM temp_abonne__union ORDER BY nom,pol DESC;

/* REMPLISSAGE TEMP ABONNE A PARTIR DE TEMP UNION ABONNE ET GRACE A INDEX UNIQUE : 
SUPPRESSION DES VRAIS DOUBLONS */
INSERT IGNORE INTO temp_abonne (POL,PRENOM,NOM,TYPE2,SPECTACLE,VERNISSAGE,COMPLEMENT,PLAGES,PROMNONS,SOCIETE,ADRESSE1,ADRESSE2,CODE,VILLE,CEDEX,TEL,PORTABLE,EMAIL) SELECT POL,PRENOM,NOM,TYPE2,SPECTACLE,VERNISSAGE,COMPLEMENT,PLAGES,PROMNONS,SOCIETE,ADRESSE1,ADRESSE2,CODE,VILLE,CEDEX,TEL,PORTABLE,EMAIL FROM temp_abonne__union_trier;

/* CREATION D UN CLONE DE TEMP ABONNE POUR FONCTION DELETE ENSUITE */
CREATE TABLE temp_abonne__clone LIKE temp_abonne;
INSERT INTO temp_abonne__clone SELECT * FROM temp_abonne;

/* SUPPRESSION QUASI-DOUBLONS POUR TEMP ABONNE */	
DELETE FROM temp_abonne
WHERE id IN (
SELECT A.id
FROM
    temp_abonne__clone A
        INNER JOIN temp_abonne__clone B
            ON A.id < B.id
WHERE lev( CONCAT(A.NOM, ' ', A.ADRESSE1), CONCAT(B.NOM, ' ', B.ADRESSE1)  ) < 5 );
			
/* CREATION D:/Drive/temp_abonne.csv */
SELECT 'ID', 'pol', 'prenom', 'nom', 'type2', 'societe', 'adresse1', 'adresse2', 'code', 'ville', 'cedex' 
UNION ALL
SELECT ID, pol, prenom, nom, type2, societe, adresse1, adresse2, code, ville, cedex FROM temp_abonne 
INTO OUTFILE 'D:/OneDrive/MYSQL/OFFICE/temp_abonne.csv' CHARACTER SET latin1 FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

J'aurais jamais réussi sans toi.

Je te dis tout simplement merci.

Et si un jour tu passes par chez moi, je te paye un bière bretonne bio

Merci encore.

Bien à toi.

Anthony.