SQL Interview Prep · 강의

공급업체별 PIVOT 및 크로스탭 구문

SQL Server의 PIVOT과 Postgres의 크로스탭, 그리고 각각의 한계를 알아봅니다.

공급업체별 PIVOT 및 크로스탭 구문은(는) CoddyKit의 무료 SQL Interview Prep 강의입니다. 이것은 4개 중 2번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 SQL Interview Prep 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. SQL Interview Prep 강의에는 총 4개의 강의가 포함되어 있습니다.

조건부 집계 너머

이미 이식 가능한 CASE 피벗을 알고 있습니다. 하지만 면접관은 사용 가능한 경우 데이터베이스 제품별 피벗 연산자도 사용할 수 있는지 확인하려고 합니다.

SQL Server에는 전용 PIVOT 연산자가 있습니다. PostgreSQL은 tablefunc 확장 기능에서 crosstab 함수를 제공합니다. 두 방식과 각각의 주의할 점을 알고 있으면 실무 경험이 있음을 보여 줄 수 있습니다.

SQL Server PIVOT 구조

SQL Server의 PIVOT에는 세 가지 요소가 필요합니다.

값 열에 적용할 집계 함수
값이 새 열이 될 열의 이름을 지정하는 FOR 절
열로 변환할 리터럴 값을 나열한 IN 목록

이 연산자는 키, 전개 열, 값만 정확히 노출하는 파생 테이블에 적용해야 하며, 그 밖의 항목은 포함하면 안 됩니다.

SELECT region, [Q1], [Q2]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
  SUM(amount)
  FOR quarter IN ([Q1], [Q2])
) AS p;

암시적 GROUP BY

면접관이 확인하는 미묘한 PIVOT 함정은 그룹화가 암시적이라는 점입니다. SQL Server는 원본에서 집계 대상 열도 FOR 열도 아닌(NOT) 모든 열을 기준으로 그룹화합니다.

따라서 파생 테이블에 order_id 같은 추가 열이 실수로 포함되면 피벗도 그 열을 기준으로 그룹화하고, 예상보다 훨씬 많은 행이 생성됩니다. 항상 내부 쿼리를 키, 전개 열, 값만 남도록 정리하세요.

-- WRONG: order_id leaks in and breaks grouping
FROM (SELECT region, quarter, amount, order_id FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2])) AS p;
-- The pivot now groups by region AND order_id

대괄호로 감싼 열 이름

SQL Server에서는 피벗된 열 이름이 데이터의 리터럴 값이며 대괄호로 감싸집니다. 값이 숫자로 시작하거나 공백을 포함하면 대괄호가 필수입니다.

바깥쪽 SELECT에서는 같은 대괄호가 붙은 이름으로 해당 열을 선택합니다. 또한 PIVOT의 IN 목록이 하드코딩되어 있기 때문에 알 수 없는 값을 동적 SQL 없이 처리할 수 없습니다.

SELECT region, [2023], [2024]
FROM (SELECT region, yr, amount FROM sales) AS s
PIVOT (SUM(amount) FOR yr IN ([2023], [2024])) AS p;

PostgreSQL 크로스탭

PostgreSQL에는 PIVOT 키워드가 없습니다. 대신 tablefunc 확장 기능이 SQL 문자열을 받아 그 결과를 재구성하는 crosstab 함수를 제공합니다.

먼저 확장 기능을 활성화해야 합니다. crosstab은 원본 쿼리가 정확히 세 개의 열, 즉 행 식별자, 범주, 값을 이 순서대로 반환하기를 요구합니다.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);

열 정의 목록

crosstab에서 오류가 가장 발생하기 쉬운 부분은 뒤에 붙는 AS ct(...) 열 정의 목록입니다. 출력 열의 이름과 형식을 직접 선언해야 하며, 범주의 개수와 순서가 일치해야 합니다.

어떤 행에서 범주가 누락되면 crosstab은 아래의 두 인수 형식을 사용하지 않는 한 값을 위치에 따라 채우므로 데이터가 어긋날 수 있습니다.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);
-- ct(...) MUST list every output column and its type

두 인수 crosstab

일부 행에 특정 범주가 없을 때 데이터가 어긋나는 문제를 피하려면 두 인수 형식을 사용하세요. 두 번째 쿼리가 범주 값의 전체 목록을 정렬된 순서로 반환하므로, crosstab은 각 값이 정확히 어느 열에 속하는지 알 수 있습니다.

