SQL supprime les enregistrements en double de la table. Supprimer les répétitions dans T-SQL

(25-07-2009)

Dans l’article précédent, nous avons cherché à résoudre le problème de duplication causé par une clé primaire manquante. Considérons maintenant un cas plus difficile, où la clé semble exister, mais elle est synthétique, ce qui, si elle n'est pas conçue correctement, peut également conduire à des doublons du point de vue du domaine.

C'est étrange, mais lorsque je parle en cours des inconvénients des clés synthétiques, je constate néanmoins constamment que les étudiants les utilisent invariablement dans leurs premiers projets de bases de données. Apparemment, une personne a un besoin génétique de tout renuméroter, et seul un psychothérapeute peut l'aider ici. :-)

Supposons donc que nous ayons une table avec un identifiant de clé primaire et un nom de colonne qui, conformément aux restrictions de domaine, doit contenir des valeurs uniques. Cependant, si vous définissez la structure du tableau comme suit

CREATE TABLE T_pk (id INT IDENTITY PRIMARY KEY , nom VARCHAR (50 ));

alors rien n'empêche l'apparition de doublons. La structure de tableau suivante doit être utilisée :

CREATE TABLE T_pk (id INT IDENTITY PRIMARY KEY, nom VARCHAR (50) UNIQUE) ;

Tout le monde sait ce qu’il faut faire, mais vous devez souvent faire face à une structure et à des données « héritées » qui violent les contraintes du domaine. Voici un exemple :

nom d'identification 1 Jean 2 Smith 3 Jean 4 Smith 5 Smith 6 Tom

Vous pouvez demander : « En quoi ce problème est-il différent du précédent ? Après tout, il existe ici une solution encore plus simple : supprimez simplement toutes les lignes de chaque groupe avec les mêmes valeurs dans la colonne de nom, en ne laissant que la ligne avec le valeur d'identifiant minimum/maximum. Par exemple, comme ceci :"

DELETE FROM T_pk WHERE id > (SELECT MIN (id) FROM T_pk X WHERE X.name = T_pk.name);

C'est vrai, mais je ne vous ai pas encore tout dit. :-) Imaginons que nous ayons une table enfant T_details liée à la table T_pk par une clé étrangère :

