Guide complet sur la suppression en cascade
Pour comprendre comment les suppressions se propagent entre tables.
Suppression des doublons, traitement des valeurs manquantes, normalisation, gestion des outliers et formatage des dates : maîtrisez toutes les techniques SQL pour nettoyer vos données efficacement.
Le nettoyage de données est une étape essentielle dans tout processus d'analyse ou de gestion de données. En particulier, lorsque vous travaillez avec de grands ensembles de données, cette tâche devient encore plus cruciale et complexe. Les données peuvent être erronées, incomplètes ou dupliquées, et ces imperfections peuvent biaiser les résultats ou rendre les analyses inefficaces.
SQL (Structured Query Language) est un langage puissant pour manipuler et nettoyer les données directement dans les bases de données relationnelles. Il permet d'interagir directement avec la base sans déplacer les données.
Workflow du nettoyage SQL
Dans les grands jeux de données, il n'est pas rare de trouver des enregistrements dupliqués (erreurs d'importation, mauvaise gestion des données). Voici 5 méthodes pour les identifier et les supprimer selon votre SGBD.
Toujours faire un SELECT pour identifier les doublons avant de les supprimer. Une sauvegarde est recommandée.
CREATE TABLE temp_table AS
SELECT DISTINCT column1, column2, column3, ...
FROM table_name;
DROP TABLE table_name;
ALTER TABLE temp_table RENAME TO table_name;DELETE FROM table_name
WHERE id NOT IN (
SELECT MIN(id)
FROM table_name
GROUP BY column1, column2, column3
);DELETE FROM table_name
WHERE id NOT IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY column1, column2, column3
ORDER BY id
) AS row_num
FROM table_name
) AS temp
WHERE row_num = 1
);DELETE t1 FROM table_name t1
INNER JOIN table_name t2
WHERE
t1.id > t2.id
AND t1.column1 = t2.column1
AND t1.column2 = t2.column2
AND t1.column3 = t2.column3;DELETE FROM table_name
WHERE id IN (
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY column1, column2, column3
ORDER BY id
) AS row_num
FROM table_name
) WHERE row_num > 1
);Les valeurs NULL peuvent être problématiques pour les calculs statistiques ou les analyses. Voici comment les traiter.
UPDATE table_name
SET column_name = 'default_value'
WHERE column_name IS NULL;-- Par 0
UPDATE table_name SET age = 0 WHERE age IS NULL;
-- Par la moyenne (sous-requête)
UPDATE table_name
SET salary = (SELECT AVG(salary) FROM table_name WHERE salary IS NOT NULL)
WHERE salary IS NULL;DELETE FROM table_name
WHERE column_name IS NULL;
-- Suppression si plusieurs colonnes NULL
DELETE FROM table_name
WHERE column1 IS NULL OR column2 IS NULL;Pour les requêtes sans modification de la base :SELECT COALESCE(column_name, 'valeur_par_defaut') FROM table_name;SELECT ISNULL(column_name, 'valeur_par_defaut') FROM table_name; (SQL Server)
Les données peuvent être enregistrées sous différentes formes (ex: "Paris", "PARIS", "paris"). La normalisation assure la cohérence.
-- Mettre en majuscules
UPDATE table_name SET city = UPPER(city);
-- Mettre en minuscules
UPDATE table_name SET city = LOWER(city);
-- Capitaliser première lettre (SQL Server)
UPDATE table_name SET city = UPPER(LEFT(city,1)) + LOWER(SUBSTRING(city,2));-- Supprimer espaces début et fin
UPDATE table_name SET column_name = TRIM(column_name);
-- Supprimer espaces multiples internes (plus complexe)
-- SQL Server
UPDATE table_name SET column_name = REPLACE(REPLACE(column_name, ' ', ' '), ' ', ' ');-- Garder uniquement les chiffres (SQL Server)
UPDATE table_name
SET phone = REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '.', '');Les grands jeux de données peuvent contenir des valeurs impossibles (âges négatifs, dates futures).
-- Âges négatifs
SELECT * FROM table_name WHERE age < 0;
-- Dates dans le futur
SELECT * FROM table_name WHERE date_naissance > CURRENT_DATE;
-- Codes postaux invalides (ex: France, 5 chiffres)
SELECT * FROM table_name WHERE postal_code NOT REGEXP '^[0-9]{5}$'; -- MySQL-- Mettre les âges négatifs à NULL
UPDATE table_name SET age = NULL WHERE age < 0;
-- Remplacer les dates futures par la date d'aujourd'hui
UPDATE table_name
SET date_naissance = CURRENT_DATE
WHERE date_naissance > CURRENT_DATE;
-- Corriger les codes postaux trop longs
UPDATE table_name
SET postal_code = LEFT(postal_code, 5)
WHERE LENGTH(postal_code) > 5;Regrouper et agréger les données permet d'obtenir des résumés pertinents et d'identifier des anomalies.
-- Montant total des transactions par client
SELECT customer_id, SUM(amount) as total_amount
FROM transactions
GROUP BY customer_id;
-- Nombre de commandes par produit avec vente moyenne
SELECT product_id,
COUNT(*) as nb_commandes,
AVG(price) as prix_moyen,
SUM(quantity) as quantite_totale
FROM orders
GROUP BY product_id;
-- Détection des anomalies : doublons potentiels
SELECT column1, column2, COUNT(*) as nb
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;Les valeurs aberrantes peuvent fausser les résultats des analyses. Voici comment les identifier et les traiter.
-- Transactions très élevées (seuil fixe)
SELECT * FROM transactions WHERE amount > 100000;
-- Méthode de l'écart-type (approximative)
WITH stats AS (
SELECT AVG(amount) as mean, STDEV(amount) as std
FROM transactions
)
SELECT * FROM transactions, stats
WHERE amount > mean + 3*std OR amount < mean - 3*std;-- Suppression des outliers
DELETE FROM transactions
WHERE amount > 100000;
-- Plafonnement (winsorisation)
UPDATE transactions
SET amount = 100000
WHERE amount > 100000;Les dates posent souvent problème en raison de formats incohérents. SQL permet de les formater et convertir.
-- PostgreSQL / Oracle
SELECT TO_CHAR(date_column, 'YYYY-MM-DD') FROM table_name;
SELECT TO_CHAR(date_column, 'DD/MM/YYYY') FROM table_name;
-- MySQL
SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM table_name;
-- SQL Server
SELECT FORMAT(date_column, 'yyyy-MM-dd') FROM table_name;
SELECT CONVERT(VARCHAR, date_column, 23) FROM table_name;
-- Conversion de chaîne en date
UPDATE table_name
SET date_column = CAST(string_column AS DATE)
WHERE string_column IS NOT NULL;Certaines colonnes ou lignes peuvent être jugées non pertinentes pour l'analyse.
ALTER TABLE table_name DROP COLUMN column_to_drop;
-- Suppression de plusieurs colonnes (MySQL)
ALTER TABLE table_name
DROP COLUMN col1,
DROP COLUMN col2;-- Supprimer les données antérieures à une date
DELETE FROM logs WHERE date < '2020-01-01';
-- Supprimer les données inactives depuis longtemps
DELETE FROM users WHERE last_login < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR);Après nettoyage, les vues SQL facilitent l'accès aux données propres sans dupliquer les données.
CREATE VIEW clean_data AS
SELECT column1, column2, column3
FROM table_name
WHERE column1 IS NOT NULL
AND column2 > 0
AND column3 != '';
-- Vue avec données transformées
CREATE VIEW normalized_sales AS
SELECT
customer_id,
UPPER(city) as city_normalized,
COALESCE(amount, 0) as amount_clean,
DATE_FORMAT(sale_date, '%Y-%m') as sale_month
FROM sales_raw
WHERE amount IS NOT NULL;Les vues ne stockent pas de données, elles sont une "fenêtre" sur les données nettoyées. Elles se mettent à jour automatiquement quand les données sources sont nettoyées.
Avant tout DELETE ou UPDATE destructeur, faites une copie de la table ou utilisez une transaction (BEGIN; ... ROLLBACK;).
Utilisez LIMIT ou WHERE pour tester vos requêtes sur un petit sous-ensemble avant de lancer sur toute la table.
Gardez une trace des règles de nettoyage (comment vous avez traité les NULL, les doublons, etc.).
Pour les opérations critiques : BEGIN TRANSACTION; ... COMMIT;. Si erreur, ROLLBACK annule tout.
Si possible, créez des vues pour le nettoyage plutôt que de modifier les données brutes. Cela préserve la traçabilité.
DELETE supprime des lignes une par une (plus lent, peut être annulé avec ROLLBACK). TRUNCATE supprime toutes les lignes en une fois (très rapide, ne peut pas être annulé sans sauvegarde). Pour le nettoyage ciblé, utilisez DELETE. Pour vider entièrement une table, TRUNCATE.
Utilisez des vues (CREATE VIEW clean_view AS SELECT ...) ou des tables temporaires (CREATE TEMP TABLE clean_data AS SELECT ...). Cela permet de garder les données brutes intactes tout en travaillant sur une version nettoyée.
Utilisez GROUP BY avec HAVING COUNT(*) > 1 : SELECT column1, column2, COUNT(*) FROM table GROUP BY column1, column2 HAVING COUNT(*) > 1; Cela liste tous les groupes dupliqués sans les supprimer.
Cela dépend du contexte : Suppression si peu de lignes et NULL sans signification. Imputation (remplacement par moyenne/médiane/mode) si les données sont importantes. Conservation avec COALESCE dans les requêtes si l'origine du NULL est informative.
Utilisez des suppressions par lots (batches) : DELETE FROM table WHERE condition LIMIT 1000; en boucle. Pour PostgreSQL, utilisez DELETE FROM table WHERE id IN (SELECT id FROM table WHERE condition LIMIT 1000);. Cela évite les locks trop longs.
SQL est très efficace pour les opérations standard (doublons, NULL, agrégations). Mais pour des transformations complexes (expressions régulières avancées, ML pour imputation, transformations non linéaires), Python/R restent plus flexibles. L'idéal est de combiner les deux : SQL pour le gros du nettoyage, Python pour les cas complexes.
Le nettoyage de grands jeux de données est une tâche incontournable pour garantir la qualité et la précision des analyses. Les requêtes SQL offrent un ensemble d'outils puissants et flexibles pour exécuter cette tâche de manière efficace, même sur des ensembles de données volumineux.