如何在ClickHouse中检查缺失日期数据？求解查询报错问题

你的查询报错是因为左侧子查询输出的是dates数组字段，但LEFT JOIN USING(date)要求左右两边都存在date字段，左侧没有该字段，所以触发NOT_FOUND_COLUMN_IN_BLOCK错误。

修正后的查询语句 #

下面是能正确找出缺失日期的查询：

WITH dates_range AS (
    SELECT 
        toDate(min(time)) AS start_date,
        toDate(max(time)) AS end_date
    FROM table
)
SELECT generated_date AS missing_date
FROM (
    SELECT 
        start_date + INTERVAL x DAY AS generated_date
    FROM dates_range
    ARRAY JOIN range(0, toUInt64(end_date - start_date) + 1) AS x
)
LEFT JOIN (
    SELECT DISTINCT toDate(time) AS existing_date
    FROM table
) ON generated_date = existing_date
WHERE existing_date IS NULL;

关键调整说明 #

• 用ARRAY JOIN将range生成的数字数组展开为逐行记录，通过start_date + INTERVAL x DAY计算出日期范围内的每一天，字段名设为generated_date
• 右侧子查询通过DISTINCT获取表中已存在的所有日期（也可以用GROUP BY toDate(time)替代）
• 左连接后，筛选existing_date IS NULL的记录，即为全周期内缺失数据的日期

简化写法（若ClickHouse版本支持generateDates） #

如果你的ClickHouse版本支持generateDates函数，可以用更简洁的写法：

WITH dates_range AS (
    SELECT 
        toDate(min(time)) AS start_date,
        toDate(max(time)) AS end_date
    FROM table
)
SELECT date AS missing_date
FROM generateDates(start_date, end_date)
LEFT JOIN (
    SELECT DISTINCT toDate(time) AS date
    FROM table
) USING(date)
WHERE date IS NULL
FROM dates_range;

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