CREATE TABLE T_details (id_pk INT RÉFÉRENCES DE CLÉ ÉTRANGÈRE T_pk ON DELETE CASCADE , couleur VARCHAR (10), CLÉ PRIMAIRE (id_pk, couleur);

Ce tableau peut contenir les données suivantes :

couleur id_pk 1 bleu 1 rouge 2 vert 2 rouge 3 rouge 4 bleu 6 rouge

Pour plus de clarté, utilisons la requête

SELECT id, nom, couleur FROM T_pk JOIN T_details ON id= id_pk;

pour voir les noms :

couleur du nom d'identification 1 John bleu 1 John rouge 2 Smith vert 2 Smith rouge 3 John rouge 4 Smith bleu 6 Tom rouge

Ainsi, il s’avère que les données qui appartiennent en réalité à une seule personne ont été attribuées par erreur à différents enregistrements parents. De plus, il y avait des doublons dans ce tableau :

1 Jean rouge 3 Jean rouge

De telles données conduiront évidemment à des analyses et à des rapports erronés. De plus, la suppression en cascade entraînera une perte de données. Par exemple, si nous laissons uniquement les lignes avec l'ID minimum dans chaque groupe de la table T_pk, nous perdrons la ligne

4 Smith bleu

dans la table T_details. Par conséquent, nous devons prendre en compte les deux tableaux lors de l’élimination des doublons.

La procédure de « nettoyage » des données peut être réalisée en deux étapes :

1. Mettez à jour la table T_details en attribuant les données liées à un nom à l'identifiant avec le nombre minimum dans le groupe.
2. Supprimez les doublons de la table T_pk, en laissant uniquement les lignes avec l'identifiant minimum dans chaque groupe avec la même valeur dans la colonne de nom.

Mise à jour de la table T_details

SELECT id_pk, nom, couleur, RANK () OVER (PARTITION BY nom, couleur ORDER BY nom, couleur, id_pk) dup, (SELECT MIN (id) FROM T_pk WHERE T_pk.name = X.name) min_id FROM T_pk X JOIN T_details SUR id=id_pk;

détermine la présence de doublons (valeur dup > 1) et la valeur minimale de l'identifiant dans un groupe de noms identiques (min_id). Voici le résultat de l'exécution de cette requête :

id_pk nom couleur dup min_id 1 John bleu 1 1 1 John rouge 1 1 3 John rouge 2 1 4 Smith bleu 1 2 2 Smith vert 1 2 2 Smith rouge 1 2 6 Tom rouge 1 6

Nous devons maintenant remplacer la valeur id_pk par la valeur min_pk pour toutes les lignes sauf la troisième, car cette ligne est un double de la deuxième ligne, comme l'indique la valeur dup=2. Une demande de mise à jour peut être écrite comme ceci :

UPDATE T_details SET id_pk=min_id FROM T_details T_d JOIN (SELECT id_pk, nom, couleur, RANK () OVER (PARTITION BY nom, couleur ORDER BY nom, couleur, id_pk) dup, (SELECT MIN (id) FROM T_pk WHERE T_pk.name = X.name) min_id FROM T_pk X JOIN T_details ON id=id_pk) Y ON Y.id_pk=T_d.id_pk WHERE dup =1 ;

Lorsque la tâche d'optimisation d'une base de données se pose ou que sa structure change, une tâche connexe consiste parfois à organiser les données déjà accumulées. Il est bon que le tableau soit déjà sous sa forme normale pendant le développement et que l'ensemble du système soit organisé de manière à ne pas accumuler d'informations en double inutiles. Si ce n'est pas le cas, lors de la finalisation d'un tel système, vous souhaitez vous débarrasser de toutes les données redondantes et tout faire avec la plus haute qualité.

Dans cet article, nous examinerons la tâche de suppression des lignes en double dans une table de base de données. Je voudrais tout de suite noter que nous parlons de la nécessité de supprimer les lignes en double. Par exemple, les enregistrements de la table de commande avec les champs « code commande », « code produit », « code client », « date de commande » ne peuvent différer que par le code commande, puisqu'un client peut commander plusieurs fois le même produit sur le le même jour une fois. Et le principal indicateur ici que tout est correct est la présence d'un champ clé.

Si nous voyons un tableau rempli de champs en double, sans besoin clair pour chaque entrée, alors c'est exactement ce qui doit être corrigé.

Un exemple de tableau clairement redondant :

Voyons maintenant comment résoudre ce problème. Plusieurs méthodes peuvent être appliquées ici.

1. Vous pouvez écrire une fonction pour comparer et parcourir toutes les données. Cela prend beaucoup de temps et vous ne souhaitez pas toujours écrire du code pour une utilisation unique.

2. Une autre solution consiste à créer une requête de sélection qui regroupe les données afin que seules les lignes uniques soient renvoyées :

SELECT country_id, city_name
DE matable
GROUPER PAR country_id, city_name

Nous obtenons l'échantillon suivant :

Ensuite, nous écrivons l'ensemble de données résultant dans une autre table.

3. Ces solutions utilisent du code de programme supplémentaire ou des tables supplémentaires. Cependant, il serait plus pratique de tout faire en utilisant uniquement des requêtes SQL sans tables supplémentaires. Et voici un exemple d'une telle solution :

SUPPRIMER a.* DE matable a,
(SÉLECTIONNER

DE matable b

)c
OÙ
a.country_id = c.country_id
ET a.city_name = c.city_name
ET a.id > c.mid

Après avoir exécuté une telle requête, seuls les enregistrements uniques resteront dans la table :

Voyons maintenant de plus près comment tout cela fonctionne. Lorsque vous demandez la suppression, vous devez définir une condition qui indiquera quelles données doivent être supprimées et lesquelles doivent être laissées. Nous devons supprimer toutes les entrées non uniques. Ceux. s'il y a plusieurs enregistrements identiques (ils sont identiques s'ils ont des valeurs country_id et city_name égales), alors vous devez prendre l'une des lignes, mémoriser son code et supprimer tous les enregistrements avec les mêmes valeurs country_id et city_name, mais un code différent (identifiant).

Chaîne de requête SQL :

SUPPRIMER a.* DE matable a,

indique que la suppression sera effectuée depuis la table mytable.

La requête de sélection génère ensuite une table auxiliaire dans laquelle nous regroupons les enregistrements afin que tous les enregistrements soient uniques :

(SÉLECTIONNER
b.country_id, b.city_name, MIN(b.id) milieu
DE matable b
GROUPER PAR b.country_id, b.city_name
)c

MIN(b.id) mid – forme la colonne du milieu (abréviation min id), qui contient la valeur d’identification minimale dans chaque sous-groupe.

Le résultat est un tableau contenant des enregistrements uniques et l'identifiant de la première ligne pour chaque groupe d'enregistrements en double.

Nous avons maintenant deux tables. Un général contenant tous les enregistrements. Les lignes supplémentaires en seront supprimées. La seconde contient des informations sur les lignes qui doivent être enregistrées.

