0Pricing
SQL Academy · 강의

교차 집계 패턴(PostgreSQL crosstab())

tablefunc 확장의 crosstab() 함수로 실제 피벗 테이블을 생성합니다.

교차 집계 패턴(PostgreSQL crosstab())은(는) CoddyKit의 무료 SQL Academy 강의입니다. 이것은 4개 중 4번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 SQL Academy 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

진정한 크로스탭이 필요한 이유

CASE 피벗에서는 각 대상 열을 직접 나열해야 합니다. 실제로 넓은 피벗을 만들 때(예: 제품마다 열 하나씩)는 tablefunc 확장의 crosstab()이 적합한 도구입니다.

확장 기능 활성화

tablefunc은 PostgreSQL contrib에 포함되어 제공됩니다.

CREATE EXTENSION IF NOT EXISTS tablefunc;

기본 crosstab 시그니처

crosstab은 3개 열로 구성된 SQL 문자열(row_key, category, value)을 받아 row_key와 범주마다 하나씩의 열을 반환합니다.

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders
    GROUP BY user_id, status
    ORDER BY user_id, status
  $$
) AS ct (
  user_id BIGINT,
  paid    INT,
  pending INT,
  cancelled INT
);

출력 열을 선언하는 이유

SQL은 정적으로 형식이 지정되므로 플래너가 구문 분석 시점에 출력 열을 알아야 합니다. 따라서 AS 절에서 데이터 형식을 포함한 스키마를 지정해야 합니다.

두 인자를 사용하는 crosstab(범주 집합 포함)

희소한 데이터에서는 범주 목록을 별도로 제공해야 누락된 값이 잘못 정렬되지 않고 NULL이 됩니다.

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders GROUP BY user_id, status
    ORDER BY user_id
  $$,
  $$ VALUES ('paid'), ('pending'), ('cancelled') $$
) AS ct (
  user_id BIGINT, paid INT, pending INT, cancelled INT
);

CASE가 crosstab보다 나은 경우

범주 집합이 작고 정해져 있다면 CASE/FILTER가 더 간단합니다. 확장이 필요하지 않고, 두 인자를 사용할 때의 함정도 없습니다. 다음과 같은 경우에는 crosstab을 사용하시면 됩니다.

  • 범주가 많은 경우
  • 범주를 동적으로 불러오는 경우
  • 외부 피벗 소비자를 위한 데이터를 생성하는 경우

동적 피벗

실행 시점에 범주를 알 수 없다면 애플리케이션에서 SQL을 생성하거나 format() + EXECUTE를 사용하는 PL/pgSQL을 사용하시면 됩니다.

-- Build the SQL dynamically:
SELECT string_agg(format('SUM(CASE WHEN status = %L THEN 1 END) AS %I',
                          status, status), ', ')
FROM (SELECT DISTINCT status FROM orders) s;

스프레드시트를 위한 넓은 형식의 피벗

분석가용 보고서에서는 넓은 형식을 원하는 경우가 많습니다. SQL에서 생성하거나, 긴 형식의 데이터를 전달한 뒤 BI 도구에서 피벗하도록 하시면 됩니다.

언피벗: 반대 작업

넓은 형식에서 긴 형식으로 변환하려면 UNION ALL 또는 PostgreSQL의 jsonb_each_text()를 사용하시면 됩니다.

SELECT id, key AS month, (value)::NUMERIC AS revenue
FROM monthly_wide,
     jsonb_each_text(to_jsonb(monthly_wide) - 'id');

성능

crosstab()은 내부 SQL을 한 번 실행한 뒤 메모리에서 피벗합니다. 병목 지점은 일반적인 GROUP BY 쿼리와 같습니다.

Crosstab의 한계

PostgreSQL에는 Oracle이나 SQL Server와 달리 기본 제공 PIVOT 키워드가 없습니다. crosstab()이 이를 해결하는 방법입니다.

복습

대부분의 피벗에서는 CASE/FILTER가 깔끔한 해결책입니다. 범주가 많거나 미리 알 수 없을 때는 crosstab()을 사용하시면 됩니다.

빠른 확인

PostgreSQL의 crosstab() 함수를 제공하는 확장은 무엇일까요?

자주 묻는 질문

“교차 집계 패턴(PostgreSQL crosstab())” 강의는 무료인가요?

네 — “교차 집계 패턴(PostgreSQL crosstab())” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 SQL Academy 강의 전체를 잠금 해제할 수 있습니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

“교차 집계 패턴(PostgreSQL crosstab())”에서 뭘 배우나요?

tablefunc 확장의 crosstab() 함수로 실제 피벗 테이블을 생성합니다. 브라우저에서 직접 실행하는 실습 코드로 SQL Academy을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

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

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

“교차 집계 패턴(PostgreSQL crosstab())” 강의는 얼마나 걸리나요?

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

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

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

이 강의의 모든 강의

  1. UNION, INTERSECT, EXCEPT
  2. UNION ALL과 UNION 비교(중복 제거 비용)
  3. CASE 표현식과 피벗 쿼리
  4. 교차 집계 패턴(PostgreSQL crosstab())
← SQL Academy(으)로 돌아가기