PostgreSQL中如何关联数据表与时间序列表的最新值

要实现关联data和timeseries表，查询每个name对应最新时间戳的value列数据，以下是两种常用的PostgreSQL实现方案：

方案一：使用窗口函数ROW_NUMBER() #

通用性强，逻辑清晰，适配多数SQL数据库：

SELECT d.name, t.value
FROM data d
JOIN (
    SELECT 
        name,
        value,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY time DESC) AS rn
    FROM timeseries
) t ON d.name = t.name
WHERE t.rn = 1;

通过ROW_NUMBER()按name分组，每组内按time倒序排序，筛选出每组第一条（即最新时间的数据），再与data表关联，确保仅返回data表中存在的name对应数据。

方案二：使用PostgreSQL特有的DISTINCT ON #

语法更简洁，在有合适索引的情况下性能更优：

SELECT DISTINCT ON (d.name) d.name, t.value
FROM data d
JOIN timeseries t ON d.name = t.name
ORDER BY d.name, t.time DESC;

DISTINCT ON (d.name)保证每个name仅返回一行，结合ORDER BY先按name分组、再按time倒序排序，直接取每组最新时间的记录。

性能优化建议 #

如果数据量较大，建议创建复合索引加速查询：

CREATE INDEX idx_timeseries_name_time ON timeseries(name, time DESC);

该索引可帮助两种方案快速定位到每组的最新数据，大幅提升查询效率。

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