ARRAY_JOIN
説明
配列内のすべての要素を、区切り文字（sep）とNULL値を置換する文字列（null_replace）に基づいて新しい文字列に結合します。
構文
ARRAY_JOIN(<arr> , <sep> [, <null_replace>])
パラメータ
| Parameter | デスクリプション |
|---|---|
<arr> | 結合する配列 |
<sep> | 区切り文字列 |
<null_replace> | NULL値を置き換える文字列 |
戻り値
以下の特別なケースを持つ新しい文字列を返します：
<sep>がNULLの場合、関数はNULLを返します。<null_replace>がNULLの場合、関数もNULLを返します。<sep>が空文字列の場合、区切り文字は適用されません。<null_replace>が空文字列または指定されていない場合、配列内のNULL要素は直接破棄されます。
例
CREATE TABLE array_test (
k1 INT,
k2 ARRAY<INT>
)
duplicate key (k1)
distributed by hash(k1) buckets 1
properties(
'replication_num' = '1'
);
INSERT INTO array_test VALUES
(1, [1, 2, 3, 4, 5]),
(2, [6, 7, 8]),
(3, []),
(4, NULL),
(5, [1, 2, 3, 4, 5, 4, 3, 2, 1]),
(6, [1, 2, 3, NULL]),
(7, [4, 5, 6, NULL, NULL]);
select k1, k2, array_join(k2, '_', 'null') from array_test order by k1;
+------+-----------------------------+------------------------------------+
| k1 | k2 | array_join(`k2`, '_', 'null') |
+------+-----------------------------+------------------------------------+
| 1 | [1, 2, 3, 4, 5] | 1_2_3_4_5 |
| 2 | [6, 7, 8] | 6_7_8 |
| 3 | [] | |
| 4 | NULL | NULL |
| 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1 |
| 6 | [1, 2, 3, NULL] | 1_2_3_null |
| 7 | [4, 5, 6, NULL, NULL] | 4_5_6_null_null |
+------+-----------------------------+------------------------------------+
select k1, k2, array_join(k2, '_') from array_test order by k1;
+------+-----------------------------+----------------------------+
| k1 | k2 | array_join(`k2`, '_') |
+------+-----------------------------+----------------------------+
| 1 | [1, 2, 3, 4, 5] | 1_2_3_4_5 |
| 2 | [6, 7, 8] | 6_7_8 |
| 3 | [] | |
| 4 | NULL | NULL |
| 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1 |
| 6 | [1, 2, 3, NULL] | 1_2_3 |
| 7 | [4, 5, 6, NULL, NULL] | 4_5_6 |
+------+-----------------------------+----------------------------+
CREATE TABLE array_test01 (
k1 INT,
k2 ARRAY<STRING>
)
duplicate key (k1)
distributed by hash(k1) buckets 1
properties(
'replication_num' = '1'
);
INSERT INTO array_test01 VALUES
(1, ['a', 'b', 'c', 'd']),
(2, ['e', 'f', 'g', 'h']),
(3, [NULL, 'a', NULL, 'b', NULL, 'c']),
(4, ['d', 'e', NULL, ' ']),
(5, [' ', NULL, 'f', 'g']);
select k1, k2, array_join(k2, '_', 'null') from array_test01 order by k1;
+------+-----------------------------------+------------------------------------+
| k1 | k2 | array_join(`k2`, '_', 'null') |
+------+-----------------------------------+------------------------------------+
| 1 | ['a', 'b', 'c', 'd'] | a_b_c_d |
| 2 | ['e', 'f', 'g', 'h'] | e_f_g_h |
| 3 | [NULL, 'a', NULL, 'b', NULL, 'c'] | null_a_null_b_null_c |
| 4 | ['d', 'e', NULL, ' '] | d_e_null_ |
| 5 | [' ', NULL, 'f', 'g'] | _null_f_g |
+------+-----------------------------------+------------------------------------+
select k1, k2, array_join(k2, '_') from array_test01 order by k1;
+------+-----------------------------------+----------------------------+
| k1 | k2 | array_join(`k2`, '_') |
+------+-----------------------------------+----------------------------+
| 1 | ['a', 'b', 'c', 'd'] | a_b_c_d |
| 2 | ['e', 'f', 'g', 'h'] | e_f_g_h |
| 3 | [NULL, 'a', NULL, 'b', NULL, 'c'] | a_b_c |
| 4 | ['d', 'e', NULL, ' '] | d_e_ |
| 5 | [' ', NULL, 'f', 'g'] | _f_g |
+------+-----------------------------------+----------------------------+