[PostgreSQL, Greenplum] 2D array를 1D array로 unnest 하는 방법 (how to unnest 2D array into 1D array in PostgreSQL)

이번 포스팅에서는 PostgreSQL, Greenplum 에서 Apahe MADlib 의 함수를 사용하여 

(1) 2D array 를 1D array 로 unnest 하기

    (Unnest 2D array into 1D array in PostgreSQL using madlib.array_unnest_2d_to_1d() function)

(2) 1D array 에서 순서대로 원소 값을 indexing 하기

하는 방법을 소개하겠습니다. 

 

 

how to unnest 2D array into 1D array and indexing in PostgreSQL, Greenplum DB

 

먼저, 예제로 사용할 간단한 2D array를 포함하는 테이블을 만들어 보겠습니다. 

 

--------------------------------------------------------------------------------
-- How to unnest a 2D array into a 1D array in PostgreSQL?
-- [reference] http://madlib.incubator.apache.org/docs/latest/array__ops_8sql__in.html#af057b589f2a2cb1095caa99feaeb3d70
--------------------------------------------------------------------------------

-- Creating a sample 2D array table
DROP TABLE IF EXISTS mat_2d_arr;
CREATE TABLE mat_2d_arr (id int, var_2d int[]);
INSERT INTO mat_2d_arr VALUES 
(1,  '{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'),
(2,  '{{10, 11, 12}, {13, 14, 15}, {16, 17, 18}}'),
(3,  '{{19, 20, 21}, {22, 23, 24}, {25, 26, 27}}'), 
(4,  '{{28, 29, 30}, {31, 32, 33}, {34, 35, 36}}');


SELECT * FROM mat_2d_arr ORDER BY id;

--id|var_2d                            |
----+----------------------------------+
-- 1|{{1,2,3},{4,5,6},{7,8,9}}         |
-- 2|{{10,11,12},{13,14,15},{16,17,18}}|
-- 3|{{19,20,21},{22,23,24},{25,26,27}}|
-- 4|{{28,29,30},{31,32,33},{34,35,36}}|

 

 

(1) 2D array 를 1D array 로 unnest 하기

    (Unnest 2D array into 1D array in PostgreSQL using madlib.array_unnest_2d_to_1d() function)

 

Apache MADlib 의 madlib.array_unnest_2d_to_1d() 함수를 사용하면 쉽게 PostgreSQL, Greenplum의 2D array를 1D array 로 unnest 할 수 있습니다. madlib.array_unnest_2d_to_1d() 함수는 'unnest_row_id' 와 'unnest_result' 의 2개 칼럼을 반환하므로, 이들 "2개 칼럼 모두"를 반환하라는 의미로 (madlib.array_unnest_2d_to_1d(var_2d)).* 함수의 처음과 끝부분에 괄호 ()로 묶고 마지막에 아스타리스크(.*) 부호를 붙여주었습니다. ().* 를 빼먹지 않도록 주의하세요. 

 

MADlib 함수를 사용하지 않는다면 직접 PL/SQL 사용자 정의 함수나 또는 PL/Python 이나 PL/R 사용자 정의 함수를 정의하고 실행해야 하는데요, 좀 번거롭고 어렵습니다. 

 

-- (1) Unnest 2D array into a 1D array using madlib.array_unnest_2d_to_1d() function
SELECT 
	id
	, (madlib.array_unnest_2d_to_1d(var_2d)).* 
FROM mat_2d_arr 
ORDER BY id, unnest_row_id;

--id|unnest_row_id|unnest_result|
----+-------------+-------------+
-- 1|            1|{1,2,3}      |
-- 1|            2|{4,5,6}      |
-- 1|            3|{7,8,9}      |
-- 2|            1|{10,11,12}   |
-- 2|            2|{13,14,15}   |
-- 2|            3|{16,17,18}   |
-- 3|            1|{19,20,21}   |
-- 3|            2|{22,23,24}   |
-- 3|            3|{25,26,27}   |
-- 4|            1|{28,29,30}   |
-- 4|            2|{31,32,33}   |
-- 4|            3|{34,35,36}   |

 

 

 

아래는 2D array를 1D array로 unnest 하는 사용자 정의함수(UDF) 를 plpgsql 로 정의해서 SQL query 로 호출해서 사용하는 방법입니다. 

 

-- UDF for unnest 2D array into 1D array
CREATE OR REPLACE FUNCTION unnest_2d_1d(ANYARRAY, OUT a ANYARRAY)
  RETURNS SETOF ANYARRAY
  LANGUAGE plpgsql IMMUTABLE STRICT AS
$func$
BEGIN
   FOREACH a SLICE 1 IN ARRAY $1 LOOP
      RETURN NEXT;
   END LOOP;
END
$func$;



-- Unnest 2D array into 1D array using the UDF above
SELECT 
	id 
	, unnest_2d_1d(var_2d) AS var_1d
FROM mat_2d_arr
ORDER BY 1, 2;

--id|var_1d    |
----+----------+
-- 1|{1,2,3}   |
-- 1|{4,5,6}   |
-- 1|{7,8,9}   |
-- 2|{10,11,12}|
-- 2|{13,14,15}|
-- 2|{16,17,18}|
-- 3|{19,20,21}|
-- 3|{22,23,24}|
-- 3|{25,26,27}|
-- 4|{28,29,30}|
-- 4|{31,32,33}|
-- 4|{34,35,36}|

 

 

 

 

(2) 1D array 에서 순서대로 원소 값을 indexing 하기

 

일단 2D array를 1D array 로 unnest 하고 나면, 그 다음에 1D array에서 순서대로 각 원소를 inndexing 해오는 것은 기본 SQL 구문을 사용하면 됩니다. 1D array 안에 각 3개의 원소들이 들어 있으므로, 순서대로 unnest_result[1], unnest_result[2], unnest_result[3] 으로 해서 indexing 을 해오면 아래 예제와 같습니다. 

 

-- (2) Indexing an unnested 1D array
SELECT 
	a.id 
	, unnest_row_id
	, unnest_result[1] AS x1
	, unnest_result[2] AS x2
	, unnest_result[3] AS x3
FROM (
	SELECT 
		id
		, (madlib.array_unnest_2d_to_1d(var_2d)).* 
	FROM mat_2d_arr 
) AS a
ORDER BY id, unnest_row_id;

--id|unnest_row_id|x1|x2|x3|
----+-------------+--+--+--+
-- 1|            1| 1| 2| 3|
-- 1|            2| 4| 5| 6|
-- 1|            3| 7| 8| 9|
-- 2|            1|10|11|12|
-- 2|            2|13|14|15|
-- 2|            3|16|17|18|
-- 3|            1|19|20|21|
-- 3|            2|22|23|24|
-- 3|            3|25|26|27|
-- 4|            1|28|29|30|
-- 4|            2|31|32|33|
-- 4|            3|34|35|36|

 

 

 

[Reference]

- Apache MADlib's madlib.array_unnest_2d_to_1d() function: http://madlib.incubator.apache.org/docs/latest/array__ops_8sql__in.html#af057b589f2a2cb1095caa99feaeb3d70

 

 

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

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