Tekil VARCHAR sütunları içinde gömülü denormalize edilmiş virgülle ayrılmış değerlerle karşılaştığınızda, bunları özel string bölme işlevleri veya lateral türev tablolar kullanmadan nasıl bireysel satırlara normalleştirirsiniz?

Sorunun cevabı.

Bu zorluk, yalnızca standartlaştırılmış dize manipülasyon işlevlerini kullanarak VARCHAR dizelerini tokenize etmeyi gerektirir. Çözüm, CSV dizisini bir yığın olarak ele alır; burada her bir tekrar seviyesi, POSITION ile ilk ayırıcıyı bularak en soldaki tokeni çıkarır, SUBSTRING aracılığıyla alt dizeyi çıkartır ve kalan kısmı bir sonraki yinelemeye geçirir.

Açılış üyesi, işlemi başlatmak için orijinal sütunu seçer ve ilk tokeni ve kalan diziyi hesaplar. Tekrarlayıcı üye, POSITION sıfır döndüğünde (daha fazla ayırıcı olmadığını belirtir) veya kalan dize boş olduğunda kadar kalan alt dize üzerindeki bu mantığı tekrar eder.

WITH RECURSIVE Splitter AS (
    SELECT 
        id,
        csv_col,
        SUBSTRING(csv_col FROM 1 FOR POSITION(',' IN csv_col) - 1) AS token,
        SUBSTRING(csv_col FROM POSITION(',' IN csv_col) + 1) AS remainder,
        1 AS ordinal
    FROM products
    WHERE csv_col IS NOT NULL AND csv_col <> ''
    
    UNION ALL
    
    SELECT 
        id,
        csv_col,
        CASE 
            WHEN POSITION(',' IN remainder) > 0 
            THEN SUBSTRING(remainder FROM 1 FOR POSITION(',' IN remainder) - 1)
            ELSE remainder 
        END,
        CASE 
            WHEN POSITION(',' IN remainder) > 0 
            THEN SUBSTRING(remainder FROM POSITION(',' IN remainder) + 1)
            ELSE '' 
        END,
        ordinal + 1
    FROM Splitter
    WHERE remainder <> ''
)
SELECT id, token, ordinal 
FROM Splitter 
ORDER BY id, ordinal;

Hayattan bir durum

Bir finans kurumu çok değerli risk göstergelerini, doğrudan bireysel risk kategorilerine karşı toplama işlemini engelleyen, virgülle ayrılmış dizeler olarak sakladı. Uyum ekibi, düzenleyici arama tablolarıyla birleştirilmesi ve her risk türü için maruz kalma metriklerini hesaplaması için normalleştirilmiş satırlara ihtiyaç duyuyordu.

Bir yaklaşım, dizeleri indeksle çıkarabilmek için kendi kendine bir sayı tablosu (tally table) kullanmayı düşünmüştü. Bu yöntem, toplu işleme için verimliydi ve kolayca paralelleştirilebiliyordu, ancak heterojen veritabanı ortamları arasında katı taşınabilirlik gereksinimlerini ihlal eden yardımcı nesnelerin oluşturulmasını gerektiriyordu. Bu sayı tablolarını dağıtılmış sistemler arasında senkronize etmenin bakım yükü, bu çözümü operasyonel olarak pahalı hale getirdi.

Bir başka alternatif, verileri bir Python ETL boru hattına çıkararak pandas dize bölme yöntemlerini kullanmaktı. Bu, üstün ham performans ve daha kolay hata ayıklama yetenekleri sunuyordu, ancak hassas finansal verilerin güvenli veritabanı sınırının dışına çıkarılması nedeniyle önemli güvenlik endişeleri doğurdu. Ayrıca, gidiş-dönüş gecikmeleri, gerçek zamanlı düzenleyici raporlamayı imkansız hale getiren senkronizasyon gecikmelerine neden oldu.

