Odpowiedź na pytanie

Wykorzystaj CTE (Common Table Expression) w połączeniu z funkcją okienną ROW_NUMBER() do deterministycznego oznaczania duplikatów. Podziel zestaw danych według unikalnych kolumn kluczy logicznych (sensor_id, granularity), a następnie zastosuj klauzulę ORDER BY, która odzwierciedla Twoje priorytety przechowywania: signal_strength DESC, a następnie timestamp ASC, oraz klucz PRIMARY KEY (np. log_id) jako ostatecznego rozstrzygającego, aby zagwarantować deterministyczność. Zewnętrzne zapytanie następnie usuwa wszystkie rekordy, gdzie przypisany numer wiersza przekracza jeden, co zapewnia, że tylko najwyżej oceniany wiersz w danej grupie przetrwa.

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

Sytuacja z życia

Platforma IoT w przemyśle przetwarzała dane wibracyjne o wysokiej częstotliwości z robotów produkcyjnych w tabeli o nazwie machine_telemetry. Z powodu ponownych prób brokera MQTT podczas częściowych awarii sieci, około czterdziestu procent tabeli składało się z duplikatów nagrań dzielących ten sam robot_id i time_bucket, ale różniących się nieznacznie w sumach kontrolnych obciążenia. Pulpity raportowe podwójnie liczyły godziny operacyjne, zniekształcając harmonogramy konserwacji.

Rozwiązanie 1: Połączony self-join. Jedno z podejść polegało na połączeniu tabeli z samą sobą na podstawie robot_id i time_bucket, usuwając wiersze, gdzie klucz zastępczy był większy niż klucz partnera. Ta metoda nie wymagała funkcji okiennych. Jednak jej złożoność czasowa zbliżała się do O(N²), co powodowało poważne pogorszenie wydajności na zbiorze danych o 300 milionach wierszy, a także niewłaściwie obsługiwała wartości NULL w kluczach złożonych, nie udając się do ich dopasowania.

Rozwiązanie 2: Tabela stagingowa z grupowaniem. Inżynierowie rozważali utworzenie tymczasowej tabeli zawierającej jedynie przetrwałe log_id, zidentyfikowane za pomocą GROUP BY i agregacji MIN(), a następnie wyczyszczenia oryginalnej tabeli i ponownego wstawienia. Choć logicznie sensowne, wymagało to znacznej przestrzeni dyskowej tymczasowej, potrzebnych uprawnień DDL, które nie były dostępne w ograniczonym środowisku produkcyjnym, a także stworzyło krótkotrwałe okno, w którym dane wydawały się zniknięte dla równoległych czytelników.

Rozwiązanie 3: Funkcja okienna CTE. Zespół wdrożył strategię ROW_NUMBER(), dzieląc według klucza duplikatu (robot_id, time_bucket) i porządkując według metryk jakości sygnału. To rozwiązanie wykonane jako jedna transakcja atomowa, zapobiegając niespójności danych podczas czyszczenia. Przetworzono całe zaległości w mniej niż cztery minuty i zmniejszono koszty magazynowania o czterdzieści procent, nie wyłączając tabeli.

Co często umyka kandydatom

Dlaczego prawdziwie unikalny klucz podstawowy musi zawsze służyć jako ostatnia kolumna w klauzuli ORDER BY funkcji deduplikacji, nawet gdy logika biznesowa wydaje się narzucać porządkowanie tylko według nieunikalnego znacznika czasu?

W ANSI SQL porządkowanie wierszy, które mają identyczne wartości dla wszystkich określonych kluczy ORDER BY, jest niedeterministyczne. Jeśli dwa duplikaty mają dokładnie ten sam timestamp i signal_strength, silnik bazy danych może je uporządkować w sposób arbitralny. W związku z tym wielokrotne wykonywanie logiki usuwania może losowo wybierać różne wiersze do zachowania, prowadząc do niespójnych wyników i potencjalnej utraty krytycznych danych. Dodanie PRIMARY KEY zapewnia całkowite uporządkowanie, gwarantując idempotentne i powtarzalne usunięcia.

Jak ANSI SQL traktuje wartości NULL w klauzuli PARTITION BY w porównaniu do standardowych predykatów równości w warunkach złączenia, i dlaczego ta różnica zagraża dokładności deduplikacji?

W klauzulach GROUP BY lub PARTITION BY ANSI SQL traktuje wartości NULL jako niedostrzegalne i grupuje je razem (efektywnie, NULL równa się NULL dla agregacji). W przeciwieństwie do tego, w klauzulach WHERE lub predykatach łączenia (ON t1.x = t2.x), wyrażenie NULL = NULL oceniane jest jako UNKNOWN, a nie TRUE. W związku z tym, jeśli deduplikujesz za pomocą self-join, wiersze z wartościami NULL w odpowiadających kolumnach nigdy nie będą rozpoznawane jako duplikaty, co powoduje, że przetrwają błędnie. Aby poprawnie obsługiwać wartości NULL w złączeniach, należy zastosować składnię IS NOT DISTINCT FROM (ANSI SQL:1999).

Usuwając miliony duplikatów w jednej transakcji, jakie konkretne zagrożenie dotyczące współbieżności i zasobów zagraża stabilności produkcji, i jaka technika ANSI SQL łagodzi to ryzyko?

Monolityczne polecenie DELETE nabywa EXCLUSIVE LOCKS na każdym dotkniętym wierszu, co może prowadzić do wzrostu do blokady na poziomie tabeli, która zablokuje wszystkie równoległe wstawienia i odczyty. Dodatkowo generuje ogromny wzrost TRANSACTION LOG, ryzykując wyczerpanie dysku lub awarie odzyskiwania. Aby złagodzić to, przestrzegając ANSI SQL, należy przetwarzać usunięcia w partiach. Obejmuje to iteracyjne usuwanie ograniczonego podzbioru identyfikowanego za pomocą FETCH FIRST n ROWS ONLY w podzapytaniu lub stosowanie kursorów przelotnych, zatwierdzając każdą małą transakcję niezależnie, aby zwolnić blokady i sukcesywnie skracać segmenty dziennika.