[PostgreSQL, Greenplum] 세로로 긴 테이블을 Pivot 하여 Wide-format으로 바꾸기 (pivot table using crosstab(), pivot() function)

지난번 포스팅에서는 PostgreSQL, Greenplum DB에서 옆으로 넓은 테이블(horizontally wide-format table)을 세로로 긴 테이블(vertically long-format table)로 변환(transpose)하는 2가지 방법을 소개하였습니다. (https://rfriend.tistory.com/713)

 

이번 포스팅에서는 반대로, PostgreSQL, Greenplum DB에서 세로로 긴 테이블을 가로로 넓은 테이블로 Pivot 하는 방법(Pivoting table, converting long-format table to wide-format table)을 소개하겠습니다. 보통 탐색적 데이터 분석, 통계 분석, 기계학습 등의 분석을 할 때는 pivot table 한 후의 각 ID별로 여러개의 칼럼이 옆으로 넓게 붙은 형태의 테이블을 사용합니다. 

 

* PostgreSQL과 Greenplum 에서 각각 다른 함수를 사용하는 것에 유의하세요. 

 

(1) PostgreSQL에서 tablefunc extension의 crosstab() 함수를 이용해 테이블 피봇하기

     (Pivoting table using crosstab() function in PostgreSQL)

(2) Greenplum 에서 Apache MADlib의 pivot() 함수를 이용해 테이블 피봇하기

     (Pivoting table using Apache MADlib's pivot() function in Greenplum)

(3) Manual 하게 Select 후 Join 해서 테이블 피봇하기 

     (Pivoting table by select and join manually) 

 

PostgreSQL, Greenplum, pivoting table, reshaping from long-format to wide-format

 

먼저, 예제로 사용할 간단한 예제 테이블을 만들어보겠습니다. 학생 ID별로 과목(class_nm) 별 점수(score) 를 저장해놓은 테이블입니다. 

 

--------------------------------------------------------------------------------
-- Pivoting table using crosstab() function in PostgreSql 9.4+
-- [ref] https://www.postgresql.org/docs/9.4/tablefunc.html
-- [ref] https://learnsql.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/
--------------------------------------------------------------------------------

DROP TABLE IF EXISTS score_long;
CREATE TABLE score_long (
	id int NOT null
	, class_nm TEXT 
	, score int
);

INSERT INTO score_long VALUES 
 (1,'english' , 100)
, (1,'korean' , 100)
, (1,'math', 100)
, (2,'english', 75)
, (2,'korean', 80)
, (2,'math', 85)
, (3,'english', 98)
, (3,'korean' , 100)
, (3,'math', 60)
, (4,'english', 75)
, (4,'korean', 80)
, (4,'math', 98)
;

SELECT * FROM score_long ORDER BY id, class_nm;
--id|class_nm|score
----+--------+-----+
-- 1|english |  100|
-- 1|korean  |  100|
-- 1|math    |  100|
-- 2|english |   75|
-- 2|korean  |   80|
-- 2|math    |   85|
-- 3|english |   98|
-- 3|korean  |  100|
-- 3|math    |   60|
-- 4|english |   75|
-- 4|korean  |   80|
-- 4|math    |   98|

 

 

(1) PostgreSQL에서 tablefunc extension의 crosstab() 함수를 이용해 테이블 피봇하기

     (Pivoting table using crosstab() function in PostgreSQL)

 

세로로 긴 테이블을 가로로 넓은 테이블로 pivot 할 때 사용되는 crosstab() 함수는 PostgreSQL 버전 8.3 이 배포되었을 때 처음 소개되었던 tablefunc extension 에 포함되었습니다. 따라서 tablefunc extension 을 처음 사용하는 경우라면

CREATE EXTENSION tablefunc; 로 활성화시켜준 후에 crosstab() 함수를 호출해서 사용할 수 있습니다. 

 

crosstab() 함수는 SELECT 문의 FROM 절에 사용이 되므로 처음 사용하는 분이라면 좀 낯설게 여길 수도 있겠습니다. crosstab() 함수에서 SELECT 문은 3개의 칼럼을 반환합니다. 

 

(칼럼 1) 첫번째 칼럼은 각 관측치를 구분하는 ID (identifier) 칼럼입니다. 위의 예에서는 학생들의 ID가 이에 해당합니다. 

(칼럼 2) 두번째 칼럼은 pivot table 에서의 범주(categories)에 해당하는 칼럼입니다. pivot을 하게 되면 각 칼럼으로 변환이 됩니다. 위의 예에서는 과목명(class_nm) 칼럼이 이에 해당합니다. 

(칼럼 3) 세번째 칼럼은 pivot table 의 각 셀에 할당이 될 값(value)에 해당하는 칼럼입니다. 위의 예에서는 점수(score) 칼럼이 이에 해당합니다. 

 

crosstab() 함수안에 SQL query로 위의 3개 칼럼을 select 한 결과를, AS final_result() 에서 pivot table 에서 재표현할 칼럼 이름과 데이터 유형을 정의해주면 됩니다. 

 

-- (1) Pivoting table using PostgreSQL's crosstab() function
-- Enabling the Crosstab Function
-- : The crosstab() function is part of a PostgreSQL extension called tablefunc. 
CREATE EXTENSION tablefunc;

-- Pivoting table
--: The crosstab() function receives an SQL SELECT command as a parameter.
SELECT *
FROM 
	crosstab(
		'select id, class_nm, score 
		from score_long 
		order by 1, 2') 
	AS final_result(id int, english_score int, korean_score int, math_score int);

--id|english_score|korean_score|math_score|
----+-------------+------------+----------+
-- 1|          100|         100|       100|
-- 2|           75|          80|        85|
-- 3|           98|         100|        60|
-- 4|           75|          80|        98|

 

 

 

(2) Greenplum 에서 Apache MADlib의 pivot() 함수를 이용해 테이블 피봇하기

     (Pivoting table using Apache MADlib's pivot() function in Greenplum)

 

Greenplum 에서는 PostgreSQL에서 사용했던 crosstab() 함수를 사용할 수 없습니다 대신 Greenplum 에서는 테이블을 pivot 하려고 할 때 Apache MADlib의 pivot() 함수를 사용합니다. 아래의 madlib.pivot() 함수 안의 구문(syntax)을 참고해서 각 매개변수 항목에 순서대로 입력을 해주면 됩니다. 

 

--------------------------------------------------------------------------------
-- Pivoting table using crosstab() function in Greenplum
-- [ref] https://madlib.apache.org/docs/latest/group__grp__pivot.html
--------------------------------------------------------------------------------

-- Pivoting table using Apache MADlib's pivot() function

DROP TABLE IF EXISTS score_pivot;
SELECT madlib.pivot(
	'score_long' 		-- source_table,
	, 'score_pivot' 	-- output_table,
	, 'id' 					-- index,
	, 'class_nm' 		-- pivot_cols,
	, 'score' 			-- pivot_values,
	, 'avg' 				-- aggregate_func,
	, 'NULL'			-- fill_value,
	, 'False'			-- keep_null,
);


SELECT * FROM score_pivot ORDER BY id;
--id|score_avg_class_nm_english|score_avg_class_nm_korean|score_avg_class_nm_math|
--+--------------------------+-------------------------+-----------------------+
-- 1|      100.0000000000000000|     100.0000000000000000|   100.0000000000000000|
-- 2|       75.0000000000000000|      80.0000000000000000|    85.0000000000000000|
-- 3|       98.0000000000000000|     100.0000000000000000|    60.0000000000000000|
-- 4|       75.0000000000000000|      80.0000000000000000|    98.0000000000000000|

 

 

칼럼 이름이 자동으로 설정('피봇값_집계함수_카테고리 칼럼이름' 형식)이 되는데요, 만약 칼럼 이름을 사용자의 입맛에 맞게 새로 바꿔주고 싶으면 ALTER TABLE table_name RENAME COLUMN old_column TO new_column; 을 사용해서 바꿔주세요. 

 

-- Renaming the column names 
ALTER TABLE score_pivot 
RENAME COLUMN score_avg_class_nm_english TO english_score;

ALTER TABLE score_pivot 
RENAME COLUMN score_avg_class_nm_korean TO korean_score;

ALTER TABLE score_pivot 
RENAME COLUMN score_avg_class_nm_math TO math_score;


SELECT * FROM score_pivot ORDER BY id;
--id|english_score       |korean_score        |math_score          |
----+--------------------+--------------------+--------------------+
-- 1|100.0000000000000000|100.0000000000000000|100.0000000000000000|
-- 2| 75.0000000000000000| 80.0000000000000000| 85.0000000000000000|
-- 3| 98.0000000000000000|100.0000000000000000| 60.0000000000000000|
-- 4| 75.0000000000000000| 80.0000000000000000| 98.0000000000000000|

 

 

 

(3) Manual 하게 Select 후 Join 해서 테이블 피봇하기 

     (Pivoting table by select and join manually)

 

물론, 피봇한 후의 테이블에서 칼럼 개수가 몇 개 안된다면 수작업으로 조건절로 SELECT 하여 JOIN 을 해서 새로운 테이블을 만들어 주는 방법도 가능합니다. 다만, pivot table 의 칼럼 개수가 수십, 수백개 된다면 이처럼 수작업으로 일일이 하나씩 SELECT 한 후에 JOIN 을 하는게 매우 번거롭고, 시간이 오래걸리고, 자칫 human error 를 만들 수도 있으니 위의 함수를 사용하는 것이 보다 나아보입니다. 

 

-- (3) Piovting table using join manually
WITH english AS (
	SELECT id, score AS english_score 
	FROM score_long
	WHERE class_nm = 'english'
), korean AS (
	SELECT id, score AS korean_score
	FROM score_long
	WHERE class_nm = 'korean'
), math AS (
	SELECT id, score AS math_score
	FROM score_long
	WHERE class_nm = 'math'
)
SELECT * FROM english 
LEFT JOIN korean USING(id)
LEFT JOIN math USING(id);

--id|english_score|korean_score|math_score|
----+-------------+------------+----------+
-- 1|          100|         100|       100|
-- 2|           75|          80|        85|
-- 3|           98|         100|        60|
-- 4|           75|          80|        98|

 

 

[ Reference ]

* PostgreSQL crosstab() function
   : https://www.postgresql.org/docs/9.4/tablefunc.html

* Pivot Tables in PostgreSQL using the Crosstab Function (by Maria Alcaraz)
   : https://learnsql.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/

* Apache MADlib pivot() function
   : https://madlib.apache.org/docs/v1.10/group__grp__pivot.html

* R reshape 패키지의 melt(), cast() 함수
   : https://rfriend.tistory.com/80

* R data.table 패키지의 melt(), cast() 함수
   : https://rfriend.tistory.com/576

* Python pandas 패키지의 pivot(), pivot_table() 함수 

   : https://rfriend.tistory.com/275

 

 

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요!  :-)