ClickHouse中CASE条件子查询报错问题求助

ClickHouse不支持在标量子查询中直接引用外部查询的表别名（如BTOP.customer_id），这类关联子查询的写法在MySQL中可行，但ClickHouse的查询优化器无法识别外部表的列，因此抛出UNKNOWN_IDENTIFIER错误。

推荐两种改写方式，优先选择第二种（性能更优）：

方案1：用EXISTS替代COUNT判断 #

将原CASE中的COUNT子查询替换为EXISTS判断，直接检查是否存在历史交易记录：

select
    BTOP.id,
    BTOP.customer_id,
    BTOP.brand_id,
    (case when NOT EXISTS (SELECT 1 from c_bill_transaction where customer_id = BTOP.customer_id AND added_on < '2023-01-01 00:00:00') then 'new' else 'existing' end) as customer_type,
    BT.id,
    BT.status,
    BTOP.added_on
from c_bill_transaction_online_precheckout BTOP
Left Join c_bill_transaction BT on BT.precheckout_id = BTOP.id
where BTOP.added_on > '2023-01-01 00:00:00'

注：部分旧版本ClickHouse可能仍不支持关联EXISTS，此时建议使用方案2。

方案2：预计算历史客户列表后关联查询 #

通过WITH子查询先一次性提取所有有历史交易的客户ID，再通过LEFT JOIN关联主查询，效率远高于逐行子查询：

WITH customer_history AS (
    SELECT DISTINCT customer_id
    FROM c_bill_transaction
    WHERE added_on < '2023-01-01 00:00:00'
)
SELECT
    BTOP.id,
    BTOP.customer_id,
    BTOP.brand_id,
    CASE WHEN CH.customer_id IS NULL THEN 'new' ELSE 'existing' END AS customer_type,
    BT.id,
    BT.status,
    BTOP.added_on
FROM c_bill_transaction_online_precheckout BTOP
LEFT JOIN c_bill_transaction BT ON BT.precheckout_id = BTOP.id
LEFT JOIN customer_history CH ON CH.customer_id = BTOP.customer_id
WHERE BTOP.added_on > '2023-01-01 00:00:00'

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