[PostgreSQL, Greenplum] 여러개의 칼럼을 행으로 변환하기(Transposing columns into rows), 여러개 칼럼을 가진 테이블을 세로로 긴 테이블로 변환하기 (Reshaping from wide into long format)
탐색적 데이터분석, 통계나 기계학습 모델링을 할 때의 데이터 형태를 보면 관측치의 식별자 ID(identifier) 별로 하나의 행(row)에 여러개의 특성정보(features)를 여러개의 칼럼(columns)으로 해서 옆으로 넓게 (horizontally wide format) 만든 데이터셋을 사용합니다.
그런데 Database의 Table 은 이와는 다르게, 보통 식별자 ID 별로 칼럼 이름(column name)과 측정값(measured value)을 여러개의 행(rows)으로 해서 세로로 길게 (vertically long format) 만든 테이블로 데이터를 관리합니다. Vertically Long Format 의 테이블이 새로 생성되는 데이터를 추가(insert into)하거나 삭제(delete from) 하기도 쉽고, 그룹별로 연산 (group by operation) 을 하기에도 쉽습니다. 그리고 API 서비스와 DB를 연계할 때도 세로로 긴 형태의 테이블이 사용됩니다.
(통계/기계학습을 하려고 할때는 DB로 부터 Query를 해서 Cross-tab 을 하여 horizontally wide format 의 DataFrame이나 Array 로 바꾸어서 이후 분석을 진행합니다.)
그럼, PostgreSQL, Greenplum DB에서 옆으로 넓은 형태의 테이블을 세로로 긴 형태의 테이블로 변형하는 2가지 방법을 소개하겠습니다. (Transposing columns into rows in PostgreSQL, Greenplum) (Reshaping horizontally wide-format into verticaly long-format table in PostgreSQL, Greenplum)
(1) UNION ALL 함수를 이용하여 여러개의 칼럼을 행으로 변환하기
(2) UNNEST() 함수를 이용하여 여러개의 칼럼을 행으로 변환하기
먼저, 예제로 사용한 간단한 테이블을 생성해 보겠습니다. 학생 ID 별로 국어, 영어, 수학, 과학, 역사, 체육 점수를 옆으로 넓은 형태(horizontally wide-format)로 저장해놓은 테이블입니다.
----------------------------------------------
-- Transposing columns into rows
-- (1) UNION ALL
-- (2) UNNEST()
----------------------------------------------
-- creating a sample table
DROP TABLE IF EXISTS score_wide;
CREATE TABLE score_wide (
id int NOT NULL
, korean_score int
, english_score int
, math_score int
, physics_score int
, history_score int
, athletics_score int
);
INSERT INTO score_wide VALUES
(1, 100, 100, 100, 100, 100, 90)
, (2, 80, 75, 85, 80, 60, 100)
, (3, 100, 98, 60, 55, 95, 85)
, (4, 80, 75, 98, 100, 85, 95);
SELECT * FROM score_wide ORDER BY id;
--id|korean_score|english_score|math_score|physics_score|history_score|athletics_score|
----+------------+-------------+----------+-------------+-------------+---------------+
-- 1| 100| 100| 100| 100| 100| 90|
-- 2| 80| 75| 85| 80| 60| 100|
-- 3| 100| 98| 60| 55| 95| 85|
-- 4| 80| 75| 98| 100| 85| 95|
(1) UNION ALL 함수를 이용하여 여러개의 칼럼을 행으로 변환하기
일반적으로 많이 알려져 있고, 또 실행 성능도 다음에 소개할 UNNEST() 보다 상대적으로 조금 더 좋습니다.
하지만 아래의 예제 코드를 보면 알 수 있는 것처럼, 칼럼의 개수가 여러개일 경우 코드가 길어지고 동일한 코드 항목 항목이 반복되어서 복잡해보이는 단점이 있습니다.
-- (1) using UNION ALL
DROP TABLE IF EXISTS score_long_union;
CREATE TABLE score_long_union AS (
SELECT id, 'korean' AS class_nm, korean_score AS score FROM score_wide
UNION ALL
SELECT id, 'english' AS class_nm, english_score AS score FROM score_wide
UNION ALL
SELECT id, 'math' AS class_nm, math_score AS score FROM score_wide
UNION ALL
SELECT id, 'physics' AS class_nm, physics_score AS score FROM score_wide
UNION ALL
SELECT id, 'history' AS class_nm, history_score AS score FROM score_wide
UNION ALL
SELECT id, 'athletics' AS class_nm, athletics_score AS score FROM score_wide
);
SELECT * FROM score_long_union ORDER BY id, class_nm;
--id|class_nm |score|
----+---------+-----+
-- 1|athletics| 90|
-- 1|english | 100|
-- 1|history | 100|
-- 1|korean | 100|
-- 1|math | 100|
-- 1|physics | 100|
-- 2|athletics| 100|
-- 2|english | 75|
-- 2|history | 60|
-- 2|korean | 80|
-- 2|math | 85|
-- 2|physics | 80|
-- 3|athletics| 85|
-- 3|english | 98|
-- 3|history | 95|
-- 3|korean | 100|
-- 3|math | 60|
-- 3|physics | 55|
-- 4|athletics| 95|
-- 4|english | 75|
-- 4|history | 85|
-- 4|korean | 80|
-- 4|math | 98|
-- 4|physics | 100|
(2) UNNEST() 함수를 이용하여 여러개의 칼럼을 행으로 변환하기
다음으로, UNNEST() 함수를 사용하는 방법은 위의 UNION ALL 대비 코드가 한결 간결해서 가독성이 좋습니다.
반면에, UNNEST() 함수를 사용하면 연산이 된 후의 ARRAY 에 대해서는 INDEX가 지원이 안되다보니 위의 UNION ALL 대비 상대적으로 실행 성능이 떨어지는 단점이 있습니다.(UNION ALL 방법이 UNNEST() 방법보다 약 2배 정도 성능이 빠름.)
-- (2) using UNNEST(ARRAY)
DROP TABLE IF EXISTS score_long_unnest;
CREATE TABLE score_long_unnest AS (
SELECT
id
, UNNEST(ARRAY['korean', 'english', 'math',
'physics', 'history', 'athletics'])
AS class_nm
, UNNEST(ARRAY[korean_score, english_score, math_score,
physics_score, history_score, athletics_score])
AS score
FROM score_wide
);
SELECT * FROM score_long_unnest ORDER BY id, class_nm;
--id|class_nm |score|
----+---------+-----+
-- 1|athletics| 90|
-- 1|english | 100|
-- 1|history | 100|
-- 1|korean | 100|
-- 1|math | 100|
-- 1|physics | 100|
-- 2|athletics| 100|
-- 2|english | 75|
-- 2|history | 60|
-- 2|korean | 80|
-- 2|math | 85|
-- 2|physics | 80|
-- 3|athletics| 85|
-- 3|english | 98|
-- 3|history | 95|
-- 3|korean | 100|
-- 3|math | 60|
-- 3|physics | 55|
-- 4|athletics| 95|
-- 4|english | 75|
-- 4|history | 85|
-- 4|korean | 80|
-- 4|math | 98|
-- 4|physics | 100|
다음번 포스팅에서는 이번 포스팅과는 반대로, PostgreSQL, Greenplum에서 세로로 긴 테이블을 pivot 하여 옆으로 넓은 테이블로 변환하는 방법을 소개하겠습니다. (https://rfriend.tistory.com/714)
이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요! :-)
