ClickHouse如何用物化视图保留各Job ID的最新Event Set行？

我在ClickHouse中有一张存储系统事件片段的表，其中“Delete”“Create”类事件对应1行，“Compute”类事件对应n行（通常3-4行），示例数据如下：

表中事件量达数百万级，我希望能便捷查询指定Job ID的最新事件集合——按Event Set ID分组的最新组即为Job的最新状态。目前我不想按Event Set ID聚合行，因为需基于Scope等行级字段查询。我研究过ReplacingMergeTree物化视图，但它似乎仅支持单一主键。请问是否存在“多行”物化视图或类似方案，可保留每个Job ID对应的最新Event Set ID的所有行？

当前我通过如下SQL统计最新事件集合的Value总和：

SELECT SUM(value) FROM events WHERE event_set_id IN (SELECT MAX(event_set_id) FROM events GROUP BY job_id)

但在大表上查询较慢，需读取大量已被新Event Set替代的无关行。

1. 基于AggregateMergeTree的物化视图存储全量行 #

利用AggregateMergeTree结合聚合函数，将每个Job ID对应的最新Event Set的所有行以数组形式存储，既能保留行级字段，又能快速获取最新数据。

创建物化视图： #

CREATE MATERIALIZED VIEW mv_latest_event_sets
ENGINE = AggregateMergeTree()
ORDER BY job_id
AS
SELECT
    job_id,
    maxState(event_set_id) AS max_event_set_id,
    arrayAggState(tuple(event_set_id, event_type, value, scope)) AS event_rows
FROM events
GROUP BY job_id

查询指定Job的最新Event Set行： #

SELECT
    job_id,
    latest_event_set_id,
    event_type,
    value,
    scope
FROM (
    SELECT
        job_id,
        maxMerge(max_event_set_id) AS latest_event_set_id,
        arrayMerge(event_rows) AS all_rows
    FROM mv_latest_event_sets
    WHERE job_id = 1234
    GROUP BY job_id
)
ARRAY JOIN all_rows AS (event_set_id, event_type, value, scope)
WHERE event_set_id = latest_event_set_id

该方案可以直接基于Scope等行级字段进行后续过滤或计算。

2. 维护最新Event Set映射表+原表索引优化 #

单独维护一张小表记录每个Job的最新Event Set ID，查询时关联原表，利用索引快速定位目标行。

创建映射物化视图： #

CREATE MATERIALIZED VIEW mv_job_latest_set
ENGINE = ReplacingMergeTree()
ORDER BY job_id
AS
SELECT
    job_id,
    max(event_set_id) AS latest_event_set_id
FROM events
GROUP BY job_id

优化原表索引： #

ALTER TABLE events ADD INDEX job_event_set_idx (job_id, event_set_id) TYPE minmax GRANULARITY 8192;

快速查询统计： #

SELECT SUM(e.value)
FROM events e
JOIN mv_job_latest_set jls ON e.job_id = jls.job_id AND e.event_set_id = jls.latest_event_set_id
WHERE e.job_id = 1234

3. 使用VersionedCollapsingMergeTree过滤旧版本集合 #

如果Event Set ID是递增的（越大越新），可以用VersionedCollapsingMergeTree标记旧版本集合为“删除”状态，合并后仅保留最新版本的行。

创建物化视图： #

CREATE MATERIALIZED VIEW mv_latest_events
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (job_id, event_set_id)
AS
SELECT
    job_id,
    event_set_id,
    event_type,
    value,
    scope,
    1 AS sign,
    event_set_id AS version
FROM events
UNION ALL
SELECT
    job_id,
    event_set_id,
    event_type,
    value,
    scope,
    -1 AS sign,
    next_event_set_id AS version
FROM (
    SELECT
        job_id,
        event_set_id,
        lead(event_set_id) OVER (PARTITION BY job_id ORDER BY event_set_id) AS next_event_set_id,
        event_type,
        value,
        scope
    FROM events
)
WHERE next_event_set_id IS NOT NULL

查询数据： #

SELECT SUM(value) FROM mv_latest_events WHERE job_id = 1234

注意：该方案依赖MergeTree的合并操作，若需强实时性，可手动触发合并：OPTIMIZE TABLE mv_latest_events FINAL。

4. 原表索引优化（无需物化视图） #

如果不想创建物化视图，直接给原表添加联合索引优化原查询：

添加索引： #

ALTER TABLE events ADD INDEX job_event_set_idx (job_id, event_set_id) TYPE minmax GRANULARITY 8192;

优化查询语句： #

SELECT SUM(e.value)
FROM events e
JOIN (
    SELECT job_id, MAX(event_set_id) AS latest_set
    FROM events
    GROUP BY job_id
) j ON e.job_id = j.job_id AND e.event_set_id = j.latest_set
WHERE e.job_id = 1234

通过索引快速定位目标行，减少全表扫描的数据量。

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