Prétraitement et Inspection des Données avec SQL : Guide complet
Valeurs manquantes, doublons, normalisation, agrégation : maîtrisez toutes les techniques SQL pour préparer vos données avant analyse ou machine learning.
- 1. Importance du prétraitement et de l'inspection des données
- 2. Inspection des données avec SQL
- 3. Nettoyage des données avec SQL
- 4. Transformation et agrégation des données
- 5. Inspection des données transformées
- 6. Automatisation avec les vues SQL
- 7. Bonnes pratiques de prétraitement
- 8. FAQ
- Conclusion
- Articles connexes
1. Importance du prétraitement et de l'inspection des données
Le prétraitement et l'inspection des données constituent des étapes cruciales dans l'analyse de données. Avant d'entraîner un modèle de machine learning ou de générer des visualisations, il est essentiel d'assurer la qualité des données par des méthodes de nettoyage, d'exploration et de normalisation. SQL, en tant que langage de gestion de bases de données relationnelles, permet d'effectuer efficacement ces étapes.
Des données de mauvaise qualité = des analyses peu fiables = des décisions erronées. SQL permet d'automatiser ces tâches sur de grandes bases avec rapidité et précision.
Les étapes clés du prétraitement
- Inspection des données : repérer les valeurs manquantes ou aberrantes
- Nettoyage : supprimer ou ajuster les valeurs incohérentes
- Normalisation et agrégation : transformer les données pour les rendre uniformes
- Gestion des duplicatas : garantir que chaque observation est unique
Schéma des étapes de prétraitement SQL
2. Inspection des données avec SQL
L'inspection des données est la première étape du prétraitement. Elle consiste à analyser la distribution et l'intégrité des données stockées.
2.1 Afficher les premières lignes
-- Aperçu général de la table
SELECT *
FROM ventes
LIMIT 10;
-- Compter le nombre total de lignes
SELECT COUNT(*) AS total_lignes FROM ventes;2.2 Identifier les valeurs manquantes
-- Compter les valeurs manquantes par colonne
SELECT
COUNT(CASE WHEN email IS NULL THEN 1 END) AS email_manquants,
COUNT(CASE WHEN age IS NULL THEN 1 END) AS age_manquants,
COUNT(CASE WHEN nom IS NULL THEN 1 END) AS nom_manquants
FROM clients;
-- Pourcentage de valeurs manquantes
SELECT
ROUND(100.0 * COUNT(CASE WHEN email IS NULL THEN 1 END) / COUNT(*), 2) AS pct_email_manquant
FROM clients;2.3 Détection de valeurs incohérentes
-- Âges impossibles
SELECT *
FROM clients
WHERE age < 0 OR age > 120;
-- Prix négatifs
SELECT *
FROM ventes
WHERE prix < 0;
-- Dates incohérentes
SELECT *
FROM commandes
WHERE date_commande > CURRENT_DATE;2.4 Statistiques descriptives avec SQL
SELECT
COUNT(*) AS nb_lignes,
COUNT(DISTINCT categorie) AS nb_categories,
AVG(prix) AS prix_moyen,
MIN(prix) AS prix_min,
MAX(prix) AS prix_max,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY prix) AS prix_median
FROM ventes;3. Nettoyage des données avec SQL
Une fois l'inspection terminée, le nettoyage s'avère indispensable pour corriger les anomalies et les valeurs manquantes.
BEGIN; ... ROLLBACK;).3.1 Suppression des valeurs manquantes
DELETE FROM clients
WHERE email IS NULL;
-- Suppression si trop de valeurs manquantes (ex: 3+ colonnes NULL)
DELETE FROM clients
WHERE (CASE WHEN email IS NULL THEN 1 ELSE 0 END +
CASE WHEN age IS NULL THEN 1 ELSE 0 END +
CASE WHEN nom IS NULL THEN 1 ELSE 0 END) >= 3;3.2 Imputation des valeurs manquantes
-- Imputation par la moyenne
UPDATE clients
SET revenu = (SELECT AVG(revenu) FROM clients WHERE revenu IS NOT NULL)
WHERE revenu IS NULL;
-- Imputation par une valeur par défaut
UPDATE clients
SET statut = 'actif'
WHERE statut IS NULL;
-- Imputation avec COALESCE (sans modifier la table)
SELECT
id,
COALESCE(email, Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser. ') AS email_clean
FROM clients;3.3 Correction des valeurs incohérentes
-- Mettre les âges aberrants à NULL
UPDATE clients
SET age = NULL
WHERE age < 0 OR age > 120;
-- Plafonner les prix trop élevés
UPDATE ventes
SET prix = 10000
WHERE prix > 10000;
-- Uniformiser la casse
UPDATE clients
SET nom = UPPER(nom),
email = LOWER(email);3.4 Suppression des duplicatas
-- Identifier les doublons
SELECT email, COUNT(*)
FROM clients
GROUP BY email
HAVING COUNT(*) > 1;
-- Supprimer les doublons (garder le plus petit ID)
DELETE FROM clients
WHERE id NOT IN (
SELECT MIN(id)
FROM clients
GROUP BY email
);
-- Alternative avec CTE (PostgreSQL, SQL Server)
WITH doublons AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM clients
)
DELETE FROM clients
WHERE id IN (SELECT id FROM doublons WHERE rn > 1);4. Transformation et agrégation des données
4.1 Normalisation des données (Min-Max)
WITH stats AS (
SELECT MIN(prix) AS min_prix, MAX(prix) AS max_prix
FROM ventes
)
SELECT
id,
prix,
(prix - min_prix) / (max_prix - min_prix) AS prix_normalise
FROM ventes, stats;4.2 Standardisation (Z-score)
SELECT
id,
prix,
(prix - AVG(prix) OVER()) / STDDEV(prix) OVER() AS prix_standardise
FROM ventes;4.3 Agrégation des données
-- Total des ventes par région
SELECT
region,
SUM(montant) AS total_ventes,
COUNT(*) AS nb_transactions,
AVG(montant) AS panier_moyen
FROM ventes
GROUP BY region
ORDER BY total_ventes DESC;
-- Ventes par mois
SELECT
DATE_TRUNC('month', date_vente) AS mois,
SUM(montant) AS total_mensuel,
COUNT(*) AS nb_ventes
FROM ventes
GROUP BY DATE_TRUNC('month', date_vente)
ORDER BY mois;4.4 Création de tables dérivées
-- Table des ventes normalisées
CREATE TABLE ventes_normalisees AS
SELECT
id,
region,
date_vente,
(prix - AVG(prix) OVER()) / STDDEV(prix) OVER() AS prix_normalise,
(quantite - AVG(quantite) OVER()) / STDDEV(quantite) OVER() AS qte_normalisee
FROM ventes;
-- Table des résumés par région
CREATE TABLE resume_ventes_region AS
SELECT
region,
SUM(montant) AS total_ventes,
COUNT(*) AS nb_transactions,
AVG(montant) AS panier_moyen
FROM ventes
GROUP BY region;5. Inspection des données transformées
Après le prétraitement, il est recommandé d'inspecter de nouveau les données pour vérifier leur qualité.
-- Vérifier la normalisation (moyenne proche de 0, écart-type proche de 1)
SELECT
AVG(prix_normalise) AS moyenne,
STDDEV(prix_normalise) AS ecart_type
FROM ventes_normalisees;
-- Vérifier qu'il n'y a plus de valeurs manquantes
SELECT
COUNT(*) - COUNT(prix_normalise) AS manquants
FROM ventes_normalisees;
-- Vérifier l'absence de doublons
SELECT COUNT(*) - COUNT(DISTINCT id) AS doublons
FROM ventes_normalisees;6. Automatisation avec les vues SQL
Les vues permettent de sauvegarder des requêtes complexes pour réutilisation, sans stocker physiquement les données.
-- Vue des clients propres
CREATE VIEW vue_clients_clean AS
SELECT *
FROM clients
WHERE email IS NOT NULL
AND age BETWEEN 0 AND 120
AND nom IS NOT NULL;
-- Vue des ventes normalisées
CREATE VIEW vue_ventes_normalisees AS
SELECT
id,
region,
date_vente,
(prix - AVG(prix) OVER()) / STDDEV(prix) OVER() AS prix_standardise
FROM ventes;
-- Utiliser la vue comme une table
SELECT * FROM vue_ventes_normalisees WHERE prix_standardise > 2;Les vues ne dupliquent pas les données. Elles se mettent à jour automatiquement quand les tables sources changent.
7. Bonnes pratiques de prétraitement
1. Toujours sauvegarder avant de modifier
Créez une copie de la table avant tout DELETE ou UPDATE destructeur.
2. Tester sur un échantillon
Utilisez LIMIT ou une condition pour tester vos transformations sur un petit sous-ensemble.
3. Documenter les transformations
Gardez une trace des règles de nettoyage (comment vous avez traité les NULL, doublons, etc.).
4. Utiliser des transactions
BEGIN TRANSACTION; ... COMMIT; permet d'annuler en cas d'erreur.
5. Préférer les vues aux modifications directes
Les vues préservent les données brutes et permettent une traçabilité parfaite.
- ✅ Avez-vous une sauvegarde ?
- ✅ Avez-vous identifié toutes les colonnes à nettoyer ?
- ✅ Avez-vous défini les règles de transformation ?
- ✅ Avez-vous testé sur un échantillon ?
8. FAQ — Prétraitement SQL
Comment gérer les valeurs manquantes dans une colonne numérique ?
Plusieurs stratégies : (1) suppression des lignes si peu nombreuses, (2) imputation par la moyenne (distribution symétrique), (3) imputation par la médiane (distribution asymétrique), (4) imputation par 0 ou une valeur par défaut, (5) prédiction par régression (avancé). Le choix dépend du contexte métier.
Comment détecter des doublons sur plusieurs colonnes ?
SELECT colonne1, colonne2, COUNT(*) FROM table GROUP BY colonne1, colonne2 HAVING COUNT(*) > 1; Cette requête identifie les combinaisons dupliquées.
Quelle est la différence entre DELETE et TRUNCATE ?
DELETE supprime des lignes une par une (plus lent, peut être annulé avec ROLLBACK, peut avoir une clause WHERE). TRUNCATE supprime toutes les lignes en une fois (très rapide, ne peut pas être annulé sans sauvegarde).
Comment normaliser des données entre 0 et 1 en SQL ?
SELECT (colonne - min_col) / (max_col - min_col) AS normalise FROM table; Il faut calculer le min et le max au préalable, via une sous-requête ou une CTE.
Les vues ralentissent-elles les requêtes ?
Les vues simples non matérialisées sont réécrites par le SGBD dans la requête principale. Elles n'ajoutent pas de surcoût significatif. Les vues matérialisées (PostgreSQL) stockent physiquement les données et peuvent accélérer les requêtes, mais nécessitent un refresh.
Comment traiter les valeurs aberrantes (outliers) ?
Méthode de l'IQR (intervalle interquartile) : WHERE valeur NOT BETWEEN Q1 - 1.5*IQR AND Q3 + 1.5*IQR. Ou méthode Z-score : WHERE ABS(valeur - moyenne) / ecart_type > 3. Décidez ensuite de supprimer, plafonner ou conserver selon le contexte.
Conclusion
Le prétraitement et l'inspection des données avec SQL sont des étapes essentielles pour garantir la qualité des données avant leur utilisation en analyse ou en modélisation. SQL propose une panoplie d'outils pour détecter, nettoyer et transformer les données, rendant le processus plus efficace.
À retenir
- Inspection :
LIMIT,COUNT,IS NULL, statistiques descriptives - Nettoyage : suppression des NULL, imputation, correction des incohérences, dédoublonnage
- Transformation : normalisation (Min-Max), standardisation (Z-score), agrégation
- Automatisation : vues SQL pour réutiliser les données propres
- Bonnes pratiques : sauvegardes, transactions, tests sur échantillons
Apprenez les méthodes et les outils pour extraire de la valeur stratégique : Data Science : Le guide complet des méthodes et outils.