SQL查询中时间旅行timestamp的正确插入位置咨询

在Snowflake中，时间旅行的AT(timestamp => '2022-08-25 12:00:00'::timestamp)语法需要紧跟在每个目标表的引用之后，包括主查询里的所有关联表，以及子查询中的引用表。以下是修改后的完整SQL语句：

SELECT Table1.MTH_COD, Table1.ID, Table1.ETV_FLG,Table2.EXT_FLG, Table2.GEN_COD, Table3.AMOUNT_VALUE, Table3.CUR_IDT_COD as "Table3.CUR_IDT_COD"
from "Database"."Schema"."Table" as Table1 AT(timestamp => '2022-08-25 12:00:00'::timestamp)
    JOIN "Database"."Schema"."Table" as Table2 AT(timestamp => '2022-08-25 12:00:00'::timestamp)
        ON Table1.MTH_COD=Table2.MTH_COD
        AND Table1.ID=Table2.ID
 LEFT JOIN "Database"."Schema"."Table" as Table3 AT(timestamp => '2022-08-25 12:00:00'::timestamp)
ON Table1.MTH_COD=Table3.MTH_COD
AND Table1.GRADE_COD=Table3.GRADE_COD
        WHERE Table1.CLASS_COD IS DISTINCT FROM (select CLASS_COD_ID from "Database"."Schema"."ReferentialTable" AT(timestamp => '2022-08-25 12:00:00'::timestamp)
                                     where CLASS_COD = 'DE')
        AND EXT_FLG = 0

关键放置位置说明： #

• 主查询FROM子句：在表名与别名as Table1之间追加AT(...)
• JOIN关联表：无论是JOIN还是LEFT JOIN，都在对应表的别名前添加时间旅行语法
• 子查询中的引用表：子查询里的ReferentialTable同样需要添加，保证子查询也读取指定时间点的历史数据

如果需要不同表读取不同时间点的历史数据，只需单独修改对应表后的AT()参数即可。

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