如何在ClickHouse中处理计数器重置问题？

1. 创建时序数据表 #

先创建适配累积计数器存储的表，包含时间戳、度量名称、实例标签和计数值：

CREATE TABLE counter_metrics
(
    `timestamp` DateTime64(3),
    `metric_name` String,
    `instance` String,
    `value` UInt64
)
ENGINE = MergeTree
ORDER BY (metric_name, instance, timestamp);

2. 插入含重置场景的测试数据 #

模拟计数器正常增长后重置的场景：

INSERT INTO counter_metrics VALUES
('2024-05-20 10:00:00.000', 'http_requests_total', 'web-server-01', 100),
('2024-05-20 10:01:00.000', 'http_requests_total', 'web-server-01', 150),
('2024-05-20 10:02:00.000', 'http_requests_total', 'web-server-01', 200),
-- 计数器发生重置，回到0
('2024-05-20 10:03:00.000', 'http_requests_total', 'web-server-01', 0),
('2024-05-20 10:04:00.000', 'http_requests_total', 'web-server-01', 30),
('2024-05-20 10:05:00.000', 'http_requests_total', 'web-server-01', 50);

3. 处理计数器重置并计算增量 #

3.1 计算单个时间点的增量 #

通过LAG窗口函数获取前一个时间点的计数值，判断是否发生重置（当前值小于前值即为重置），进而计算正确的增量：

SELECT
    timestamp,
    metric_name,
    instance,
    value,
    LAG(value) OVER (PARTITION BY metric_name, instance ORDER BY timestamp) AS prev_value,
    CASE
        WHEN prev_value IS NULL THEN 0  -- 首个数据点无前置值，增量为0
        WHEN value < prev_value THEN value  -- 重置场景，增量取当前值
        ELSE value - prev_value  -- 正常增长，增量为差值
    END AS increment
FROM counter_metrics
ORDER BY timestamp;

3.2 按时间区间聚合总增量 #

如果需要按分钟、小时等时间维度聚合总指标值，基于增量结果再做聚合即可：

WITH counter_increments AS (
    SELECT
        toStartOfMinute(timestamp) AS minute,
        metric_name,
        instance,
        CASE
            WHEN LAG(value) OVER (PARTITION BY metric_name, instance ORDER BY timestamp) IS NULL THEN 0
            WHEN value < LAG(value) OVER (PARTITION BY metric_name, instance ORDER BY timestamp) THEN value
            ELSE value - LAG(value) OVER (PARTITION BY metric_name, instance ORDER BY timestamp)
        END AS increment
    FROM counter_metrics
)
SELECT
    minute,
    metric_name,
    instance,
    sum(increment) AS total_requests
FROM counter_increments
GROUP BY minute, metric_name, instance
ORDER BY minute;

3.3 用物化视图预计算增量（优化大查询性能） #

对于海量时序数据，可通过物化视图提前计算增量，避免每次查询重复计算窗口函数：

-- 创建物化视图存储增量数据
CREATE MATERIALIZED VIEW counter_increments_mv
ENGINE = MergeTree
ORDER BY (metric_name, instance, timestamp)
AS
SELECT
    timestamp,
    metric_name,
    instance,
    CASE
        WHEN LAG(value) OVER (PARTITION BY metric_name, instance ORDER BY timestamp) IS NULL THEN 0
        WHEN value < LAG(value) OVER (PARTITION BY metric_name, instance ORDER BY timestamp) THEN value
        ELSE value - LAG(value) OVER (PARTITION BY metric_name, instance ORDER BY timestamp)
    END AS increment
FROM counter_metrics;

-- 查询物化视图获取增量数据
SELECT * FROM counter_increments_mv ORDER BY timestamp;

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