ClickHouse数组列左连接实现：解决INVALID_JOIN_ON_EXPRESSION错误

ClickHouse的JOIN ON子句不支持直接使用has()这类数组包含判断函数，因为JOIN条件要求是等值匹配（或可被优化为哈希连接的条件），而非等值的包含判断无法满足JOIN的底层机制，因此会触发INVALID_JOIN_ON_EXPRESSION错误。

你可以通过以下两种方式修改查询：

方法1：先展开数组再JOIN（推荐） #

先对数组列执行arrayJoin展开，将数组元素转为单行记录，再与右侧表做等值JOIN，最后重新聚合回原数组结构：

SELECT 
  foo.name AS name,
  foo.policies AS policies,
  groupArray(profits7d.profits) AS profits7D
FROM (
  -- 替换为你的原foo子查询逻辑
  ...
) AS foo
LEFT JOIN (
   SELECT 
     groupArray((x, y)) as profits,
     policy
   FROM (
     SELECT
        day as x,
        SUM(profit) as y,
        policy
      FROM profits
      WHERE x >= toDateTime(NOW() - INTERVAL '1 week')
      GROUP BY x, policy
   )
   GROUP BY policy
) AS profits7d 
-- 用arrayJoin展开后的数组元素做等值匹配
ON profits7d.policy = arrayJoin(foo.policies)
-- 按原表的主键重新聚合，恢复数组结构
GROUP BY foo.name, foo.policies

方法2：使用数组存在性关联（适合小数据集） #

如果数据集较小，可以用arrayExists结合子查询的方式，但性能不如方法1：

SELECT 
  foo.name AS name,
  foo.policies AS policies,
  (
    SELECT groupArray(profits)
    FROM (
       SELECT 
         groupArray((x, y)) as profits,
         policy
       FROM (
         SELECT
            day as x,
            SUM(profit) as y,
            policy
          FROM profits
          WHERE x >= toDateTime(NOW() - INTERVAL '1 week')
          GROUP BY x, policy
       )
       GROUP BY policy
    ) AS profits7d
    WHERE arrayExists(p -> p = profits7d.policy, foo.policies)
  ) AS profits7D
FROM (
  -- 替换为你的原foo子查询逻辑
  ...
) AS foo

注意事项 #

• 若使用ANY LEFT JOIN，需确保聚合逻辑不会引入重复数据，可根据实际场景调整聚合函数（如any(profits)替代groupArray）。
• 方法1的性能更优，因为它利用了ClickHouse的哈希连接优化，适合大数据场景。

内容的提问来源于stack exchange，提问作者Kakulukian