Antwoord op de vraag

Gebruik een CTE (Common Table Expression) gecombineerd met de ROW_NUMBER() venstervariabele om duplicaten deterministisch te taggen. Verdeel de dataset op basis van de unieke logische sleutelkolommen (sensor_id, granularity), en pas een ORDER BY-clausule toe die je retentieprioriteit weerspiegelt: signal_strength DESC gevolgd door timestamp ASC, en cruciaal, de PRIMARY KEY (bijv. log_id) als een laatste tiebreaker om determinisme te garanderen. De buitenste query verwijdert dan alle records waar het toegewezen rijnummer hoger is dan één, zodat alleen de hoogste prioriteitsrij per groep overblijft.

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
);

Situatie uit het leven

Een industriële IoT-platform verzamelde hoogfrequente vibratiegegevens van fabricagerobots in een tabel genaamd machine_telemetry. Door MQTT-broker herhalingen tijdens netwerkonderbrekingen bestond ongeveer veertig procent van de tabel uit dubbele opnames die dezelfde robot_id en time_bucket deelden, maar iets verschilden in payload checksums. Rapportagedashboards telden operationele uren dubbel, wat de onderhoudschema's verstoorde.

Oplossing 1: Correlatieve Zelf-Join. Een aanpak omvatte het aansluiten van de tabel op zichzelf op robot_id en time_bucket, rijen verwijderen waar de surrogaatsleutel groter was dan de sleutel van de partner. Deze methode vereiste geen venstervariabelen. Echter, de tijdcomplexiteit benaderde O(N²), wat ernstige prestatieproblemen veroorzaakte op de dataset van 300 miljoen rijen, en het behandelde NULL-waarden in samengestelde sleutels onjuist door ze niet te matchen.

Oplossing 2: Staging Tabel met Groepering. Ingenieurs overwegen het creëren van een tijdelijke tabel waarin alleen de overlevende log_ids worden ondergebracht die zijn geïdentificeerd via GROUP BY en MIN()-aggregaties, gevolgd door het truncaten van de originele en opnieuw invoegen. Hoewel logisch, vereiste dit aanzienlijke tijdelijke opslagcapaciteit, vereiste DDL-rechten die niet beschikbaar waren in de beperkte productieomgeving, en creëerde een korte periode waarin gegevens verdwenen leken voor gelijktijdige lezers.

Oplossing 3: Venstervariabele CTE. Het team implementeerde de ROW_NUMBER() strategie, partitionerend op de dubbele sleutel (robot_id, time_bucket) en ordenend op signaalkwaliteitsmetrieken. Deze oplossing werd uitgevoerd als een enkele atomische transactie, wat gegevensinconsistentie tijdens de opruiming verhinderde. Het verwerkte de gehele achterstand in minder dan vier minuten en verlaagde de opslagskosten met veertig procent zonder de tabel offline te nemen.

Wat kandidaten vaak missen

Waarom moet een echt unieke primaire sleutel altijd de laatste kolom zijn in de ORDER BY-clausule van een deduplicatie venstervariabele, zelfs wanneer bedrijfslogica ogenschijnlijk alleen op een niet-unieke tijdstempel dicteert?

In ANSI SQL is de ordening van rijen met identieke waarden voor alle opgegeven ORDER BY-sleutels niet-deterministisch. Als twee dubbele records exact dezelfde timestamp en signal_strength delen, kan de database-engine ze willekeurig ordenen. Gevolgelijk kan het uitvoeren van de verwijderingslogica meerdere keren verschillende rijen selecteren voor bewaring, wat leidt tot inconsistente resultaten en mogelijk verlies van kritieke gegevens. Het toevoegen van de PRIMARY KEY zorgt voor een totale ordening, wat garant staat voor idempotente en reproduceerbare verwijderingen.

Hoe behandelt ANSI SQL NULL-waarden binnen een PARTITION BY-clausule in vergelijking met standaard gelijkheidspredicaten in een join-voorwaarde, en waarom brengt dit onderscheid de nauwkeurigheid van deduplicatie in gevaar?

Binnen GROUP BY of PARTITION BY-clausules behandelt ANSI SQL NULL-waarden als ononderscheidbaar en groepeert ze samen (effectief, NULL gelijk NULL voor aggregatie). Daarentegen evalueert de expressie NULL = NULL in WHERE-clausules of join-predicaten (ON t1.x = t2.x) tot ONBEKEND, niet WAAR. Daarom, als je dedupliceert via een zelf-join, zullen rijen met NULL-waarden in de bijpassende kolommen nooit als duplicaten worden herkend, waardoor ze onterecht overleven. Om NULLs correct in joins af te handelen, moet je de syntaxis IS NOT DISTINCT FROM gebruiken (ANSI SQL:1999).

Wanneer je miljoenen duplicaten in één transactie verwijdert, welk specifiek concurrentie- en hulpbronrisico bedreigt de productie-stabiliteit, en welke ANSI SQL-techniek verlicht dit risico?

Een monolithische DELETE verklaring verwerft EXCLUSIVE LOCKS op elke aangetaste rij, wat potentieel escaleert naar een tabelniveau slot dat alle gelijktijdige invoegen en lezen blokkeert. Bovendien genereert het enorme TRANSACTION LOG groei, wat risico op schijfuitputting of herstelfalen met zich meebrengt. Om dit te mitigeren terwijl je aan ANSI SQL voldoet, moet men de verwijderingen in batches verwerken. Dit houdt in dat je iteratief een beperkte subset verwijdert die wordt geïdentificeerd door FETCH FIRST n ROWS ONLY binnen een subquery of een scrollbare cursor, en elke kleine transactie onafhankelijk commit, om sloten vrij te geven en logsegmenten geleidelijk te verkorten.