ClickHouse中如何创建存储各col1最新10000条记录的表或物化视图？

方案一：预存储表+物化视图+定时清理 #

该方案会实时同步原表数据，定期自动清理每个col1下超过10000条的旧记录，让查询直接读取预计算好的最新数据。

1. 创建目标存储表 #

CREATE TABLE db.tbl1_top10000 (
    col1 Int64,
    col2 Int64,
    col3 Int64,
    timestamp DateTime
) ENGINE = MergeTree()
-- 按col1分区，方便按分组清理数据
PARTITION BY col1
-- 按col1+timestamp降序排序，确保每组最新记录排在最前面
ORDER BY (col1, timestamp DESC);

2. 创建物化视图同步数据 #

物化视图会自动将原表tbl1的新数据同步到目标表：

CREATE MATERIALIZED VIEW db.tbl1_top10000_mv TO db.tbl1_top10000 AS
SELECT col1, col2, col3, timestamp
FROM db.tbl1;

3. 定期执行清理任务 #

通过定时任务（如Linux crontab）周期性执行以下SQL，删除每个col1中除最新10000条外的旧记录：

ALTER TABLE db.tbl1_top10000 DELETE
WHERE (col1, timestamp) IN (
    SELECT col1, timestamp
    FROM db.tbl1_top10000
    ORDER BY col1, timestamp DESC
    -- 跳过每组前10000条最新记录，删除剩余旧数据
    LIMIT -1 OFFSET 10000 BY col1
);

注意：ClickHouse的DELETE是标记删除，需等待后台合并才能真正释放磁盘空间。若需立即生效，可在删除后执行OPTIMIZE TABLE db.tbl1_top10000 FINAL;，但该操作资源消耗较大，建议低峰期执行。

方案二：优化原表排序提升查询速度 #

如果不想新建表，可通过修改原表排序键，让原查询直接利用索引快速获取每组top10000数据：

1. 新建排序优化后的表 #

由于原表已有数十亿数据，直接修改排序键成本较高，建议新建表并迁移数据：

CREATE TABLE db.tbl1_new (
    col1 Int64,
    col2 Int64,
    col3 Int64,
    timestamp DateTime
) ENGINE = MergeTree()
-- 改为按col1+timestamp降序排序，匹配查询的排序需求
ORDER BY (col1, timestamp DESC);

2. 迁移原表数据 #

INSERT INTO db.tbl1_new SELECT * FROM db.tbl1;

3. 替换原表（可选） #

数据迁移完成后，可重命名表替换原表：

RENAME TABLE db.tbl1 TO db.tbl1_old, db.tbl1_new TO db.tbl1;

修改后，原查询会直接利用排序索引，无需扫描每组所有数据，查询速度会大幅提升。

方案三：聚合表存储每组topN记录 #

利用ClickHouse的聚合函数topK存储每组最新记录，适合仅需查询topN的场景：

1. 创建聚合表 #

CREATE TABLE db.tbl1_top10000_agg (
    col1 Int64,
    -- 用topK(10000)存储每组最新的(col2, col3, timestamp)元组
    top_records AggregateFunction(topK(10000), Tuple(Int64, Int64, DateTime))
) ENGINE = AggregateMergeTree()
ORDER BY col1;

2. 创建物化视图聚合数据 #

CREATE MATERIALIZED VIEW db.tbl1_top10000_agg_mv TO db.tbl1_top10000_agg AS
SELECT
    col1,
    -- 按timestamp降序插入，确保最新记录被保留
    topKState(10000)(tuple(col2, col3, timestamp)) AS top_records
FROM db.tbl1
GROUP BY col1;

3. 查询聚合数据 #

展开聚合结果获取具体字段：

SELECT
    col1,
    record.1 AS col2,
    record.2 AS col3,
    record.3 AS timestamp
FROM (
    SELECT
        col1,
        arrayJoin(topKMerge(top_records)) AS record
    FROM db.tbl1_top10000_agg
    WHERE col1 IN (1,2,3)
)
ORDER BY col1, timestamp DESC;

注意：topK函数默认按插入顺序保留最新记录，若需严格按timestamp排序，需确保数据插入时timestamp是递增的，或在聚合时结合排序逻辑。

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