如何高效聚合多版本物品文档的最新状态数据？

我的物品会随时间变更所属类别，每次变更都会生成一条记录当前状态的新文档。我希望基于物品的最新状态进行基础分析，比如统计各类别物品数量、数值总和等合并聚合操作，但现有方案在数据量达到数百万级时无法扩展。使用PPL语句可以实现需求，但性能跟不上大规模数据场景。

使用的PPL语句 #

search source=test-stats
| sort -timestamp
| dedup 1 id
| stats sum(value) as sum_value, COUNT(id) by category

测试文档 #

{"_id": "doc-1-1", "id": "doc-1", "category": "c1", "value": 1010.0, "timestamp": "2023-07-07T00:00:00.000Z"}
{"_id": "doc-1-2", "id": "doc-1", "category": "c1", "value": 1012.0, "timestamp": "2023-07-08T00:00:00.000Z"}
{"_id": "doc-1-3", "id": "doc-1", "category": "c2", "value": 1013.0, "timestamp": "2023-07-09T00:00:00.000Z"}
{"_id": "doc-2-1", "id": "doc-2", "category": "c2", "value": 1021.0, "timestamp": "2023-07-07T00:00:00.000Z"}
{"_id": "doc-2-2", "id": "doc-2", "category": "c3", "value": 1022.0, "timestamp": "2023-07-10T00:00:00.000Z"}
{"_id": "doc-2-3", "id": "doc-2", "category": "c4", "value": 1023.0, "timestamp": "2023-07-11T00:00:00.000Z"}
{"_id": "doc-3-1", "id": "doc-3", "category": "c2", "value": 1031.0, "timestamp": "2023-07-07T00:00:00.000Z"}
{"_id": "doc-3-2", "id": "doc-3", "category": "c2", "value": 1032.0, "timestamp": "2023-07-10T00:00:00.000Z"}
{"_id": "doc-3-3", "id": "doc-3", "category": "c2", "value": 1033.0, "timestamp": "2023-07-11T00:00:00.000Z"}

预期结果 #

sum_value  COUNT(id)  category
1023       1          c4
2046       2          c2

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