Réponse à la question

Utilisez une CTE (expression de table commune) combinée à la fonction de fenêtre ROW_NUMBER() pour étiqueter les doublons de manière déterministe. Divisez l'ensemble de données par les colonnes de clé logique unique (sensor_id, granularity), et appliquez une clause ORDER BY qui reflète votre priorité de conservation : signal_strength DESC suivi de timestamp ASC, et surtout, la CLÉ PRINCIPALE (par exemple, log_id) comme dernier critère de départage pour garantir le déterminisme. La requête externe supprime ensuite tous les enregistrements dont le numéro de ligne attribué dépasse un, veillant ainsi à ce que seule la ligne de priorité la plus élevée par groupe survive.

WITH RankedLogs AS (
  SELECT log_id,
         ROW_NUMBER() OVER (
           PARTITION BY sensor_id, granularity 
           ORDER BY signal_strength DESC, timestamp ASC, log_id ASC
         ) AS priority_rank
  FROM telemetry_logs
)
DELETE FROM telemetry_logs
WHERE log_id IN (
  SELECT log_id 
  FROM RankedLogs 
  WHERE priority_rank > 1
);

Situation de la vie réelle

Une plateforme IoT industrielle a ingéré des données de vibration à haute fréquence provenant de robots de fabrication dans une table nommée machine_telemetry. En raison des tentatives de répétition du broker MQTT lors des partitions réseau, environ quarante pour cent de la table consistait en enregistrements en double partageant le même robot_id et time_bucket, bien que différant légèrement dans les sommes de contrôle des charges utiles. Les tableaux de bord de reporting comptaient doublement les heures d'exploitation, faussant les horaires de maintenance.

Solution 1 : Auto-jointure corrélée. Une approche consistait à joindre la table à elle-même sur robot_id et time_bucket, en supprimant les lignes où la clé de substitution était supérieure à celle du partenaire. Cette méthode ne nécessitait pas de fonctions de fenêtre. Cependant, sa complexité temporelle approchait O(N²), provoquant une dégradation sévère des performances sur l'ensemble de données de 300 millions de lignes, et elle traitait incorrectement les valeurs NULL dans les clés composites en échouant à les faire correspondre.

Solution 2 : Table de staging avec regroupement. Les ingénieurs ont envisagé de créer une table temporaire hébergeant uniquement les log_id survivants identifiés via GROUP BY et des agrégations MIN(), puis de tronquer l'original et de réinsérer. Bien que logiquement solide, cela nécessitait un espace de stockage temporaire significatif, nécessitait des privilèges DDL non disponibles dans l'environnement de production restreint, et créait une brève fenêtre où les données semblaient manquantes pour les lecteurs concurrents.

Solution 3 : CTE fonction de fenêtre. L'équipe a mis en œuvre la stratégie ROW_NUMBER(), en partitionnant par la clé dupliquée (robot_id, time_bucket) et en ordonnant par des métriques de qualité du signal. Cette solution s'est exécutée comme une seule transaction atomique, empêchant l'incohérence des données pendant le nettoyage. Elle a traité l'ensemble du backlog en moins de quatre minutes et a réduit les coûts de stockage de quarante pour cent sans mettre la table hors ligne.

Ce que les candidats manquent souvent

Pourquoi une clé primaire véritablement unique doit-elle toujours servir de dernière colonne dans la clause ORDER BY d'une fonction de fenêtre de dé-duplication, même lorsque la logique métier semble dicter un ordre uniquement par un timestamp non unique ?

Dans SQL ANSI, l'ordre des lignes qui possèdent des valeurs identiques pour toutes les clés spécifiées dans ORDER BY est non déterministe. Si deux enregistrements en double partagent exactement le même timestamp et signal_strength, le moteur de base de données est libre de les arranger de manière arbitraire. Par conséquent, l'exécution de la logique de suppression plusieurs fois pourrait sélectionner aléatoirement différentes lignes à préserver, entraînant des résultats incohérents et une perte potentielle de données critiques. Ajouter la CLÉ PRINCIPALE garantit un ordre total, assurant des suppressions idempotentes et reproductibles.

Comment SQL ANSI traite-t-il les valeurs NULL dans une clause PARTITION BY par rapport aux prédicats d'égalité standard dans une condition de jointure, et pourquoi cette distinction met-elle en danger l'exactitude de dé-duplication ?

Dans les clauses GROUP BY ou PARTITION BY, SQL ANSI traite les valeurs NULL comme indistinguables et les regroupe ensemble (effectivement, NULL égal NULL pour l'agrégation). En revanche, dans les clauses WHERE ou les prédicats de jointure (ON t1.x = t2.x), l'expression NULL = NULL s'évalue comme INCONNUE, pas VRAIE. Par conséquent, si vous dédupliquez via une auto-jointure, les lignes avec des valeurs NULL dans les colonnes correspondantes ne seront jamais reconnues comme des doublons, les laissant survivre erronément. Pour traiter correctement les NULL dans les jointures, vous devez utiliser la syntaxe IS NOT DISTINCT FROM (SQL ANSI : 1999).

Lors de la suppression de millions de doublons en une seule transaction, quel risque de concurrence et de ressources menace la stabilité de la production, et quelle technique SQL ANSI atténue ce risque ?

Une instruction DELETE monolithique acquiert des VERROUS EXCLUSIFS sur chaque ligne affectée, pouvant s'élever à un verrouillage au niveau de la table qui bloque tous les inserts et lectures concurrents. De plus, elle génère une croissance massive du LOG DES TRANSACTIONS, risquant l'épuisement du disque ou des défaillances de récupération. Pour atténuer cela tout en respectant SQL ANSI, il faut traiter les suppressions par lots. Cela implique de supprimer de manière itérative un sous-ensemble limité identifié par FETCH FIRST n ROWS ONLY dans une sous-requête ou en utilisant un curseur défilant, en engageant chaque petite transaction indépendamment pour libérer les verrous et tronquer progressivement les segments de journal.