Historia pytania.
Wymaganie to pochodzi z procesów podawania danych migracji z systemów arkuszy kalkulacyjnych lub eksportów plików płaskich, gdzie metryki szeregów czasowych lub atrybuty kategoryczne są denormalizowane do szerokich nagłówków kolumn (np. Jan_Sales, Feb_Sales) zamiast wierszy znormalizowanych. Takie schematy są powszechne w procesach biznesowych napędzanych przez Excel przed ETL do magazynów relacyjnych, co wymaga transformacji do wąskich tabel faktów, aby umożliwić analizę temporalną i wymiarowe JOIN. Wyzwanie polega na transponowaniu tych statycznych projekcji kolumn w dynamiczne strumienie krotek bez uciekania się do imperatywnych przetwarzań wiersz po wierszu.
Problem.
Standardowe SELECT wiersze ustalają zidentyfikowane kolumny projekcji w czasie analizy, zapobiegając jednolitej projekcji emitującej różne kolumny źródłowe w różnych wierszach wyjściowych bez LATERAL korelacji lub iteracji proceduralnej. Celem jest wytworzenie iloczynu kartezjańskiego między każdym wierszem źródłowym a wirtualną tabelą wymiarową enumerującą nazwy atrybutów, a następnie multiplexowanie poprawnej wartości źródłowej do ogólnej kolumny wyniku za pomocą logiki warunkowej. Musi to być osiągnięte przy użyciu jedynie standardowej składni złączania i skalarnych wyrażeń dostępnych w ANSI SQL:1999 i później.
Rozwiązanie.
Wykorzystaj CROSS JOIN z tabelą pochodną wyrażoną za pomocą konstruktora wierszy VALUES, który wylicza klucze kategoryczne (np. nazwy miesięcy) jako wiersze. W obrębie listy SELECT weź pod uwagę wykorzystanie wyrażenia CASE, które mapuje każdy klucz na odpowiadającą mu kolumnę źródłową, efektywnie projekując denormalizowaną wartość w znormalizowaną strukturę wiersza. Przefiltruj wynik, aby wykluczyć wartości NULL generowane, gdy atrybut źródłowy jest brakujący dla danego klucza, zapewniając, że ostateczny wynik zawiera tylko ważne pomiary.
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 -- ... dodatkowe miesiące 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 -- ... powtórz dla wszystkich, aby uniknąć NULLi END IS NOT NULL;
Dział finansowy wyeksportował alokacje budżetowe na rok fiskalny z modelu korporacyjnego Excel do tabeli stagingowej, gdzie dwanaście miesięcznych kolumn (M01_Amt przez M12_Amt) reprezentowało denormalizowane okresy czasowe dla każdego centrum kosztów. Docelowy magazyn danych SAP wymagał wąskiej struktury schematu tabeli faktów (CostCenter_ID, Fiscal_Month, Amount), co wymagało transformacji unpivot w ramach skryptu ładowania ANSI SQL, aby uniknąć pośredniego przetwarzania Python. Ilość pięćdziesięciu milionów rekordów wykluczyła ręczną transformację lub strategie ładowania z wieloma przejściami.
Rozwiązanie 1: Union All dla każdej kolumny.
Początkowe podejście wykorzystało dwanaście oddzielnych zapytań SELECT, z których każde projekowało inną kolumnę miesiąca zakodowaną na stałe w ogólnych kolumnach Amount i Month_Name, połączonych za pomocą UNION ALL. Zalety: Ta metoda ma uniwersalną kompatybilność, działa na starych bazach danych mainframe i archaicznych silnikach SQL, które nie obsługują nowoczesnej składni złączania. Wady: Wykonuje dwanaście pełnych skanów tabeli na danych źródłowych, co prowadzi do degradacji I/O w linii; plan zapytania staje się obszerna i trudna do buforowania, a wszelkie zmiany schematu (dodanie trzynastego okresu) wymagają zmiany dwanaście oddzielnych list projekcji.
Rozwiązanie 2: Dynamiczne generowanie SQL.
Alternatywą było konstruowanie tekstu zapytania dynamicznie w warstwie aplikacji, iterując po tabelach metadanych, aby generować potrzebne gałęzie CASE lub ramiona UNION w czasie wykonywania. Zalety: Zapewnia elastyczność w stosunku do ewoluujących schematów i redukuje manualny trud w tworzeniu SQL podczas obsługi setek kolumn. Wady: Narusza zakaz na logikę proceduralną; wprowadza wektory ataków SQL injection i narzut kompilacji, a powstałe wyrażenie nie może być zawarte w statycznym widoku bazy danych ani definicji procedury składowanej.
Rozwiązanie 3: Cross Join z Values.
Akceptowana implementacja stosowała CROSS JOIN z konstruktorem VALUES, definiującym dwanaście okresów fiskalnych, multiplexując poprawną kwotę za pomocą wyrażenia CASE uzależnionego od wirtualnego identyfikatora okresu. Zalety: Wykonuje się jako jeden przebieg nad tabelą źródłową, wykorzystuje efektywne algorytmy złączania i jest w pełni deklaratywna i przenośna między Oracle, SQL Server, PostgreSQL i Db2 bez wskazówek dostawcy. Wady: Wymaga wsparcia SQL:1999 dla konstruktorów wierszy, niew dostępnych w przestarzałych systemach, a obszerność wyrażenia CASE zwiększa nakład na utrzymanie, chyba że jest generowane za pomocą szablonów.
Wynik.
Czas transformacji zmniejszył się z dwudziestu pięciu minut do mniej niż dziewięćdziesięciu sekund, eliminując zbędne skany tabeli wynikające z wzorca UNION ALL. Proces ładowania stał się odporny na zmiany schematu, wymagając jedynie dodania wiersza do konstruktora VALUES, gdy nowe okresy fiskalne są wprowadzane. Ponadto logika ta została ujęta w standardowym widoku, umożliwiając bezpośrednie ad-hoc zapytania przez użytkowników Tableau bez pośrednich kroków ETL.
Jak zapobiec wartościom NULL w kolumnach źródłowych, aby nie pojawiały się jako wiersze w rezultacie unpivot bez powodowania, że wyrażenie CASE będzie oceniane dwukrotnie w planie wykonania?
Kandydaci często osadzają wyrażenie CASE wewnątrz predykatu klauzuli WHERE, takiego jak WHERE CASE ... END IS NOT NULL, co zmusza optymalizator do obliczenia projekcji dwukrotnie — raz do filtrowania i raz do wyjścia. Efektywny wzorzec ANSI SQL materializuje wynik w tabeli pochodnej lub Common Table Expression (CTE): SELECT * FROM (SELECT ..., CASE ... END AS val FROM ... CROSS JOIN ...) sub WHERE val IS NOT NULL. To oblicza CASE raz, filtruje wiersze i utrzymuje czyste rozdzielenie odpowiedzialności dla optymalizatora zapytań.
Kiedy unpivotujesz kolumny z heterogenicznymi typami danych (np. kolumna komentarzy VARCHAR obok kolumny kwot DECIMAL), jaka konkretna strategia rzutowania ANSI SQL zapewnia spójność typów w jednej kolumnie wartości rezultatu bez utraty danych?
Wielu kandydatów błędnie polega na domyślnej konwersji typów, która może trunktować ciągi lub stracić precyzję dziesiętną, lub próbują UNION ALL nie zdając sobie sprawy, że zasady rzutowania typów różnią się w zależności od platformy. Solidne rozwiązanie wyraźnie rzutuje każdą kolumnę źródłową na wspólny supertyp — zazwyczaj VARCHAR — w każdej gałęzi WHEN wyrażenia CASE: CASE WHEN key='Comment' THEN CAST(text_col AS VARCHAR(500)) ELSE CAST(num_col AS VARCHAR(500)) END. To zapewnia, że wszystkie zwracane wartości dzielą jeden typ danych kompatybilny z definicją kolumny wyniku, zachowując tekstową reprezentację danych numerycznych w razie potrzeby.
Dlaczego podejście CROSS JOIN z VALUES powierzchownie wydaje się tworzyć eksplozję iloczynu kartezjańskiego, a jak zazwyczaj optymalizator to łagodzi w porównaniu do zachowania eliminacji NULL natywnego operatora UNPIVOT?
CROSS JOIN logicznie generuje M×N wierszy (wiersze źródłowe pomnożone przez liczbę atrybutów) przed filtrowaniem, co obawia się wywołać pogorszenie wydajności w dużych zbiorach danych. Jednak nowoczesne optymalizatory kosztowe rozpoznają zależność danych wyrażenia CASE na małej stałej tabeli i często przekształcają plan w prostą projekcję lub wewnętrzny operator UNPIVOT, unikając rzeczywistego mnożenia wierszy. W odróżnieniu od natywnego UNPIVOT, które zazwyczaj automatycznie eliminuje wyniki NULL, ta metoda wymaga wyraźnej klauzuli WHERE, aby odrzucić wiersze, w których atrybut źródłowy był NULL, w przeciwnym razie zbiór wyników zawiera fałszywe puste fakty, które psują obliczenia agregatów w dalszym etapie.