ARRAY JOIN | ClickHouse Documentation

ARRAY JOIN 子句允许在数据表的内部，与数组或嵌套类型的字段进行 JOIN 操作，从而将一行数组展开为多行。接下来让我们看看它的基础用法。

-- 首先新建一张包含 Array 数组字段的测试表
CREATE TABLE query_v1
(
    title String,
    value Array(UInt8)
) ENGINE = Log;

-- 接着写入测试数据
INSERT INTO query_v1 VALUES ('food', [1,2,3]), ('fruit', [3,4]), ('meat', []);

SELECT title,value FROM query_v1;
┌─title──┬─value──────┐
│ food   │ [1,2,3]    │
│ fruit  │ [3,4]      │
│ meat   │ []         │
└────────┴────────────┘

INNER & LEFT ARRAY JOIN

在一条 SELECT 语句中，只能存在一个 ARRAY JOIN（使用子查询除外）。目前支持 INNER 和 LEFT 两种 JOIN 策略：

1. INNER ARRAY JOIN

   ARRAY JOIN 在默认情况下使用的是 INNER JOIN 策略，例如下面的语句：

   SELECT title,value FROM query_v1 ARRAY JOIN value
   ┌─title──┬───value──┐
   │ food   │     1    │
   │ food   │     2    │
   │ food   │     3    │
   │ fruit  │     3    │
   │ fruit  │     4    │
   └────────┴──────────┘
   

   从查询结果可以发现，最终的数据基于 value 数组被展开成了多行，并且排除掉了空数组。在使用 ARRAY JOIN 时，如果为原有的数组字段添加一个别名，则能够访问展开前的数组字段，例如：

   SELECT title,value,**v** FROM query_v1 **ARRAY JOIN value AS v**
   ┌─title──┬─value────┬─v──┐
   │ food   │ [1,2,3]  │ 1  │
   │ food   │ [1,2,3]  │ 2  │
   │ food   │ [1,2,3]  │ 3  │
   │ fruit  │ [3,4]    │ 3  │
   │ fruit  │ [3,4]    │ 4  │
   └────────┴──────────┴────┘
   

2. LEFT ARRAY JOIN

   ARRAY JOIN 子句支持 LEFT 连接策略，例如执行下面的语句：

   SELECT title,value,v FROM query_v1 **LEFT ARRAY JOIN value AS v**
   ┌─title──┬─value────┬─v──┐
   │ food   │ [1,2,3]  │ 1  │
   │ food   │ [1,2,3]  │ 2  │
   │ food   │ [1,2,3]  │ 3  │
   │ fruit  │ [3,4]    │ 3  │
   │ fruit  │ [3,4]    │ 4  │
   │ meat   │ []       │ 0  │
   └────────┴──────────┴────┘
   

   在改为 LEFT 连接查询后，可以发现，在 INNER JOIN 中被排除掉的空数组出现在了返回的结果集中。

多个数组进行 ARRAY JOIN

当同时对多个数组字段进行 ARRAY JOIN 操作时，查询的计算逻辑是按行合并而不是产生笛卡儿积，例如下面的语句：

-- ARRAY JOIN 多个数组时，是合并，不是笛卡儿积
SELECT 
	title,
	value,
	v,
	arrayMap(x -> x * 2,value) as mapv,
	v_1 
FROM query_v1 
LEFT ARRAY JOIN value AS v , mapv as v_1;

┌─title─┬──value──┬─v─┬─mapv────┬─v_1─┐
│ food  │ [1,2,3] │ 1 │ [2,4,6] │ 2   │
│ food  │ [1,2,3] │ 2 │ [2,4,6] │ 4   │
│ food  │ [1,2,3] │ 3 │ [2,4,6] │ 6   │
│ fruit │ [3,4]   │ 3 │ [6,8]   │ 6   │
│ fruit │ [3,4]   │ 4 │ [6,8]   │ 8   │
│ meat  │ []      │ 0 │ []      │ 0   │
└───────┴─────────┴───┴─────────┴─────┘

value 和 mapv 数组是按行合并的，并没有产生笛卡儿积。

对嵌套数据进行 ARRAY JOIN

在前面介绍数据定义时曾介绍过，嵌套数据类型的本质是数组，所以 ARRAY JOIN 也支持嵌套数据类型。接下来继续用一组示例说明。

-- ARRAY JOIN 嵌套类型
CREATE TABLE query_v2
(
	title String,
	nest Nested(
		v1 UInt32,
		v2 UInt64)
) ENGINE = Log

-- 接着写入测试数据，在写入嵌套数据类型时，记得同一行数据中各个数组的长度需要对齐，而对多行数据之间的数组长度没有限制
INSERT INTO query_v2 VALUES ('food', [1,2,3], [10,20,30]), ('fruit', [4,5], [40,50]), ('meat', [], [])

SELECT title, nest.v1, nest.v2 FROM query_v2

┌─title─┬─nest.v1─┬─nest.v2───┐
│ food  │ [1,2,3] │ [10,20,30]│
│ fruit │ [4,5]   │ [40,50]   │
│ meat  │ []      │ []        │
└───────┴─────────┴───────────┘