如何在ClickHouse中创建物化视图追踪用户首次操作时间？

方案思路 #

要实现追踪每个用户的首次操作时间，这里提供两种可行方案，分别基于AggregatingMergeTree和反向排序的ReplacingMergeTree：

方案一：使用AggregatingMergeTree（推荐） #

通过聚合函数直接计算每个用户的最小时间戳，逻辑清晰且性能更优：

1. 创建底层聚合表

CREATE TABLE user_first_action
(
    user_id UInt64,
    first_action_time AggregateFunction(min, DateTime)
)
ENGINE = AggregatingMergeTree()
ORDER BY user_id;

2. 创建物化视图

CREATE MATERIALIZED VIEW user_first_action_mv TO user_first_action
AS
SELECT
    user_id,
    minState(timestamp) AS first_action_time
FROM user_operation_table
GROUP BY user_id;

3. 查询实际结果
   用minMerge()解析聚合状态得到首次操作时间：

SELECT
    user_id,
    minMerge(first_action_time) AS first_action_time
FROM user_first_action
GROUP BY user_id;

方案二：反向排序的ReplacingMergeTree #

利用ReplacingMergeTree保留排序后最后一行的特性，通过倒序时间戳让首次操作记录被保留：

1. 创建目标表

CREATE TABLE user_first_action_replacing
(
    user_id UInt64,
    timestamp DateTime,
    dummy UInt8
)
ENGINE = ReplacingMergeTree(dummy)
ORDER BY (user_id, -toUnixTimestamp(timestamp));

这里通过-toUnixTimestamp(timestamp)实现时间戳倒序，dummy作为无意义的替换键，确保同一user_id下最早的记录在Merge时被保留。
2. 创建物化视图

CREATE MATERIALIZED VIEW user_first_action_replacing_mv TO user_first_action_replacing
AS
SELECT
    user_id,
    timestamp,
    1 AS dummy
FROM user_operation_table;

3. 查询结果
   依赖自动Merge或手动执行OPTIMIZE后，查询去重结果：

SELECT DISTINCT user_id, timestamp AS first_action_time
FROM user_first_action_replacing
ORDER BY user_id;

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