Antwort auf die Frage.

Diese Herausforderung erfordert die Tokenisierung von VARCHAR-Strings unter Verwendung von nur standardisierten String-Manipulationsfunktionen innerhalb eines rekursiven CTE. Die Lösung behandelt den CSV-String als einen Stapel, wobei jede Rekursionsebene das linkeste Token abzieht, indem sie den ersten Trennzeichen mit POSITION lokalisiert, den Teilstring mit SUBSTRING extrahiert und den Rest an die nächste Iteration übergibt.

Das Ankermitglied initialisiert den Prozess, indem es die ursprüngliche Spalte auswählt und das erste Token sowie den verbleibenden String berechnet. Das rekursive Mitglied wiederholt dann diese Logik für den verbleibenden Teilstring, bis POSITION null zurückgibt (was bedeutet, dass keine weiteren Trennzeichen vorhanden sind) oder der verbleibende String leer wird.

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;

Lebenssituation

Eine Finanzinstitution speicherte mehrwertige Risikoinformationen als durch Kommas getrennte Strings in einem ANSI SQL konformen Data Warehouse, was eine direkte Aggregation gegen einzelne Risikokategorien verhinderte. Das Compliance-Team benötigte normalisierte Zeilen, um mit regulativen Lookup-Tabellen zu verbinden und Risiko-Kennzahlen pro Risikotyp zu berechnen.

Ein Ansatz bestand darin, eine temporäre Zahlen-Tabelle (Tally-Tabelle) mit Selbstverknüpfungen zu verwenden, um Teilstrings nach Index zu extrahieren. Obwohl dies effizient für die Batch-Verarbeitung war und leicht parallelisiert werden konnte, erforderte diese Methode die Erstellung zusätzlicher Objekte, die gegen strenge Portabilitätsanforderungen in heterogenen Datenbankumgebungen mit gemischten Oracle, PostgreSQL und IBM Db2-Instanzen verstießen. Der Wartungsaufwand zur Synchronisierung dieser Tally-Tabellen über verteilte Systeme machte diese Lösung betrieblich kostspielig.

Eine andere Alternative bestand darin, die Daten in eine Python ETL-Pipeline zu extrahieren und die String-Splitting-Methoden von pandas zu verwenden. Dies bot überlegene Rohleistung und einfachere Debugging-Möglichkeiten, brachte jedoch erhebliche Sicherheitsbedenken mit sich, da sensible Finanzdaten außerhalb des gesicherten Datenbankperimeters exportiert wurden. Darüber hinaus führte die Rückfall-Latenz zu Synchronisierungsverzögerungen, die eine Echtzeit-Regulierungsberichterstattung unmöglich machten.

Die gewählte Lösung nutzte ein rein ANSI SQL rekursives CTE, das SUBSTRING und POSITION verwendete, um jede Zeichenfolge in situ iterativ zu tokenisieren. Dieser Ansatz erfüllte die Sicherheitsanforderungen, indem er die Berechnung innerhalb der Datenbank-Engine hielt, keine externen Abhängigkeiten oder temporären Tabellen erforderte und deterministische Ergebnisse auf allen Datenbankplattformen ohne prozedurale Logik lieferte.

Die Implementierung zerlegte erfolgreich zehn Millionen denormalisierte Datensätze innerhalb von Minuten in eine Faktentabelle im Sternschema, wodurch das Risikomanagement-Dashboard in der Lage war, Sub-Sekunden-Aggregationen auf zuvor unzugänglichen kategorischen Dimensionen durchzuführen.

Was Kandidaten oft übersehen

Wie gehen Sie mit leeren Tokens zwischen aufeinanderfolgenden Trennzeichen um (z. B. "a,,c"), ohne die Positionsintegrität der ordinalen Spalte zu verlieren?

Kandidaten gehen oft davon aus, dass SUBSTRING automatisch leere Zeilen für aufeinanderfolgende Kommas ausgibt, aber die POSITION-Funktion überspringt leere Trennzeichen, wenn sie die Grenzen des Teilstrings berechnet. Um leere Tokens zu erhalten, müssen Sie explizit erkennen, wenn POSITION denselben Index wie die vorherige Iteration zurückgibt (was auf ein Null-Längen-Token hinweist) und eine leere Zeichenfolgenzeile ausgeben, bevor Sie den Rest verarbeiten. Dies erfordert die Verfolgung sowohl der aktuellen als auch der vorherigen Trennzeichenpositionen im rekursiven Mitglied, typischerweise durch Speichern der vorherigen Restlängen und den Vergleich mit der aktuellen Position.

Welche Schutzmaßnahmen verhindern unendliche Rekursionen, wenn der Eingabestring keine Trennzeichen enthält oder zirkuläre Referenzen in einem fehlerhaften Import aufweist?

Ohne eine ordnungsgemäße Abschlusslogik könnte ein rekursives CTE versuchen, unendliche Rekursion auszuführen, wenn der verbleibende String nie kürzer wird. ANSI SQL erfordert, dass das rekursive Mitglied null Zeilen produziert, um sich natürlich zu beenden. Sie müssen sicherstellen, dass jede Iteration die Länge des Rests strikt reduziert, indem Sie überprüfen, dass SUBSTRING mindestens ein Zeichen über das Trennzeichen hinausgeht. Darüber hinaus sollten Sie einen Tiefenzähler implementieren, der die Beendigung nach einem konservativen Maximum (z. B. 1000 Ebenen) erzwingt, um gegen pathologische Eingaben zu schützen, obwohl die echte ANSI SQL-Portabilität von der booleschen Bedingung abhängt, dass der Rest nicht leer ist, und nicht von spezifischen Dialekten zur Zykluserkennung.

Wie funktioniert diese Technik bei breiten Tabellen, die mehrere CSV-Spalten enthalten, die gleichzeitig geteilt werden müssen, während die Zeicheneinheit erhalten bleibt?

Viele Kandidaten versuchen, mehrere rekursive CTEs zu schachteln oder die Ergebnisse zu kreuzverknüpfen, was eine kartesische Explosion erzeugt und die Beziehung zwischen Spalten derselben ursprünglichen Zeile zerstört. Der richtige Ansatz besteht darin, zunächst die mehreren CSV-Spalten in eine normalisierte Struktur zu entpivotieren (unter Verwendung von UNION ALL im Ankermitglied unter Kennzeichnung jeder Quellspalte) und dann einen einzelnen rekursiven Durchgang anzuwenden, der ein Spaltenbezeichner-Flag trägt. Dies gewährleistet, dass Tokens aus verschiedenen Spalten mit ihrer gemeinsamen übergeordneten Zeilen-ID assoziiert bleiben, ohne dass prozedurale Schleifen oder LATERAL-Joins erforderlich sind, obwohl dies eine sorgfältige Handhabung der Rekursionstiefe erfordert, die sich nun mit der Anzahl der gesplitterten Spalten multipliziert.