SQL에서 pivot 테이블 생성하기
in SQL
CASE를 활용한 pivot 테이블
쿼리 문
select
ts.sensorkit_name as sensorkit_name
,max(case when a.sensor_id = 'carbon_dioxide' then a.sensor_data end) as carbon_dioxide
,max(case when a.sensor_id = 'carbon_monoxide' then a.sensor_data end) as carbon_monoxide
,max(case when a.sensor_id = 'fine_dust' then a.sensor_data end) as fine_dust
,max(case when a.sensor_id = 'humidity' then a.sensor_data end) as humidity
,max(case when a.sensor_id = 'temperature' then a.sensor_data end) as temperature
,max(case when a.sensor_id = 'ultrafine_dust' then a.sensor_data end) as ultrafine_dust
,max(case when a.sensor_id = 'visitor_cnt' then a.sensor_data end) as visotor_cnt
,max(case when a.sensor_id = 'voc' then a.sensor_data end) as voc
from
(select tsd.sensorkit_id, tsd.sensor_id, avg(tsd.sensor_data)::numeric(10, 2) as sensor_data
from tb_sensor_data tsd
group by sensorkit_id, sensor_id) as a
inner join tb_sensorkit ts
on a.sensorkit_id = ts.sensorkit_id
group by sensorkit_name;
max 를 쓰지 않으면 하나의 index에 대해 엄청 많은 row가 생긴다.
| col1 | col2 | col3 | col4 | col5 | |
|---|---|---|---|---|---|
| a | 1 | null | null | null | null |
| a | null | 2 | null | null | null |
| a | null | null | 3 | null | null |
| a | null | null | null | 4 | null |
| a | null | null | null | null | 5 |
하지만 max 를 사용하면 아래와 같이 바뀐다.
| col1 | col2 | col3 | col4 | col5 | |
|---|---|---|---|---|---|
| a | 1 | 2 | 3 | 4 | 5 |
FILTER 활용한 pivot 테이블
postgreSql에서는 FILTER라는 메서드를 제공해준다. FILTER의 경우 집계된 행을
괄호 안의 조건을 만족하는 행으로 제한한다. 이게 무슨 말이냐면…
COALESCE(MAX(a.sensor_data) FILTER (WHERE a.sensor_id = 'carbon_dioxide'),0) carbon_dioxide
→ COALESCE : Null인 경우 0으로 처리.
하지만 FILTER의 경우 PostgreSQL과 SQLite에서만 지원해주기 때문에 사용하지 않았다.
crosstab 활용한 pivot 테이블
PostgreSQL에는 tablefunc extension에서 지원하는 crosstab을 활용할 수 있다.
주의할 점은 crosstab은 aggregate가 이미 되어 있는 테이블에서 pivot만 가능하다.
먼저, table을 aggregate한 후 crosstab에 적용해야 한다.
성공해본적이 없다…
정리
참고 블로그1 에서도 볼 수 있듯, CASE문을 사용해서 pivot 테이블을 만드는 것이 가장 일반적인
방법인거 같다. 왜냐면 FILTER나 crossTab의 경우에는 특정 데이터베이스에 종속적이기
때문이다.
📚 참고 블로그1
📚 참고 블로그2
