sql - 按clickhouse中两个数组的元素联合

Question

我有以下两列（浮动：

+-------------+-------------+
|col1         |col2         |
+-------------+-------------+
|[0.5,0.6]    |[900,1000]    |
|[0.7,0.8,0.9]|[1100,1200,1300]|
+-------------+-------------+

我想要以下输出：

+-------------+-------------   +----------------------------+
|col1         |col2            |col3                        |
+-------------+-------------   +----------------------------+
|[0.5,0.6]    |[900,1000]      |[0.5 900 , 0.6 1000]        | 
|[0.7,0.8,0.9]|[1100,1200,1300]|[0.7 1100,0.8 1200,0.9 1300]|
+-------------+----------- ----+----------------------------+

我尝试过 ArrayZip，但出现异常“无法从 JDBC 数组中提取数组数据”

谢谢您的帮助。

标签： sqlclickhouse

Answer 1

JDBC驱动不支持Array(Tuple(String)等复杂数据类型，可以在CH中转换成字符串。

SELECT
    [0.5, 0.6] AS col1,
    [900, 1000] AS col2,
    arrayMap((i, j) -> concat(toString(i), ' ', toString(j)), col1, col2) AS r

┌─col1──────┬─col2───────┬─r──────────────────────┐
│ [0.5,0.6] │ [900,1000] │ ['0.5 900','0.6 1000'] │
└───────────┴────────────┴────────────────────────┘


SELECT
    [0.5, 0.6] AS col1,
    [900, 1000] AS col2,
    toString(arrayMap((i, j) -> concat(toString(i), ' ', toString(j)), col1, col2)) AS r

┌─col1──────┬─col2───────┬─r──────────────────────┐
│ [0.5,0.6] │ [900,1000] │ ['0.5 900','0.6 1000'] │
└───────────┴────────────┴────────────────────────┘


SELECT
    [0.5, 0.6] AS col1,
    [900, 1000] AS col2,
    concat('[', arrayStringConcat(arrayMap((i, j) -> concat(toString(i), ' ', toString(j)), col1, col2), ', '), ']') AS r

┌─col1──────┬─col2───────┬─r───────────────────┐
│ [0.5,0.6] │ [900,1000] │ [0.5 900, 0.6 1000] │
└───────────┴────────────┴─────────────────────┘


SELECT
    [0.5, 0.6] AS col1,
    [900, 1000] AS col2,
    arrayZip(col1, col2)

Query id: 8a7aa26b-71f7-4544-b5c7-79e47f1c6453

┌─col1──────┬─col2───────┬─arrayZip([0.5, 0.6], [900, 1000])─┐
│ [0.5,0.6] │ [900,1000] │ [(0.5,900),(0.6,1000)]            │
└───────────┴────────────┴───────────────────────────────────┘


SELECT
    [0.5, 0.6] AS col1,
    [900, 1000] AS col2,
    toString(arrayZip(col1, col2))

┌─col1──────┬─col2───────┬─toString(arrayZip([0.5, 0.6], [900, 1000]))─┐
│ [0.5,0.6] │ [900,1000] │ [(0.5,900),(0.6,1000)]                      │
└───────────┴────────────┴─────────────────────────────────────────────┘