여러 속성 열을 정규화된 키-값 행으로 언피벗하는 ANSI SQL 관용구를 독점 UNPIVOT 연산자나 LATERAL 유도 테이블을 사용하지 않고 보여주세요?

질문에 대한 답변.

질문의 배경.

이 요구 사항은 레거시 스프레드시트 시스템이나 평면 파일 내보내기에서 마이그레이션되는 데이터 수집 파이프라인에서 시작되며, 시간 시계열 메트릭이나 범주형 속성이 정규화된 행 대신 넓은 형식의 열 헤더(예: Jan_Sales, Feb_Sales)로 비정규화됩니다. 이러한 스키마는 관계형 웨어하우스로 ETL되기 전에 Excel 기반 비즈니스 프로세스에서 흔하게 발생하며, 시간적 분석 및 차원 JOIN을 가능하게 하기 위해 좁은 사실 테이블로 변환해야 합니다. 문제는 이러한 고정 열 프로젝션을 동적 튜플 스트림으로 변환하는 것입니다. 이를 위해 행 단위 처리를 하지 않고도 수행해야 합니다.

문제점.

표준 SELECT 문은 파싱 시간에 프로젝션된 열 ID를 고정하므로, LATERAL 상관 또는 절차적 반복 없이 단일 프로젝션이 서로 다른 출력 행에서 서로 다른 소스 열을 방출하는 것을 방지합니다. 목표는 각 소스 행과 속성 이름을 나열하는 가상 차원 테이블之间의 카르테시안 곱을 만드는 것이며, 그런 다음 조건 논리를 통해 일반 결과 열로 올바른 소스 값을 멀티플렉싱해야 합니다. 이는 ANSI SQL:1999 및 이후 버전에서 제공되는 표준 조인 구문 및 스칼라 표현식만을 사용하여 수행해야 합니다.

해결책.

범주형 키(예: 월 이름)를 행으로 나열하는 VALUES 행 생성기를 통해 유도된 테이블에 대해 CROSS JOIN을 이용합니다. SELECT 목록 내에서 각 키를 해당 소스 열에 매핑하는 검색된 CASE 표현식을 사용하여 비정규화된 값을 정규화된 행 구조로 효과적으로 프로젝션합니다. 특정 키에 대해 소스 속성이 없을 때 생성된 NULL 값을 제외하도록 결과를 필터링하여 최종 출력에 유효한 측정값만 포함되도록 합니다.

SELECT 
  s.cost_center_id,
  m.fiscal_month,
  CASE m.fiscal_month
    WHEN 'M01' THEN s.m01_amt
    WHEN 'M02' THEN s.m02_amt
    WHEN 'M03' THEN s.m03_amt
    -- ... 추가 월
    WHEN 'M12' THEN s.m12_amt
  END AS amount
FROM budget_wide s
CROSS JOIN (
  VALUES ('M01'), ('M02'), ('M03'), ('M04'), 
         ('M05'), ('M06'), ('M07'), ('M08'), 
         ('M09'), ('M10'), ('M11'), ('M12')
) AS m(fiscal_month)
WHERE CASE m.fiscal_month 
        WHEN 'M01' THEN s.m01_amt 
        -- ... NULL을 피하기 위해 모두 반복
      END IS NOT NULL;

실제 상황

재무 부서가 기업 Excel 모델에서 회계 연도 예산 할당을 스테이징 테이블로 내보냈습니다. 여기서 12개의 월 열(M01_Amt부터 M12_Amt까지)은 각 비용 센터에 대한 비정규화된 기간을 나타냈습니다. 대상 SAP 데이터 웨어하우스는 (CostCenter_ID, Fiscal_Month, Amount)의 좁은 사실 테이블 스키마를 요구하여, 중간 Python 처리를 피하기 위해 ANSI SQL 로딩 스크립트 내에서 언피벗 변환이 필요했습니다. 5천만 개의 레코드 양은 수동 변환이나 다중 프로세스 로드 전략을 불가능하게 했습니다.

해결책 1: 열별 Union All.

초기 접근 방식은 12개의 별도의 SELECT 쿼리를 사용하여 각 쿼리가 서로 다른 월 열을 일반 Amount 및 Month_Name 열로 하드코딩하여 UNIONS ALL을 통해 결합했습니다. 장점: 이 방법은 레거시 메인프레임 데이터베이스 및 현대 조인 구문이 없는 구식 SQL 엔진에서 작동하는 보편적인 호환성을 갖습니다. 단점: 이것은 소스 데이터에 대해 12번의 전체 테이블 스캔을 수행하여 선형 I/O 저하를 초래하고 쿼리 계획은 방대해지고 캐시하기 어려워지며, 스키마 수정을 (13번째 기간 추가) 요구할 때는 12개의 별도 프로젝션 목록을 수정해야 합니다.

해결책 2: 동적 SQL 생성.

