如何在ClickHouse物化视图中仅使用特定记录的最新状态统计

方案1：将原表引擎替换为ReplacingMergeTree（推荐） #

这是改动最小、长期维护成本最低的方案，完全兼容现有clickhouse_sinker、snuba等写入工具的逻辑，不需要修改下沉链路：

1. 修改原表引擎为ReplacingMergeTree，指定版本字段为updatedAt，同时调整排序键把唯一标识sqlId放在首位（用于去重判断）：

ALTER TABLE orders 
ENGINE = ReplacingMergeTree(updatedAt)
ORDER BY (sqlId, createdAt);

2. 后台合并时会自动清理同一个sqlId下的历史版本，仅保留updatedAt最大的最新记录。如果需要查询时强制拿到实时最新数据，在查询语句末尾加FINAL关键字即可，新版本ClickHouse对FINAL的性能已经做了大幅优化。
3. 后续统计直接基于去重后的原表即可，原物化视图无需修改，统计结果自然正确。

方案2：新增中间层物化视图存储最新订单状态（不改原表结构） #

如果不希望修改原表的引擎和排序键，可以加一层中间物化视图预先聚合每个订单的最新状态：

1. 建中间状态物化视图：

CREATE MATERIALIZED VIEW order_latest_state
ENGINE = AggregatingMergeTree
ORDER BY sqlId
AS SELECT
    sqlId,
    argMax(price, updatedAt) AS latest_price,
    argMax(createdAt, updatedAt) AS latest_createdAt,
    max(updatedAt) AS max_updatedAt
FROM orders
GROUP BY sqlId;

2. 基于中间视图构建最终的每日统计物化视图或者直接查询：

-- 直接查询统计结果
SELECT
    toStartOfDay(latest_createdAt) AS day,
    sum(latest_price) AS volume
FROM order_latest_state
GROUP BY day
ORDER BY day;

该方案完全兼容现有写入链路和原表结构，查询性能也接近原生表，仅额外占用少量存储资源存储中间状态。

方案3：查询时实时去重（适合低频查询场景） #

如果不需要预计算，仅偶尔做统计查询，可以直接在查询逻辑中加一层去重，不需要修改任何表结构：

SELECT
    toStartOfDay(createdAt) AS day,
    sum(price) AS volume
FROM (
    SELECT
        sqlId,
        argMax(price, updatedAt) AS price,
        argMax(createdAt, updatedAt) AS createdAt
    FROM orders
    GROUP BY sqlId
) t
GROUP BY day;

该方案灵活度最高，但每次查询都需要做全量订单的聚合计算，数据量较大时查询性能较低，适合小数据量或者临时查询场景。

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