Ответ на вопрос

Используйте CTE (Common Table Expression) в сочетании с оконной функцией ROW_NUMBER() для детерминированной отметки дубликатов. Разделите набор данных по уникальным логическим ключевым столбцам (sensor_id, granularity), и примените ORDER BY, который отражает ваш приоритет хранения: signal_strength DESC и затем timestamp ASC, и, что немаловажно, PRIMARY KEY (например, log_id) в качестве финального критерия разрешения споров для обеспечения детерминированности. Внешний запрос затем удаляет все записи, где присвоенный номер строки превышает один, гарантируя, что выживет только запись с самым высоким приоритетом в каждой группе.

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

Ситуация из жизни

Промышленная IoT-платформа обрабатывала данные о вибрациях с высокочастотных роботов-манипуляторов в таблице с именем machine_telemetry. Из-за повторных попыток брокера MQTT во время сетевых разделений приблизительно сорок процентов таблицы состояло из дублирующих записей с одинаковым robot_id и time_bucket, но незначительно отличающихся по контрольным суммам полезной нагрузки. Отчетные панели удваивали учет рабочих часов, искажая графики обслуживания.

Решение 1: Коррелированный самосоединение. Один из подходов заключался в том, чтобы соединить таблицу саму с собой по robot_id и time_bucket, удаляя строки, где суррогатный ключ был больше, чем партнёрский ключ. Этот метод не требовал оконных функций. Однако его временная сложность приближалась к O(N²), что приводило к серьезному ухудшению производительности на наборе данных из 300 миллионов строк, и он неправильно обрабатывал NULL значения в составных ключах, не сопоставляя их.

Решение 2: Временная таблица с группировкой. Инженеры рассматривали создание временной таблицы, в которой хранились бы только выжившие log_id, идентифицированные через GROUP BY и агрегирование MIN(), после чего проходило бы усечение оригинала и повторная вставка. Хотя это логически верно, это требовало значительного временного пространства для хранения, необходимо было бы иметь привилегии DDL, недоступные в ограниченной производственной среде, и создавало бы кратковременное окно, когда данные выглядели пропавшими для параллельных читателей.

Решение 3: Оконная функция CTE. Команда реализовала стратегию ROW_NUMBER(), разделяя по дубликатному ключу (robot_id, time_bucket) и упорядочивая по метрикам качества сигнала. Это решение выполнялось как одна атомарная транзакция, предотвращая несоответствие данных во время очистки. Оно обработало весь прошлый объем менее чем за четыре минуты и сократило затраты на хранение на сорок процентов без отключения таблицы.

Что часто пропускают кандидаты

Почему действительно уникальный первичный ключ всегда должен служить последним столбцом в предложении ORDER BY функции оконной дедупликации, даже когда бизнес-логика, казалось бы, диктует порядок исключительно по неуникальной метке времени?

В ANSI SQL порядок строк, обладающих идентичными значениями для всех указанных ключей ORDER BY, является недетерминированным. Если две дублирующие записи имеют одинаковые timestamp и signal_strength, движок базы данных может располагать их произвольным образом. Следовательно, выполнение логики удаления несколько раз может случайным образом выбрать разные строки для сохранения, что приводит к непостоянным результатам и потенциальной потере критических данных. Добавление PRIMARY KEY обеспечивает полный порядок, гарантируя идемпотентные и воспроизводимые удаления.

Как ANSI SQL обрабатывает значения NULL внутри предложения PARTITION BY в сравнении со стандартными предикатами равенства в условиях соединения, и почему это различие ставит под угрозу точность дедупликации?

Внутри предложений GROUP BY или PARTITION BY, ANSI SQL рассматривает значения NULL как неразличимые и группирует их вместе (фактически, NULL равно NULL для агрегации). Напротив, в предложениях WHERE или предикатах соединения (ON t1.x = t2.x), выражение NULL = NULL оценивается как UNKNOWN, а не TRUE. Поэтому, если выполнять дедупликацию через самосоединение, строки с NULL значениями в соответствующих столбцах никогда не будут распознаны как дубликаты, что приведет к их ошибочному сохранению. Для корректной обработки NULL в соединениях, необходимо использовать синтаксис IS NOT DISTINCT FROM (ANSI SQL:1999).

При удалении миллионов дубликатов в одной транзакции, какой конкретный риск конкурентности и ресурсов угрожает стабильности производства, и какая техника ANSI SQL снижает этот риск?

Монолитное DELETE утверждение захватывает EXCLUSIVE LOCKS на каждую затронутую строку, что может привести к блокировке на уровне таблицы, заблокировав все параллельные вставки и чтения. Более того, оно приводит к значительному росту TRANSACTION LOG, что может создать риск исчерпания диска или сбоя восстановления. Чтобы смягчить это, соблюдая ANSI SQL, необходимо обрабатывать удаления пакетами. Это включает итеративное удаление ограниченного подмножества, определяемого с помощью FETCH FIRST n ROWS ONLY внутри подзапроса или с использованием прокручиваемого курсора, фиксируя каждую небольшую транзакцию независимо, чтобы освободить блокировки и постепенно сокращать сегменты журнала.