Odpowiedź na pytanie.

To wyzwanie wymaga tokenizacji ciągów VARCHAR przy użyciu tylko standardowych funkcji manipulacji ciągami w ramach Recursive CTE. Rozwiązanie traktuje ciąg CSV jako stos, w którym każdy poziom rekurencji odrywa najbardziej lewy token, lokalizując pierwszy ogranicznik za pomocą POSITION, wyciągając podciąg za pomocą SUBSTRING i przekazując resztę do następnej iteracji.

W członie głównym proces rozpoczyna się od wyboru oryginalnej kolumny oraz obliczenia pierwszego tokena i pozostałego ciągu. Następnie w członie rekurencyjnym powtarza się tę samą logikę dla pozostałego podciągu, aż POSITION zwróci zero (co wskazuje na brak dalszych ograniczników) lub pozostały ciąg stanie się pusty.

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;

Sytuacja z życia

Instytucja finansowa przechowywała wskaźniki ryzyka jako ciągi oddzielone przecinkami w zgodnym z ANSI SQL hurtowni danych, co uniemożliwiało bezpośrednią agregację według indywidualnych kategorii ryzyka. Zespół ds. zgodności wymagał znormalizowanych wierszy do połączenia z tabelami odniesienia regulacyjnego oraz obliczenia metryk narażenia według typu ryzyka.

Jednym z rozważanych podejść było wykorzystanie tymczasowej tabeli liczb (tabeli tally) z samojoincami do wydobywania podciągów według indeksu. Choć było to efektywne dla przetwarzania wsadowego i łatwe do równoległego przetwarzania, metoda ta wymagała tworzenia obiektów pomocniczych, które naruszały ścisłe wymagania przenośności w różnych środowiskach baz danych łączących instancje Oracle, PostgreSQL i IBM Db2. Utrzymanie synchronizacji tych tabel tally w rozproszonych systemach uczyniło to rozwiązanie kosztownym operacyjnie.

Inną alternatywą było wydobycie danych do potoku ETL w Pythonie, korzystając z metod rozdzielania ciągów pandas. Oferowało to lepszą wydajność surową i łatwiejsze możliwości debugowania, ale wprowadzało istotne obawy związane z bezpieczeństwem, eksportując wrażliwe dane finansowe poza zabezpieczony perimeter bazy danych. Dodatkowo, opóźnienie w obiegu tworzyło opóźnienia synchronizacji, które uniemożliwiały raportowanie regulacyjne w czasie rzeczywistym.

Wybrane rozwiązanie wykorzystało czysto ANSI SQL Recursive CTE, wykorzystując SUBSTRING i POSITION do iteracyjnego tokenizowania każdego ciągu w miejscu. Podejście to spełniało wymogi bezpieczeństwa, trzymając obliczenia w silniku bazy danych, nie wymagało zewnętrznych zależności ani tymczasowych tabel i zapewniało deterministyczne wyniki w różnych platformach baz danych bez wymogu logiki proceduralnej.

Wdrożenie skutecznie rozłożyło dziesięć milionów denormalizowanych rekordów na tabelę faktów w schemacie gwiazdy w ciągu minut, umożliwiając pulpit zarządzania ryzykiem wykonywanie agregacji w czasie sub-sekundowym na wcześniej niedostępnych wymiarach kategorycznych.

Co często umyka kandydatom

Jak obsługujesz puste tokeny między kolejnymi ogranicznikami (np. "a,,c"), nie tracąc integralności pozycyjnej kolumny ordinal?

Kandydaci często zakładają, że SUBSTRING naturalnie wyprodukuje puste wiersze dla kolejnych przecinków, ale funkcja POSITION pomija puste ograniczniki podczas obliczania granic podciągu. Aby zachować puste tokeny, trzeba wyraźnie wykryć, gdy POSITION zwróci ten sam indeks co w poprzedniej iteracji (co wskazuje na token o zerowej długości) i wyemitować pusty ciąg przed przetworzeniem reszty. Wymaga to śledzenia zarówno bieżącej, jak i poprzedniej pozycji ogranicznika w członie rekurencyjnym, zazwyczaj przez przechowywanie długości poprzedniego podciągu i porównanie jej z bieżącą pozycją.

Jakie zabezpieczenia zapobiegają nieskończonej rekurencji, jeśli ciąg wejściowy nie zawiera żadnych ograniczników lub zawiera odniesienia cykliczne w błędnym imporcie?

Bez odpowiedniej logiki zakończenia, Recursive CTE może próbować nieskończonej rekurencji, jeśli ciąg pozostały nigdy się nie skraca. ANSI SQL wymaga, aby człon rekurencyjny produkował zero wierszy, aby zakończyć w sposób naturalny. Musisz zapewnić, że każda iteracja ściśle skraca długość pozostałego ciągu, weryfikując, że SUBSTRING przesuwa się przynajmniej o jeden znak za ogranicznik. Dodatkowo należy wdrożyć licznik głębokości, który wymusza zakończenie po konserwatywnej maksymalnej wartości (np. 1000 poziomów), aby chronić przed patologicznie skomponowanymi danymi, chociaż prawdziwa przenośność ANSI SQL opiera się na warunku boolowskim, że pozostały ciąg nie jest pusty, a nie na detekcji cykli specyficznej dla dialektów.

Jak ta technika działa na szerokich tabelach zawierających wiele kolumn CSV, które muszą być jednocześnie dzielone, zachowując tożsamość wiersza?

Wielu kandydatów próbuje zagnieżdżać wiele Recursive CTE lub łączyć wyniki podziału, co powoduje eksplozję kartezjańską i niszczy relację między kolumnami z tego samego pierwotnego wiersza. Prawidłowe podejście polega na najpierw unpivotowaniu wielu kolumn CSV w znormalizowaną strukturę (używając UNION ALL w członie głównym podczas oznaczania każdej źródłowej kolumny), a następnie zastosowaniu pojedynczego przejścia rekurencyjnego, które nosi znacznik identyfikatora kolumny. To zapewnia, że tokeny z różnych kolumn pozostają powiązane z ich wspólnym identyfikatorem wiersza rodzica, bez konieczności wykorzystywania pętli proceduralnych lub złączy LATERAL, chociaż wymaga starannego zarządzania głębokością rekurencji, która teraz mnoży się przez liczbę kolumn, które są dzielone.