ARRAY JOIN子句

对于包含数组列的表来说是一种常见的操作，用于生成一个新表，该表具有包含该初始列中的每个单独数组元素的列，而其他列的值将被重复显示。 这是 ARRAY JOIN 语句最基本的场景。

它可以被视为执行 JOIN 并具有数组或嵌套数据结构。 类似于 arrayJoin 功能，但该子句功能更广泛。

语法:

  1. SELECT <expr_list>
  2. FROM <left_subquery>
  3. [LEFT] ARRAY JOIN <array>
  4. [WHERE|PREWHERE <expr>]
  5. ...

您只能在 SELECT 查询指定一个 ARRAY JOIN 。

ARRAY JOIN 支持的类型有:

  • ARRAY JOIN - 一般情况下，空数组不包括在结果中 JOIN.
  • LEFT ARRAY JOIN - 的结果 JOIN 包含具有空数组的行。 空数组的值设置为数组元素类型的默认值（通常为0、空字符串或NULL）。

基本 ARRAY JOIN 示例

下面的例子展示 ARRAY JOIN 和 LEFT ARRAY JOIN 的用法，让我们创建一个表包含一个 Array 的列并插入值:

  1. CREATE TABLE arrays_test
  2. (
  3.     s String,
  4.     arr Array(UInt8)
  5. ) ENGINE = Memory;
  7. INSERT INTO arrays_test
  8. VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);
  1. ┌─s───────────┬─arr─────┐
  2. │ Hello       │ [1,2]   │
  3. │ World       │ [3,4,5] │
  4. │ Goodbye     │ []      │
  5. └─────────────┴─────────┘

下面的例子使用 ARRAY JOIN 子句:

  1. SELECT s, arr
  2. FROM arrays_test
  3. ARRAY JOIN arr;
  1. ┌─s─────┬─arr─┐
  2. │ Hello │   1 │
  3. │ Hello │   2 │
  4. │ World │   3 │
  5. │ World │   4 │
  6. │ World │   5 │
  7. └───────┴─────┘

下一个示例使用 LEFT ARRAY JOIN 子句:

  1. SELECT s, arr
  2. FROM arrays_test
  3. LEFT ARRAY JOIN arr;
  1. ┌─s───────────┬─arr─┐
  2. │ Hello       │   1 │
  3. │ Hello       │   2 │
  4. │ World       │   3 │
  5. │ World       │   4 │
  6. │ World       │   5 │
  7. │ Goodbye     │   0 │
  8. └─────────────┴─────┘

使用别名

在使用ARRAY JOIN 时可以为数组指定别名，数组元素可以通过此别名访问，但数组本身则通过原始名称访问。 示例:

  1. SELECT s, arr, a
  2. FROM arrays_test
  3. ARRAY JOIN arr AS a;
  1. ┌─s─────┬─arr─────┬─a─┐
  2. │ Hello │ [1,2]   │ 1 │
  3. │ Hello │ [1,2]   │ 2 │
  4. │ World │ [3,4,5] │ 3 │
  5. │ World │ [3,4,5] │ 4 │
  6. │ World │ [3,4,5] │ 5 │
  7. └───────┴─────────┴───┘

可以使用别名与外部数组执行 ARRAY JOIN 。 例如:

  1. SELECT s, arr_external
  2. FROM arrays_test
  3. ARRAY JOIN [1, 2, 3] AS arr_external;
  1. ┌─s───────────┬─arr_external─┐
  2. │ Hello       │            1 │
  3. │ Hello       │            2 │
  4. │ Hello       │            3 │
  5. │ World       │            1 │
  6. │ World       │            2 │
  7. │ World       │            3 │
  8. │ Goodbye     │            1 │
  9. │ Goodbye     │            2 │
  10. │ Goodbye     │            3 │
  11. └─────────────┴──────────────┘

在 ARRAY JOIN 中，多个数组可以用逗号分隔, 在这例子中 JOIN 与它们同时执行（直接sum，而不是笛卡尔积）。 请注意，所有数组必须具有相同的大小。 示例:

  1. SELECT s, arr, a, num, mapped
  2. FROM arrays_test
  3. ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 1, arr) AS mapped;
  1. ┌─s─────┬─arr─────┬─a─┬─num─┬─mapped─┐
  2. │ Hello │ [1,2]   │ 1 │   1 │      2 │
  3. │ Hello │ [1,2]   │ 2 │   2 │      3 │
  4. │ World │ [3,4,5] │ 3 │   1 │      4 │
  5. │ World │ [3,4,5] │ 4 │   2 │      5 │
  6. │ World │ [3,4,5] │ 5 │   3 │      6 │
  7. └───────┴─────────┴───┴─────┴────────┘

