Soru Tarihçesi.
Bu gereksinim, zaman serisi metriklerinin veya kategorik niteliklerin geniş formatlı sütun başlıklarına (örneğin, Jan_Sales, Feb_Sales) normalleştirilmediği, eski elektronik tablo sistemlerinden veya düz dosya ihracatlarından veri aktarım hatlarında ortaya çıkar. Bu tür şemalar, ilişkisel depolara ETL yapılmadan önceki Excel-tahrikli iş süreçlerinde yaygındır ve zaman analizi ve boyutsal JOIN'leri etkinleştirmek için dar faktör tablolarına dönüşüm gerektirmektedir. Zorluk, bu statik sütun projeksiyonlarını, zorunlu satırdan-satıra işleme başvurmadan dinamik tuple akışlarına dönüştürmektir.
Problem.
Standart SELECT ifadeleri, parsel zamanında projeksiyon edilen sütun kimliklerini sabitler, bu da tek bir projeksiyonun farklı çıktı satırlarında farklı kaynak sütunları yaymasına engel olur; bu da LATERAL korelasyonu veya prosedürel yinelemeyi gerektirir. Amaç, her kaynak satırı ile nitelik adlarını sayan sanal bir boyut tablosu arasında bir kartezyen çarpım oluşturmaktır, ardından doğru kaynak değerini koşullu mantık yoluyla genel bir sonuç sütununa çoklamak.^n Bunu yalnızca standart birleştirme sözdizimini ve ANSI SQL:1999 ve sonrası için mevcut olan skalar ifadeleri kullanarak başarmak gerekir.
Çözüm.
Kategorik anahtarların (örneğin, ay adları) satırlar olarak sıralandığı VALUES satır kurucusu yoluyla ifade edilen türevli tabloya karşı bir CROSS JOIN kullanın. SELECT listesi içinde, her anahtarı karşılık gelen kaynak sütununa eşleyen aranmış bir CASE ifadesini kullanarak, denormalize edilmiş değeri normalleştirilmiş bir satır yapısına etkili bir şekilde projekte edin. Sonuçları, belirli bir anahtar için bir kaynak niteliği eksik olduğunda oluşan NULL değerleri dışlayacak şekilde filtreleyin ve nihai çıktının yalnızca geçerli ölçümler içerdiğinden emin olun.
SELECT s.cost_center_id, m.fiscal_month, CASE m.fiscal_month WHEN 'M01' THEN s.m01_amt WHEN 'M02' THEN s.m02_amt WHEN 'M03' THEN s.m03_amt -- ... ilave aylar WHEN 'M12' THEN s.m12_amt END AS amount FROM budget_wide s CROSS JOIN ( VALUES ('M01'), ('M02'), ('M03'), ('M04'), ('M05'), ('M06'), ('M07'), ('M08'), ('M09'), ('M10'), ('M11'), ('M12') ) AS m(fiscal_month) WHERE CASE m.fiscal_month WHEN 'M01' THEN s.m01_amt -- ... NULL'leri önlemek için tümünü tekrar edin END IS NOT NULL;
Mali birim, on iki aylık sütun (M01_Amt'dan M12_Amt'ye) ile birlikte, her maliyet merkezi için denormalize edilmiş zaman dilimlerini temsil eden bütçe tahsislerini kurumsal bir Excel modelinden bir sahneleme tablosuna aktardı. Hedef SAP veri ambarı, (CostCenter_ID, Fiscal_Month, Amount) yapısına sahip dar bir faktör tablo şemasına ihtiyaç duymaktaydı; bu nedenle, ara Python işlemeden kaçınmak için ANSI SQL yükleme betiği içinde bir unpivot dönüşümü gerektirmekteydi. Elli milyon kayıt hacmi, manuel dönüşüm veya çok geçişli yükleme stratejilerini engellemiştir.
Çözüm 1: Her sütun için Union All.
Başlangıç yaklaşımı, her biri farklı bir ay sütununu genel Miktar ve Ay_Adı sütunlarına projekte eden on iki ayrı SELECT sorgusu kullanarak UNION ALL ile birleştirerek uygulandı. Artılar: Bu yöntem evrensel uyumluluğa sahiptir, modern birleştirme sözdizimini desteklemeyen eski ana bilgisayar veritabanlarında ve antik SQL motorlarında çalışmaktadır. Eksikler: Bu yöntem, kaynak veriler üzerinde on iki tam tablo taraması gerçekleştirir, bu da doğrusal G/Ç bozulmasına yol açar; sorgu planı hacimli hale gelir ve önbelleğe almak zorlaşır, ve herhangi bir şema değişikliği (on üçüncü bir dönem eklenmesi) on iki ayrı projeksiyon listesinin değiştirilmesini gerektirir.
Çözüm 2: Dinamik SQL üretimi.
Alternatif bir yaklaşım, uygulama katmanında gerekli CASE kollarını veya UNION kollarını oluşturmak için meta veritabanları üzerinde yineleme yaparak sorgu metnini dinamik olarak inşa etmekti. Artılar: Bu, gelişen şemalara karşı esneklik sağlar ve yüzlerce sütunla çalışırken manuel SQL yazma zahmetini azaltır. Eksiler: Prosedürel mantık üzerindeki yasaklamayı ihlal eder; SQL enjeksiyon saldırısı vektörleri ve derleme aşamasında ek yük getirir; ve ortaya çıkan ifade, statik bir veritabanı görünümü veya saklı prosedür tanımı içinde kapsüllenemez.
Çözüm 3: Cross Join with Values.
Kabul edilen uygulama, on iki mali dönemi tanımlayan bir VALUES kurucusu ile bir CROSS JOIN kullanarak doğru miktarı sanal dönem kimliğine dayalı bir CASE ifadesi ile çoklamaktı. Artılar: Bu, kaynak tablo üzerinde tek geçiş olarak çalışır, verimli birleştirme algoritmalarından yararlanır ve Oracle, SQL Server, PostgreSQL ve Db2 gibi platformlar arasında tamamen tarif edilebilir ve taşınabilir şekilde çalışır. Eksiler: Eski sistemlerde mevcut olmayan SQL:1999 satır kurucularına ihtiyaç duyar ve CASE ifadesinin uzunluğu, şablonlar yoluyla üretilmediği takdirde bakım yükünü artırır.
Sonuç.
Dönüşüm gecikmesi, UNION ALL deseninin doğasına bağlı olarak gereksiz tablo taramalarını ortadan kaldırarak yirmi beş dakikadan doksan saniyenin altına düştü. Yükleme işlemi şema uzantılarına karşı dayanıklı hale geldi; yeni mali dönemler tanıtıldığında yalnızca VALUES kurucusuna bir satır eklenmesi gerekmekteydi. Ayrıca, mantık standart bir görünüm içinde kapsüllenmiş durumda olduğundan, ara ETL adımları olmaksızın doğrudan ad-hoc sorgulama imkanı sağladı.
Kaynak sütunlarda NULL değerlerinin unpivoted sonuçlarda satır olarak görünmesini nasıl engelleyebilirsiniz, CASE ifadesinin yürütme planında iki kez değerlendirilmesini gerçekleştirmeden?
Adaylar çoğunlukla WHERE CASE ... END IS NOT NULL gibi bir WHERE koşul yargısı içinde CASE ifadesini yerleştirirler; bu da optimizasyoncunun projeksiyonu iki kez hesaplamasını gerektirir—birinde filtreleme ve diğerinde çıktıda. Etkili ANSI SQL deseni, sonucu türevli bir tablo veya Common Table Expression (CTE) içinde somutlaştırır: SELECT * FROM (SELECT ..., CASE ... END AS val FROM ... CROSS JOIN ...) alt WHERE val IS NOT NULL. Bu, CASE ifadesini bir kez hesaplar, satırları filtreler ve sorgu optimizasyoncusu için temiz bir konu ayırımı sağlar.
Heterojen veri türlerine sahip sütunları unpivot ederken (örneğin, bir VARCHAR yorum sütunu ile birlikte bir DECIMAL miktar sütunu), tek sonuç değeri sütununda veri kaybı olmaksızın tür uyumluluğunu sağlamak için hangi özel ANSI SQL casting stratejisi kullanılır?
Birçok aday, dizgelerin kesilmesine veya ondalık kesinliğin kaybolmasına neden olan veya platforma göre tür zorlaması kurallarının değiştiğini fark etmeden, doğrudan UNION ALL yapmaya çalışarak örtük tür dönüşümüne güveniyor. Güçlü çözüm, her WHEN dalında her kaynak sütunu ortak bir süper türe—genellikle VARCHAR—a açıkça cast etmektir: CASE WHEN key='Comment' THEN CAST(text_col AS VARCHAR(500)) ELSE CAST(num_col AS VARCHAR(500)) END. Bu, tüm dönüş değerlerinin, gerektiğinde sayısal verilerin metinsel temsilini koruyarak, sonuç sütunu tanımına uyumlu tek bir veri türünü paylaşmasını sağlar.
CROSS JOIN with VALUES yaklaşımı neden yüzeysel olarak bir kartezyen çarpım patlaması yaratıyor gibi görünmektedir ve optimizasyoncu genellikle bunu, yerel bir UNPIVOT operatörünün NULL eleme davranışıyla karşılaştırarak nasıl hafifletir?
CROSS JOIN, mantıksal olarak M×N satırları (kaynak satırları çarpı nitelik sayısı) üretir ve filtrelemeden önce, adayların büyük veri kümesi üzerinde performansı düşürme korkusu taşır. Ancak, modern maliyet bazlı optimizasyoncular, CASE ifadesinin küçük sabit tabloya bağlılığını tanır ve genellikle planı basit bir projeksiyon veya içsel olarak bir UNPIVOT fiziksel operatörüne dönüştürür, gerçek satır çarpımından kaçınarak. Yerel UNPIVOT'un genellikle NULL sonuçları otomatik olarak ortadan kaldırdığı gerçeğine kıyasla, bu yöntem, kaynak niteliği NULL olduğunda satırları atmak için açık bir WHERE koşulu gerektirir; aksi takdirde sonuç kümesinde toplam hesaplamaları bozan sahte boş gerçekler bulunur.