Réponse à la question.

Ce défi nécessite de tokeniser des chaînes VARCHAR en utilisant uniquement des fonctions de manipulation de chaînes normalisées dans un CTE Récursif. La solution traite la chaîne CSV comme une pile, où chaque niveau de récursion détache le jeton le plus à gauche en localisant le premier délimiteur avec POSITION, en extrayant la sous-chaîne via SUBSTRING, et en passant le reste à l'itération suivante.

Le membre d'ancrage initialise le processus en sélectionnant la colonne d'origine et en calculant le premier jeton et la chaîne restante. Le membre récursif répète ensuite cette logique sur la sous-chaîne restante jusqu'à ce que POSITION renvoie zéro (indiquant qu'il n'y a plus de délimiteurs) ou que la chaîne restante devienne vide.

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;

Situation de la vie

Une institution financière a stocké des indicateurs de risque à valeurs multiples sous forme de chaînes séparées par des virgules dans un entrepôt de données conforme à ANSI SQL, empêchant l'agrégation directe contre des catégories de risque individuelles. L'équipe de conformité exigeait des lignes normalisées pour se joindre aux tables de référence réglementaires et calculer des métriques d'exposition par type de risque.

Une approche envisagée consistait à utiliser une table temporaire de nombres (table de répartition) avec des auto-joints pour extraire des sous-chaînes par index. Bien qu'efficace pour le traitement par lots et facile à paralléliser, cette méthode nécessitait la création d'objets auxiliaires qui violaient des exigences de portabilité strictes entre des environnements de bases de données hétérogènes mélangeant des instances Oracle, PostgreSQL, et IBM Db2. La charge d'entretien de la synchronisation de ces tables de répartition à travers des systèmes distribués rendait cette solution opérationnellement coûteuse.

Une autre alternative consistait à extraire les données dans un pipeline ETL Python utilisant les méthodes de division de chaînes de pandas. Cela offrait des performances brutes supérieures et des capacités de débogage plus faciles, mais introduisait des préoccupations de sécurité significatives en exportant des données financières sensibles en dehors du périmètre de la base de données sécurisée. De plus, la latence de retour créait des retards de synchronisation rendant le reporting réglementaire en temps réel impossible.

La solution choisie a utilisé un CTE Récursif ANSI SQL pur tirant parti de SUBSTRING et POSITION pour tokeniser itérativement chaque chaîne sur place. Cette approche satisfaisait les contraintes de sécurité en conservant le calcul au sein du moteur de base de données, ne nécessitait aucune dépendance externe ou table temporaire, et fournissait des résultats déterministes sur toutes les plateformes de bases de données sans logique procédurale.

L'implémentation a réussi à décomposer dix millions d'enregistrements non normalisés en une table de faits en étoile en quelques minutes, permettant au tableau de bord de gestion des risques d'effectuer des agrégations en sous-seconde sur des dimensions catégorielles auparavant inaccessibles.

Ce que les candidats manquent souvent

Comment gérez-vous des jetons vides entre des délimiteurs consécutifs (par exemple, "a,,c") sans perdre l'intégrité positionnelle de la colonne ordinale ?

Les candidats supposent souvent que SUBSTRING émettra naturellement des lignes vides pour des virgules consécutives, mais la fonction POSITION saute les délimiteurs vides lors du calcul des limites de la sous-chaîne. Pour préserver les jetons vides, vous devez détecter explicitement lorsque POSITION renvoie le même index que l'itération précédente (indiquant un jeton de longueur zéro) et émettre une ligne de chaîne vide avant de traiter le reste. Cela nécessite de suivre les positions de délimiteur actuelles et précédentes au sein du membre récursif, généralement en stockant la longueur de reste précédente et en la comparant à la position actuelle.

Quelles garanties empêchent une récursion infinie si la chaîne d'entrée n'a pas de délimiteurs ou contient des références circulaires dans un import mal formé ?

Sans logique de terminaison appropriée, un CTE Récursif pourrait tenter une récursion infinie si la chaîne restante ne se raccourcit jamais. ANSI SQL exige que le membre récursif produise zéro lignes pour se terminer naturellement. Vous devez vous assurer que chaque itération réduit strictement la longueur du reste en vérifiant que SUBSTRING avance d'au moins un caractère après le délimiteur. De plus, vous devriez implémenter un compteur de profondeur qui force la terminaison après un maximum conservateur (par exemple, 1000 niveaux) pour protéger contre des entrées pathologiques, bien que la véritable portabilité ANSI SQL repose sur la condition booléenne que le reste n'est pas vide plutôt que sur des détections de cycles spécifiques à un dialecte.

Comment cette technique performe-t-elle sur des tables larges contenant plusieurs colonnes CSV qui doivent être divisées simultanément tout en maintenant l'identité de ligne ?

De nombreux candidats tentent de nicher plusieurs CTE Récursifs ou de joindre les résultats séparés, ce qui crée une explosion cartésienne et détruit la relation entre les colonnes de la même ligne d'origine. L'approche correcte consiste d'abord à dé-plier les multiples colonnes CSV dans une structure normalisée (en utilisant UNION ALL dans le membre d'ancrage tout en balisant chaque colonne source), puis à appliquer un seul passage récursif qui transporte un indicateur d'identification de colonne. Cela garantit que les jetons de colonnes différentes restent associés à leur identifiant de ligne parent commun sans nécessiter de boucles procédurales ou de jointures LATERAL, bien que cela nécessite une gestion minutieuse de la profondeur de récursion qui multiplie désormais par le nombre de colonnes à diviser.