質問への回答。

この課題は、VARCHAR文字列を標準化された文字列操作関数のみを使用してトークン化することを要求します。解決策は、CSV文字列をスタックとして扱い、各再帰レベルで最初のデリミタを特定し、左最初のトークンを削除し、POSITIONを使用してサブストリングを抽出し、残りの部分を次の反復に渡します。

アンカーメンバーは、元の列を選択し、最初のトークンと残りの文字列を計算することでプロセスを初期化します。再帰メンバーは、残りのサブストリングに対してこのロジックを繰り返し、POSITIONがゼロを返すまで（デリミタがこれ以上ないことを示す）または残りの文字列が空になるまで続けます。

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;

実生活からの状況

金融機関は、複数の値を持つリスク指標をカンマ区切りの文字列としてANSI SQL準拠のデータウェアハウスに保存し、個別のリスクカテゴリーに対して直接集計することを防いでいました。コンプライアンスチームは、規制の参照テーブルと結合してリスクタイプごとのエクスポージャーメトリックを計算するために、正規化された行を必要としました。

考慮されたアプローチの 1 つは、一時的な数値テーブル (タリーテーブル) を自己結合してインデックスによってサブストリングを抽出することでした。この方法はバッチ処理には効率的で並列処理も簡単でしたが、Oracle、PostgreSQL、および IBM Db2 インスタンスを混在させた異種データベース環境間での厳格なポータビリティ要件を違反する補助オブジェクトの作成が必要でした。これらのタリーテーブルを分散システム間で同期するためのメンテナンスオーバーヘッドにより、このソリューションは運用コストが高くなりました。

別の選択肢は、pandas 文字列分割メソッドを使用して Python ETL パイプラインにデータを抽出することでした。これにより、優れた生データのパフォーマンスとデバッグ機能が提供されましたが、機密の財務データを安全なデータベースの境界外にエクスポートすることで重大なセキュリティ上の懸念が生じました。さらに、往復遅延により、リアルタイムの規制報告が不可能になる同期遅延が発生しました。

選択されたソリューションは、SUBSTRINGとPOSITIONを利用した純粋なANSI SQL Recursive CTEを使用して、各文字列をインプレースで反復的にトークン化しました。このアプローチは、計算をデータベースエンジン内に維持することでセキュリティ制約を満たし、外部依存関係や一時テーブルを必要とせず、手続き的なロジックなしで、すべてのデータベースプラットフォームで決定論的な結果を提供しました。

この実装は、非正規化された 1000 万件のレコードを数分以内にスタースキーマファクトテーブルに分解することに成功し、リスク管理ダッシュボードが以前はアクセスできなかったカテゴリーの次元に対してサブ秒集計を実行できるようになりました。

候補者が見逃すことがよくあること

空のトークンを連続するデリミタの間でどのように処理し、順序列の整合性を失わないようにしますか？(例えば、「a,,c」)

候補者はしばしば、SUBSTRINGが連続したカンマで自然に空の行を排出すると考えますが、POSITION関数はサブストリングの境界を計算する際に空のデリミタをスキップします。空のトークンを保持するには、POSITIONが前の反復と同じインデックスを返す（ゼロ長のトークンを示す）ときに明示的に検出し、残りの処理の前に空の文字列の行を排出する必要があります。これには、再帰メンバー内で現在のデリミタの位置と前のデリミタの位置を追跡する必要があります。通常は、以前の残りの長さを保存し、現在の位置と比較して行います。

誤ったインポートの無限再帰を防ぐための安全策は何ですか、入力文字列にデリミタがない場合や循環参照がある場合は？

適切な終了ロジックがない場合、再帰 CTEは残りの文字列が短くならない場合に無限再帰を試みる可能性があります。ANSI SQLでは、再帰メンバーが自然に終了するためにはゼロ行を生成する必要があります。各反復がSUBSTRINGがデリミタを超えて少なくとも1文字進んでいることを確認することにより、残りの長さが厳密に減少することを保証する必要があります。さらに、保存された最大値 (例: 1000 レベル) の後に強制終了のための深さカウンターを実装する必要がありますが、真のANSI SQLのポータビリティは、特定の方言のサイクル検出ではなく、残りの値が空でないというブール条件に依存しています。

この技術は、同時に分割する必要がある複数のCSV列を含むワイドテーブルでどのように機能しますか、行のアイデンティティを保持しながら？

多くの候補者は、複数のRecursive CTEをネストしたり、分割結果をクロス結合したりしようとしますが、これによりデカルト爆発が発生し、元の行から同じ列間の関係が失われます。正しいアプローチは、最初に複数の CSV 列を正規化された構造にアンピボットし (アンカーメンバーで UNION ALL を使用し、各ソース列にタグを付ける)、次に列識別子フラグを持つ単一の再帰パスを適用することです。これにより、異なる列のトークンが、手続き的ループやLATERALジョインを必要とせずに、共通の親行 ID に関連付けられたまま維持されますが、再帰の深さの取り扱いに注意が必要で、この深さは分割される列の数によって増加します。

非正規化されたカンマ区切りの値が単一のVARCHAR列に埋め込まれている場合、独自の文字列分割関数やラテラル派生テーブルに依存せず、ANSI SQLの再帰CTEを使用してこれらを個別の行に正規化する方法は？

質問への回答。

実生活からの状況

候補者が見逃すことがよくあること