Clickhouse不支持JOIN范围条件，如何用LEFT JOIN实现等价查询？

Clickhouse确实不支持JOIN ON子句中包含<、>、<=、>=或BETWEEN这类非等值条件，针对你的需求，这里提供两种等价实现方式：

方案一：等值JOIN后通过CASE WHEN过滤 #

先按uid做等值LEFT JOIN，再在聚合阶段用CASE WHEN筛选出符合时间范围的付费用户，逻辑和原查询完全一致：

SELECT
    S.created_at AS date,
    -- 只统计安装后0-3天内付费的用户数
    count(DISTINCT CASE 
        WHEN dateDiff('day', S.created_at, P.pay_time) BETWEEN 0 AND 3 THEN P.uid 
    END) / count(DISTINCT S.uid) * 100 AS conv_by_install_date
FROM
    -- 简化原查询的嵌套子查询，直接获取每个用户的首次安装时间
    (
        SELECT
            uid,
            min(created_at) AS created_at
        FROM states
        GROUP BY uid
    ) S
LEFT JOIN
    (
        SELECT
            uid,
            min(created_at) AS pay_time
        FROM payment
        WHERE count = 0
        GROUP BY uid
    ) P ON S.uid = P.uid
GROUP BY S.created_at
ORDER BY S.created_at;

逻辑说明： #

• 先完成uid的等值关联，保留所有安装用户（符合LEFT JOIN的语义）
• CASE WHEN会自动排除未付费（P.pay_time为NULL）或付费时间不符合范围的用户
• 最终计算的比例和原查询需求完全匹配

方案二：使用LATERAL JOIN（Clickhouse 21.8+版本支持） #

如果你的Clickhouse版本在21.8及以上，可以用LATERAL JOIN实现逐行关联筛选，更贴近原查询的条件写法：

SELECT
    S.created_at AS date,
    count(DISTINCT P.uid) / count(DISTINCT S.uid) * 100 AS conv_by_install_date
FROM
    (
        SELECT
            uid,
            min(created_at) AS created_at
        FROM states
        GROUP BY uid
    ) S
LEFT JOIN LATERAL
    (
        SELECT
            uid,
            min(created_at) AS pay_time
        FROM payment
        WHERE 
            count = 0
            AND uid = S.uid
            AND dateDiff('day', S.created_at, created_at) BETWEEN 0 AND 3
        GROUP BY uid
    ) P ON 1=1
GROUP BY S.created_at
ORDER BY S.created_at;

逻辑说明： #

• LATERAL JOIN会逐行读取S表的用户数据，关联P表中符合uid匹配且时间范围的记录
• 同样保留所有安装用户，未匹配到符合条件付费记录的用户会显示NULL，聚合时自动排除

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