Sorunun Cevabı

Tekrarları deterministik olarak etiketlemek için CTE (Ortak Tablo İfadesi) ile birlikte ROW_NUMBER() pencere işlevini kullanın. Veri kümesini benzersiz mantıksal anahtar sütunlarına (sensor_id, granularity) göre bölün ve saklama önceliğinizi yansıtan bir ORDER BY ifadesi uygulayın: signal_strength DESC ardından timestamp ASC, ve kesinlikle deterministikliği sağlamak için son bir eşitlik belirleyici olarak PRIMARY KEY (örneğin, log_id) ekleyin. Dış sorgu, atanmış satır numarasının birden fazla olduğu tüm kayıtları silerek, her grup için yalnızca en yüksek öncelikli satırın kalmasını sağlar.

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

Hayattan Bir Durum

Bir endüstriyel IoT platformu, üretim robotlarından yüksek frekanslı titreşim verilerini machine_telemetry adlı bir tabloya aldı. MQTT komut kuyruğu ağ bölünmeleri sırasında yeniden denemeler nedeniyle, tablonun yaklaşık yüzde kırkı, aynı robot_id ve time_bucket ile paylaşan fakat payload kontrol toplamlarında hafif farklılıklar gösteren tekrarlayan kayıtlar içeriyordu. Raporlama panelleri operasyonel saatleri çift sayarak bakım programlarını çarpıttı.

Çözüm 1: İlişkili Kendi Kendine Birleşim. Bir yaklaşım, tabloyu kendine robot_id ve time_bucket ile birleştirerek, yedek anahtarın partnerin anahtarından büyük olduğu satırları silmekti. Bu yöntem pencere işlevleri gerektirmiyordu. Ancak zaman karmaşıklığı O(N²) seviyesine yaklaştı, bu da 300 milyon satırlık veri kümesinde ciddi performans düşüşüne neden oldu ve birleşik anahtarların NULL değerlerini yanlış yöneterek eşleştirmeyi başaramadı.

Çözüm 2: Gruplama ile Aşamalı Tablo. Mühendisler, yalnızca hayatta kalan log_id'leri içeren geçici bir tablo oluşturmayı düşündü ve GROUP BY ve MIN() toplama işlemleri aracılığıyla tanımladıktan sonra, orijinal tabloyu kesip yeniden eklemeyi planladılar. Mantıksal olarak sağlam olsa da, bu önemli geçici depolama alanı gerektiriyordu, kısıtlı üretim ortamında mevcut olmayan DDL yetkileri gerektiriyordu ve verilerin eşzamanlı okuyuculara kaybolmuş gibi görünmesine neden olan kısa bir pencere yarattı.

Çözüm 3: Pencere İşlevi CTE. Ekip, tekrar eden anahtar (robot_id, time_bucket) ile bölünerek ve sinyal kalitesi metriklerine göre sıralanarak ROW_NUMBER() stratejisini uyguladı. Bu çözüm, veri tutarsızlığını önleyerek tek bir atomik işlem olarak gerçekleştirildi. Tüm backlog'u dört dakikadan kısa sürede işledi ve tabloyu çevrimdışı almadan depolama maliyetlerini yüzde kırk düşürdü.

Adayların Genellikle Gözden Kaçırdığı Noktalar

Neden gerçekten benzersiz birincil anahtar, bir deduplikasyon pencere işlevinin ORDER BY ifadesindeki son sütun olarak her zaman kullanılmalıdır, hatta iş mantığı sadece benzersiz olmayan bir zaman damgasına göre sıralamayı dikte etse bile?

ANSI SQL'de, tüm belirtilen ORDER BY anahtarları için aynı değerlere sahip satırların sıralanması deterministik değildir. Eğer iki tekrarlayan kayıt aynı timestamp ve signal_strength değerlerine sahipse, veritabanı motoru bunları keyfi olarak düzenleme özgürlüğüne sahiptir. Sonuç olarak, silme mantığını birden fazla kez çalıştırmak, korunacak farklı satırları rastgele seçebilir, bu da tutarsız sonuçlara ve kritik verilerin kaybına yol açabilir. PRIMARY KEY ekleyerek, toplam bir sıralama sağlanır ve idempotent ve tekrarlanabilir silmeler garanti edilir.

ANSI SQL, PARTITION BY ifadesi içindeki NULL değerlerini, bir birleşim koşulundaki standart eşitlik yargıları ile nasıl işler ve bu ayrım deduplikasyon doğruluğunu neden tehlikeye atar?

GROUP BY veya PARTITION BY ifadeleri içinde, ANSI SQL NULL değerlerini ayırt edilemez olarak kabul eder ve bunları bir araya getirir (etkili olarak, NULL NULL eşittir). Tam tersi, WHERE ifadelerinde veya birleşim yargılarında (ON t1.x = t2.x), NULL = NULL ifadesi UNKNOWN olarak değerlendirilir, TRUE olarak değil. Bu nedenle, bir kendi kendine birleşim yoluyla deduplikasyonda, eşleşen sütunlarda NULL değerleri olan satırlar asla tekrar olarak tanınmaz ve yanlışlıkla hayatta kalmalarına neden olur. Birleşimlerde NULL'ları doğru şekilde yönetmek için, IS NOT DISTINCT FROM sözdizimini kullanmalısınız (ANSI SQL:1999).

Milyonlarca tekrarı tek bir işlemle silerken, üretim istikrarını tehdit eden belirli bir eş zamanlılık ve kaynak tehlikesi nedir ve bu riski azaltmak için hangi ANSI SQL tekniği kullanılır?

Monolitik bir DELETE ifadesi, etkilenen her satır üzerinde EXCLUSIVE LOCKS alır, potansiyel olarak tüm eşzamanlı eklemeleri ve okumaları engelleyen bir tablo seviyesine kilitlenebilir. Dahası, büyük bir TRANSACTION LOG büyümesine yol açar, disk tükenmesi veya kurtarma hataları riski taşır. Bunu azaltmak için ANSI SQL standardına uyarak, silmeleri partiler halinde işlemek gerekir. Bu, bir alt sorgu içinde FETCH FIRST n ROWS ONLY ile belirlenen sınırlı bir alt kümenin tekrarlı olarak silinmesini ve her küçük işlemi bağımsız olarak taahhüt ederek kilitleri serbest bırakmayı ve günlüğü kademeli olarak kısaltmayı içerir.