如何查询timestamp小于指定值的实体最新修订版本

假设你的实体修订记录表包含entity_id（实体唯一标识）、revision_data（修订内容字段）、timestamp（时间戳字段），要实现传入最大timestamp（示例为180）后，返回每个实体timestamp小于该值的最新修订版本，可以用以下两种主流SQL方案：

方案一：窗口函数（推荐，逻辑直观） #

利用ROW_NUMBER()窗口函数按实体分组并按时间戳倒序排序，筛选每组的第一条记录：

WITH ranked_revisions AS (
    SELECT 
        entity_id,
        revision_data,
        timestamp,
        -- 按实体分组，组内按时间戳降序编号，最新版本编号为1
        ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY timestamp DESC) AS revision_rank
    FROM entity_revisions
    WHERE timestamp < 180
)
SELECT entity_id, revision_data, timestamp
FROM ranked_revisions
WHERE revision_rank = 1;

如果同一实体在同一时间戳存在多个修订版本，需要保留所有同时间版本的话，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()。

方案二：子查询关联 #

先查询每个实体符合时间条件的最大时间戳，再关联原表获取对应记录：

SELECT er.entity_id, er.revision_data, er.timestamp
FROM entity_revisions er
INNER JOIN (
    -- 先找出每个实体满足timestamp < 180的最大时间戳
    SELECT entity_id, MAX(timestamp) AS latest_valid_ts
    FROM entity_revisions
    WHERE timestamp < 180
    GROUP BY entity_id
) latest_rev ON er.entity_id = latest_rev.entity_id AND er.timestamp = latest_rev.latest_valid_ts;

这个方案在部分数据库引擎上的性能可能优于窗口函数，适合数据量较大的场景。

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