Seçilen çözüm, her dizeyi yerinde iteratif bir şekilde tokenize etmek için tamamen ANSI SQL Recursive CTE kullandı. Bu yaklaşım, tüm veritabanı motoru içinde hesaplamayı tutarak güvenlik kısıtlamalarını karşıladı, dış bağımlılık veya geçici tablolara ihtiyaç duymadı ve tüm veritabanı platformlarında kesintisiz sonuçlar sağlamada öngörülebilirlik sundu.

Uygulama, on milyon denormalize edilmiş kaydı birkaç dakika içinde bir yıldız-şemalı faktör tablosuna dönüştürdü ve risk yönetimi panosunun daha önce erişilemeyen kategorik boyutlarda sub-saniye toplanmalar yapmasına olanak sağladı.

Adayların genellikle kaçırdığı noktalar

Boş tokenleri art arda gelen ayırıcılar arasında (örneğin, "a,,c") nasıl işlersiniz, ordinal sütununun pozisyon bütünlüğünü kaybetmeden?

Adaylar sıklıkla SUBSTRING'in art arda gelen virgüller için doğal olarak boş satırlar vereceğini varsayarlar, ancak POSITION işlevi, alt dize sınırlarını hesaplarken boş ayırıcılarla atlar. Boş tokenleri korumak için, POSITION önceki yineleme ile aynı indeksi döndüğünde (sıfır uzunluğunda bir token olduğunu belirten) açıkça tespit etmeniz ve kalan kısmı işlemden önce bir boş dize satırı yayınlamanız gerekir. Bu, tipik olarak, önceki kalan uzunluğunu saklayarak ve bunu mevcut pozisyonla karşılaştırarak hem güncel hem de önceki ayırıcı pozisyonlarını izlemeyi gerektirir.

Giriş dizesinin hiç ayırıcı içermediği veya bozuk bir içe aktarımda dairesel referanslar içerdiği durumlarda sonsuz yinelemeyi önleyen hangi güvenlik önlemleri vardır?

Uygun bir sonlandırma mantığı olmadan, bir Recursive CTE, kalan dize hiç kısalmadığında sonsuz yinelemeyi denemeye başlayabilir. ANSI SQL, tekrarlayan üyenin doğal olarak sonlandırmak için sıfır satır üretmesi gerektiğini gerektirir. Her yinelemenin, SUBSTRING'in en az bir karakter ilerlemesini sağladığını doğrulayarak, kalan uzunluğunu doğru bir şekilde azaltması gerektiğini sağlamalısınız. Ayrıca, patolojik girdilere karşı korumak için muhafazakar bir maksimum (örneğin, 1000 seviye) ile zorlanma sağlayan bir derinlik sayacı uygulamalısınız; gerçi gerçek ANSI SQL taşınabilirliği, kalan dize boş olmadığını belirten boole koşuluna dayanır, diyalekt spesifik döngü tespitinden ziyade.

Bu teknik, satır kimliğini koruyarak aynı anda birden fazla CSV sütununu ayırmanız gereken geniş tablolarda nasıl performans gösterir?

Birçok aday, birden fazla Recursive CTE'yi iç içe geçirmeye veya sonuçları çapraz birleştirmeye çalışır, bu da Kartezyen patlamasına neden olarak aynı orijinal satırdan gelen sütunlar arasındaki ilişkiyi yok eder. Doğru yaklaşım, önce çoklu CSV sütunlarını normalleştirilmiş bir yapıya dönüştürmek (açılış üyesinde UNION ALL kullanarak ve her kaynak sütunu etiketleyerek) ve ardından bir sütun tanımlayıcı bayrağı taşıyan tek bir tekrarlayan geçiş uygulamaktır. Bu, farklı sütunlardan gelen tokenlerin, prosedürel döngüler veya LATERAL birleştirmeler gerektirmeden, ortak ana satır kimliği ile ilişkilendirilmesini garanti eder, ancak çoğalma durumunu dikkatlice ele almayı gerektirir.