ClickHouse中使用ArrayJoin时如何处理空数组？

Question

在ClickHouse中使用 `ARRAY JOIN` 时，如何处理空数组是一个常见问题。当被连接的数组字段为空时，`ARRAY JOIN` 默认会跳过该行，导致查询结果丢失原始数据。这种行为可能不符合预期，特别是在需要保留原始记录的情况下。为了解决这个问题，通常可以使用 `LEFT ARRAY JOIN` 或结合 `COALESCE` 函数将空数组替换为包含默认值的数组。此外，也可以通过预处理数据或调整业务逻辑来避免空数组带来的影响。理解这些处理方式对于正确使用 `ARRAY JOIN`、确保数据分析准确性至关重要。

Answer 1

一、ClickHouse 中 ARRAY JOIN 的默认行为与空数组问题

在 ClickHouse 中，`ARRAY JOIN` 是一种特殊的连接方式，用于将数组字段展开为多行。例如，假设某一行数据包含一个名为 `tags` 的数组字段，其值为 ['a', 'b']，那么使用 `ARRAY JOIN tags` 会将该行扩展为两行。

然而，当被连接的数组字段为空时（如 tags = []），`ARRAY JOIN` 默认会跳过该行，导致原始记录丢失。这种行为在某些场景下可能不符合预期，尤其是在需要保留所有原始记录的情况下。

SELECT * FROM table_with_array ARRAY JOIN tags;

上述查询中，如果某行的 `tags` 字段为空，则该行不会出现在结果集中。

二、LEFT ARRAY JOIN：保留原始记录的解决方案

为了解决空数组导致行丢失的问题，ClickHouse 提供了 `LEFT ARRAY JOIN`，它会在数组为空时仍然保留原始记录，并将数组字段设为 NULL 或默认值。

SELECT id, tag
FROM (
    SELECT 1 AS id, ['a', 'b'] AS tags
    UNION ALL
    SELECT 2 AS id, [] AS tags
) AS data
LEFT ARRAY JOIN tags AS tag;

id	tag
1	a
1	b
2	NULL

可以看到，即使 `tags` 为空，`id=2` 的记录仍然保留在结果集中，只是 `tag` 为 NULL。

三、COALESCE 函数：替代空数组为默认值

另一种处理空数组的方式是结合 `COALESCE` 函数，在数组为空时返回一个包含默认值的数组，从而避免丢失记录。

SELECT id, tag
FROM (
    SELECT 1 AS id, ['a', 'b'] AS tags
    UNION ALL
    SELECT 2 AS id, [] AS tags
) AS data
ARRAY JOIN COALESCE(tags, ['default']) AS tag;

id	tag
1	a
1	b
2	default

通过这种方式，可以确保即使原数组为空，也能生成一行记录并带有默认值。

四、预处理数据：从业务层规避空数组问题

除了 SQL 层面的处理方式外，也可以在数据写入 ClickHouse 前进行预处理，例如：

• 在 ETL 过程中替换空数组为 [NULL] 或 ['default']。
• 在业务逻辑中确保数组字段至少包含一个元素。
• 对于日志类数据，可设置默认标签或分类。

这样做的好处是可以减少查询时的复杂度，并提高查询性能。

五、综合对比：不同处理方式的适用场景

方法	优点	缺点	适用场景
ARRAY JOIN	简单高效	丢弃空数组记录	仅需关注非空数组的情况
LEFT ARRAY JOIN	保留原始记录	可能导致 NULL 值处理复杂	需要保留所有记录但允许 NULL 值
COALESCE 替换	保证每行都有输出	可能引入虚假数据	需要展示默认值或占位符
预处理数据	提升查询效率	增加写入复杂度	数据一致性要求高或批量处理

六、流程图：选择合适处理方式的决策路径

graph TD
    A[是否允许丢失原始记录？] -->|否| B(使用 LEFT ARRAY JOIN)
    A -->|是| C[是否希望每行都有输出？]
    C -->|否| D(使用 ARRAY JOIN)
    C -->|是| E[是否允许默认值？]
    E -->|是| F(COALESCE 替换默认值)
    E -->|否| G(预处理数据填充默认值)