AI’ın LEFT JOIN sorgusunda kaydı olmayan gruplar neden kayboluyor?

Sıfır açık işi olan grup rapordan düşmemeli

Yönetim raporunda üç çalışma grubu bulunması gerekiyor; ajan tarafından yazılan sorgu yalnız birini gösteriyor. Görünen grubun açık iş sayısı doğru. Eksik olan şey, açık işi olmayan grupların sıfırla görünmesi. Yalnız sayıları kontrol etmek bu hatayı yakalayamaz; hangi sol kimliklerin sonuçta korunması gerektiğini de denetlemek gerekir.

LEFT JOIN, sol tablonun eşleşmeyen satırlarını sağ alanları NULL olacak biçimde korur. Ancak birleştirmeden sonra WHERE ile sağ tabloya koşul uygulandığında bu korunmuş satırlar yeniden elenebilir. PostgreSQL belgeleri, dış birleştirmede ON ve WHERE koşullarının farklı aşamalarda değerlendirilmesinin sonucu değiştirebildiğini örnekler. Bu yazıda farkı üç grupla görünür kılacağız.Kaynak: PostgreSQL — Table Expressions

Verimiz sentetik ve tam bir anlık görüntüdür. Grup listesi A, B ve C’den oluşur. İş tablosundaki bütün kayıtların bu örnek için mevcut olduğu varsayılır. Hiç iş satırı bulunmamasını burada sıfır sayabiliriz. Gerçek sistemde veri aktarımı eksikse aynı yokluğu sıfır diye yorumlamak uygun olmayabilir; o durum ayrı veri kalitesi kararı gerektirir.

Açık, kapalı ve hiç kaydı olmayan durum

A’da bir açık, bir kapalı iş vardır. B’nin yalnız kapalı işi bulunur. C’nin hiçbir iş kaydı yoktur. İstenen çıktı bütün grupları koruyarak açık işleri saymaktır: A için bir, B ve C için sıfır. B ile C’nin sıfıra ulaşma nedenleri farklı olduğu için ikisini de testte tutmak önemlidir.

SELECT g.id, COUNT(j.id) AS open_count
FROM groups g LEFT JOIN jobs j ON j.group_id = g.id
WHERE j.status = 'open'
GROUP BY g.id;
-- Beklenen bütün gruplar yerine yalnız A=1 döner.

Birleştirmeden sonra A’nın iki, B’nin bir, C’nin sağ alanları NULL olan bir satırı vardır. WHERE koşulu A’nın açık satırını korur. A’nın kapalı satırı ve B’nin tek satırı false nedeniyle elenir. C için status alanı NULL olduğundan koşul true değildir; C de elenir. Böylece doğru görünen A sayısı eksik kapsamı gizler.

Açık iş koşulunu eşleşmeye dahil edin

SELECT g.id, COUNT(j.id) AS open_count
FROM groups g
LEFT JOIN jobs j
  ON j.group_id = g.id AND j.status = 'open'
GROUP BY g.id
ORDER BY g.id;
-- Beklenen: A=1, B=0, C=0.

Bu sorguda sağ satır ancak grup kimliği eşleşiyorsa ve iş açıksa eşleşme sayılır. B’nin kapalı işi koşulu sağlamaz; bu yüzden B de C gibi sağ tarafı NULL olan korunmuş bir sol satırla sonuçta yer alır. A ise açık I1 satırıyla eşleşir. Filtrenin yerini değiştirmek, raporun açıkça istenen sol kapsamını korur.

COUNT için hangi alanı kullandığınız da belirleyicidir. Eşleşmiş her işin id alanı bu örnekte doludur. COUNT(j.id), sağ tarafı NULL olan korunmuş satırları saymaz. COUNT(*) ise satırın kendisini sayar; B ve C için birer tane verir. Bu iki fonksiyon aynı “iş sayısı” etiketi altında birbirinin yerine kullanılamaz.

İş kimliği gerçek tabloda NULL olabiliyorsa COUNT(j.id) de beklenen anlamı vermeyebilir. Burada kimliğin dolu olması açık bir önkoşuldur. Saymak istediğiniz olayın hangi alanla güvenilir biçimde temsil edildiğini belirleyin. Sorgu değişikliğini bu veri sözleşmesinden bağımsız bir kopyala yapıştır çözümü olarak uygulamayın.

