열을 행으로 언피벗하기

언피벗은 피벗의 거울상입니다. 넓은 테이블을 가져와 열을 다시 행으로 바꿉니다. 데이터가 스프레드시트 형태로 들어오지만 분석을 위해 정규화해야 할 때 면접관이 이 주제를 묻습니다.

예를 들어 지역별로 q1, q2, q3, q4 열이 있는 테이블은 (region, quarter, amount) 행으로 바뀌어야 합니다. 이 세로형이 집계, 조인, 차트 작성에 모두 더 적합합니다.

방언에 의존하지 않는 답은 UNION ALL입니다. 원본 열마다 하나의 SELECT를 작성하고, 각 쿼리에서 리터럴 레이블과 해당 열의 값을 출력합니다.

중복 제거 비용을 지불하지 않고 두 셀이 같은 값을 갖는 경우에도 모든 행을 유지하려면 UNION이 아니라 UNION ALL을 사용하세요.

이는 고전적인 면접 함정입니다. UNION은 전체 결과에서 중복 행을 제거합니다. 동부와 서부가 모두 Q1에 100을 기록했다면 일반적인 UNION은 동일한 행을 하나로 합쳐 데이터가 사라집니다.

UNION ALL은 중복을 제거하지 않고 연결하므로 언피벗에 필요한 동작을 합니다. 중복 제거를 위한 정렬이나 해시가 필요하지 않아 더 빠르기도 합니다.

UNION ALL의 모든 분기는 같은 개수의 열을 같은 순서로 생성해야 하며, 열의 형식도 호환되어야 합니다. 열 이름은 첫 번째 SELECT에서 결정됩니다.

넓은 테이블의 열 형식이 서로 다르면(예를 들어 하나는 int이고 다른 하나는 decimal인 경우) 엔진이 공통 형식을 선택합니다. 형식이 실제로 호환되지 않으면 명시적으로 형 변환하여 union이 실패하지 않도록 하세요.

SQL Server에는 UNION ALL보다 간결한 전용 UNPIVOT 연산자가 있습니다. 새 값 열과 새 레이블 열의 이름을 지정하고, 합칠 원본 열을 나열합니다.

한 가지 중요한 동작은 UNPIVOT이 값이 NULL인 행을 제거한다는 점입니다. 면접관은 이 부수 효과를 알고 있는지 확인합니다.

어떤 지역의 q3이 NULL이면 SQL Server의 UNPIVOT은 해당 행을 출력에서 간단히 제외합니다. NULL 여부와 관계없이 모든 열에 대한 행이 필요하다면 이를 보존하는 UNION ALL로 대체하세요.

면접에서는 이 절충점을 설명하세요. 기본 제공 UNPIVOT은 간결하지만 NULL을 잃고, UNION ALL은 장황하지만 완전합니다.

PostgreSQL에는 UNPIVOT이 없지만, 깔끔한 관용 패턴으로 VALUES 목록에 CROSS JOIN LATERAL을 적용하는 방법이 있습니다. 각 넓은 행을 (레이블, 값) 쌍으로 이루어진 작은 인라인 테이블과 결합해 확장합니다.

긴 UNION ALL보다 깔끔하고 원본 테이블도 한 번만 읽습니다.

언급할 만한 성능 관련 사항이 있습니다. 단순한 UNION ALL은 분기마다 넓은 테이블을 한 번씩 스캔합니다(분기 네 개면 네 번 스캔합니다). LATERAL VALUES 형식과 SQL Server의 UNPIVOT은 원본을 한 번 읽습니다.

대규모 테이블에서는 이것이 중요합니다. UNION ALL을 사용해야 하더라도 최적화기가 반복해서 스캔할 수 있으므로, 더 효율적인 방법으로 LATERAL이나 UNPIVOT을 언급하세요.

UNION ALL이나 LATERAL을 사용하면 값이 NULL인 행도 유지됩니다. 값이 있는 셀만 원한다면 필터 조건을 추가하세요. 이는 SQL Server의 UNPIVOT이 자동으로 수행하는 동작과 같습니다.

NULL을 유지할지 제거할지는 상황에 따른 판단이므로, 작성하기 전에 면접관에게 요구 사항을 명확히 확인하세요.

흔히 이어지는 질문은 다음과 같습니다. “분기별 넓은 형식 테이블에서 모든 분기에 걸친 지역별 총수익을 구하십시오.” 긴 형식으로 언피벗하고 나면 집계는 간단합니다. 지역별로 그룹화한 단일 SUM이면 됩니다.

이것이 먼저 언피벗해야 하는 진짜 이유를 보여 줍니다. 네 개의 열을 각각 더하는 방식은 취약하지만, 긴 형식의 SUM(amount) GROUP BY region은 분기 수가 얼마든 확장할 수 있습니다.

문제 설명에서 언피벗 신호를 알아보십시오.

• 입력에 반복되는 열이 있으며, 실제로는 값(월, 연도, 지표)입니다.
• 그 값들을 기준으로 집계하거나 조인하거나 차트로 표시해야 합니다.
• 가져오는 과정에서 비정규화된 스프레드시트 데이터를 정규화하려고 합니다.

긴 형식은 이후 SQL 작업에 거의 항상 적합한 형태이므로, 언피벗은 자주 수행하는 첫 단계입니다.

가장 흔한 언피벗 문제를 알고 있는지 확인하십시오.

언피벗은 열을 행으로 바꿉니다.

• 이식 가능: 열마다 하나의 SELECT를 사용하고 UNION ALL로 결합합니다(일반 UNION은 사용하지 않습니다).
• SQL 서버: 기본 제공 UNPIVOT을 사용합니다. 간결하지만 NULL 값을 제거합니다.
• PostgreSQL: CROSS JOIN LATERAL (VALUES ...)을 사용하며, 한 번만 스캔합니다.
• 각 분기에서 열의 개수와 형식을 맞추고, 질문에서 요구하는 경우 NULL을 필터링합니다.