clickhouse ARRAY JOIN函数

关注微信公共号：小程在线

关注CSDN博客：程志伟的博客

dblab-VirtualBox :) CREATE TABLE arrays_test
:-] (
:-]     s String,
:-]     arr Array(UInt8)
:-] ) ENGINE = Memory;

CREATE TABLE arrays_test
(
    `s` String,
    `arr` Array(UInt8)
)
ENGINE = Memory

Query id: 6294f6b5-de74-4952-8e9c-40ecfc515190

Ok.

0 rows in set. Elapsed: 0.003 sec. 

dblab-VirtualBox :) INSERT INTO arrays_test
:-] VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);

INSERT INTO arrays_test VALUES

Query id: 2aedf3e2-f638-4c35-88c9-ffc0bd3846d1

Ok.

3 rows in set. Elapsed: 0.004 sec. 

dblab-VirtualBox :) select * from arrays_test;

SELECT *
FROM arrays_test

Query id: 6fa1d3cc-fabd-4e92-a1db-424e8a3e8f01

┌─s───────┬─arr─────┐
│ Hello   │ [1,2]   │
│ World   │ [3,4,5] │
│ Goodbye │ []      │
└─────────┴─────────┘

3 rows in set. Elapsed: 0.008 sec.

使用ARRAY JOIN函数，和oracle、mysql中的行转列函数差不多

dblab-VirtualBox :) SELECT s, arr
:-] FROM arrays_test
:-] ARRAY JOIN arr;

SELECT
    s,
    arr
FROM arrays_test
ARRAY JOIN arr

使用LEFT ARRAY JOIN函数，以左边数据为准，空值补充为0。

dblab-VirtualBox :) SELECT s, arr
:-] FROM arrays_test
:-] LEFT ARRAY JOIN arr;

SELECT
    s,
    arr
FROM arrays_test
LEFT ARRAY JOIN arr

 可以为ARRAY JOIN子句中的数组指定别名。

dblab-VirtualBox :) SELECT s, arr, a
:-] FROM arrays_test
:-] ARRAY JOIN arr AS a;

SELECT
    s,
    arr,
    a
FROM arrays_test
ARRAY JOIN arr AS a

Query id: 7c1f2977-8cbc-4809-b898-e9d1e85d03d0

使用别名，您可以ARRAY JOIN使用外部数组执行

dblab-VirtualBox :) SELECT s, arr_external
:-] FROM arrays_test
:-] ARRAY JOIN [1, 2, 3] AS arr_external;

SELECT
    s,
    arr_external
FROM arrays_test
ARRAY JOIN [1, 2, 3] AS arr_external

Query id: 3cadf87f-98e6-4b8b-81fb-1fd93ebe7236

arrayEnumerate函数：相当于row_number()

arrayMap函数：（表达式，参数）

dblab-VirtualBox :) SELECT s, arr, a, num, mapped　FROM arrays_test　ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 1, arr) AS mapped

SELECT
    s,
    arr,
    a,
    num,
    mapped
FROM arrays_test
ARRAY JOIN
    arr AS a,
    arrayEnumerate(arr) AS num,
    arrayMap(x -> (x + 1), arr) AS mapped

Query id: 8ed8da75-b014-4c13-bdb9-d20976930221