[PostgreSQL] tablefunc crosstab — (연,월,수량) 행을 연×월 피벗 리포트로

설치·접속: PostgreSQL 설치와 접속

부제: 월별 매출 로우를 "연도 × 1~12월" 표로 뽑아야 하는데, CASE WHEN을 12개 쓰기는 지겨울 때

정규화된 (yr, mon, qty) 행 데이터를 연도별 12개월 열로 돌려야 하는 리포트. crosstab은 이 행→열 피벗을 함수 한 번으로 처리한다.

CREATE EXTENSION IF NOT EXISTS tablefunc;

CREATE TABLE sales (yr int, mon int, qty int);
INSERT INTO sales(yr, mon, qty) VALUES
 (2024,1,120),(2024,2,90),(2024,3,140),(2024,5,60),(2024,11,200),(2024,12,240),
 (2025,1,150),(2025,2,110),(2025,3,175),(2025,4,130),(2025,7,80),(2025,12,300);

SELECT * FROM crosstab(
  'SELECT yr, mon, qty FROM sales ORDER BY 1,2',
  'SELECT g FROM generate_series(1,12) g'
) AS ct(yr int,
        m1 int, m2 int, m3 int, m4 int, m5 int, m6 int,
        m7 int, m8 int, m9 int, m10 int, m11 int, m12 int);

  yr  | m1  | m2  | m3  | m4  | m5 | m6 | m7 | m8 | m9 | m10 | m11 | m12 
------+-----+-----+-----+-----+----+----+----+----+----+-----+-----+-----
 2024 | 120 |  90 | 140 |     | 60 |    |    |    |    |     | 200 | 240
 2025 | 150 | 110 | 175 | 130 |    |    | 80 |    |    |     |     | 300
(2 rows)

첫 인자는 (row_name, category, value) 순서의 소스 쿼리다. 여기서는 (yr, mon, qty). 두 번째 인자가 핵심인데, generate_series(1,12)로 월 목록을 명시한다. 이 2-인자 형식을 쓰면 데이터에 없는 달(2024년 4·6~10월)도 열 위치를 지켜 NULL로 채운다.

CASE 12개와 비교

같은 결과를 크로스탭 없이 만들면 집계를 12번 나열해야 한다.

SELECT yr,
  sum(qty) FILTER (WHERE mon=1)  AS m1,
  sum(qty) FILTER (WHERE mon=2)  AS m2,
  ... -- (중략) 12번 반복
  sum(qty) FILTER (WHERE mon=12) AS m12
FROM sales GROUP BY yr ORDER BY yr;

  yr  | m1  | m2  | m3  | m4  | m5 | m6 | m7 | m8 | m9 | m10 | m11 | m12 
------+-----+-----+-----+-----+----+----+----+----+----+-----+-----+-----
 2024 | 120 |  90 | 140 |     | 60 |    |    |    |    |     | 200 | 240
 2025 | 150 | 110 | 175 | 130 |    |    | 80 |    |    |     |     | 300
(2 rows)

결과는 똑같다. 차이는 작성량과 유지보수다. 열 개수가 고정(12개월)이면 둘 다 쓸 만하지만, 축이 바뀌면 crosstab은 카테고리 쿼리만 갈아끼우면 되고 FILTER 방식은 줄을 통째로 다시 쓴다.

함정: 2-인자 형식을 꼭 써라

1-인자 형식(crosstab('...소스쿼리...'))은 카테고리 목록을 모른 채 "나온 순서대로" 열을 채운다. 그래서 중간에 빠진 달이 있으면 값이 왼쪽으로 밀린다.

SELECT * FROM crosstab('SELECT yr, mon, qty FROM sales ORDER BY 1,2')
  AS ct(yr int, c1 int, c2 int, c3 int, c4 int, c5 int, c6 int);

  yr  | c1  | c2  | c3  | c4  | c5  | c6  
------+-----+-----+-----+-----+-----+-----
 2024 | 120 |  90 | 140 |  60 | 200 | 240
 2025 | 150 | 110 | 175 | 130 |  80 | 300

2024년 5월 값 60이 c4(4번째 열)에 들어가 버렸다. 4월이 없으니 5월이 그 자리를 차지한 것이다. 같은 c4인데 2024는 5월, 2025는 4월이라 열의 의미가 행마다 달라진다 — 리포트로는 쓰레기다. 빠진 값이 있을 수 있는 데이터에는 반드시 2-인자 형식으로 카테고리 축을 고정한다.

이렇게도 쓴다

카테고리 축을 실제 테이블에서 뽑는다. 월 대신 "존재하는 상품 카테고리"처럼 동적 목록일 때. (조합: 카테고리 서브쿼리)

SELECT * FROM crosstab(
  'SELECT region, product, sum(amt) FROM sales2 GROUP BY 1,2 ORDER BY 1,2',
  'SELECT DISTINCT product FROM sales2 ORDER BY 1'
) AS ct(region text, prod_a int, prod_b int, prod_c int);

 

행 합계 열을 붙인다. 피벗 결과를 서브쿼리로 감싸 12개월 합을 추가. (조합: 바깥 SELECT)

SELECT ct.*, (coalesce(m1,0)+coalesce(m2,0)+coalesce(m3,0)+coalesce(m4,0)
             +coalesce(m5,0)+coalesce(m6,0)+coalesce(m7,0)+coalesce(m8,0)
             +coalesce(m9,0)+coalesce(m10,0)+coalesce(m11,0)+coalesce(m12,0)) AS total
FROM crosstab(
  'SELECT yr, mon, qty FROM sales ORDER BY 1,2',
  'SELECT g FROM generate_series(1,12) g'
) AS ct(yr int, m1 int,m2 int,m3 int,m4 int,m5 int,m6 int,m7 int,m8 int,m9 int,m10 int,m11 int,m12 int);

 

NULL을 0으로 채워 리포트를 깔끔하게 한다. (조합: coalesce)

SELECT yr, coalesce(m1,0) AS m1, coalesce(m2,0) AS m2  -- ... 이하 동일
FROM crosstab('SELECT yr, mon, qty FROM sales ORDER BY 1,2',
              'SELECT g FROM generate_series(1,12) g')
  AS ct(yr int, m1 int,m2 int,m3 int,m4 int,m5 int,m6 int,m7 int,m8 int,m9 int,m10 int,m11 int,m12 int);

 

FILTER 집계가 나은 경우. 열마다 다른 집계(합·평균·개수)가 섞이거나 열 수가 적으면 crosstab보다 명시적이다. (경계: 언제 CASE/FILTER로)

SELECT yr,
  sum(qty) FILTER (WHERE mon<=6)  AS h1_sum,
  avg(qty) FILTER (WHERE mon>6)   AS h2_avg,
  count(*) FILTER (WHERE qty>=200) AS big_months
FROM sales GROUP BY yr ORDER BY yr;