Cassandra：如何查询指定folder_id下各file_id的最新version？

你的需求完全可行，当前的数据设计也能支持，只是之前的查询语句用错了，导致没得到预期结果。

为什么原语句不符合预期 #

你执行的SELECT folder_id,file_id,MAX(version) FROM example_table WHERE folder_id=1;没有指定GROUP BY file_id，Cassandra会把整个folder_id=1的分区当作一个聚合组，返回的是整个分区里最大的version值，而不是按每个file_id分组后的最大值，所以和预期不符。

正确的查询方法 #

方法1：利用DISTINCT ON特性 #

因为你的表已经将file_id设为聚类键，且version按降序排列，每个file_id下的最新版本记录就是该分组的第一条数据。可以用DISTINCT ON按file_id分组，取每组的第一条：

SELECT DISTINCT ON (file_id) folder_id, file_id, version, -- 这里加上你需要的其他业务字段
FROM example_table
WHERE folder_id = 1
ORDER BY file_id, version DESC;

DISTINCT ON (file_id)会强制按file_id分组，结合ORDER BY的降序规则，就能拿到每个file_id对应的最新version记录。

方法2：优化数据模型（推荐） #

如果这个查询是高频操作，符合Cassandra"为查询设计表"的思路，可以新增一张表专门存储每个file_id的最新版本记录：

CREATE TABLE example_table_latest (
    folder_id <你的字段类型>, -- 和原表保持一致，比如UUID或int
    file_id <你的字段类型>,
    version TIMESTAMP,
    -- 原表的其他业务字段
    PRIMARY KEY (folder_id, file_id)
);

每次往example_table写入新版本记录时，同时执行UPSERT操作更新这张表：

INSERT INTO example_table_latest (folder_id, file_id, version, <其他字段>)
VALUES (1, 'xxx', '2024-05-20 10:00:00', <对应字段值>)
USING TIMESTAMP <对应时间戳>;

之后查询直接从这张表取数据，效率更高：

SELECT * FROM example_table_latest WHERE folder_id = 1;

总结 #

当前的数据设计可以满足需求，只是需要用正确的查询语句；如果追求查询性能，优化数据模型是更贴合Cassandra设计理念的方案。

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