Zorunlu bir kontrol listesine karşı tam uyumu doğrularken, kısmi yerine tam yerine getirmeyi yeterli kılmadığında, ANSI SQL'de ilişkisel bölme nasıl uygulanır ve GROUP BY toplama işlemlerine bağlı kalınmadan tüm gereksinimleri karşılayan varlıkları nasıl tanımlarsınız?

Sorunun yanıtı

Sorunun Tarihi

İlişkisel bölme, 1970 yılında Edgar F. Codd tarafından, evrensel kuantifikasyonu (∀) ilişkisel cebirde ifade etmek amacıyla standart bir tanım ile kartezyen çarpımın tersine tanımlanmıştır. ANSI SQL, varlıklar arasında doğal olarak kullanılan WHERE koşulları ve birleşimler aracılığıyla varoluşsal kuantifikasyonu (∃) yerel olarak uygulanmasına rağmen, yerel bir bölme operatörüne sahip olmadığı için geliştiricilerin bu küme teorik işlemi mantıksal olumsuzlama veya sayım stratejileri kullanarak simüle etmek zorunda kalmaktadır. Bu desen, "tam setleri" tanımlamak için kritik öneme sahip olduğu düzenleyici uyum, yetkilendirme matrisleri ve yeterlilik izleme sistemlerinde sürekli olarak ortaya çıkmaktadır.

Problem

Bir bölünen tablo EmployeeTraining(employee_id, module_id) ve bir bölücü tablo RequiredModules(module_id) verildiğinde, amaç tüm bölücü satırlarla ilişkili her employee_id'yi döndürmektir. Zorluk, herhangi bir eşleşme bulmayı hedefleyen basit birleşimlerin ötesine geçmektedir; bölme, toplam kapsayıcılığı doğrulamayı gerektirir. Kritik olarak, çözüm, yinelenen tamamlama kayıtları, boş gereksinim setleri (boşlukta doğru) ile başa çıkmalı ve prosedürel mantık olmadan verimli bir şekilde çalışmalıdır.

Çözüm

Klasik ANSI SQL yaklaşımı, mevcut olmayan bir gereksinim modülü olmadığını doğrulayan seçkin çalışanlar ile iki kez olumsuzlama kullanır. Bu, iç içe geçmiş NOT EXISTS koşullarına dönüşür. Alternatif olarak, sayım yöntemi, belirli tamamlama sayısını gerekli toplam ile karşılaştırır, ancak yinelenen kayıtların dikkatli bir şekilde ele alınmasını gerektirir.

-- Çift Olumsuzlama: Saf İlişkisel Bölme
SELECT DISTINCT e.employee_id
FROM EmployeeTraining e
WHERE NOT EXISTS (
    SELECT 1
    FROM RequiredModules r
    WHERE NOT EXISTS (
        SELECT 1
        FROM EmployeeTraining e2
        WHERE e2.employee_id = e.employee_id
          AND e2.module_id = r.module_id
    )
);

-- Sayım Yöntemi (yinelenen kayıtlarla birlikte)
SELECT employee_id
FROM (
    SELECT e.employee_id, 
           COUNT(DISTINCT e.module_id) AS completed_count
    FROM EmployeeTraining e
    JOIN RequiredModules r ON e.module_id = r.module_id
    GROUP BY e.employee_id
) sub
WHERE completed_count = (SELECT COUNT(*) FROM RequiredModules);

Hayat Durumundan

Hayat Durumundan

Bir havacılık bakım firması, motor onarımı için teknisyenleri sertifikalandırmak zorundaydı. FAA, Mechanic_Completions tablosunda izlenen beş belirli güvenlik modülünün tamamlanmasını zorunlu kıldı, ancak teknisyenler genellikle başarısız modülleri tekrar aldığından, yinelenen satırlar oluşturuyordu. 1,200 teknisyen için ortaya çıkan 200 olası modülü her gün kontrol etmek, yinelenenleri göz ardı eden ve gereksinim listesinin geçici olarak boş olabileceği denetim senaryolarını ele alan bir sorgu gerektiriyordu.

Çözüm 1: COUNT(DISTINCT) ile GROUP BY Bu yaklaşım tablolara katıldı, teknisyene göre grupladı ve farklı sayıları karşılaştırdı. Temel avantajı okunabilirlikti; genç geliştiriciler mantığı hemen anladı. Ancak, 2 milyon tarihsel kayıtta DISTINCT işlemi nedeniyle önemli ölçüde performans kaybına uğradı. Daha kritik bir şekilde, belirli bir COALESCE işleme olmadan, RequiredModules tablosu boşken sıfır teknisyen döndürmesi matematiksel ilkeyi ihlal ediyordu; boş küme üzerindeki evrensel kuantifikasyon her öğe için boşlukta doğru kabul ediliyordu.

