[PostgreSQL] 데이터 행과 열 변환

데이터 행과 열 변환

행을 열로 변환하기

DATASET QUERY

CREATE TABLE kpi (
    date        varchar(255)
  , part varchar(255)
  , val       integer
);

INSERT INTO kpi
VALUES
    ('2020-01-01', 'sessions', 1800)
  , ('2020-01-01', 'pages',  500)
  , ('2020-01-01', 'users',  200)
  , ('2020-02-01', 'sessions', 2000)
  , ('2020-02-01', 'pages',  700)
  , ('2020-02-01', 'users',  250)
  , ('2020-03-01', 'sessions', 1200)
  , ('2020-03-01', 'pages',  400)
  , ('2020-03-01', 'users',  6000)
;

CREATE TABLE purchase_log (
    purchase_id integer
  , product_id  varchar(255)
  , price       integer
);

INSERT INTO purchase_log
VALUES
    (100001, 'A001', 3000)
  , (100001, 'A002', 4000)
  , (100001, 'A003', 2000)
  , (100002, 'D001', 5000)
  , (100002, 'D002', 3000)
  , (100003, 'A001', 3000)
  , (100003, 'A001', 2200)
  , (100003, 'A002', 4000)
  , (100003, 'D001', 3000)
;

위의 데이터셋 테이블을 MAX값을 활용하여 행으로 저장된 지표 값을 열로 변환해보겠습니다.

select date
	,max(case when part = 'sessions' then val end) as sessions
	,max(case when part = 'pages' then val end) as pages
	,max(case when part = 'users' then val end) as users
from kpi
group by date
order by date;

이번에는 값이 아닌 문자열로 집약해보겠습니다.

문자열은 STRING_AGG을 활용해서 집약합니다.

STRING_AGG(대상칼럼, 구분된문자열)

Select purchase_id
	,string_agg(product_id, ',') as purchase_ids
	,sum(price) as amount
from purchase_log
group by purchase_id
order by purchase_id;

CF) mysql

- group_concat 활용

SELECT GROUP_CONCAT(product_id ',') AS product_ids
FROM purchase_log
group by pruchase_id
order by pruchase_id

 

열을 행으로 변환하기

DATASET QUERY

CREATE TABLE quarterly_sales (
    year integer
  , q1   integer
  , q2   integer
  , q3   integer
  , q4   integer
);

INSERT INTO quarterly_sales 
VALUES 
	(2015, 82000, 83000, 78000, 83000) 
	,(2016, 85000, 85000, 80000, 81000) 
	,(2017, 92000, 81000, 80000, 90000 ) 
	,(2018, 80000, 83000, 90000, 91000 ) 
	,(2019, 98000, 83000, 90000 , 80000 ) ;
	,(2020, 100000, 93000, 70000 , 90000 ) ;

 

이번에는 열에 있는 분기를 행으로 변환하여 피벗테이블을 만들어보겠습니다.

select
	q.year
	,case
		when p.idx = 1 then 'q1'
		when p.idx = 2 then 'q2'
		when p.idx = 3 then 'q3'
		when p.idx = 4 then 'q4'
	end as quarter
	,case
		when p.idx = 1 then q.q1
		when p.idx = 2 then q.q2
		when p.idx = 3 then q.q3
		when p.idx = 4 then q.q4
	end as sales
from quarterly_sales as q
	CROSS JOIN( SELECT 1 as idx
			  UNION ALL SELECT 2 as idx
			  UNION ALL SELECT 3 as idx
			  UNION ALL SELECT 4 as idx
			   ) AS p;
			  

임의의 길이를 가진 배열을 행으로 변환하기

string_to_array, unnest 활용하여 변환

먼저 문자열된 것을 배열로 변환할때는 string_to_array함수를 활용합니다.

그리고 배열을 레코드로 분해해야하는데 이때 활용되는 것이 테이블 함수이며 unnest 함수가 있습니다. 데이터셋은 위에서 문자열로 집약한 데이터를 활용합니다.

select purchase_id, product_id
from purchase_log as p
	cross join unnest(string_to_array(product_ids, ',')) as product_id

regexp_split_to_table

또한 PostgreSQL은 문자열을 구분자로 분할해서 테이블화를 한번에 해주는 regexp_split_to_table함수가 있습니다.

select purchase_id
	,regexp_split_to_table(product_ids, ',') as product_id
from purchase_log;