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

Question

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

City   Temp  Date        As_of_Datetime
———    ———-  ———-        -——————-
Boston 32    2022/07/01  2022/06/28 13:23:00
Boston 31    2022/07/01  2022/06/29 13:23:00
Miami  74    2022/07/01  2022/06/28 13:23:00
Miami  75    2022/07/01  2022/06/29 13:23:00

什么雪花查询将基于最近的As_of_Datetime给出每个日期的数据的最新快照?

预期结果将是

City   Temp    Date
Boston 31      2022/07/01
Miami  75      2022/07/01

我尝试使用last_value函数

select City, Date, last_value(Temp) over (partition by City, Date order by As_of_Datetime) as Temp
from temperature_table
order by City, Date

，但会产生重复的行，其中最后一个值重复:

Boston   31    2022/07/01
Boston   31    2022/07/01
Miami    75    2022/07/01
Miami    75    2022/07/01

理想情况下，每个(城市，日期)组合应该只返回1行。

事先感谢您的考虑和答复。

Answer 1

可以通过使用qualit_number和ROW_NUMBER(按城市、日期分区，按按As_of_DateTime降序排序)来实现:

SELECT *
FROM tab
QUALIFY ROW_NUMBER() OVER(PARTITION BY City, Date ORDER BY As_of_DateTime DESC) = 1