R, Python 분석과 프로그래밍의 친구 (by R Friend)

이번 포스팅에서는 PostgreSQL, Greenplum Database에서 문자열에 대한 함수 3가지를 소개하겠습니다. 

(1) STRING_AGG() : 개별 문자열을 그룹별로 하나의 문자열로 합치기

     (concatenate individual strings into a string by group) 

(2) STRING_TO_ARRAY() : 문자열을 구분자를 기준으로 나누어서 여러개의 원소를 가진 Array 로 만들기

      (splits a string into array elements using supplied delimiter)

(3) UNNEST(STRING_TO_ARRAY()) : 문자열 원소를 가진 ARRAY를 나누어서 개별 문자열 행으로 풀기

       (the opposite of STRING_AGG(), splits array elements and unnest it into multiple rows)

예제로 사용할 country 테이블을 만들어보겠습니다. 

---------------------------------------------------------------------------------------------
-- String functions and operators in PostgreSQL, Greenplum Database
-- string_agg(), string_to_array(), unnest(string_to_array())
----------------------------------------------------------------------------------------------

DROP TABLE IF EXISTS country;
CREATE TABLE country (
	continent  TEXT
	, country TEXT 
);
INSERT INTO country VALUES 
('Asia', 'Korea')
, ('Asia', 'China')
, ('Asia', 'Japan')
, ('Ameria', 'USA')
, ('Ameria', 'Canada')
, ('Ameria', 'Mexico')
, ('Europe', 'UK')
, ('Europe', 'Fance')
, ('Europe', 'Germany');


SELECT * FROM country ORDER BY 1, 2;
--continent|country|
-----------+-------+
--Ameria   |Canada |
--Ameria   |Mexico |
--Ameria   |USA    |
--Asia     |China  |
--Asia     |Japan  |
--Asia     |Korea  |
--Europe   |Fance  |
--Europe   |Germany|
--Europe   |UK     |

(1) STRING_AGG() : 개별 문자열을 그룹별로 하나의 문자열로 합치기

     (concatenate individual strings into a string by group) 

-- (1) string_agg()
-- : non-null input values concatenated into a string, separated by delimiter
-- syntax: string_agg(expression, delimiter [order by])
DROP TABLE IF EXISTS country_agg_tbl;
CREATE TABLE country_agg_tbl AS (
	SELECT 
		continent
		, STRING_AGG(country, ',') AS country_agg
	FROM country 
	GROUP BY continent
); 

SELECT * FROM country_agg_tbl ORDER BY 1, 2;
--continent|country_agg      |
-----------+-----------------+
--Ameria   |USA,Canada,Mexico|
--Asia     |Korea,China,Japan|
--Europe   |UK,Fance,Germany |

(2) STRING_TO_ARRAY() : 문자열을 구분자를 기준으로 나누어서 여러개의 원소를 가진 Array 로 만들기

      (splits a string into array elements using supplied delimiter)

-- (2) string_to_array()
-- : splits string into array elements using supplied delimiter and optional null string
-- syntax: string_to_array(text, text [, text])


SELECT 
	continent
	, STRING_TO_ARRAY(country_agg, ',') AS country_array
FROM country_agg_tbl
ORDER BY 1;

--continent|country_array      |
-----------+-------------------+
--Ameria   |{USA,Canada,Mexico}|
--Asia     |{Korea,China,Japan}|
--Europe   |{UK,Fance,Germany} |

옵션으로 세번째 매개변수 위치에 "NULL 처리할 문자열"을 지정할 수 있습니다. 아래 예에서는 'USA' 문자열에 대해서 NULL 처리하였습니다. 

-- NULL string optional
SELECT 
	continent
	, STRING_TO_ARRAY(
		country_agg -- string
		, ','       -- delimiter
		, 'USA' -- NULL string
		) AS country_array
FROM country_agg_tbl
ORDER BY 1;

--continent|country_array       |
-----------+--------------------+
--Ameria   |{NULL,Canada,Mexico}|   -- 'USA' --> NULL
--Asia     |{Korea,China,Japan} |
--Europe   |{UK,Fance,Germany}  |

(3) UNNEST(STRING_TO_ARRAY()) : 문자열 원소를 가진 ARRAY를 나누어서 개별 문자열 행으로 풀기

       (the opposite of STRING_AGG(), splits array elements and unnest it into multiple rows)

-- (3) unnest(string_to_array())
-- splits array elements and unnest it into multiple rows
-- the opposite of string_agg()

SELECT 
	continent
	, UNNEST(STRING_TO_ARRAY(country_agg, ',')) AS country
FROM country_agg_tbl
ORDER BY 1, 2;

--continent|country|
-----------+-------+
--Ameria   |Canada |
--Ameria   |Mexico |
--Ameria   |USA    |
--Asia     |China  |
--Asia     |Japan  |
--Asia     |Korea  |
--Europe   |Fance  |
--Europe   |Germany|
--Europe   |UK     |

[Reference]

* PostgreSQL's STRING_AGG() function
   : https://www.postgresql.org/docs/9.4/functions-aggregate.html 

* PostgreSQL's STRING_TO_ARRAY() function
   : https://www.postgresql.org/docs/9.1/functions-array.html

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

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