Çözüm 2: NOT EXISTS ile Çift Olumsuzlama Bu yöntem, eksik modülleri kontrol etmek için iki iç içe NOT EXISTS koşulu kullandı. Yalnızca varlığı kontrol ettiğinden, yinelenen tamamlama kayıtlarını doğal olarak ele aldı (yarı-join davranışı); gereksinim seti boş olduğunda tüm teknisyenleri doğru bir şekilde geri döndürdü. Dezavantajı daha karmaşık yürütme planlarını içermesiydi; optimizasyoncular, bazen karmaçlama yerine iç içe döngü birleşimlerini seçti, ancak module_id üzerindeki uygun indeksleme bunu hafifletmişti.

Seçilen Çözüm ve Sonuç Ekip, veri bütünlüğü kurallarının yinelenen tamamlama girişlerine izin vermesi nedeniyle, sayım yönteminin pahalı DISTINCT işlemleri olmadan riskli olmasını gerekçe göstererek çift olumsuzlama yaklaşımını seçti. Sorgu, 1,200 teknisyenden 847'sinin tam olarak sertifikalandığını 150 ms'den kısa bir sürede tespit etti. Tüm gereksinimlerin geçici olarak askıya alındığı sonraki düzenleyici bir denetim sırasında, sorgu tüm 1,200 teknisyeni uyumlu olarak doğru bir şekilde tanımladı (boşlukta doğru) ve gereksiz yere iş gücünün etkisiz hale getirilmesini önleyerek mantıksal doğruluğu korudu.

Adayların Sıklıkla Gözden Kaçırdığı Noktalar

Adayların Sıklıkla Gözden Kaçırdığı Noktalar

Gereksinim Modülleri tablosu sıfır satır içerdiğinde sorgu nasıl davranır ve bu neden matematiksel olarak önemlidir?

Bölücü boş olduğunda, ilişkisel bölmenin tüm bölüm setini (tüm çalışmaları) döndürmesi gerekmektedir, çünkü boşlukta doğru, her öğenin "boş kümedeki tüm nesneler için geçerlidir" olmasını zorunlu kılar. Çift olumsuzlama yöntemi bunu doğal olarak gerçekleştirir; zira gereksinim modülü olmadığında, içteki NOT EXISTS hiçbir eksik modül bulamaz, bu nedenle dış koşul kimseyi hariç tutmaz. Buna karşılık, sayım yöntemi completed_count = (SELECT COUNT(*) FROM RequiredModules) sayıları sıfıra eşitlediği için yalnızca sıfır tamamlama sahibi olan teknisyenleri döndürür. Adayların, bölücü boşken tüm satırları döndürmek için bir COALESCE sarmalayıcı veya CASE mantığı uygulaması, ya da bu kenar durumunu içsel olarak ele alan çift olumsuzlama modelini kullanması gerekmektedir.

COUNT(*) ile COUNT(DISTINCT module_id) kullanmak neden yanlış pozitifler üretir ve yinelenmeler çift olumsuzlama yöntemini nasıl etkiler?

Bir teknisyen Modül A'yı iki kez (ilk başarısızlık, ardından tekrar) tamamlarsa, COUNT(*) 2 döndürür. Yalnızca Modüller A ve B'nin gerektiği durumda, B'yi kaçırmış olan bir teknisyen, ancak iki A kaydıyla, eşitlik kontrolünü yanlış şekilde sağlarken 2 sayısı gösterir. Bu kritik uyum boşlukları oluşturur. Adaylar genellikle dış anahtar kısıtlamalarının yinelenmeleri önleyebileceğini varsayarak DISTINCT'i atlar. Çift olumsuzlama yöntemi yalnızca varlığı kontrol eder (SELECT 1), bu nedenle bölüm tablosundaki yinelenen kayıtlara karşı bağışıklıdır; herhangi bir ilişki varsa, modül karşılanır. Bu ayrımın, mükemmel benzersizlik kısıtlamalarının olmadığı veri ortamları için kritik olduğunu anlamak önemlidir.

Kesin ilişkisel bölme ile kalanı olan bölme arasındaki fark nedir ve tam olarak gerekli modülleri tamamlayan çalışanları bulmak için sorguyu nasıl değiştirirsiniz?

Yukarıdaki çözümler "kalanı olan bölme" (gevşek bölme) uygular ve en az gereken modüllere (üretkenler) sahip çalışanları döndürür. Kesin bölme, çalışanın ihtiyaç duyulan modüllerin ötesinde hiçbir ek modül bulundurmaması gerektiğini gerektirir. Bunu başarmak için, adayların, teknisyenin toplam farklı modül sayısının gereken sayı ile eşit olduğuna dair bir filtreleme koşulu eklemesi gerekmektedir: HAVING COUNT(DISTINCT module_id) = (SELECT COUNT(*) FROM RequiredModules). Birçok aday, ilişkisel bölümün "tam olarak bu ve yalnızca bunlar" olduğunu varsayarak yanlış onaylamalar yol açan yetkilendirme hatalarına neden olur.