让我们考虑以下场景：
- 当需要转型时Array to a relation（行集）
/* get error */
SELECT 1
WHERE 1 IN ([1, 2]);
/* ok */
SELECT 1
WHERE 1 IN (SELECT arrayJoin([1, 2]));
/* get error */
SELECT *
FROM (SELECT [1, 2] a)
WHERE a = 2;
/* ok */
SELECT *
FROM (SELECT arrayJoin([1, 2]) a)
WHERE a = 2;
- 展开/展平行
SELECT
metric_id,
metric_name,
arrayJoin(metric_values) AS metric_value
FROM
( /* test data */
SELECT
1 AS metric_id,
'name_1' AS metric_name,
[1, 4, 55] AS metric_values
UNION ALL
SELECT
2 AS metric_id,
'name_2' AS metric_name,
[-7, 11] AS metric_values
)
/* result
┌─metric_id─┬─metric_name─┬─metric_value─┐
│ 1 │ name_1 │ 1 │
│ 1 │ name_1 │ 4 │
│ 1 │ name_1 │ 55 │
│ 2 │ name_2 │ -7 │
│ 2 │ name_2 │ 11 │
└───────────┴─────────────┴──────────────┘
*/
/* produce Cartesian product */
SELECT
arrayJoin([1, 2]) AS n,
arrayJoin(['a', 'b']) AS ll,
arrayJoin(['A', 'B']) AS ul
/* result
┌─n─┬─ll─┬─ul─┐
│ 1 │ a │ A │
│ 1 │ a │ B │
│ 1 │ b │ A │
│ 1 │ b │ B │
│ 2 │ a │ A │
│ 2 │ a │ B │
│ 2 │ b │ A │
│ 2 │ b │ B │
└───┴────┴────┘
*/
/* flatten the multidimension array */
SELECT arrayJoin(arrayJoin([[1, 2], [3, 4]])) AS d
/* result
┌─d─┐
│ 1 │
│ 2 │
│ 3 │
│ 4 │
└───┘
*/
当您需要逐项链接数组而不是获取笛卡尔积时，请考虑使用数组连接 https://clickhouse.tech/docs/en/sql-reference/statements/select/array-join/:
/* cartesian product */
SELECT
arrayJoin(arr1),
arrayJoin(arr2)
FROM
(
SELECT
[1, 2] AS arr1,
[11, 22] AS arr2
)
/*
┌─arrayJoin(arr1)─┬─arrayJoin(arr2)─┐
│ 1 │ 11 │
│ 1 │ 22 │
│ 2 │ 11 │
│ 2 │ 22 │
└─────────────────┴─────────────────┘
*/
/* connect array's item one by one */
SELECT a1, a2, arr1, arr2
FROM
(
SELECT
[1, 2] AS arr1,
[11, 22] AS arr2
)
ARRAY JOIN arr1 as a1, arr2 as a2
/*
┌─a1─┬─a2─┬─arr1──┬─arr2────┐
│ 1 │ 11 │ [1,2] │ [11,22] │
│ 2 │ 22 │ [1,2] │ [11,22] │
└────┴────┴───────┴─────────┘
*/
