查询双时态数据最新快照的SQL查询

Question

给定一个双时间建模的数据表，其中有两个日期:(i)数据适用的日期，(ii)事实已知的日期。

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

哪个SQL查询将给出基于As_of_Datetime的数据日期的最新快照?例如

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

Answer 1

select
t1.*
from
temperature_table t1,
(select max(As_of_Datetime) as max_as_of, “City”, “Date” from temperature_table group by “City”, “Date”) t_temp
where
t1.”City” = t_temp.”City” and t1.”Date” = t_temp.”Date” and t1.”As_of_Datetime” = t_temp.”max_as_of”
order by
t1.”City”, t1.”Date”