Il ne reste plus qu'à créer une condition qui dit : vous devez supprimer toutes les lignes où les champs country_id et city_name correspondent, mais l'id ne correspondra pas. Dans ce cas, la valeur minimale de l'identifiant est sélectionnée, donc tous les enregistrements dont l'identifiant est supérieur à celui sélectionné dans la table temporaire sont supprimés.

Il convient également de noter que l'opération décrite peut être effectuée s'il existe un champ clé dans la table. Si vous tombez soudainement sur une table sans identifiant unique, ajoutez-la simplement :

ALTER TABLE ` matable` ADD `id` INT(11) NOT NULL AUTO_INCREMENT , AJOUTER UNE CLÉ PRIMAIRE (`id`)

En exécutant une telle requête, nous obtenons une colonne supplémentaire remplie de valeurs numériques uniques pour chaque ligne du tableau.

Nous effectuons toutes les actions nécessaires. Une fois l'opération d'effacement du tableau des enregistrements en double terminée, ce champ peut également être supprimé.

Supprimer les répétitions

Source de la base de données

La nécessité de dédupliquer les données est courante, en particulier lorsqu'il s'agit de résoudre des problèmes de qualité des données dans des environnements où la duplication est survenue en raison d'un manque de contraintes pour garantir l'unicité des données. Pour le démontrer, utilisons le code suivant pour préparer un exemple de données avec des commandes en double dans une table nommée MyOrders :

SI OBJECT_ID("Sales.MyOrders") N'EST PAS NULL DROP TABLE Sales.MyOrders; ALLER SELECT * INTO Sales.MyOrders FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders;

Imaginez que vous deviez éliminer les données en double, en ne laissant qu'une seule instance de chacune avec une valeur d'ID de commande unique. Les numéros en double sont marqués à l'aide de la fonction ROW_NUMBER, en partitionnant par une valeur soi-disant unique (orderid dans notre cas) et en utilisant un ordre aléatoire si vous ne vous souciez pas de la ligne à conserver et de celle à supprimer. Voici le code dans lequel la fonction ROW_NUMBER marque les doublons :

SELECT orderid, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders;

Ensuite, vous devez envisager différentes options en fonction du nombre de lignes à supprimer, du pourcentage de la taille de la table, de ce nombre, de l'activité de l'environnement de production et d'autres circonstances. Lorsque le nombre de lignes supprimées est faible, il suffit généralement d'utiliser une opération de suppression entièrement journalisée, qui supprime toutes les instances dont le numéro de ligne est supérieur à un :

Mais si le nombre de lignes supprimées est important, en particulier lorsqu'il représente une grande proportion des lignes de la table, une suppression avec un journal complet de l'opération sera trop lente. Dans ce cas, vous souhaiterez peut-être envisager d'utiliser une opération de journalisation en masse telle que SELECT INTO pour copier les lignes uniques (numérotées 1) dans une autre table. Après cela, la table d'origine est supprimée, puis la nouvelle table reçoit le nom de la table supprimée et les index de contraintes et les déclencheurs sont recréés. Voici le code de la solution terminée :

AVEC C AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders) SELECT orderid, cusid, empid, orderdate, requisdate, shippingdate, shipperid, fret, shipname, shipaddress, shipcity, shipregion, shippostalcode, shipcountry INTO Sales.OrdersTmp FROM C WHERE n = 1 ; DROP TABLE Ventes.MesCommandes ; EXEC sp_rename "Sales.OrdersTmp", "MyOrders" ; -- recréer des index, des contraintes et des déclencheurs

Par souci de simplicité, je n'ai ajouté aucun contrôle de transaction ici, mais vous devez toujours vous rappeler que plusieurs utilisateurs peuvent travailler avec les données en même temps. Lors de la mise en œuvre de cette méthode dans un environnement de production, vous devez suivre la séquence suivante :

Transaction ouverte.

Obtenez un verrou de table.

Exécutez l'instruction SELECT INTO.

Supprimez et renommez des objets.

Recréez des index, des contraintes et des déclencheurs.

Validez la transaction.

Il existe une autre option : filtrer uniquement les lignes uniques ou non uniques. ROW_NUMBER et RANK sont calculés en fonction de l'ID de commande, quelque chose comme ceci :

SELECT orderid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk FROM Sales.MyOrders ;

Notez que dans les résultats, une seule ligne pour chaque valeur unique dans orderid correspond au numéro de ligne et au classement de la ligne. Par exemple, si vous devez supprimer une petite partie des données, vous pouvez encapsuler la requête précédente dans une définition CTE et, dans la requête externe, exécuter l'instruction pour supprimer les lignes ayant des numéros et des classements différents.