조건부 집계로 피벗하기

보고서 작성 면접에서 가장 흔한 과제 중 하나는 행을 열로 변환하는 것입니다. sales(region, quarter, amount)와 같은 긴 테이블이 있고, 면접관은 분기마다 하나의 열이 있는 가로형 보고서를 원합니다.

면접관이 듣고 싶어 하는 이식 가능하고 방언에 종속되지 않는 답변은 조건부 집계입니다. 즉 CASE 표현식을 SUM과 같은 집계 함수 안에 넣는 방식입니다. 이것을 익히면 PIVOT 키워드가 없는 데이터베이스에서도 모든 데이터베이스에서 피벗할 수 있습니다.

피벗하기 전에 데이터 형태를 명확히 합시다. 세로형은 행마다 하나의 사실을 저장합니다. 각 지역과 분기의 조합이 각각의 행이 됩니다. 가로형은 하나의 범주를 여러 열로 펼칩니다.

• 세로형: 삽입하기는 쉽지만 나란히 비교해 읽기는 어렵습니다.
• 가로형: 사람이 읽는 보고서에 적합합니다.

피벗은 세로형을 가로형으로 변환합니다. 이 주제가 면접에서 자주 다뤄지는 이유는 단순한 구문이 아니라 집계를 이해하는지 확인할 수 있기 때문입니다.

핵심 요령은 다음과 같습니다. 결과 열마다 해당 행이 그 열과 일치할 때 값을 반환하고, 그렇지 않으면 NULL을 반환하는 CASE를 작성합니다. 이를 집계 함수로 감싸 그룹을 키당 한 행으로 축약합니다.

다음과 같이 이해하면 됩니다. 금액을 합산하되 Q1 행에 대해서만 합산한다는 뜻입니다. SUM은 NULL을 무시하므로 일치하지 않는 행은 아무것도 더하지 않습니다.

이 패턴이 작동하는 이유는 면접관이 확인할 중요한 사실 하나에 있습니다. 집계 함수는 NULL을 건너뜁니다. ELSE가 없는 CASE는 일치하는 분기가 없을 때 NULL을 반환하므로, SUM(CASE WHEN ... THEN amount END)은 선택한 행만 더합니다.

대신 ELSE 0을 작성해도 SUM에서는 작동합니다(0을 더해도 결과가 달라지지 않기 때문입니다). 하지만 AVG, MIN, COUNT에서는 문제가 됩니다.

다음은 샘플 데이터에 적용한 전체 쿼리입니다. 각 지역은 하나의 행이 되고, 각 분기는 하나의 열이 됩니다.

GROUP BY region이 네 개의 입력 행을 두 개의 출력 행으로 압축합니다. 이 구문이 없으면 입력 행마다 하나씩 행이 생성되고 대부분 NULL이 됩니다.

CASE를 감싸는 집계 함수는 질문의 의도와 맞아야 합니다.

• 각 셀의 값 합계가 필요하면 SUM을 사용합니다.
• 각 지역/분기 조합에 값이 정확히 하나씩 있고 그 값을 그대로 표시하려면 MAX 또는 MIN을 사용합니다.
• 각 셀에서 일치하는 행의 개수를 세려면 COUNT를 사용합니다.

면접에서는 COUNT 변형을 자주 묻습니다. 상태별·월별 주문 수는 얼마인가요?

각 키/범주 조합에 값이 하나만 들어 있는 경우(합계가 아닌 실제 교차표라면) MAX 또는 MIN을 사용합니다. 두 함수 모두 유일한 NULL이 아닌 값을 반환하고, 일치하지 않는 분기에서 나온 NULL은 무시합니다.

이는 금액을 합산하는 대신 속성을 재구성할 때 안전한 선택입니다. 예를 들어 키/값 설정 테이블을 엔터티별 한 행으로 바꿀 때 사용할 수 있습니다.

어떤 지역에 Q2 매출이 없으면 해당 지역의 q2 셀은 NULL이 됩니다. 면접관이 대신 0으로 표시하라고 할 수도 있습니다. 전체 집계 함수를 COALESCE로 감싸세요.

COALESCE는 CASE 안이 아니라 집계 함수 바깥에 두어야 합니다. 그래야 그룹 전체에 일치하는 행이 없을 때만 값을 대체할 수 있습니다.

자주 이어지는 질문은 피벗된 모든 열의 합계를 추가하라는 것입니다. 열 이름을 하나씩 더할 필요는 없습니다. 같은 그룹에 일반적인 SUM(amount)을 적용하면 CASE의 필터링을 전혀 적용하지 않으므로 행 전체의 합계가 계산됩니다.

이렇게 하면 SELECT 안의 각 집계가 동일한 그룹을 대상으로 독립적으로 계산된다는 점을 이해하고 있음을 면접관에게 보여 줄 수 있습니다.

PostgreSQL과 SQL 표준은 조건부 집계를 더 깔끔하게 작성하는 방법인 FILTER (WHERE ...)를 지원합니다. 이 방식은 읽기 쉽고 CASE를 반복해서 작성하는 부분도 줄여 줍니다.

면접에서 이를 언급하면 폭넓은 지식을 보여 줄 수 있지만, MySQL과 SQL Server는 이를 지원하지 않으므로 CASE가 여전히 이식 가능한 답이라는 점을 알아 두세요.

조건부 집계에는 면접관이 집요하게 확인하는 한 가지 문제가 있습니다. 모든 출력 열을 직접 하나씩 나열해야 합니다. 분기나 범주를 미리 알 수 없다면 이 정적 쿼리는 그에 맞게 바뀌지 않습니다.

이 문제를 동적 피벗이라고 하며, 생성된 SQL이 필요합니다. 하지만 범주 집합이 고정되어 있고 알려져 있다면 조건부 집계가 깔끔하고 이식성 높은 최선의 방법입니다.

조건부 집계 패턴을 제대로 이해했는지 확인해 보세요.

조건부 집계는 모든 면접관이 인정하는 이식 가능한 피벗 방법입니다.

• 출력 열마다 하나의 CASE를 작성하고 집계 함수로 감쌉니다.
• 합계에는 SUM, 값이 하나인 셀에는 MAX/MIN, 개수에는 COUNT를 사용합니다.
• 집계 함수가 일치하지 않는 분기에서 나온 NULL을 무시하기 때문에 동작합니다.
• COALESCE를 사용해 빈 셀을 0으로 바꿉니다.
• 한계는 열을 하드코딩해야 한다는 점이며, 다음에는 동적 피벗으로 이어집니다.