Clickhouse中计算分钟时间区间首尾值变化百分比的方案验证

原代码的核心问题 #

• 笛卡尔积关联错误：最后用from beginning, ending是笛卡尔积关联，会把所有起始区间和结束区间两两匹配，完全无法对应到正确的首尾区间对
• 字段名不一致：WHERE条件用的是datetime，但SELECT里转时间用的是mydatetime，执行会直接报错
• 引用不存在的字段：最后SELECT里的beginning.timeBeginningOfInterval在CTE里没有定义，应该是beginning.beginningOfInterval
• 窗口函数逻辑不符合需求：你用sum(count(*)) OVER (Rows BETWEEN 1 PRECEDING AND CURRENT ROW)是计算当前分钟+前一分钟的行数总和，但这和“区间首尾值”的描述不匹配，需要先明确你的“首尾值”具体指向

两种正确实现方案 #

场景1：计算每个分钟区间内的行数，与上一分钟区间行数的变化百分比 #

如果你的需求是统计每个分钟（比如00:00-00:01）内的总行数，再计算该数值与上一个分钟区间行数的变化百分比，代码如下：

WITH minute_stats AS (
    SELECT
        toStartOfInterval(toDateTime(mydatetime), INTERVAL 1 MINUTE) AS minute_interval,
        count(*) AS row_count
    FROM mytable
    WHERE mydatetime >= '2023-08-01 00:00:00' AND mydatetime <= '2023-08-01 23:59:59'
    GROUP BY minute_interval
    ORDER BY minute_interval ASC
)
SELECT
    minute_interval,
    row_count,
    LAG(row_count) OVER (ORDER BY minute_interval) AS prev_minute_count,
    -- 处理除零情况，避免报错
    IF(LAG(row_count) OVER (ORDER BY minute_interval) = 0,
       0,
       (row_count - LAG(row_count) OVER (ORDER BY minute_interval)) / LAG(row_count) OVER (ORDER BY minute_interval) * 100
    ) AS percentage_change
FROM minute_stats

场景2：计算截至每个分钟区间起始/结束时刻的累计总行数变化百分比 #

如果你的需求是计算截至每个分钟起始时刻的累计总行数，和截至该分钟结束时刻的累计总行数之间的变化百分比，代码如下：

WITH cumulative_stats AS (
    SELECT
        toStartOfInterval(toDateTime(mydatetime), INTERVAL 1 MINUTE) AS minute_interval,
        count(*) AS minute_row_count,
        -- 计算截至当前分钟起始时刻的累计行数
        SUM(count(*)) OVER (ORDER BY minute_interval ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS cumulative_at_start,
        -- 计算截至当前分钟结束时刻的累计行数
        SUM(count(*)) OVER (ORDER BY minute_interval) AS cumulative_at_end
    FROM mytable
    WHERE mydatetime >= '2023-08-01 00:00:00' AND mydatetime <= '2023-08-01 23:59:59'
    GROUP BY minute_interval
    ORDER BY minute_interval ASC
)
SELECT
    minute_interval,
    cumulative_at_start,
    cumulative_at_end,
    -- 处理累计起始值为0的情况
    IF(cumulative_at_start = 0,
       0,
       (cumulative_at_end - cumulative_at_start) / cumulative_at_start * 100
    ) AS percentage_change
FROM cumulative_stats

补充说明 #

• 请确保你的时间字段名统一（要么全用datetime，要么全用mydatetime）
• 原代码分母加1的方式虽能避免除零，但会导致起始值为0时的百分比计算失真，用IF判断逻辑更严谨

内容的提问来源于stack exchange，提问作者Zala Pierre GOUPIL