如何在SQL中计算两个求和值的差值（ClickHouse适配Grafana场景）

方案1：使用sumIf单表查询（性能最优） #

这个方案只需要一次表扫描，执行效率更高，适合大部分场景：

SELECT 
    -- 时间对齐：7天前的时段统一加7天，和当前时段的时间轴对齐
    toUnixTimestamp(
        if(
            itime BETWEEN toDateTime(1631870605) AND toDateTime(1631874205),
            toStartOfMinute(itime),
            toStartOfMinute(itime + INTERVAL 7 DAY)
        )
    ) * 1000 as t,
    method,
    sumIf(count, itime BETWEEN toDateTime(1631870605) AND toDateTime(1631874205)) as current_c,
    sumIf(count, itime BETWEEN toDateTime(1631870605 - 7*86400) AND toDateTime(1631874205 - 7*86400)) as last_week_c
FROM me.my_table
WHERE 
    -- 时间范围同时覆盖当前时段和7天前对应时段
    itime BETWEEN toDateTime(1631870605 - 7*86400) AND toDateTime(1631874205)
    AND method like 'a%'
GROUP BY method, t
-- 筛选差值大于等于100，同时保留你原有的c>500的过滤条件
HAVING current_c - last_week_c >= 100 AND current_c > 500
ORDER BY t

如果是在Grafana中使用，建议直接用内置的时间变量替换写死的时间戳，适配仪表盘的时间选择器：

SELECT 
    toUnixTimestamp(
        if(
            itime BETWEEN $from AND $to,
            toStartOfMinute(itime),
            toStartOfMinute(itime + INTERVAL 7 DAY)
        )
    ) * 1000 as t,
    method,
    sumIf(count, itime BETWEEN $from AND $to) as current_c,
    sumIf(count, itime BETWEEN $from - INTERVAL 7 DAY AND $to - INTERVAL 7 DAY) as last_week_c
FROM me.my_table
WHERE 
    itime BETWEEN $from - INTERVAL 7 DAY AND $to
    AND method like 'a%'
GROUP BY method, t
HAVING current_c - last_week_c >= 100 AND current_c > 500
ORDER BY t

方案2：使用JOIN关联两个时段的结果 #

如果后续需要扩展更复杂的聚合逻辑，可以用子查询分别计算两个时段的结果再关联：

WITH current_data AS (
    SELECT 
        toStartOfMinute(itime) as minute_t,
        method,
        sum(count) as current_c
    FROM me.my_table
    WHERE itime BETWEEN toDateTime(1631870605) AND toDateTime(1631874205)
        AND method like 'a%'
    GROUP BY method, minute_t
    HAVING current_c > 500
),
last_week_data AS (
    SELECT 
        toStartOfMinute(itime) as minute_t,
        method,
        sum(count) as last_week_c
    FROM me.my_table
    WHERE itime BETWEEN toDateTime(1631870605 - 7*86400) AND toDateTime(1631874205 - 7*86400)
        AND method like 'a%'
    GROUP BY method, minute_t
)
SELECT 
    toUnixTimestamp(c.minute_t) * 1000 as t,
    c.method,
    c.current_c,
    l.last_week_c
FROM current_data c
INNER JOIN last_week_data l 
    ON c.minute_t = l.minute_t + INTERVAL 7 DAY 
    AND c.method = l.method
WHERE c.current_c - l.last_week_c >= 100
ORDER BY t

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