获取双时态数据基于As_of_Datetime最新快照的SQL查询方法

给定采用双时态建模的数据表，表中包含两类日期字段：

• 数据对应的业务生效日期：Date字段
• 事实被获知录入的系统时间：As_of_Datetime字段

源表示例数据如下：

City    Temperature  Date         As_of_Datetime
----    -----------  ----         --------------
Boston  32           2022/07/01   2022/06/28 13:23:00
Boston  31           2022/07/01   2022/06/29 12:00:00
Miami   81           2022/07/01   2022/06/28 13:23:00
Miami   85           2022/07/01   2022/06/29 12:00:00

需求为基于As_of_Datetime字段获取对应数据日期的最新快照，预期输出结果：

City    Temperature  Date         
----    -----------  ----         
Boston  31           2022/07/01   
Miami   85           2022/07/01   

推荐实现方案（窗口函数法） #

目前主流数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等）均支持窗口函数，写法简洁执行效率高，核心逻辑是按业务维度分组后按系统录入时间倒序排序，取每组第一条记录：

SELECT City, Temperature, Date
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY City, Date
            ORDER BY As_of_Datetime DESC
        ) AS row_rank
    FROM 你的实际表名
) temp
WHERE row_rank = 1;

注意：如果同一城市、同一业务日期下存在多条As_of_Datetime完全一致的记录，ROW_NUMBER()会随机返回其中一条；如果需要保留同时间的所有记录，可将ROW_NUMBER()替换为RANK()。

兼容低版本数据库方案（关联查询法） #

如果使用不支持窗口函数的老版本数据库（如MySQL 5.x），可以先聚合查询出每个业务分组下最新的系统录入时间，再关联原表匹配对应记录：

SELECT t1.City, t1.Temperature, t1.Date
FROM 你的实际表名 t1
JOIN (
    SELECT
        City,
        Date,
        MAX(As_of_Datetime) AS latest_entry_time
    FROM 你的实际表名
    GROUP BY City, Date
) t2
ON t1.City = t2.City
AND t1.Date = t2.Date
AND t1.As_of_Datetime = t2.latest_entry_time;

内容的提问来源于stack exchange，提问作者Ramón J Romero y Vigil