如何在ClickHouse中对比昨日与当日的查询结果集？

为什么UNION ALL无法解决问题 #

UNION ALL的作用是将两个表的行数据纵向合并，它无法把当日和昨日同一小时的数据对应到同一行，自然没办法直接计算差值。你需要的是横向关联同维度数据，所以应该用JOIN类的关联查询。

基础解决方案（按小时字段关联） #

假设两张表结构如下：

• table_1（2022-12-16数据）：包含hour_of_day（1-24的小时数）、loads字段
• table_2（2022-12-15数据）：包含hour_of_day、loads字段

使用INNER JOIN关联同一小时的数据，直接计算差值：

SELECT
    t1.hour_of_day,
    t1.loads AS current_loads,
    t2.loads AS yesterday_loads,
    t1.loads - t2.loads AS load_diff
FROM
    table_1 t1
INNER JOIN
    table_2 t2 ON t1.hour_of_day = t2.hour_of_day
ORDER BY
    t1.hour_of_day;

带日期过滤的严谨写法 #

如果表中包含record_date（记录日期）字段，可添加日期条件确保数据准确性：

SELECT
    t1.hour_of_day,
    t1.loads AS current_loads,
    t2.loads AS yesterday_loads,
    t1.loads - t2.loads AS load_diff
FROM
    table_1 t1
INNER JOIN
    table_2 t2 ON t1.hour_of_day = t2.hour_of_day
WHERE
    t1.record_date = '2022-12-16'
    AND t2.record_date = '2022-12-15'
ORDER BY
    t1.hour_of_day;

处理缺失小时的场景 #

如果某一天存在小时数据缺失的情况，INNER JOIN会过滤掉无匹配的小时。若要保留所有小时（缺失数据补0），可以用LEFT JOIN配合COALESCE函数：

SELECT
    COALESCE(t1.hour_of_day, t2.hour_of_day) AS hour_of_day,
    COALESCE(t1.loads, 0) AS current_loads,
    COALESCE(t2.loads, 0) AS yesterday_loads,
    COALESCE(t1.loads, 0) - COALESCE(t2.loads, 0) AS load_diff
FROM
    table_1 t1
LEFT JOIN
    table_2 t2 ON t1.hour_of_day = t2.hour_of_day
WHERE
    t1.record_date = '2022-12-16'
    AND (t2.record_date = '2022-12-15' OR t2.record_date IS NULL)
ORDER BY
    hour_of_day;

若需同时保留两天所有存在的小时（哪怕其中一天无数据），可改用FULL OUTER JOIN（部分数据库如MySQL不支持，可通过UNION模拟）。

内容的提问来源于stack exchange，提问作者Rico Sancho Abarro