怎么从明细表得到最新状态表？

Viewed 42
2.1

我们有一张doris明细表如下

id user value commit_time
1 1 100 "2024-05-01 14:00:01"
2 2 200 "2024-05-01 15:00:01"
3 3 300 "2024-05-01 15:00:01"
4 1 150 "2024-05-01 15:30:01"

需求是根据user的最新commit_time得到出下列结果

2 2 200 "2024-05-01 15:00:01"
3 3 300 "2024-05-01 15:00:01"
4 1 150 "2024-05-01 15:30:01"

使用窗口函数是可以的成功得到

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user ORDER BY commit_time DESC) AS row_num
    FROM table
) AS subquery
WHERE row_num = 1

目前问题是希望能透过物化视图加速这个查询，不用每次query都重新计算一次，但物化视图目前好像还不支援使用窗口函数，想请教一下大家有没有什么好解法？

Roger19
1 Answers

窗口函数通常涉及到全量数据的计算，不易于物化视图的维护和更新。
可以考虑以下几种解决方案：

1、创建一个不包含窗口函数的物化视图：例如可以创建一个基于最新 commit_time 的物化视图，该视图在数据导入时就已经按照 user 和 commit_time 排序，并只保留每个 user 的最新记录。这样，物化视图本身就已经包含了每个用户的最新数据，无需窗口函数即可查询。

CREATE MATERIALIZED VIEW mv_latest_user_commit AS
SELECT user, MAX(commit_time) AS latest_commit_time
FROM table
GROUP BY user;

然后通过连接原始表和物化视图来获取结果：

SELECT t.id, t.user, t.value, t.commit_time
FROM table t
JOIN mv_latest_user_commit mv ON t.user = mv.user AND t.commit_time = mv.latest_commit_time;

这种方法避免了在查询时使用窗口函数，但需要在数据变更时更新物化视图。

2、使用 Doris 的索引功能：如果查询场景比较固定，可以尝试为 commit_time 和 user 创建倒排索引，这样虽然不能完全避免计算，但可以加速查询。

3、预计算：在数据导入阶段，通过 ETL 过程预先计算每个用户的最新记录，并将其存储在一个单独的表中，查询时直接从这张表中获取数据。

4、查询优化：如果上述方法都不可行，可以考虑在查询时使用一些优化技巧，比如增加 commit_time 的索引，或者调整查询策略，减少全表扫描。

SELECT t1.*
FROM table t1
LEFT JOIN table t2 ON t1.user = t2.user AND t1.commit_time < t2.commit_time
WHERE t2.id IS NULL;

这种方法利用了左连接和条件过滤来找到每个用户的最新记录。

张彬华7082