범주가 드문드문 존재할 때 면접관이 기대하는 견고한 형식입니다.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric);

MySQL에는 둘 다 없음

면접관이 MySQL에 대해 묻는다면 답은 간단합니다. MySQL에는 PIVOT도 crosstab도 없습니다. 이 경우 사용할 수 있는 유일한 방법은 CASE를 사용한 조건부 집계입니다(또는 SUM(... ) + IF() 간편 구문을 사용할 수 있습니다).

이것이 이식 가능한 CASE 패턴이 높은 평가를 받는 정확한 이유입니다. 어디서나 동작하는 가장 폭넓은 공통 해법이기 때문입니다.

-- MySQL: only conditional aggregation works
SELECT
  region,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2
FROM sales
GROUP BY region;

실습 예제: SQL Server의 상태별 개수

보고서 요구 사항은 다음과 같습니다. “지역별로 한 행을 만들고, 각 상태의 주문 개수를 열로 표시하세요.” SQL Server에서는 정리한 파생 테이블을 COUNT와 함께 PIVOT에 입력합니다.

상태 열 자체를 세므로 각 구간에 있는 NULL이 아닌 모든 상태 행이 집계됩니다. 바깥쪽 SELECT에서는 각 상태를 대괄호로 감싼 열로 나열합니다. 이는 세 개의 COUNT(CASE ...) 표현식을 작성하는 간결한 대안입니다.

SELECT region, [pending], [shipped], [delivered]
FROM (SELECT region, status FROM orders) AS src
PIVOT (
  COUNT(status)
  FOR status IN ([pending], [shipped], [delivered])
) AS p;

공통적인 한계

PIVOT과 crosstab은 조건부 집계와 동일한 핵심 한계를 공유합니다. 쿼리를 작성할 때 출력 열을 알고 있어야 합니다.

SQL Server: IN 목록이 리터럴입니다.
PostgreSQL 크로스탭: 열 정의 목록이 리터럴입니다.

두 방식 모두 실행 시점에 범주를 찾아낼 수 없습니다. 그러려면 SQL 문자열을 동적으로 만들어야 합니다.

무엇을 사용해야 할까요

좋은 면접 답변은 다음과 같이 솔직하게 비교합니다.

CASE 집계: 이식 가능하고 읽기 쉬우며 모든 엔진에서 동작합니다. 기본 선택입니다.
SQL Server PIVOT: 열이 많을 때 간결하지만, 암시적 그룹화가 혼란을 줄 수 있습니다.
PostgreSQL 크로스탭: 강력하지만 장황하고, 확장 기능과 열 정의 목록이 필요합니다.

확신이 서지 않을 때는 조건부 집계를 선택하고, 대안으로 제품별 연산자도 언급하세요.

빠른 확인

면접관이 확인하는 SQL Server PIVOT의 동작을 정확히 이해해 보세요.

복습

한 화면으로 정리한 제품별 피벗 구문입니다.

SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b]))을 사용하며, 남은 열을 기준으로 암시적 GROUP BY가 적용됩니다.
PostgreSQL: tablefunc의 crosstab()을 사용하며 열 정의 목록이 필요합니다. 데이터가 드문드문 존재하면 두 인수 형식을 사용하세요.
MySQL: 둘 다 없으므로 CASE를 사용합니다.
세 방식 모두 쿼리를 작성할 때 열을 알고 있어야 합니다.

자주 묻는 질문

“공급업체별 PIVOT 및 크로스탭 구문” 강의는 무료인가요?

네 — “공급업체별 PIVOT 및 크로스탭 구문” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 SQL Interview Prep 강의 전체를 잠금 해제할 수 있습니다. SQL Interview Prep 강의에는 총 4개의 강의가 포함되어 있습니다.

“공급업체별 PIVOT 및 크로스탭 구문”에서 뭘 배우나요?

SQL Server의 PIVOT과 Postgres의 크로스탭, 그리고 각각의 한계를 알아봅니다. 브라우저에서 직접 실행하는 실습 코드로 SQL Interview Prep을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

SQL Interview Prep을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 SQL Interview Prep은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 2번째 강의입니다.

“공급업체별 PIVOT 및 크로스탭 구문” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 SQL Interview Prep 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 SQL Interview Prep 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← SQL Interview Prep(으)로 돌아가기