[PostgreSQL, Greenplum] 차원이 다른 Array를 2D Array로 aggregation 하기
Greenplum and PostgreSQL Database 2023. 3. 5. 21:53이번 포스팅에서는 PostgreSQL, Greenplum에서 차원이 다른 Array를 2D Array로 Aggregation 할 때, 그룹별 Array의 최대 길이를 계산해서 동적으로 최대길이에 모자란 부분만큼 '0'으로 채워서(padding) 차원을 동일하게 맞춘 후에 2D Array로 Aggregation 하는 방법을 소개하겠습니다.
(1) 가상의 시계열 데이터 생성
(2) 2D Array Aggregation 하는 사용자 정의 함수(UDF) 정의
(3) 차원이 다를 경우 2D Array Aggregation Error 발생
: cannot concatenate incompatible arrays
(4) 그룹별 Array의 차원을 일치시켜서 2D Array Aggregation 하는 방법
(1) 가상의 시계열 데이터 생성
먼저, 예제로 사용할 시계열 데이터로 이루어진 테이블을 만들어보겠습니다.
10의 ID 그룹이 있고, 각 ID별로 20개의 param_id 그룹이 있으며, 각 param_id별로 또 20개의 ts(TimeStamp)별로 측정값(measure_val)이 있는 시계열 데이터셋을 가진 테이블입니다.
이때 무작위로 생성한 난수가 0.5보다 작을 경우는 난수를 측정값으로 사용하고, 그렇지 않을 경우는 결측값(NULL)으로 해서 measure_val 칼럼을 만들어주었습니다.
-- (1) create sample dataset table
DROP TABLE IF EXISTS my_tbl;
CREATE TABLE my_tbl AS (
SELECT
d.*
-- about 50% NULL, 50% measured values
, CASE WHEN random() < 0.5 THEN round(random()::numeric, 2) ELSE NULL
END AS measure_val
FROM (
SELECT
a.* -- id
, b.* -- param_id
, c.* -- ts (TimeStamp)
FROM
(SELECT generate_series(1, 10) AS id) AS a
CROSS JOIN
(SELECT generate_series(1, 20) AS param_id) AS b
CROSS JOIN
(SELECT generate_series(1, 20) AS ts) AS c
) AS d
);
-- the measure_vals will be different due to random number generation
SELECT * FROM my_tbl ORDER BY 1, 2, 3 LIMIT 10;
--id|param_id|ts|measure_val|
----+--------+--+-----------+
-- 1| 1| 1| 0.93|
-- 1| 1| 2| |
-- 1| 1| 3| 0.87|
-- 1| 1| 4| 0.41|
-- 1| 1| 5| 0.57|
-- 1| 1| 6| |
-- 1| 1| 7| 0.60|
-- 1| 1| 8| 0.67|
-- 1| 1| 9| 0.02|
-- 1| 1|10| 0.21|
-- total number of rows: 10 * 20 * 20 = 4,000
SELECT count(1) AS row_cnt FROM my_tbl;
--row_cnt|
---------+
-- 4000|
PostgreSQL 에 있는 array_agg() 함수를 사용해서 id별, param_id별 그룹을 기준으로 param_id, ts, measure_val을 1D array로 aggregation 해보면 아래와 같습니다. 이때 조건절에 "WHERE measure_val IS NOT NULL"을 부여해서 결측값을 제거해주면 각 id, param_id 그룹별로 array 내 원소(element)의 개수가 서로 달라지게 됩니다. (<-- 이 부분이 나중에 (3)번에서 1D array를 2D array로 aggregation 할 때 1D array의 차원이 달라서 2D array로 묶을 수 없다는 에러를 발생시키게 됩니다.)
-- 1D array aggregation using array_agg() function
SELECT
id
, param_id
, array_agg(param_id) AS param_id_arr
, array_agg(ts ORDER BY ts) AS ts_arr
, array_agg(measure_val ORDER BY ts) AS measure_val_arr
FROM my_tbl
WHERE measure_val IS NOT NULL
GROUP BY 1, 2
ORDER BY 1, 2
LIMIT 5
(2) 2D Array Aggregation 하는 사용자 정의 함수(UDF) 정의
PostgreSQL에는 1D array를 2D array로 묶어주는 내장함수가 없으므로 아래와 같이 사용자 정의 함수(User Defined Function)를 만들고, Select 문으로 사용자 정의함수를 호출해서 사용하면 됩니다.
인풋으로 사용하는 데이터 유형별로 각각 사용자 정의함수를 정의해주는데요, 아래는 정수(Integer) 데이터 타입에 대한 1D array를 2D array로 묶어주는 사용자 정의함수입니다.
-- UDF for 2D array aggregation for INTEGER
DROP FUNCTION IF EXISTS array_append_2d_int(integer[][], integer[]) CASCADE;
CREATE OR REPLACE FUNCTION array_append_2d_int(integer[][], integer[])
RETURNS integer[][]
LANGUAGE SQL
AS 'select array_cat($1, ARRAY[$2])'
IMMUTABLE
;
DROP AGGREGATE IF EXISTS array_agg_array_int(integer[]) CASCADE;
CREATE ORDERED AGGREGATE array_agg_array_int(integer[])
(
SFUNC = array_append_2d_int
, STYPE = integer[][]
);
아래는 NUMERIC 데이터 유형을 원소로 가지는 1D array를 2D array 로 묶어주는 사용자 정의 함수입니다.
-- UDF for 2D array aggregation (NUMERIC)
DROP FUNCTION IF EXISTS array_append_2d_numeric(NUMERIC[][], NUMERIC[]) CASCADE;
CREATE OR REPLACE FUNCTION array_append_2d_numeric(NUMERIC[][], NUMERIC[])
RETURNS NUMERIC[][]
LANGUAGE SQL
AS 'select array_cat($1, ARRAY[$2])'
IMMUTABLE
;
DROP AGGREGATE IF EXISTS array_append_2d_numeric(NUMERIC[]) CASCADE;
CREATE ORDERED AGGREGATE array_append_2d_numeric(NUMERIC[])
(
SFUNC = array_append_2d_numeric
, STYPE = NUMERIC[][]
);
(3) 차원이 다를 경우 2D Array Aggregation Error 발생
: cannot concatenate incompatible arrays
아래의 SQL Query 처럼 각 Array의 원소(element) 개수가 다를 경우, 이를 2D Array로 합치려고(concatenation) 하면 Array의 차원이 일치하지 않는다는(incompatible) 에러가 발생합니다.
"SQL Error [2202E]: ERROR: cannot concatenate incompatible arrays (seg4 slice1 10.0.1.238:6004 pid=7073)
Detail: Arrays with differing element dimensions are not compatible for concatenation.
Where: SQL function "array_append_2d_numeric" statement 1"
--SQL Error [2202E]: ERROR: cannot concatenate incompatible arrays (seg0 slice1 10.0.1.238:6000 pid=30201)
-- Detail: Arrays with differing element dimensions are not compatible for concatenation.
-- Where: SQL function "array_append_2d_numeric" statement 1
SELECT
a.id
, array_agg_array_int(param_id_arr ORDER BY param_id) AS param_id_arr2d
, array_agg_array_int(ts_arr ORDER BY param_id) AS ts_arr2d
, array_agg_array_numeric(measure_val_arr ORDER BY param_id) AS measure_val_arr2d
FROM (
SELECT
id
, param_id
, array_agg(param_id) AS param_id_arr
, array_agg(ts ORDER BY ts) AS ts_arr
, array_agg(measure_val ORDER BY ts) AS measure_val_arr
FROM my_tbl
WHERE measure_val IS NOT NULL
GROUP BY 1, 2
) AS a
GROUP BY 1;
-- it eraises an ERROR: cannot concatenate incompatible arrays
(4) 그룹별 Array의 차원을 일치시켜서 2D Array Aggregation 하는 방법
1D array의 원소 개수가 서로 달라서 2D array 로 묶을 수 없을 경우(ERROR: cannot concatenate incompatible arrays), 아래의 절차를 따라서 각 그룹별 1D array의 차원을 일치시킨 후에 2D array로 묶을 수 있습니다.
(a) 묶고자 하는 기준이 되는 그룹별로 array_agg() 함수를 사용해서 1D array로 묶고,
(b) 그룹별로 1D array들의 각 원소 개수를 세어서 그룹별로 1D array의 최대 길이 (arr_max_length)를 계산하고,
(c) 그룹별로 1D array의 최대 길이 모자라는 개수만큼 '0'으로 각 1D Array를 채워서(padding) 차원을 일치시킨 후에,
(d) 그룹별로 1D array를 2D array로 (2)번에서 정의한 사용자 정의함수를 사용해서 묶어주기
-- Dynamic 2D array aggregation for arrays with NULL and different dimentions
DROP TABLE IF EXISTS my_tbl_2d_arr;
CREATE TABLE my_tbl_2d_arr AS (
WITH t AS (
SELECT
id
, param_id
, array_agg(param_id ORDER BY param_id, ts)
AS param_id_arr
, array_agg(ts ORDER BY param_id, ts)
AS ts_arr
, array_agg(measure_val ORDER BY param_id, ts)
AS measure_val_arr
FROM my_tbl
WHERE measure_val IS NOT NULL
GROUP BY 1, 2
), t2 AS (
SELECT
id
, max(array_length(measure_val_arr, 1))
AS arr_max_length
FROM t
GROUP BY 1
), t_padded AS (
SELECT
t.id
, t.param_id
, array_cat(
t.param_id_arr,
array_fill(NULL::INTEGER, ARRAY[arr_max_length
- COALESCE(array_length(t.param_id_arr, 1), 0)])
) AS param_id_arr_padded
, array_cat(
t.ts_arr,
array_fill(NULL::INTEGER, ARRAY[arr_max_length
- COALESCE(array_length(t.ts_arr, 1), 0)])
) AS ts_arr_padded
, array_cat(
t.measure_val_arr,
array_fill(NULL::NUMERIC, ARRAY[arr_max_length
- COALESCE(array_length(t.measure_val_arr, 1), 0)])
) AS measure_val_arr_padded
FROM t, t2
WHERE t.id = t2.id
)
SELECT
a.id
, array_agg_array_int(param_id_arr_padded ORDER BY param_id)
AS param_id_arr2d
, array_agg_array_int(ts_arr_padded ORDER BY param_id)
AS ts_arr2d
, array_agg_array_numeric(measure_val_arr_padded ORDER BY param_id)
AS measure_val_arr2d
FROM t_padded AS a
GROUP BY 1
);
SELECT * FROM my_tbl_2d_arr ORDER BY 1 LIMIT 5;
다음 포스팅에서는 그룹별로 묶어놓은 2D array를 그룹별 1D array 로 unnest 하는 방법(https://rfriend.tistory.com/728)를 참고하세요.
이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요! :-)