下面的例子使用 arrayEnumerate 功能:

  1. SELECT s, arr, a, num, arrayEnumerate(arr)
  2. FROM arrays_test
  3. ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num;
  1. ┌─s─────┬─arr─────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
  2. │ Hello │ [1,2]   │ 1 │   1 │ [1,2]               │
  3. │ Hello │ [1,2]   │ 2 │   2 │ [1,2]               │
  4. │ World │ [3,4,5] │ 3 │   1 │ [1,2,3]             │
  5. │ World │ [3,4,5] │ 4 │   2 │ [1,2,3]             │
  6. │ World │ [3,4,5] │ 5 │   3 │ [1,2,3]             │
  7. └───────┴─────────┴───┴─────┴─────────────────────┘

具有嵌套数据结构的数组连接

ARRAY JOIN 也适用于 嵌套数据结构:

  1. CREATE TABLE nested_test
  2. (
  3.     s String,
  4.     nest Nested(
  5.     x UInt8,
  6.     y UInt32)
  7. ) ENGINE = Memory;
  9. INSERT INTO nested_test
  10. VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], []);
  1. ┌─s───────┬─nest.x──┬─nest.y─────┐
  2. │ Hello   │ [1,2]   │ [10,20]    │
  3. │ World   │ [3,4,5] │ [30,40,50] │
  4. │ Goodbye │ []      │ []         │
  5. └─────────┴─────────┴────────────┘
  1. SELECT s, `nest.x`, `nest.y`
  2. FROM nested_test
  3. ARRAY JOIN nest;
  1. ┌─s─────┬─nest.x─┬─nest.y─┐
  2. │ Hello │      1 │     10 │
  3. │ Hello │      2 │     20 │
  4. │ World │      3 │     30 │
  5. │ World │      4 │     40 │
  6. │ World │      5 │     50 │
  7. └───────┴────────┴────────┘

当指定嵌套数据结构的名称 ARRAY JOIN，意思是一样的 ARRAY JOIN 它包含的所有数组元素。 下面列出了示例:

  1. SELECT s, `nest.x`, `nest.y`
  2. FROM nested_test
  3. ARRAY JOIN `nest.x`, `nest.y`;
  1. ┌─s─────┬─nest.x─┬─nest.y─┐
  2. │ Hello │      1 │     10 │
  3. │ Hello │      2 │     20 │
  4. │ World │      3 │     30 │
  5. │ World │      4 │     40 │
  6. │ World │      5 │     50 │
  7. └───────┴────────┴────────┘

这种变化也是有道理的:

  1. SELECT s, `nest.x`, `nest.y`
  2. FROM nested_test
  3. ARRAY JOIN `nest.x`;
  1. ┌─s─────┬─nest.x─┬─nest.y─────┐
  2. │ Hello │      1 │ [10,20]    │
  3. │ Hello │      2 │ [10,20]    │
  4. │ World │      3 │ [30,40,50] │
  5. │ World │      4 │ [30,40,50] │
  6. │ World │      5 │ [30,40,50] │
  7. └───────┴────────┴────────────┘

可以将别名用于嵌套数据结构，以便选择 JOIN 结果或源数组。 例如:

  1. SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`
  2. FROM nested_test
  3. ARRAY JOIN nest AS n;
  1. ┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┐
  2. │ Hello │   1 │  10 │ [1,2]   │ [10,20]    │
  3. │ Hello │   2 │  20 │ [1,2]   │ [10,20]    │
  4. │ World │   3 │  30 │ [3,4,5] │ [30,40,50] │
  5. │ World │   4 │  40 │ [3,4,5] │ [30,40,50] │
  6. │ World │   5 │  50 │ [3,4,5] │ [30,40,50] │
  7. └───────┴─────┴─────┴─────────┴────────────┘

使用功能 arrayEnumerate 的例子:

  1. SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`, num
  2. FROM nested_test
  3. ARRAY JOIN nest AS n, arrayEnumerate(`nest.x`) AS num;
  1. ┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┬─num─┐
  2. │ Hello │   1 │  10 │ [1,2]   │ [10,20]    │   1 │
  3. │ Hello │   2 │  20 │ [1,2]   │ [10,20]    │   2 │
  4. │ World │   3 │  30 │ [3,4,5] │ [30,40,50] │   1 │
  5. │ World │   4 │  40 │ [3,4,5] │ [30,40,50] │   2 │
  6. │ World │   5 │  50 │ [3,4,5] │ [30,40,50] │   3 │
  7. └───────┴─────┴─────┴─────────┴────────────┴─────┘

实现细节

运行时优化查询执行顺序 ARRAY JOIN. 虽然 ARRAY JOIN 必须始终之前指定 WHERE/PREWHERE 子句中的查询，从技术上讲，它们可以以任何顺序执行，除非结果 ARRAY JOIN 用于过滤。 处理顺序由查询优化器控制。