ClickHouse关联子查询报错：如何修改查询补全月度报表缺失数据？

问题背景 #

你的表中每条date对应的block_date覆盖范围是该日期前15天至后50天，当查询date='2022-01-27'时，block_date仅能覆盖2022-01-13至2022-03-18，而你需要获取2022年1月全月（2022-01-01至2022-01-31）的block_date数据，缺失的2022-01-01至2022-01-12部分需要通过回溯更早的date快照来补充。原查询因ClickHouse不支持该场景下的EXISTS子查询报错，可通过以下方式修改：

修改后的查询方案 #

方案1：用IN子查询替代EXISTS

直接将EXISTS替换为IN，ClickHouse对IN子查询的支持更广泛，能满足你的需求：

SELECT
    `date`,
    block_date,
    total_plan_volume_grp20
FROM schema.table
WHERE `date` = '2022-01-27'
  AND block_date BETWEEN '2022-01-01' AND '2022-01-31'

UNION ALL

SELECT
    addDays(t1.`date`, 1) AS target_date,
    t1.block_date,
    t1.total_plan_volume_grp20
FROM schema.table t1
WHERE addDays(t1.`date`, 1) IN (SELECT DISTINCT `date` FROM schema.table)
  AND t1.block_date BETWEEN '2022-01-01' AND '2022-01-31'

UNION ALL

SELECT
    addDays(t1.`date`, 2) AS target_date,
    t1.block_date,
    t1.total_plan_volume_grp20
FROM schema.table t1
WHERE addDays(t1.`date`, 2) IN (SELECT DISTINCT `date` FROM schema.table)
  AND t1.block_date BETWEEN '2022-01-01' AND '2022-01-31'

方案2：更高效的批量回溯（推荐）

手动多次UNION ALL扩展性差，可先生成需要回溯的date列表，再关联原表获取对应数据，一次性覆盖所有需要补充的日期：

WITH target_date = '2022-01-27'
-- 生成需要回溯的日期范围：从target_date往前推14天（确保覆盖到2022-01-01）到target_date本身
SELECT
    d.backtrack_date,
    t.block_date,
    t.total_plan_volume_grp20
FROM (
    SELECT toDate(target_date) - INTERVAL number DAY AS backtrack_date
    FROM numbers(0, 15) -- 生成0到14天的偏移，共15个日期
) d
-- 关联原表，只保留存在的date快照
JOIN schema.table t ON t.`date` = d.backtrack_date
-- 筛选1月全月的block_date
WHERE t.block_date BETWEEN '2022-01-01' AND '2022-01-31'

关键说明 #

• ClickHouse中EXISTS子查询在部分关联场景下支持有限，用IN或JOIN替代是更稳妥的方案。
• 方案2通过numbers()函数生成批量回溯日期，避免了重复写UNION ALL，更适合需要回溯多天的场景，且性能更优。
• 最后通过WHERE t.block_date BETWEEN '2022-01-01' AND '2022-01-31'精准过滤出月度报表需要的数据，避免冗余记录。

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