OR ile NULL eklemek B’yi geri getirmeyebilir

Sık önerilen düzeltme, WHERE j.status = open OR j.id IS NULL koşuludur; SQL metninde open bir metin sabiti olarak tırnaklanır. Bu koşul C’yi geri getirir, çünkü C’nin sağ tarafı NULL’dır. Fakat B başlangıçta kapalı I3 satırıyla eşleşmiştir. B’nin iş kimliği NULL olmadığı ve durumu open olmadığı için B yine elenir.

WHERE açık koşulu: yalnız A.
WHERE açık koşulu OR sağ kimlik NULL: A ve C.
Açık koşulu ON içinde: A, B ve C.
Bu nedenle yalnız hiç kaydı olmayan C ile test yapmak yarım düzeltmeyi başarılı gösterebilir.

B satırı bu uygulamanın ayırt edici kontrolüdür: sağda kayıt vardır, fakat istenen durumdaki kayıt yoktur. Test verisini yalnız başarılı eşleşme ve tamamen boş grup ile kurarsanız bu ara durum görünmez kalır. Küçük veride farklı nedenlerle sıfıra ulaşan grupları korumak, büyük raporda kayıp kapsamın fark edilmesini kolaylaştırır.

Sayı ile kimlik kapsamını birlikte kontrol edin

Gruplar A,B,C. İşler I1/A/open, I2/A/closed, I3/B/closed. İş kimlikleri dolu; örnek veri eksiksiz. Bütün grupları koruyarak açık işleri say. Sağ durum filtresini WHERE ve ON içinde kullanan iki salt okunur sorguyu karşılaştır. WHERE açık OR sağ kimlik NULL önerisini B ile sına. COUNT(*) ile COUNT(j.id) farkını göster. Sonuçta grup kümesi tam olarak A,B,C ve sayılar 1,0,0 olmalı.

Ajanın yanıtında toplam açık iş sayısı bir bulunabilir; bu tek başına yeterli değildir. Bir toplam üç gruplu doğru rapordan da yalnız A’yı içeren eksik rapordan da çıkabilir. Kabul kontrolünü hem kimlik kümesine hem grup başına değere uygulayın. Ayrıca birleştirme öncesindeki açık iş kimliği kümesi ile sayılan iş kimlikleri uyumlu olmalıdır.

Sorguyu yerel bir deneme tablosunda çalıştırıp sonuçları sıralı olarak karşılaştırabilirsiniz. Tablo adları kullandığınız motorda ayrılmış sözcüklerle çakışıyorsa güvenli örnek adları seçin. Bu çalışma herhangi bir satır eklemeyi, silmeyi veya gerçek operasyon kaydı oluşturmayı gerektirmez; örnek veriyle salt okunur çıktı denetimi yeterlidir.

Kapalı gruba açık iş ekleyin

B grubuna I4 kimlikli open bir iş eklensin. Doğru raporun yeni değerlerini bulun. İlk WHERE sorgusunda şimdi hangi gruplar görünür? COUNT(*) kullanılan ON sorgusunda C için hangi yanlış sayı çıkar? Üç yanıtı ayrı yazmak, kapsam hatası ile sayım hatasını ayırmanızı sağlar.

Doğru çıktı A=1, B=1, C=0. İlk WHERE sorgusu A ve B’yi gösterir, C’yi düşürür. ON sorgusunda COUNT(*) kullanılırsa C’nin korunmuş satırı 1 sayılır; COUNT(j.id) ise C için 0 verir. Toplam açık iş sayısı 2 olmalıdır.

Kendi raporunuzda önce sonuçta bulunması gereken grup kümesini yazın. Ardından eşleşmeyen, yalnız koşulu sağlamayan kaydı bulunan ve koşulu sağlayan kaydı bulunan grupları birlikte sınayın. Bu üç durum korunduğunda, sıfırların gerçekten görünür olduğu bir raporu değerlendirebilirsiniz.