ClickHouse物化视图计算30分钟时间范围价差失效问题咨询

该物化视图写法无法生效，核心是对ClickHouse物化视图的工作机制存在认知偏差：

• ClickHouse物化视图本质是插入触发的流式计算钩子，并非常规数据库中定期刷新的视图表：只有新数据写入源表market时，物化视图才会对本次刚写入的新数据块执行SELECT逻辑，将计算结果写入目标表；它不会自动扫描源表历史数据，也不会在无新数据写入时自动更新存量结果。你写的where create_time >= date_sub(MINUTE, 30, now())、order by create_time limit 1这类针对全表30分钟数据的查询逻辑，仅在POPULATE初始化阶段会全表执行一次，后续新数据写入时，只会从刚插入的小批量新数据中做过滤、排序、取limit 1，完全拿不到30分钟窗口的全局首尾价格。
• 子查询中的LIMIT 1未按id分组，即便处理新插入的数据块，也只会取块内时间最大/最小的单条记录，所有id的统计结果都会被这一条记录覆盖，计算逻辑完全错误。
• 所用ReplacingMergeTree()未指定版本字段，后台合并时无法判断哪条记录是最新统计值，会长期残留旧的错误结果。
• now()函数在物化视图中取的是数据插入时刻的时间，并非统计窗口的统一时间点，会导致时间字段逻辑混乱。

针对30分钟滚动窗口价差计算需求，有两种成熟稳定的实现方式，可按需选择：

方案1：分钟级预聚合 + 查询时计算窗口值（性能最优，推荐） #

核心思路是先将原始数据按分钟粒度做预聚合，把数据量压缩几个数量级，查询30分钟窗口时直接扫描聚合表做轻量计算即可，无需扫描原始表，可实现毫秒级响应。

1. 首先创建分钟级聚合结果存储表：

CREATE TABLE IF NOT EXISTS market_minute_stats
(
    id          UInt64,
    minute_ts   DateTime,
    start_price AggregateFunction(argMin, DECIMAL128(18), UInt64),
    end_price   AggregateFunction(argMax, DECIMAL128(18), UInt64)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(minute_ts)
ORDER BY (id, minute_ts);

2. 创建绑定源表的物化视图，实时写入时自动聚合分钟级数据：

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_market_minute_stats
TO market_minute_stats
AS
SELECT
    id,
    toStartOfMinute(FROM_UNIXTIME(create_time)) AS minute_ts,
    argMinState(price, create_time)             AS start_price,
    argMaxState(price, create_time)             AS end_price
FROM market
GROUP BY id, minute_ts;

3. 业务需要查询30分钟价差时，直接执行以下SQL即可，性能极高：

SELECT
    id,
    (argMaxMerge(end_price) - argMinMerge(start_price))                                  AS price_delta,
    argMinMerge(start_price)                                                            AS start_price,
    argMaxMerge(end_price)                                                              AS end_price,
    round(toFloat64(argMaxMerge(end_price) - argMinMerge(start_price)) / toFloat64(argMinMerge(start_price)), 2) AS price_delta_rate,
    now()                                                                               AS update_time
FROM market_minute_stats
WHERE minute_ts >= now() - INTERVAL 30 MINUTE
GROUP BY id;

方案2：定时刷新预物化结果表（适合直接查表取结果的场景） #

如果需要把30分钟窗口结果提前物化到表中，无需查询时做计算，不要用插入触发的物化视图做全窗口计算，改用定时任务周期性刷新结果表即可。

1. 创建30分钟统计结果表，指定版本字段保证读取到最新值：

CREATE TABLE IF NOT EXISTS market_stats_30min
(
    id               UInt64,
    price_delta      DECIMAL128(18),
    start_price      DECIMAL128(18),
    end_price        DECIMAL128(18),
    price_delta_rate Float64,
    update_time      DateTime,
    version          UInt64
) ENGINE = ReplacingMergeTree(version)
ORDER BY id;

2. 配置调度任务（可使用ClickHouse内置Scheduled Tasks，也可使用外部调度器），每1分钟执行一次以下SQL，刷新最近30分钟的统计结果：

INSERT INTO market_stats_30min
SELECT
    id,
    (end_p - start_p)                                                                 AS price_delta,
    start_p                                                                           AS start_price,
    end_p                                                                             AS end_price,
    round(toFloat64(end_p - start_p) / toFloat64(start_p), 2)                         AS price_delta_rate,
    now()                                                                             AS update_time,
    toUnixTimestamp(now())                                                            AS version
FROM (
    SELECT
        id,
        argMin(price, create_time) AS start_p,
        argMax(price, create_time) AS end_p
    FROM market
    WHERE create_time >= toUnixTimestamp(now() - INTERVAL 30 MINUTE)
    GROUP BY id
);

3. 查询时直接读取结果表即可，如果需要强一致性可加FINAL关键字，日常查询靠ReplacingMergeTree的版本合并机制就能自动过滤旧数据：

SELECT id, price_delta, start_price, end_price, price_delta_rate, update_time FROM market_stats_30min;

• 永远不要在ClickHouse物化视图的SELECT逻辑中写针对源表的全量子查询、全局排序、LIMIT、跨历史数据的时间过滤条件，这类逻辑只会在POPULATE阶段执行一次，后续增量写入时不会访问历史数据，结果必然不符合预期。
• 滚动时间窗口的统计涉及旧数据过期淘汰，无法仅靠插入触发的物化视图实现完整逻辑，必须配合预聚合或定时刷新方案。

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