단일 varchar 열에 포함된 비정규화된 쉼표로 구분된 값을 마주쳤을 때, 전용 문자열 분할 함수나 측면 파생 테이블에 의존하지 않고 ANSI SQL 재귀 CTE를 사용하여 이를 개별 행으로 정규화하려면 어떻게 해야 합니까?

질문에 대한 답변.

이 문제는 표준 문자열 조작 기능만 사용하여 VARCHAR 문자열을 토큰화하는 것을 요구합니다. 해결책은 CSV 문자열을 스택으로 간주하여, 각 재귀 수준에서 POSITION을 사용하여 첫 번째 구분 기호를 찾아 왼쪽의 가장 바람직한 토큰을 제거하고, SUBSTRING을 통해 하위 문자열을 추출한 후 나머지를 다음 반복으로 전달하는 방식입니다.

앵커 멤버는 원래 열을 선택하고 첫 번째 토큰과 남은 문자열을 계산하여 프로세스를 시작합니다. 재귀 멤버는 그런 다음 남은 하위 문자열에 대해 이 논리를 반복하며, POSITION이 0을 반환할 때까지 (즉, 더 이상 구분 기호가 없음을 나타내는 경우) 또는 남은 문자열이 비어 있을 때까지 반복합니다.

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;

일상 생활에서의 상황

금융 기관은 다중 값 위험 지표를 쉼표로 구분된 문자열로 저장했습니다. 이는 개별 위험 범주에 대한 직접 집계를 방지했습니다. 준수 팀은 규제 조회 테이블과 조인하고 위험 유형별 노출 메트릭을 계산하기 위해 정규화된 행이 필요했습니다.

한 가지 접근 방식은 인덱스별로 하위 문자열을 추출하기 위해 임시 숫자 테이블(태리 테이블)을 사용하는 것이었습니다. 배치 처리에는 효율적이며 병렬 처리하기 쉽지만, 이 방법은 Oracle, PostgreSQL, 및 IBM Db2 인스턴스가 혼합된 이질적인 데이터베이스 환경 간의 엄격한 이동성 요구 사항을 위반하는 보조 객체를 생성해야 했습니다. 분산 시스템에서 이러한 태리 테이블을 동기화해야 하는 유지 관리 오버헤드는 이 솔루션을 운영적으로 비쌀 수 있게 만들었습니다.

또 다른 대안은 pandas 문자열 분할 방법을 사용하여 Python ETL 파이프라인으로 데이터를 추출하는 것이었습니다. 이는 우수한 원시 성능과 더 쉬운 디버깅 기능을 제공했지만, 보호된 데이터베이스 경계 밖으로 민감한 재무 데이터를 내보내면서 심각한 보안 문제를 일으켰습니다. 또한, 왕복 지연이 발생하여 실시간 규제 보고를 불가능하게 만든 동기화 지연을 초래했습니다.

선택된 솔루션은 ANSI SQL 재귀 CTE를 사용하여 SUBSTRING 및 POSITION을 활용하여 각 문자열을 제자리에 반복적으로 토큰화했습니다. 이 접근 방식은 계산을 데이터베이스 엔진 내에 유지함으로써 보안 제약 조건을 충족하고, 외부 종속성이나 임시 테이블이 필요하지 않았으며, 절차적 논리 없이 모든 데이터베이스 플랫폼에서 결정론적인 결과를 제공했습니다.

구현은 10백만 개의 비정규화된 레코드를 몇 분 내에 스타 스키마 팩트 테이블로 분해하는 데 성공했으며, 위험 관리 대시보드가 이전에 접근할 수 없었던 범주형 차원에 대해 서브초 단위 집계를 수행할 수 있도록 해주었습니다.

후보자들이 종종 놓치는 점

연속적인 구분 기호 사이의 빈 토큰(예: "a,,c")을 어떻게 처리하여 ordinal 열의 위치 무결성을 잃지 않습니까?

후보자들은 종종 SUBSTRING이 자연스럽게 연속적인 쉼표로 인해 빈 행을 방출할 것이라고 가정하지만, POSITION 함수는 하위 문자열 경계를 계산할 때 빈 구분 기호를 건너뜁니다. 빈 토큰을 유지하려면 POSITION이 이전 반복과 동일한 인덱스를 반환할 때(즉, 길이가 0인 토큰을 나타내는 경우)을 명시적으로 감지해야 하며, 나머지를 처리하기 전에 빈 문자열 행을 방출해야 합니다. 이는 재귀 멤버 내에서 현재 및 이전 구분 기호 위치를 추적해야 하며, 일반적으로 이전의 나머지 길이를 저장하고 이를 현재 위치와 비교해야 합니다.

잘못된 가져오기에서 입력 문자열이 구분 기호를 결여하거나 순환 참조를 포함할 경우 무한 재귀를 방지하기 위한 어떤 안전 장치가 있습니까?

적절한 종료 논리가 없으면 재귀 CTE는 나머지 문자열의 길이가 결코 줄어들지 않으면 무한 재귀를 시도할 수 있습니다. ANSI SQL은 재귀 멤버가 자연스럽게 종료되도록 제로 행을 생성하도록 요구합니다. 각 반복이 SUBSTRING이 구분 기호를 지나 한 문자 이상 진행되도록 하여 남은 길이를 엄격히 줄이는지 확인해야 합니다. 추가적으로, 최대 깊이를 강제로 종료하는 카운터를 구현해야 하며(예: 1000 수준) 악의적인 입력으로부터 보호합니다. 그러나 진정한 ANSI SQL 이동성이 보장되는 조건은 나머지가 비어 있지 않다는 것을 확인하는 부울 조건에 의존합니다.

이 기술이 여러 CSV 열이 동시에 분할되어야 하는 넓은 테이블에서 행의 정체성을 유지하면서 어떻게 수행됩니까?

많은 후보자들이 여러 재귀 CTE를 중첩하거나 분할 결과를 교차 조인하려고 시도하지만, 이것은 데카르트 곱을 생성하고 동일한 원래 행의 열 간의 관계를 파괴합니다. 올바른 접근 방식은 첫 번째로 여러 CSV 열을 정규화된 구조로 비틀어 (앵커 멤버에서 UNION ALL을 사용하고 각 소스 열에 태그를 추가하여)한 다음, 열 식별자 플래그를 전달하는 단일 재귀 호출을 적용하는 것입니다. 이렇게 하면 서로 다른 열에서 온 토큰이 해당 공통 부모 행 ID와 연관된 상태로 유지됩니다. 이를 통해 절차적 루프나 LATERAL 조인이 필요하지 않지만, 이제 재귀 깊이를 신중하게 처리해야 하며, 이는 분할되는 열의 수에 따라 증가합니다.