ClickHouse复杂查询报错：JOIN条件含左右表列问题

问题 #

我在ClickHouse中编写复杂查询时遇到错误，无法定位问题所在。我拥有三张ClickHouse表：

• jump_news_sentiment.news_sentiment_after：字段包括assetid、date、agg_sentiment、agg_sentiment_rw、n
• jump_news_sentiment.news_sentiment_before：字段包括assetid、date、agg_sentiment、agg_sentiment_rw、n
• jump_news_sentiment.date_next：字段包括date、date_next

我的需求：

1. 将news_sentiment_after表的日期向前偏移1天，预处理情感数据
2. 合并特定日期前后的资产情感数据，处理空值并按需求求和
3. 基于date_next表定义的区间过滤合并后的数据，按资产聚合情感得分与计数

原查询语句：

WITH shifted_dates AS (
        SELECT 
            assetid, 
            dateAdd(day, 1, date) AS date, 
            agg_sentiment, 
            agg_sentiment_rw, 
            n
        FROM jump_news_sentiment.news_sentiment_after
    ), merged_data AS (
        SELECT 
            coalesce(b.assetid, a.assetid) AS assetid, 
            coalesce(b.date, a.date) AS date, 
            coalesce(b.agg_sentiment, 0) + coalesce(a.agg_sentiment, 0)  AS agg_sentiment,
            coalesce(b.agg_sentiment_rw, 0) + coalesce(a.agg_sentiment_rw, 0) AS agg_sentiment_rw,
            coalesce(a.n, 0) + coalesce(b.n, 0) AS n
        FROM shifted_dates a
        FULL OUTER JOIN jump_news_sentiment.news_sentiment_before b USING (date, assetid)
    ),
    aggregated_data AS (
        SELECT 
            m.assetid AS assetid,
            max(m.date) AS date,
            sum(m.agg_sentiment) AS agg_sentiment,
            sum(m.agg_sentiment_rw) AS agg_sentiment_rw,
            sum(m.n) AS n
        FROM merged_data m
        JOIN jump_news_sentiment.date_next e ON m.date > e.date AND m.date <= e.date_next
        GROUP BY m.assetid, e.date_next
    )
    SELECT * FROM aggregated_data;

执行后错误：

JOIN  merged_data AS __table2 ALL INNER JOIN jump_news_sentiment.date_next AS __table6 ON (__table2.date > __table6.date) AND (__table2.date <= __table6.date_next) join expression contains column from left and right table.

错误原因分析 #

ClickHouse不支持在JOIN的ON条件中使用左右表字段进行范围比较（比如m.date > e.date这种跨表的范围判断），这类关联属于非等值JOIN范畴，而ClickHouse的常规JOIN优化逻辑仅支持基于等值条件的关联。你的查询中用范围条件关联两张表，触发了ClickHouse的语法限制。

解决方法 #

可以通过两种方式改写查询绕过该限制：

方法1：改用EXISTS子查询过滤 #

把date_next的范围过滤逻辑放到WHERE子句的EXISTS条件中，避免直接使用范围JOIN：

WITH shifted_dates AS (
        SELECT 
            assetid, 
            dateAdd(day, 1, date) AS date, 
            agg_sentiment, 
            agg_sentiment_rw, 
            n
        FROM jump_news_sentiment.news_sentiment_after
    ), merged_data AS (
        SELECT 
            coalesce(b.assetid, a.assetid) AS assetid, 
            coalesce(b.date, a.date) AS date, 
            coalesce(b.agg_sentiment, 0) + coalesce(a.agg_sentiment, 0)  AS agg_sentiment,
            coalesce(b.agg_sentiment_rw, 0) + coalesce(a.agg_sentiment_rw, 0) AS agg_sentiment_rw,
            coalesce(a.n, 0) + coalesce(b.n, 0) AS n
        FROM shifted_dates a
        FULL OUTER JOIN jump_news_sentiment.news_sentiment_before b USING (date, assetid)
    ),
    aggregated_data AS (
        SELECT 
            m.assetid AS assetid,
            max(m.date) AS date,
            sum(m.agg_sentiment) AS agg_sentiment,
            sum(m.agg_sentiment_rw) AS agg_sentiment_rw,
            sum(m.n) AS n,
            e.date_next AS period_end_date
        FROM merged_data m
        JOIN jump_news_sentiment.date_next e ON 1=1
        WHERE m.date > e.date AND m.date <= e.date_next
        GROUP BY m.assetid, e.date_next
    )
    SELECT * FROM aggregated_data;

方法2：笛卡尔积+过滤（适用于date_next数据量小的场景） #

如果date_next表行数不多，可以先用笛卡尔积关联两张表，再过滤符合范围条件的数据：

WITH shifted_dates AS (
        SELECT 
            assetid, 
            dateAdd(day, 1, date) AS date, 
            agg_sentiment, 
            agg_sentiment_rw, 
            n
        FROM jump_news_sentiment.news_sentiment_after
    ), merged_data AS (
        SELECT 
            coalesce(b.assetid, a.assetid) AS assetid, 
            coalesce(b.date, a.date) AS date, 
            coalesce(b.agg_sentiment, 0) + coalesce(a.agg_sentiment, 0)  AS agg_sentiment,
            coalesce(b.agg_sentiment_rw, 0) + coalesce(a.agg_sentiment_rw, 0) AS agg_sentiment_rw,
            coalesce(a.n, 0) + coalesce(b.n, 0) AS n
        FROM shifted_dates a
        FULL OUTER JOIN jump_news_sentiment.news_sentiment_before b USING (date, assetid)
    ),
    aggregated_data AS (
        SELECT 
            m.assetid AS assetid,
            max(m.date) AS date,
            sum(m.agg_sentiment) AS agg_sentiment,
            sum(m.agg_sentiment_rw) AS agg_sentiment_rw,
            sum(m.n) AS n,
            e.date_next AS period_end_date
        FROM merged_data m, jump_news_sentiment.date_next e
        WHERE m.date > e.date AND m.date <= e.date_next
        GROUP BY m.assetid, e.date_next
    )
    SELECT * FROM aggregated_data;

补充说明 #

• 方法1在merged_data数据量较大时性能更优，通过JOIN+WHERE过滤的方式避免了无意义的笛卡尔积计算。
• 方法2仅适合date_next表数据量极小的场景，否则会生成大量中间数据，导致查询性能骤降。

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