有人能解释一下arrayJoin()函数在Clickhouse数据库中是如何工作的吗

Question

我是ClickHouse DB的新手，文档中提供的示例无助于正确理解这个概念。请解释arrayjoin((如何用于简单场景。

Answer 1

让我们考虑下一个场景：

1. 需要将数组转换为关系时(行集(

/* 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;

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 │
└───┘
*/

---

当您需要逐项链接数组而不是获得笛卡尔乘积时，请考虑使用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] │
└────┴────┴───────┴─────────┘
*/