[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 하기
하는 방법을 소개하겠습니다.
먼저, 예제로 사용할 간단한 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
이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요! :-)
