Respuesta a la pregunta

Utiliza una CTE (Expresión de Tabla Común) combinada con la función de ventana ROW_NUMBER() para etiquetar duplicados de manera determinista. Particiona el conjunto de datos por las columnas de clave lógica única (sensor_id, granularity), y aplica una cláusula ORDER BY que refleje tu prioridad de retención: signal_strength DESC seguido de timestamp ASC, y crucialmente, la PRIMARY KEY (por ejemplo, log_id) como un último desempate para garantizar determinismo. La consulta externa luego elimina todos los registros donde el número de fila asignado excede uno, asegurando que solo sobreviva la fila de mayor prioridad por grupo.

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

Situación de la vida

Una plataforma industrial de IoT ingirió datos de vibración de alta frecuencia de robots de manufactura en una tabla llamada machine_telemetry. Debido a los reintentos del broker MQTT durante particiones de red, aproximadamente el cuarenta por ciento de la tabla consistía en grabaciones duplicadas que compartían el mismo robot_id y time_bucket, pero diferían ligeramente en los checksums de carga útil. Los paneles de informes estaban contando dobles las horas operativas, distorsionando los cronogramas de mantenimiento.

Solución 1: Auto-Unión Correlacionada. Un enfoque involucró unir la tabla consigo misma en robot_id y time_bucket, eliminando filas donde la clave sustituta era mayor que la clave de la pareja. Este método no requirió funciones de ventana. Sin embargo, su complejidad temporal se aproximó a O(N²), causando una degradación severa del rendimiento en el conjunto de datos de 300 millones de filas, y manejó valores NULL en claves compuestas incorrectamente al no poder emparejarlos.

Solución 2: Tabla Intermedia con Agrupación. Los ingenieros consideraron crear una tabla temporal que alojara solo los log_id sobrevivientes identificados mediante GROUP BY y agregaciones MIN(), luego truncar la original y volver a insertar. Aunque lógicamente sólida, esto requería un espacio de almacenamiento temporal significativo, necesitaba privilegios DDL no disponibles en el entorno de producción restringido, y creó una breve ventana donde los datos parecían faltar a lectores concurrentes.

Solución 3: CTE de Función de Ventana. El equipo implementó la estrategia de ROW_NUMBER(), particionando por la clave duplicada (robot_id, time_bucket) y ordenando por métricas de calidad de señal. Esta solución se ejecutó como una sola transacción atómica, previniendo la inconsistencia de datos durante la limpieza. Procesó todo el retraso en menos de cuatro minutos y redujo los costos de almacenamiento en un cuarenta por ciento sin sacar la tabla fuera de línea.

Lo que los candidatos a menudo pasan por alto

¿Por qué debe siempre servir una clave primaria verdaderamente única como la última columna en la cláusula ORDER BY de una función de ventana de desduplicación, incluso cuando la lógica de negocio parece dictar el orden solo por un timestamp no único?

En ANSI SQL, el orden de las filas que poseen valores idénticos para todas las claves especificadas en ORDER BY no es determinista. Si dos registros duplicados comparten el mismo timestamp y signal_strength, el motor de la base de datos es libre de ordenarlos de manera arbitraria. En consecuencia, ejecutar la lógica de eliminación múltiples veces podría seleccionar aleatoriamente diferentes filas para preservación, llevando a resultados inconsistentes y posible pérdida de datos críticos. Adjuntar la PRIMARY KEY garantiza un orden total, asegurando eliminaciones idempotentes y reproducibles.

¿Cómo trata ANSI SQL los valores NULL dentro de una cláusula PARTITION BY en comparación con predicados de igualdad estándar en una condición de unión, y por qué esta distinción pone en peligro la precisión de la desduplicación?

Dentro de cláusulas GROUP BY o PARTITION BY, ANSI SQL trata los valores NULL como indistinguibles y los agrupa juntos (efectivamente, NULL igual a NULL para la agregación). Por el contrario, en cláusulas WHERE o predicados de unión (ON t1.x = t2.x), la expresión NULL = NULL evalúa a DESCONOCIDO, no a VERDADERO. Por lo tanto, si se desduplican mediante una auto-unión, las filas con valores NULL en las columnas coincidentes nunca serán reconocidas como duplicadas, lo que provoca que sobrevivan erróneamente. Para manejar correctamente los NULL en uniones, debes emplear la sintaxis IS NOT DISTINCT FROM (ANSI SQL:1999).

Al eliminar millones de duplicados en una sola transacción, ¿qué riesgo específico de concurrencia y recursos amenaza la estabilidad de producción, y qué técnica de ANSI SQL mitiga este riesgo?

Una declaración DELETE monolítica adquiere EXCLUSIVE LOCKS en cada fila afectada, lo que puede escalar a un bloqueo a nivel de tabla que bloquea todas las inserciones y lecturas concurrentes. Además, genera un crecimiento masivo del TRANSACTION LOG, arriesgando la saturación del disco o fallos en la recuperación. Para mitigar esto mientras se adhiere a ANSI SQL, se debe procesar las eliminaciones en lotes. Esto implica eliminar iterativamente un subconjunto limitado identificado por FETCH FIRST n ROWS ONLY dentro de una subconsulta o utilizando un cursor desplazable, comprometiendo cada pequeña transacción de manera independiente para liberar bloqueos y truncar segmentos de registro progresivamente.