PostgreSQL - Record to column

데이터를 추출하면서, 레코드 데이터를 컬럼 데이터로 변환해야 할 때가 많이 있다. Application layer에서 처리할 수 있겠지만 DB Layer에서 처리할 수 있으면 DB 쪽에서 처리하는 것이 맞다고 생각한다. 물론 현재 진행중인 JPA 기반 프로젝트에서는 어쩔 수 없이 Application layer에서 처리해야 한다. 

아래 예는 일별 날씨 지표이다. 일별로 온도와 풍속, 강우량 등을 측정하는 테이블로써, 레코드로 데이터가 전개되어 있다. 이를 컬럼으로 전개해보자.

CREATE TABLE daily_weather (
    reg_date        varchar(255)
  , keywords varchar(255)
  , val       integer
);

INSERT INTO daily_weather
VALUES
    ('2018-01-01', 'temp', 21)
  , ('2018-01-01', 'wind'   ,  33)
  , ('2018-01-01', 'rain_quant'      ,  10)
  , ('2018-01-02', 'temp', 22)
  , ('2018-01-02', 'wind'   ,  31)
  , ('2018-01-02', 'rain_quant'      ,  5)
;

SELECT
	reg_date
	,  CASE WHEN keywords = 'temp' then val end as temprature
	, case when keywords = 'wind' then val end as wind_indicator
	,  case when keywords = 'rain_quant' then val end as rain_indicator
FROM daily_weather
ORDER by reg_date;

CASE WHEN 구문을 이용해서 아래와 같이 레코드 데이터를 컬럼 데이터로 전개할 수 있다. 하지만, 우리가 원한 결과는 아니다.  어떻게 할까? 

그렇다. 위 표에서 MAX 함수를 이용해서 아래와 같이 값 하나를 추출해야 한다. 

SELECT
	reg_date
	, MAX(CASE WHEN keywords = 'temp' then val end) as temprature
	, MAX(case when keywords = 'wind' then val end) as wind_indicator
	, MAX(case when keywords = 'rain_quant' then val end) as rain_indicator
FROM daily_weather
GROUP by reg_date
ORDER by reg_date;	

string_agg함수를 이용해서, 행을 쉼표로 구분한 문자 배열로 반환할 수 있다. 아래 데이터는 부서별 상품 판매량이다. 부서별 상품을 쉼표로 구분해서 총 판매한 금액을 집계해보자.

CREATE TABLE dept_sales_log (
    dept_id varchar(100)
  , product_id  varchar(255)
  , price       integer
);

INSERT INTO dept_sales_log
VALUES
    ('DEPT01', 'A001', 3000)
  , ('DEPT01', 'A002', 4000)
  , ('DEPT01', 'A003', 2000)
  , ('DEPT02', 'D001', 5000)
  , ('DEPT02', 'D002', 3000)
  , ('DEPT03', 'A001', 3000)
;

SELECT
	dept_id
	, string_agg(product_id, ',') as products
	, SUM(price) as amount
FROM
	dept_sales_log
GROUP BY dept_id
ORDER BY dept_id;

결과는 아래와 같다.