대안으로, 쿼리 텍스트를 애플리케이션 계층에서 메타데이터 테이블을 반복하면서 동적으로 만들어 필요한 CASE 분기를 생성했습니다. 장점: 이는 진화하는 스키마에 대해 유연성을 제공하고 수백 개의 열을 처리할 때 수동 SQL 작성 노력을 줄입니다. 단점: 절차적 로직에 대한 금지를 위반하며 SQL 인젝션 공격 벡터와 컴파일 오버헤드를 도입하고, 결과 구문은 정적 데이터베이스 보기나 저장 프로시저 정의에 캡슐화할 수 없습니다.

해결책 3: 값과의 교차 조인.

수용된 구현은 12개의 회계 기간을 정의하는 VALUES 생성기와 함께 CROSS JOIN을 사용하여 가상의 기간 식별자에 키를 매핑한 CASE 표현식을 통해 올바른 금액을 멀티플렉싱했습니다. 장점: 이는 소스 테이블에 대해 단일 패스로 실행되며 효율적인 조인 알고리즘을 활용하고, Oracle, SQL Server, PostgreSQL 및 Db2를 통해 전적으로 선언적이며 이식 가능합니다. 단점: 이는 row 생성기용 SQL:1999 지원이 필요하여 구식 시스템에서는 사용할 수 없으며, CASE 표현식의 장황함은 템플릿을 통해 생성하지 않는 한 유지 관리 오버헤드를 증가시킵니다.

결과.

변환 대기 시간이 25분에서 90초 이하로 줄어들어 UNION ALL 패턴의 중복 테이블 스캔을 제거했습니다. 로딩 프로세스는 스키마 확장에 대해 탄력적이 되어 새로운 회계 기간이 도입될 때 VALUES 생성자에 단지 행을 추가하기만 하면 됩니다. 또한, 이 로직은 표준 보기로 캡슐화되어 Tableau 사용자가 중간 ETL 단계 없이 직접 즉석 쿼리를 할 수 있게 했습니다.

후보자들이 종종 놓치는 것

정 방출 때 NULL 값이 있는 소스 열이 언피벗된 결과에 나타나지 않도록 하려면 어떻게 해야 하며 CASE 표현식이 실행 계획에서 두 번 평가되는 것을 피합니까?

후보자들은 종종 WHERE 절 조건문에 CASE 표현식을 내장하여 WHERE CASE ... END IS NOT NULL와 같이 작성하는데, 이는 최적화기가 프로젝션을 두 번 계산하게 만듭니다—한 번은 필터링을 위해, 또 한 번은 출력을 위해. 효율적인 ANSI SQL 패턴은 파생 테이블이나 공통 테이블 표현(CTE) 내에서 결과를 물질화합니다: SELECT * FROM (SELECT ..., CASE ... END AS val FROM ... CROSS JOIN ...) sub WHERE val IS NOT NULL. 이는 CASE를 한 번 계산하고, 행을 필터링하며 쿼리 최적화기의 관점을 깔끔하게 분리합니다.

이종 데이터 유형이 있는 열을 언피벗할 때(예: VARCHAR 주석 열과 함께 DECIMAL 금액 열), 데이터 손실 없이 단일 결과 값 열에서 유형 일관성을 보장하는 특정 ANSI SQL 캐스팅 전략은 무엇입니까?

많은 후보자들이 암시적 유형 변환에 의존하지만, 이는 문자열을 잘라내거나 소수 정밀도를 잃게 하고, 또는 데이터 유형 강제 규칙이 플랫폼마다 다름을 인식하지 못한 채 UNION ALL을 시도합니다. 강력한 솔루션은 CASE 표현식의 각 WHEN 분기 내에서 모든 소스 열을 일반 수퍼타입—일반적으로 VARCHAR—으로 명시적으로 캐스팅하는 것입니다: CASE WHEN key='Comment' THEN CAST(text_col AS VARCHAR(500)) ELSE CAST(num_col AS VARCHAR(500)) END. 이는 모든 반환 값이 결과 열 정의와 호환되는 단일 데이터 유형을 공유하도록 보장하며, 필요한 경우 숫자 데이터의 텍스트 표현을 유지합니다.

CROSS JOIN과 VALUES 접근 방식이 표면상 카르테시안 곱 폭발을 생성하는 것처럼 보이는데, 최적화기가 일반적으로 이를 어떻게 완화하는가? 이는 기본 UNPIVOT 연산자의 NULL 제거 동작과 비교해볼 때 어떤가?

CROSS JOIN은 논리적으로 M×N 행(출처 행과 속성 수의 곱)을 생성하지만, 후보자들은 데이터 세트가 클 경우 성능이 저하될 것이라고 걱정합니다. 그러나 현대의 비용 기반 최적화기는 CASE 표현식이 작은 상수 테이블에 대한 데이터 종속성을 인식하고 종종 계획을 단순 프로젝션이나 내부적으로 UNPIVOT 물리적 연산자로 변환하여 실제 행 곱셈을 피합니다. 기본 UNPIVOT와 달리, 일반적으로 NULL 결과를 자동으로 제거하지만, 이 방법은 명시적인 WHERE 절이 필요하여 소스 속성이 NULL인 행을 폐기해야 합니다. 그렇지 않으면 결과 집합은 유효하지 않은 집합 계산에 영향을 미치는 스푸리어스 빈 사실을 포함합니다.